Re: Inheritance planner CPU and memory usage change since 9.3.2

On Thu, Jun 18, 2015 at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ... although I see that range_table_mutator doesn't bother to copy/change
> the column alias substructure.  (Wonder if that gives rise to any
> observable EXPLAIN bugs...)  But it still seems like the append_rel_list
> shouldn't be all that much bulkier than all the other crap that gets
> generated inside this loop.  We're not doing anything at all to reclaim
> space consumed inside subquery_planner, and you'd think that would be
> a lot.
>
> By the by, the tablesample additions to range_table_mutator are obviously
> broken.

Whee.

Meanwhile, here is an updated patch.  The attached script (a modified
version of something Thomas Munro sent me privately) contains a bunch
of test queries.  With the original patch I sent earlier, here are the
timings I got:

Q1 Time: 16215.887 ms
Q2 Time: 18674.139 ms
Q3 Time: 1029.093 ms
Q4 Time: 86497.781 ms
Q5 Time: 1143.851 ms

This version is about the same for the last three, but the first two
get much faster:

Q1 Time: 2951.231 ms
Q2 Time: 1251.809 ms
Q3 Time: 1049.235 ms
Q4 Time: 88477.803 ms
Q5 Time: 1172.965 ms

The speedup comes from the following trick: the first time we hit a
query that might requite a ChangeVarNodes() on the append_rel_list, we
compute a bitmapset of varnos that appear in that list.  Then, every
time we're thinking about doing a ChangeVarNodes from rti to new_rti,
we check whether rti appears in the Bitmapset.  If not, we can skip
ChangeVarNodes().  That seems to reduce the amount of object-copying
and object-walking attributable to this loop to something negligible
in all of these test cases.

The extraordinarily planning time for query 4 is caused by a
completely different problem: SearchCatCache eats up huge amounts of
CPU; its callers are get_attavgwidth and get_typlen.  It's not clear
to me why doubling the number of relations causes such an enormous
explosion in calls to those functions; I would expect the number of
calls to double, but presumably the actual increase is much more.
That's a separate problem, though, unconnected to
c03ad5602f529787968fa3201b35c119bbc6d782 and not necessarily a
regression.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company