Keith Browne <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]