From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Fabiani <johnf@jfcomputer.com>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: a general ? on select performance
Date: Sat, 28 Aug 2010 11:35:07 -0400
Message-ID: <19999.1283009707@sss.pgh.pa.us> (raw)
In-Reply-To: <201008280821.40740.johnf@jfcomputer.com>
References: <201008280821.40740.johnf@jfcomputer.com>
John Fabiani <johnf@jfcomputer.com> writes:
> I have program that loops though several thousands of records. The loop
> contains a select statement that confirms that I don't have a dup and then
> inserts a record into a different table. IOW's the statement is checking
> against a static table. The size of the table I'm checking does not change
> during the loop. I'm sure many have done something similar.
Are you really, truly making *no* changes to the table you're reading?
What I'm suspecting is that you are making such changes, in fact lots
of them. The number of live rows may not change, but the table is
bloating with lots of dead row versions, which can't be cleaned up yet
since the transaction that deleted them is still open. This leads to
O(N^2) or worse slowdown.
There are usually ways around this type of problem, but we'd need more
details about what you're really doing.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, johnf@jfcomputer.com
Subject: Re: a general ? on select performance
In-Reply-To: <19999.1283009707@sss.pgh.pa.us>
* 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