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