Re: Unused indexes

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