From: John Fabiani <johnf@jfcomputer.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: a general ? on select performance
Date: Sat, 28 Aug 2010 09:11:23 -0700
Message-ID: <201008280911.23765.johnf@jfcomputer.com> (raw)
In-Reply-To: <19999.1283009707@sss.pgh.pa.us>
References: <201008280821.40740.johnf@jfcomputer.com>
<19999.1283009707@sss.pgh.pa.us>
On Saturday, August 28, 2010 08:35:07 am Tom Lane wrote:
> 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
I'm not making any changes to the lookup table. But I did discover the
performance problem - rather I was helped into the light (used the IRC).
There is a custom function in my select statement. It turns out that the
function was labeled as "VOLATILE" and a simple change to 'STABLE' made all
the difference in the world. I was not aware of this setting (I won't forget
it). Learn something everyday!
But your answer has me thinking. I often do exactly what you are describing
(of course not for thousands of records). So how does one get around the
issues bloat?
Johnf
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: johnf@jfcomputer.com, tgl@sss.pgh.pa.us
Subject: Re: a general ? on select performance
In-Reply-To: <201008280911.23765.johnf@jfcomputer.com>
* 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