Unused indexes

On Mon, Feb 5, 2024 at 9:02 AM arun chirappurath <arunsnmimt@gmail.com>
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;.

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

On Mon, Feb 5, 2024 at 9:54 PM Greg Sabino Mullane <htamfids@gmail.com>
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.