Re: Unused indexes

Поиск
Список
Период
Сортировка
От Greg Sabino Mullane
Тема Re: Unused indexes
Дата
Msg-id CAKAnmmLhmcAGMnBWpdroO-ot8hdDyZf+8GLEHDfvQguhrYAm0Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Unused indexes  (Ron Johnson <ronljohnsonjr@gmail.com>)
Ответы Re: Unused indexes
Список pgsql-general
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
 

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: select from composite type
Следующее
От: Greg Sabino Mullane
Дата:
Сообщение: Re: How to do faster DML