Re: BUG #14263: Query planner is slow to plan UPDATE on a table with many partitions

Greetings Joel,

* Joel Sanger (joelsanger(at)gmail(dot)com) wrote:
> Just to keep this bug current, I am also experiencing the same issue. I'm
> using PostgreSQL 9.6.1 on Debian 8.6.
>
> As an example, i have a table with a *very* large number of partitions. A
> simple select clause has a planning time of 600ms where as the same clause
> as an update results in a 8300ms planning time.
>
> Unfortunately the partitioning scheme we have is ideal for our regular
> workflow, so using less partitions isn't practical.
>
> If there's any update, plans to fix (or not) that would be appreciated.

The short answer is "it's being actively worked on." The slightly
longer answer is that the next version of PG (PG10) is going to have
declarative partitioning, such that you can actually say something like
"ALTER TABLE ... ATTACH PARTITION FOR VALUES FROM x TO y".

That is just the start and we don't (yet) have code committed to improve
planning time when using declarative partitioning, but it's being worked
on and it might make it in time for PG10, or it might not.

If you're really curious and interested in all of this, the right place
to discuss it is on the pgsql-hackers list. If you don't want to follow
all of that traffic then I would suggest you watch
https://planet.postgresql.org as there will surely be blog posts about
all of this as patches are committed to improve things in this area.

Thanks!

Stephen