Re: Help with slow table update

>
> [skipped]
>
>
>> But remember that if you update or delete a row, removing it from an
>>> index, the data will stay in that index until vacuum comes along.
>>>
>>> Also, there's no point in doing a REINDEX after a VACUUM FULL;
>>> vacuum full rebuilds all the indexes for you.
>>>
>>>
>>> I was being desperate :)
>>>
>>> I still think there is something very wrong with this particular table.
>>> First, I have production systems that employ this function on way larger
>>> data set, and there is no problem (so far, but still). This machine is
>>> part of a test deployment, there is no constant load, the only data that
>>> is being written now is when I do these tests. Vacuuming should prune
>>> all that dead stuff, and if it's absent, it's unclear where is the time
>>> spent navigating/updating the table with 24 rows :)
>>>
>>
>> I think you definitely have a problem with dead rows, as evidenced by the
>> huge improvement VACUUM FULL made.
>>
>
> But it's not clear why (and not reasonable, IMHO, that) it wouldn't
> improve past current point.
>

What I should've done is 'VACUUM FULL VERBOSE'. Once I did, it told me
there were 800k dead rows that can't be removed. After digging around I
found some dangling prepared transactions, going back months. Once I threw
those away, and re-vacuumed, things got back to normal.

Thanks for all your help and advice.