Search Postgresql Archives

Re: Nested loop in simple query taking long time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux