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

From: Simon Riggs <simon(at)2ndquadrant(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-23 08:40:30
Message-ID: 1111567230.11750.596.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-performance

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

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message r.ranft 2005-03-23 13:27:21 BUG #1559: Typo in translation of postmaster
Previous Message Bruce Momjian 2005-03-23 05:49:41 Re: BUG #1517: SQL interval syntax is accepted by the parser,

Browse pgsql-performance by date

  From Date Subject
Next Message Rick Jansen 2005-03-23 08:52:27 Re: Tsearch2 performance on big database
Previous Message Michael Ryan S. Puncia 2005-03-23 04:11:56 Re: best practices with index on varchar column