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

On 2005-03-23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>> Changing the order so that the referenced table is fully populated, or at
>> least populated with more than a handful of pages of rows, before doing
>> _any_ insert on a referencing table in the same session will avoid the
>> misplan of the FK trigger queries, because when the first insert happens
>> on a referencing table, there will be no reason for the planner to prefer
>> a sequential scan. So this result is not surprising at all.
>
> 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.

Think "1 row", not "0 rows".

It is not reasonable to assume that _all_ cases of data loading (other than
perhaps the very largest) will be done by loading entire tables at a time,
especially when importing from external sources where the data is
differently structured.

> 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 won't; the problem is with 1 page, not 0.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services