Re: BUG #1552: massive performance hit between 7.4 and 8.0.1

Tom Lane wrote:

> I'm still looking for an example that demonstrates why this is a common
> problem that we need to worry about.  ISTM that if an FK reference is
> hit when there are still zero entries in the referenced table, that
> insertion will fail anyway, and so people wouldn't try to load data in
> such an order.

Tom,

We're filling pairs of tables with rows having nearly a one-to-one
mapping; very rarely, the second table will have multiple rows
corresponding to one row in the first table.  When we insert the first
row in the second table, therefore, we've just put the corresponding row
into the first table, so the foreign key constraint is satisfied.

I can't say how common this sort of thing will be.  It appears to me
that BUG #1541 is similar to what we're seeing, and a search of the
mailing lists also turns up this message:

http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php

which also describes symptoms similar to what I'm seeing.

> We could band-aid this in 8.0 as previously suggested (have the planner
> assume > 0 pages when it sees actually 0 pages) but without seeing a
> concrete example I can't tell if that will fix the complaint or not.

It sounds like this could work for us, if it would disable sequential
searches into a table which grows from 0 to >60,000 rows in one session.
  Is breaking and re-establishing the database session the best
workaround, or is there a better way to provide a hint to the planner?

Regards,

Keith Browne
tuxedo@deepsky.com