Thread: a general ? on select performance

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

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

<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">OnSaturday, August 28, 2010 08:35:07 am Tom Lane wrote:<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> John Fabiani
<johnf@jfcomputer.com>writes:<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> > I have program that loops though several thousands of
records.The loop<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> > contains a select statement that confirms that I don't have a dup and<p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> then inserts a record into a different table. IOW's the statement is<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> checking against a static table. The size of the table I'm checking<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> does not change during the loop. I'm sure many have done something<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> similar.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Are you really, truly
making*no* changes to the table you're reading?<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> What
I'msuspecting is that you are making such changes, in fact lots<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> of them. The number of
liverows may not change, but the table is<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> bloating with lots of dead row versions,
whichcan't be cleaned up yet<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> since the transaction that deleted them is still open. This
leadsto<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> O(N^2) or worse slowdown.<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>There are usually ways around this type of problem, but we'd need more<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
detailsabout what you're really doing.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> regards, tom lane<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">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
functionin my select statement. It turns out that the function was labeled as "<span style=" font-family:'Courier
New,courier';">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! </span><p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;
font-family:'CourierNew,courier';"><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><span style=" font-family:'Courier New,courier';">But your
answerhas me thinking. I often do exactly what you are describing (of course not for thousands of records). So how does
oneget around the issues bloat?</span><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0; font-family:'Courier
New,courier';"><pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;"><span style=" font-family:'Courier New,courier';">Johnf </span>