On Wed, Jun 17, 2015 at 9:32 AM, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > 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.
Yes. The copyObject() call introduced by this commit seems to have complexity O(T^2*C) where T is the number of child tables and C is the number of columns per child. That's because the List that is being copied is a list of AppendRelInfo nodes, each of which has a translated_vars member that is a list of every Var in one table, and we copy that list once per child. It appears that in a lot of cases this work is unnecessary. The second (long) for loop in inheritance_planner copies root->rowMarks and root->append_rel_list so as to be able to apply ChangeVarNodes() to the result, but we only actually do that if the rte is of type RTE_SUBQUERY or if it has security quals. In the common case where we reach inheritance_planner not because of UNION ALL but just because the table has a bunch of inheritance children (none of which have RLS policies applied), we copy everything and then modify none of it, using up startling large amounts of memory in ways that pre-9.2 versions did not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers