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 99B255658A for ; Mon, 21 Mar 2005 15:27:32 +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 26893-04 for ; Mon, 21 Mar 2005 15:27:27 +0000 (GMT) Received: from freewebz.com (herndon-13.freewebs.com [38.144.96.13]) by svr1.postgresql.org (Postfix) with ESMTP id 0FB0F56553 for ; Mon, 21 Mar 2005 15:27:26 +0000 (GMT) Received: from [127.0.0.1] (members [127.0.0.1]) by freewebz.com (8.11.6/8.11.6) with ESMTP id j2LFRL225370; Mon, 21 Mar 2005 10:27:23 -0500 Message-ID: <423EE7D5.7090506@freewebz.com> Date: Mon, 21 Mar 2005 10:27:17 -0500 From: Zeki User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Brian O'Reilly" Cc: pgsql-bugs@postgresql.org Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1 References: <20050318232102.08FA0F0E5D@svr2.postgresql.org> In-Reply-To: <20050318232102.08FA0F0E5D@svr2.postgresql.org> 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.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/286 X-Sequence-Number: 11406 Have you tried an analyze after 1,000 or so inserts? Also, you should be able to disable sequence scans for the duration of the connection using SET enable_seqscan=false. -Zeki Brian O'Reilly wrote: >The following bug has been logged online: > >Bug reference: 1552 >Logged by: Brian O'Reilly >Email address: fade@deepsky.com >PostgreSQL version: 8.0.1 >Operating system: Linux 2.6.11 >Description: massive performance hit between 7.4 and 8.0.1 >Details: > >When doing a lot of inserts to an empty table with a foreign key to another >table, there is an incredible performance degredation issue on 8.0.1. I have >a program that is inserting rows in an iterative loop, and in this form it >inserts about 110,000 rows. On postgresql 7.4 on a debian machine it takes a >shade over 2 minutes to complete. On an amd64 box running gentoo, it takes >over an hour and fourty minutes to complete. The query plan on the debian >host that completes quickly follows: > > "Fast" machine, Debian, PSQL 7.4: > >---------------------------------------------------------------------------- >---------------------------------------------------- > Index Scan using requirements_pkey on requirements (cost=0.00..4.82 rows=2 >width=0) (actual time=0.013..0.013 rows=0 loops=1) > Index Cond: (reqid = 10::bigint) > Total runtime: 0.134 ms >(3 rows) > >and the query plan on the 'slow' machine: > > > QUERY PLAN >---------------------------------------------------------------------------- >-------------------------- > Seq Scan on requirements (cost=0.00..0.00 rows=1 width=0) (actual >time=0.002..0.002 rows=0 loops=1) > Filter: (reqid = 10::bigint) > Total runtime: 0.040 ms >(3 rows) > >The script I am using to show this behaviour follows: > >CREATE TABLE packages > (name text PRIMARY KEY); >CREATE TABLE binary_packages > (name text REFERENCES packages, > version text, > PRIMARY KEY(name, version)); >CREATE TABLE requirements > (reqid bigint PRIMARY KEY, > name text, > version text, > FOREIGN KEY (name, version) REFERENCES >binary_packages); >CREATE TABLE constraints > (constid bigint PRIMARY KEY, > reqid bigint REFERENCES requirements, > type text, > name text REFERENCES packages, > version text DEFAULT '', > relation character(2)); > >explain analyze select 1 from only requirements where reqid='10'; > >the query optimiser seems to be setting a default strategy of doing >sequential scans on an empty table, which is a fast strategy when the table >is empty and not particularly full, but obviously on a large table the >performance is O(N^2). This is clearly a bug. Please let me know if I can >provide any more information. > >Brian O'Reilly >System Architect., >DeepSky Media Resources > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > >