Re: Nested loop in simple query taking long time

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Henrik <henke(at)mac(dot)se>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: Nested loop in simple query taking long time
Date: 2007-12-07 11:56:01
Message-ID: 20071207115601.GB5192@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2007-12-07 12:05:22 Re: Q: using generate_series to fill in the blanks
Previous Message Sam Mason 2007-12-07 11:46:27 Re: aggregate and order by