Re: since when has pg_stat_user_indexes.idx_scan been counting?

Dne 12.5.2011 17:39, Willy-Bas Loos napsal(a):
> Hi,
>
> We have some indexes that don't seem to be used at all.
> I'd like to know since when they have not been used.
> That is, the time when postgres started counting to reach the number
> that is in pg_stat_user_indexes.idx_scan
>
> Is there a way to retrieve that from the database ?

Well, not really :-( You could call pg_postmaster_start_time() to get
the start time, but that has two major drawbacks

(1) The stats may be actually collected for much longer, because restart
does not reset them.

(2) If someone called pg_stat_reset(), the stats are lost but the start
time remains the same.

So there really is no reliable way to do detect this.

In 9.1 this is not true - there's a timestamp for each database (and
global stats) to keep track of the last reset.

regards
Tomas