MIME-Version: 1.0
References: 
 <CA+dbf3gvWCeF8j_DBjtaPcNmm=E9d_Z7sPdaJRuanGfAw2Rvow@mail.gmail.com>
In-Reply-To: 
 <CA+dbf3gvWCeF8j_DBjtaPcNmm=E9d_Z7sPdaJRuanGfAw2Rvow@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 31 Mar 2026 19:50:26 -0700
Message-ID: 
 <CAHg+QDdoo0u+WWuHYOnKn+ygYrg87yufiPUS_X+=0BrUEM3JYw@mail.gmail.com>
Subject: Re: Proposal: Track last-used timestamp for index usage
To: Raghav Mittal <mittalr086@gmail.com>
Cc: pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000003f316064e5d267e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDdoo0u%2BWWuHYOnKn%2BygYrg87yufiPUS_X%2B%3D0BrUEM3JYw%40mail.gmail.com>
Precedence: bulk

--00000000000003f316064e5d267e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Raghav,

On Tue, Mar 31, 2026 at 7:23=E2=80=AFPM Raghav Mittal <mittalr086@gmail.com=
> wrote:

> Hi all,
>
> I=E2=80=99ve been exploring index usage statistics in PostgreSQL and noti=
ced that
> pg_stat_user_indexes only provides cumulative counters (idx_scan), but no=
t
> recency information.
>
> Problem:
> - Counters reset on restart or pg_stat_reset()
> - No way to determine when an index was last used
> - Makes it hard to safely identify unused indexes
>
> Proposal:
> Introduce lightweight tracking of last-used timestamp for indexes.
>

Could you elaborate the problem trying to solve here? As you mentioned
idx_scan already tracks the usage. Periodic snapshots should provide the
usage over a period of time. What is the purpose of exact timestamp?

Thanks,
Satya

--00000000000003f316064e5d267e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Hi Raghav,</div><div><br><div class=3D"gmail_quote gmail_=
quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Mar 31, 2026=
 at 7:23=E2=80=AFPM Raghav Mittal &lt;<a href=3D"mailto:mittalr086@gmail.co=
m">mittalr086@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-=
style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)"><div dir=
=3D"ltr"><div>Hi all,<br><br>I=E2=80=99ve been exploring index usage statis=
tics in PostgreSQL and noticed that<br>pg_stat_user_indexes only provides c=
umulative counters (idx_scan), but not<br>recency information.<br><br>Probl=
em:<br>- Counters reset on restart or pg_stat_reset()<br>- No way to determ=
ine when an index was last used<br>- Makes it hard to safely identify unuse=
d indexes<br><br>Proposal:<br>Introduce lightweight tracking of last-used t=
imestamp for indexes.</div></div></blockquote><div dir=3D"auto"><br></div><=
div dir=3D"auto">Could you elaborate=C2=A0the problem trying to solve here?=
 As you mentioned idx_scan already tracks the usage. Periodic snapshots sho=
uld provide the usage over a period of time. What is the purpose of exact t=
imestamp?</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks,</div><=
div dir=3D"auto">Satya</div><div dir=3D"auto"><br></div></div></div>

--00000000000003f316064e5d267e--