Skip site navigation (1) Skip section navigation (2)

Re: count(*) performance

From: "Mikael Carneholm" <Mikael(dot)Carneholm(at)WirelessCar(dot)com>
To: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: count(*) performance
Date: 2006-03-27 21:57:43
Message-ID: 7F10D26ECFA1FB458B89C5B4B0D72C2B088295@sesrv12.wirelesscar.com (view raw or flat)
Thread:
Lists: pgsql-performance
This is where a "last_vacuumed" (and "last_analyzed") column in
pg_statistic(?) would come in handy. Each time vacuum or analyze has
finished, update the row for the specific table that was
vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed
column. No more guessing "maybe I haven't vacuumed/analyzed in a while",
and each time a user complains about bad performance, one could request
the user to do a "select s.last_vacuumed, s.last_analyzed from
pg_statistic s, pg_attribute a, pg_class c where ..."

It SOUNDS easy to implement, but that has fooled me before... :-)

- Mikael

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Guido
Neitzer
Sent: den 27 mars 2006 21:44
To: Brendan Duddridge
Cc: Postgresql Performance
Subject: Re: [PERFORM] count(*) performance


On 27.03.2006, at 21:20 Uhr, Brendan Duddridge wrote:

> Does that mean that even though autovacuum is turned on, you still  
> should do a regular vacuum analyze periodically?

It seems that there are situations where autovacuum does not a really  
good job.

However, in our application I have made stupid design decision which  
I want to change as soon as possible. I have a "visit count" column  
in one of the very large tables, so updates are VERY regular. I've  
just checked and saw that autovacuum does a great job with that.

Nevertheless I have set up a cron job to do a standard vacuum every  
month. I've used vacuum full only once after I did a bulk update of  
about 200.000 rows ...

cug

-- 
PharmaLine, Essen, GERMANY
Software and Database Development



Responses

pgsql-performance by date

Next:From: Matthew T. O'ConnorDate: 2006-03-27 22:43:02
Subject: Re: count(*) performance
Previous:From: Rodrigo MaderaDate: 2006-03-27 20:16:18
Subject: Large Binary Objects Middleware

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group