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: 20050318232102.08FA0F0E5D@svr2.postgresql.org (view raw or flat)
Thread:
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
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

Responses

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-2014 The PostgreSQL Global Development Group