Re: count(*) 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