pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeda general ? on select performance
3+ messages / 2 participants
[nested] [flat]
* a general ? on select performance
@ 2010-08-28 15:21 John Fabiani <johnf@jfcomputer.com>
2010-08-28 15:35 ` Re: a general ? on select performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: John Fabiani @ 2010-08-28 15:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: a general ? on select performance
2010-08-28 15:21 a general ? on select performance John Fabiani <johnf@jfcomputer.com>
@ 2010-08-28 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-28 16:11 ` Re: a general ? on select performance John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2010-08-28 15:35 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: a general ? on select performance
2010-08-28 15:21 a general ? on select performance John Fabiani <johnf@jfcomputer.com>
2010-08-28 15:35 ` Re: a general ? on select performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-08-28 16:11 ` John Fabiani <johnf@jfcomputer.com>
0 siblings, 0 replies; 3+ messages in thread
From: John Fabiani @ 2010-08-28 16:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-08-28 16:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-28 15:21 a general ? on select performance John Fabiani <johnf@jfcomputer.com>
2010-08-28 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-28 16:11 ` John Fabiani <johnf@jfcomputer.com>
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