Skip site navigation (1) Skip section navigation (2)

BUG #1552: massive performance hit between 7.4 and 8.0.1

From: "Brian O'Reilly" <fade(at)deepsky(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1552: massive performance hit between 7.4 and 8.0.1
Date: 2005-03-18 23:21:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-performance
The following bug has been logged online:

Bug reference:      1552
Logged by:          Brian O'Reilly
Email address:      fade(at)deepsky(dot)com
PostgreSQL version: 8.0.1
Operating system:   Linux 2.6.11
Description:        massive performance hit between 7.4 and 8.0.1

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:

                          (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
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


pgsql-performance by date

Next:From: Mike RylanderDate: 2005-03-19 13:16:42
Subject: Re: queries on huge tables
Previous:From: Manfred KoizarDate: 2005-03-18 10:42:23
Subject: Re: multi-column index

pgsql-bugs by date

Next:From: Andrew - SupernewsDate: 2005-03-18 23:48:34
Subject: Re: BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES
Previous:From: Tom LaneDate: 2005-03-18 17:41:56
Subject: Re: BUG #1549: initdb doesn't work

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group