Inheritance planner CPU and memory usage change since 9.3.2 - Mailing list pgsql-hackers

Hi

We saw a rather extreme performance problem in a cluster upgraded from
9.1 to 9.3.  It uses a largish number of child tables (partitions) and
many columns.  Planning a simple UPDATE via the base table started
using a very large amount of memory and CPU time.

My colleague Rushabh Lathia tracked the performance change down to
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c03ad5602f529787968fa3201b35c119bbc6d782
.

The call to copyObject in the loop introduced here seems to be
problematic (copying append_rel_list for every element in
append_rel_list unconditionally), though we're still trying to figure
it out.  Attached is a simple repro script, with variables to tweak.

Quite a few others have posted about this sort of thing and been
politely reminded of the 100 table caveat [1][2] which is fair enough,
but the situations seems to have got dramatically worse for some users
after an upgrade.

[1] http://www.postgresql.org/message-id/8c9acaa.1f453.14c0da0402f.Coremail.chjischj@163.com
[2]
http://www.postgresql.org/message-id/flat/20141107185824.2513.53433@wrigleys.postgresql.org#20141107185824.2513.53433@wrigleys.postgresql.org

--
Thomas Munro
http://www.enterprisedb.com