X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3CF9053598 for ; Wed, 23 Mar 2005 20:12:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10592-02 for ; Wed, 23 Mar 2005 20:12:43 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5041D535A6 for ; Wed, 23 Mar 2005 20:12:42 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2NKChl9013842; Wed, 23 Mar 2005 15:12:43 -0500 (EST) To: andrew@supernews.com Cc: pgsql-bugs@postgresql.org, Keith Browne , "Brian O'Reilly" Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1 In-reply-to: References: <20050318232102.08FA0F0E5D@svr2.postgresql.org> <1111567230.11750.596.camel@localhost.localdomain> <4241C1DF.2020000@deepsky.com> Comments: In-reply-to Andrew - Supernews message dated "Wed, 23 Mar 2005 19:46:50 +0000" Date: Wed, 23 Mar 2005 15:12:43 -0500 Message-ID: <13841.1111608763@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/208 X-Sequence-Number: 11328 Andrew - Supernews 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. In the long term it would be good to replan the FK plans when the referenced tables have grown so much that the plan ought to change. Once we have the plan invalidation machinery that Neil is working on, it might be fairly practical to do that; but no such thing is going to appear in existing release branches of course. 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. regards, tom lane