Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7F5E66335F9 for ; Sat, 28 Aug 2010 13:11:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 38562-10 for ; Sat, 28 Aug 2010 16:11:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from newserver.jfcomputer.com (unknown [76.14.161.106]) by mail.postgresql.org (Postfix) with ESMTP id 55F5863308B for ; Sat, 28 Aug 2010 13:11:28 -0300 (ADT) Received: from localhost (unknown [127.0.0.1]) by newserver.jfcomputer.com (Postfix) with ESMTP id 394FA12208; Sat, 28 Aug 2010 16:11:21 +0000 (UTC) X-Virus-Scanned: amavisd-new at site Received: from newserver.jfcomputer.com ([127.0.0.1]) by localhost (linux-jfp8.site [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id yEr1rK8NIvvK; Sat, 28 Aug 2010 09:11:18 -0700 (PDT) Received: from linux-desktop.localnet (unknown [76.14.161.106]) by newserver.jfcomputer.com (Postfix) with ESMTP id 92E1B12207; Sat, 28 Aug 2010 09:11:18 -0700 (PDT) From: John Fabiani Organization: J.F. Computer Solutions To: Tom Lane Subject: Re: a general ? on select performance Date: Sat, 28 Aug 2010 09:11:23 -0700 User-Agent: KMail/1.13.5 (Linux/2.6.34-12-desktop; KDE/4.4.4; i686; ; ) Cc: postgres list References: <201008280821.40740.johnf@jfcomputer.com> <19999.1283009707@sss.pgh.pa.us> In-Reply-To: <19999.1283009707@sss.pgh.pa.us> X-KMail-Markup: true MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="Boundary-01=_rUTeMou2keYdYtQ" Content-Transfer-Encoding: 7bit Message-Id: <201008280911.23765.johnf@jfcomputer.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.106 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201008/83 X-Sequence-Number: 34523 --Boundary-01=_rUTeMou2keYdYtQ Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit On Saturday, August 28, 2010 08:35:07 am Tom Lane wrote: > John Fabiani 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 --Boundary-01=_rUTeMou2keYdYtQ Content-Type: text/html; charset="iso-8859-15" Content-Transfer-Encoding: 7bit

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

--Boundary-01=_rUTeMou2keYdYtQ--