Message-ID: <4DCC245D.9030001@fuzzy.cz>
Date: Thu, 12 May 2011 20:18:05 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101219 Lightning/1.0b3pre Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: since when has pg_stat_user_indexes.idx_scan been counting?
References: <BANLkTikhNLPhCjE819=RFG-+CB6UV4GbUA@mail.gmail.com>
In-Reply-To: <BANLkTikhNLPhCjE819=RFG-+CB6UV4GbUA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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