Unused indexes

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Unused indexes

Ron-2
For indexes that existed before the cluster was last started, and for which
REINDEX hasn't been run on them since the cluster was last started... is it
valid to say that an index has not been used since the cluster was started
if these three pg_stat_all_indexes fields all have a value of 0?

idx_scan
idx_tup_read
idx_tup_fetch

If it matters, the version is 9.6.6.

Thanks

--
Angular momentum makes the world go 'round.

Reply | Threaded
Open this post in threaded view
|

Re: Unused indexes

ichbinrene
I think your assumption is correct, as long as the statistics collector is working correctly (I've never seen this not being the case), and the setting "track_counts" is set to on.

Am Sa., 1. Dez. 2018 um 05:24 Uhr schrieb Ron <[hidden email]>:
For indexes that existed before the cluster was last started, and for which
REINDEX hasn't been run on them since the cluster was last started... is it
valid to say that an index has not been used since the cluster was started
if these three pg_stat_all_indexes fields all have a value of 0?

idx_scan
idx_tup_read
idx_tup_fetch

If it matters, the version is 9.6.6.

Thanks

--
Angular momentum makes the world go 'round.



--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Reply | Threaded
Open this post in threaded view
|

Re: Unused indexes

Martín Marqués
In reply to this post by Ron-2
El sáb., 1 dic. 2018 a las 8:24, Ron (<[hidden email]>) escribió:
>
> For indexes that existed before the cluster was last started, and for which
> REINDEX hasn't been run on them since the cluster was last started... is it
> valid to say that an index has not been used since the cluster was started
> if these three pg_stat_all_indexes fields all have a value of 0?
>
> idx_scan

Just this one, and that the index doesn't force a constraint (it's not
a unique index).

One other thing to keep in mind is that, if you have hot_standby
replicas, the index might be used there, and the primary doesn't have
information of index_scans on other nodes of the cluster.

Regards,

--
Martín Marqués
It’s not that I have something to hide,
it’s that I have nothing I want you to see