Re: BUG #1552: massive performance hit between 7.4 and 8.0.1 - Mailing list pgsql-bugs
From | Zeki |
---|---|
Subject | Re: BUG #1552: massive performance hit between 7.4 and 8.0.1 |
Date | |
Msg-id | 423EE7D5.7090506@freewebz.com Whole thread Raw |
In response to | BUG #1552: massive performance hit between 7.4 and 8.0.1 ("Brian O'Reilly" <fade@deepsky.com>) |
List | pgsql-bugs |
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@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 > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > >
pgsql-bugs by date: