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

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