Re: Unused indexes
On Mon, Feb 5, 2024 at 9:54 PM Greg Sabino Mullane wrote: > The pg_stat_*_tables tables idx_* columns has accumulated usage since the >> last time you started the postmaster. >> > > Actually, those persist at restart - you can use > > select datname, stats_reset from pg_stat_database; > > to see when/if they were reset. You can look for zero/low entries in > pg_stat_user_indexes.idx_scan to find unused indexes. Note that replicas > maintain their own stats, so checking only the primary may cause a false > positive. > > >> I sql server we have this option to disable it and need to rebuild it to >>> ensemble it >>> >> >> Sadly, PG does not have ALTER INDEX ... DISABLE;. >> > > Not really sure what the purpose of that is in sql server, > To tell the system to stop using a specific index without having to drop the index. Its only purpose is to make the DBA's life easier. IMNSHO, that's an excellent reason to have such a feature. > but Ron is correct, we have nothing equivalent. General usage in Postgres > is to drop the index if it is unused. If you need to create it again, easy > enough with CREATE INDEX CONCURRENTLY. > Unless it's blocked by existing readers. I've seen that more than a few times. > Keeping your schema changes in a VCS (e.g. git) is a good way to document > when and why the index was dropped. I suppose in a pinch you could keep the > old index around by sticking it in a table comment. > The ALTER INDEX ... DISABLE / ALTER INDEX ... REBUILD pair ensures that Mistakes Were Not Made. You *can't* make the mistake of re-creating an index incorrectly if you didn't actually drop the index.
Re: Unused indexes
> > The pg_stat_*_tables tables idx_* columns has accumulated usage since the > last time you started the postmaster. > Actually, those persist at restart - you can use select datname, stats_reset from pg_stat_database; to see when/if they were reset. You can look for zero/low entries in pg_stat_user_indexes.idx_scan to find unused indexes. Note that replicas maintain their own stats, so checking only the primary may cause a false positive. > I sql server we have this option to disable it and need to rebuild it to >> ensemble it >> > > Sadly, PG does not have ALTER INDEX ... DISABLE;. > Not really sure what the purpose of that is in sql server, but Ron is correct, we have nothing equivalent. General usage in Postgres is to drop the index if it is unused. If you need to create it again, easy enough with CREATE INDEX CONCURRENTLY. Keeping your schema changes in a VCS (e.g. git) is a good way to document when and why the index was dropped. I suppose in a pinch you could keep the old index around by sticking it in a table comment. Cheers, Greg
Re: Unused indexes
On Mon, Feb 5, 2024 at 9:02 AM arun chirappurath wrote: > Hi All, > > Do we have a script to get unused indexes for 30 days and once identified > do we have an option to disable and enable when required? > The pg_stat_*_tables tables idx_* columns has accumulated usage since the last time you started the postmaster. > I sql server we have this option to disable it and need to rebuild it to > ensemble it > Sadly, PG does not have ALTER INDEX ... DISABLE;.
Unused indexes
Hi All, Do we have a script to get unused indexes for 30 days and once identified do we have an option to disable and enable when required? I sql server we have this option to disable it and need to rebuild it to ensemble it Thanks, Arun
Re: Unused indexes
El sáb., 1 dic. 2018 a las 8:24, Ron () 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
Re: Unused indexes
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 : > 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/
Unused indexes
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.
