When was ANALYZE run in the past?

From: Benjamin Rutt <rutt(dot)4(at)osu(dot)edu>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: When was ANALYZE run in the past?
Date: 2014-11-29 20:27:07
Message-ID: CAMWA1_pYy6sAQg3xwQe66ma1wRgvin2YTDqQb2ZTKhBq+u5k6Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On *https://wiki.postgresql.org/wiki/Slow_Counting*
<https://wiki.postgresql.org/wiki/Slow_Counting> I read that

SELECT reltuples FROM pg_class WHERE relname = 'tbl';

may be a good way to get an estimate of the # of rows in the table, but
depends on how frequently ANALYZE has been running. I run autovacuum under
a default configuration, but I suspect ANALYZE is not running frequently
enough for my purposes (when I ran the above command on my table, it
consistently returned 1.4 million for ~20 minutes straight; when I
explicitly ran an ANALYZE command at that point (when I realized the
estimate was not updating even every few minutes), the ANALYZE command took
a few seconds, then the above command returned .7 million which matches
what ‘select count(*)’ was returning). So I suspect ANALYZE is not running
frequently enough or is stepping over my table for some reason.

So, given the above context, my question is, is there any way to tell at
what times ANALYZE has been run in the past on the db or on a particular
table? I am running a fairly vanilla postgres 8.4 db on linux, with a few
minor tweaks to postgresql.conf:

synchronous_commit = off
log_line_prefix = '%t '
log_min_messages = info

I don’t see anything in the stderr of the db other than the following:

2014-11-26 20:01:55 GMT LOG: database system was shut down at 2014-11-26
18:39:41 GMT
2014-11-26 20:01:55 GMT LOG: database system is ready to accept connections
2014-11-26 20:01:55 GMT LOG: autovacuum launcher started

Thanks!

--
Benjamin Rutt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2014-11-29 20:35:22 Re: When was ANALYZE run in the past?
Previous Message Eric Svenson 2014-11-29 08:25:24 Re: Problem with pg_dump and decimal mark