Re: performance drop on 8.2.4, reverting to 8.1.4 - Mailing list pgsql-performance

"Steven Flatt" <steven.flatt@gmail.com> writes:
> One instance of our problem goes like this, and I have included a
> self-contained example with which you can reproduce the problem.

This is fairly interesting, because if you run the query by hand after
the function finishes, it's pretty fast.  What I think is happening is
that the plpgsql function caches a plan for the catalog query that is
predicated on pg_constraint and pg_inherits being small, and after
you've inserted a few thousand rows in them, that's not true anymore.

In CVS 8.2 (and HEAD), the core of the query seems to be
planned like this initially:

               ->  Hash Join  (cost=1.24..8.70 rows=1 width=76)
                     Hash Cond: (c.conrelid = i.inhparent)
                     ->  Seq Scan on pg_constraint c  (cost=0.00..7.35 rows=27 width=76)
                           Filter: (confrelid <> 0::oid)
                     ->  Hash  (cost=1.23..1.23 rows=1 width=8)
                           ->  Seq Scan on pg_inherits i  (cost=0.00..1.23 rows=1 width=8)
                                 Filter: (inhrelid = 42154::oid)

With a thousand or so rows inserted in each catalog, it likes
this plan better:

               ->  Nested Loop  (cost=0.00..16.55 rows=1 width=76)
                     ->  Index Scan using pg_inherits_relid_seqno_index on pg_inherits i  (cost=0.00..8.27 rows=1
width=8)
                           Index Cond: (inhrelid = 42154::oid)
                     ->  Index Scan using pg_constraint_conrelid_index on pg_constraint c  (cost=0.00..8.27 rows=1
width=76)
                           Index Cond: (c.conrelid = i.inhparent)
                           Filter: (c.confrelid <> 0::oid)

and indeed that plan is a lot better as the catalogs grow.
But the plpgsql function cached the other plan at start.

I'm not entirely sure why 8.1 doesn't fall into the same trap ---
perhaps it's because it's unable to rearrange outer joins.
It's certainly not being any smarter than 8.2.

Anyway, it seems that you could either try to get some pg_constraint and
pg_inherits rows created before you start this function, or you could
change it to use an EXECUTE to force replanning of the inner query.
Or just start a new session after the first few hundred table creations.

I was hoping that the auto plan invalidation code in CVS HEAD would get
it out of this problem, but it seems not to for the problem-as-given.
The trouble is that it won't change plans until autovacuum analyzes the
tables, and that won't happen until the transaction commits and sends
off its I-inserted-lotsa-rows report to the stats collector.  So any
given large transaction is stuck with the plans it first forms.  There's
probably nothing we can do about that in time for 8.3, but it's
something to think about for future releases ...

            regards, tom lane