From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: postgres list <pgsql-sql@postgresql.org>
Subject: a general ? on select performance
Date: Sat, 28 Aug 2010 08:21:40 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.34-12-desktop; KDE/4.4.4; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201008280821.40740.johnf@jfcomputer.com>

Hi,
In general what are the causes of SQL select statement slow downs - other than 
the size of the data?  I realize this is a broad question but read on please.

Here's where I'm coming from:

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.

The loop performs very well for the first 500 - 800 items.  Then starts 
slowing down. And somewhere around 1200 really slows to a crawl.  To the point 
it does not complete in a 12 hour period. But when I take  several of the 
statements and execute them using pgAdmin - they take almost no time (0.001 if 
memory serves)

for rec in check_set_data:
select pkid from check_table where check_field = rec.field_name
if found
   skip
else insert into new table.

I'm using 8.4 on openSUSE 11.3 multi-processor, 8 GB of ram.

Johnf