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