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
