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

Re: BUG #1552: massive performance hit between 7.4 and 8.0.1

From: Zeki <zekispam(at)freewebz(dot)com>
To: "Brian O'Reilly" <fade(at)deepsky(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Date: 2005-03-21 15:27:17
Message-ID: 423EE7D5.7090506@freewebz.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-performance
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(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
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>  
>


In response to

pgsql-performance by date

Next:From: Hannu KrosingDate: 2005-03-21 16:07:25
Subject: Re: What needs to be done for real Partitioning?
Previous:From: Jan WieckDate: 2005-03-21 14:59:22
Subject: Re: column name is "LIMIT"

pgsql-bugs by date

Next:From: Guido BarosioDate: 2005-03-21 17:22:02
Subject: BUG #1554: wrong character on the sjis_to_utf8.map file
Previous:From: Neil ConwayDate: 2005-03-21 04:48:18
Subject: Re: BUG #1553: Function arguments and table fields

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