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 CAF6856962 for ; Fri, 18 Mar 2005 23:23:53 +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 91226-03 for ; Fri, 18 Mar 2005 23:23:51 +0000 (GMT) Received: from svr2.postgresql.org (unknown [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id 03B465687E for ; Fri, 18 Mar 2005 23:23:51 +0000 (GMT) Received: by svr2.postgresql.org (Postfix, from userid 80) id 08FA0F0E5D; Fri, 18 Mar 2005 23:21:02 +0000 (GMT) To: pgsql-bugs@postgresql.org Subject: BUG #1552: massive performance hit between 7.4 and 8.0.1 From: "Brian O'Reilly" Message-Id: <20050318232102.08FA0F0E5D@svr2.postgresql.org> Date: Fri, 18 Mar 2005 23:21:02 +0000 (GMT) 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/183 X-Sequence-Number: 11303 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