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... :-)
[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Guido
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
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 ...
PharmaLine, Essen, GERMANY
Software and Database Development
pgsql-performance by date
|Next:||From: Matthew T. O'Connor||Date: 2006-03-27 22:43:02|
|Subject: Re: count(*) performance|
|Previous:||From: Rodrigo Madera||Date: 2006-03-27 20:16:18|
|Subject: Large Binary Objects Middleware|