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

Keith Browne <tuxedo(at)deepsky(dot)com> writes:
> Tom Lane wrote:
>> I'm still looking for an example that demonstrates why this is a common
>> problem that we need to worry about.

> 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.

Hmm ...

>> 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,

No, it wouldn't, because by the time you do the first FK trigger you'd
have one row/one page in the referenced table, so it'd still look like a
seqscan situation to the planner. The only way we could make that work
is to effectively disable seqscans entirely, by *always* pretending the
table size is large enough to trigger an indexscan, even when the
planner can plainly see that it's not. This is not an acceptable answer
IMHO.

[ thinks for a bit... ] The reason 7.4 and before worked reasonably
for you is that they assumed the 10/1000 statistics for any
never-yet-vacuumed table, whether it is empty or not. (This worked fine
for your problem but shot a lot of other people in the foot, because
that's what the estimate would stay at even if the table grew vastly
larger, so long as it wasn't vacuuumed.) Maybe we could
put in a hack that detects whether a table has yet been vacuumed, and
sets 10/1000 as the minimum stats --- not fixed values, but minimum
values that can be overridden when the table is actually larger ---
until it has been vacuumed. I'm not sure if this is workable. It looks
to me like we'd have to approximate the "never vacuumed" condition by
checking whether pg_class.reltuples and relpages are both zero, which
is the initial condition all right but would also arise after a vacuum
finds nothing in the table. So basically the planner would never
optimize the entirely-empty-table condition properly, even after vacuum.
Maybe this is the least bad alternative for 8.0.*.

regards, tom lane