Re: Nested loop in simple query taking long time

Henrik wrote:
>
> 6 dec 2007 kl. 20.26 skrev Alvaro Herrera:
>
>> Henrik wrote:
>>
>>> I think I have a clue why its so off. We update a value in that table
>>> about
>>> 2 - 3 million times per night and as update creates a new row it becomes
>>> bloated pretty fast. The table hade a size of 765 MB including indexes
>>> and
>>> after vacuum full and reindex it went down to 80kB... I guess I need
>>> routine reindex on this table. Thank god is not big. :)
>>
>> I suggest you put a lone VACUUM on that table in cron, say once every 5
>> minutes, and you should be fine. You shouldn't need a reindex at all.
> Instead of cron can't I just have really aggressive autovacuum settings on
> this table?

Not on 8.2 (or earlier), because it can only process one table at a
time, so if it ever takes much longer than 5 minutes for vacuuming other
tables, this table will be neglected for at least that long -- and
probably a lot longer actually.

On 8.3 this should be somewhat less of a problem because autovacuum can
process more than one table at a time. It will still be a problem if
there are several tables that take much longer than 5 minutes, but it
should be much less severe.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"XML!" Exclaimed C++. "What are you doing here? You're not a programming
language."
"Tell that to the people who use me," said XML.