From: Simon Riggs <simon@2ndquadrant.com>
To: Brian O'Reilly <fade@deepsky.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Date: Wed, 23 Mar 2005 08:40:30 +0000
Message-ID: <1111567230.11750.596.camel@localhost.localdomain> (raw)
In-Reply-To: <20050318232102.08FA0F0E5D@svr2.postgresql.org>
References: <20050318232102.08FA0F0E5D@svr2.postgresql.org>
On Fri, 2005-03-18 at 23:21 +0000, 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:
>
This may be a bug, thanks for filing it.
However, we can't tell at the moment from what you've said.
The EXPLAINs you've enclosed are for SELECTs, yet your bug report
describes INSERTs as being the things that are slow.
[You may find better performance from using COPY]
Also, your tests have compared two systems, so it might be that the
hardware or configuration of one system is different from the other.
If you could repeat the test on one single system, then this would
assist in the diagnosis of this bug report. Also, if you could describe
the workload that is giving you a problem more exactly, that would help.
Specifically, can you confirm that you have run ANALYZE on the tables,
and also give us some idea of numbers of rows in each table at the time
you first run your programs.
> 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.
There is clearly a problem, but it is not yet clearly a bug. If it is a
bug, we're interested in solving it as much as you.
> Please let me know if I can
> provide any more information.
Yes, all of the above, plus more.
Best Regards, Simon Riggs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, fade@deepsky.com, pgsql-bugs@postgresql.org
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
In-Reply-To: <1111567230.11750.596.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox