From: John Fabiani <johnf@jfcomputer.com>
To: postgres list <pgsql-sql@postgresql.org>
Subject: a general ? on select performance
Date: Sat, 28 Aug 2010 08:21:40 -0700
Message-ID: <201008280821.40740.johnf@jfcomputer.com> (raw)
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
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
Subject: Re: a general ? on select performance
In-Reply-To: <201008280821.40740.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