Re: Optimizer failure on update w/integer column

nolan@celery.tssi.com writes:
> If I drop the index on missing_ids completely, it runs much faster, 35
> seconds the first time, 38 seconds when I ran it a second time.

> I then recreated the index on missing_ids(memtranseq), the execution time
> slowed down to 48 seconds the first time I reran the update, and it took
> 262 seconds when I ran the update again.  Subsequent passes got progressivly
> slower: 371 seconds, then 764 seconds.

This is a unique index, right?  Seems like the cost must be related to
checking for uniqueness violations --- the index code has to plow
through all the index entries with the same key, visit their associated
heap tuples, confirm those tuples are dead (or being deleted by the
current transaction).  You could check this by seeing what the cost
profile looks like with a nonunique index in place.

I'm not quite sure *why* it's happening though.  7.3 is supposed to have
code in it to forestall indefinite growth of the number of heap visits
that have to be made.  Hmm ... were you doing the repeated passes all in
a single transaction block, or were you allowing the previous updates to
commit before you tried again?

            regards, tom lane