[SQL] 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a general ? on select performance
John Fabiani jo...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a general ? on select performance
On Saturday, August 28, 2010 08:35:07 am Tom Lane wrote: John Fabiani jo...@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
