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 7D84753578 for ; Wed, 23 Mar 2005 20:55:12 +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 22728-06 for ; Wed, 23 Mar 2005 20:55:09 +0000 (GMT) Received: from deepsky.com (outrider.deepsky.com [66.207.216.43]) by svr1.postgresql.org (Postfix) with SMTP id 40D6D533AA for ; Wed, 23 Mar 2005 20:55:08 +0000 (GMT) Received: (qmail 24872 invoked from network); 23 Mar 2005 20:55:09 -0000 Received: from unknown (HELO ?127.0.0.1?) (66.207.216.43) by outrider.deepsky.com with SMTP; 23 Mar 2005 20:55:09 -0000 Message-ID: <4241D7AB.4030607@deepsky.com> Date: Wed, 23 Mar 2005 15:55:07 -0500 From: Keith Browne User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane , pgsql-bugs@postgresql.org Cc: andrew@supernews.com, Brian O'Reilly Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1 References: <20050318232102.08FA0F0E5D@svr2.postgresql.org> <1111567230.11750.596.camel@localhost.localdomain> <4241C1DF.2020000@deepsky.com> <13841.1111608763@sss.pgh.pa.us> In-Reply-To: <13841.1111608763@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/211 X-Sequence-Number: 11331 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