Re: since when has pg_stat_user_indexes.idx_scan been counting? - Mailing list pgsql-performance

From Tomas Vondra
Subject Re: since when has pg_stat_user_indexes.idx_scan been counting?
Date
Msg-id 4DCC245D.9030001@fuzzy.cz
Whole thread Raw
In response to since when has pg_stat_user_indexes.idx_scan been counting?  (Willy-Bas Loos <willybas@gmail.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Eric McKeeth
Date:
Subject: Re: Postgres refusing to use >1 core
Next
From: Tomas Vondra
Date:
Subject: Re: since when has pg_stat_user_indexes.idx_scan been counting?