4 ms·

I hope postgres adopts easily disabling an index instead of deleting it. MySQL has this feature and it's very useful for testing whether an index can be deleted

by valzam 4y ago

I hope postgres adopts easily disabling an index instead of deleting it. MySQL has this feature and it's very useful for testing whether an index can be deleted on production load. Transactional DDL is amazing already, disabling indexes would be a great addition for low-risk performance tuning.

pramsey 4y ago
update pg_index set indisvalid = false where indexrelid = 63514;
- ioltas 4y ago
  It's been argued a few times that we should have the possibility to make an index invalid on sight with a proper ALTER INDEX command, yes.
  - samokhvalov 4y ago
    I see to use cases: - disable for all - disable only for my session, to check what would happen with the plan, and only then decide to proceed with disabling for all (or to drop it) ALTER is quite invasive way, even more than "UPDATE .. SET indisvalid = false ...". It would be good to do it via SET as it was proposed in the plantuner extension long ago.
- samokhvalov 4y ago
  There is an opinion that it's unsafe: https://twitter.com/petervgeoghegan/status/1599191964045672449 https://twitter.com/petervgeoghegan/status/15991919640456724... There was an extension from Teodor some time ago, plantuner, to disable indexes in a session or globally ("set plantuner.forbid_index='id_idx2';") – but it didn't make it to core, and even to contribs. Maybe because the functionality to disable indexes was mixed with planner hints there. It's a very old story, discussion from 2009: https://www.postgresql.org/message-id/flat/47E63672-972E-452C-AFBE-37C3C883D9FC%40decibel.org#bf15ae4127caff5cc6a70a97e53d625b https://www.postgresql.org/message-id/flat/47E63672-972E-452...
viraptor 4y ago
The completely unused indexes can be found through https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW https://www.postgresql.org/docs/current/monitoring-stats.htm...
haki 4y ago
You can utilize transactional DDL for that https://hakibenita.com/sql-tricks-application-dba#make-indexes-invisible https://hakibenita.com/sql-tricks-application-dba#make-index...

by valzam 4y ago

pramsey 4y ago

update pg_index set indisvalid = false where indexrelid = 63514;

ioltas 4y ago
It's been argued a few times that we should have the possibility to make an index invalid on sight with a proper ALTER INDEX command, yes.
- samokhvalov 4y ago
  I see to use cases: - disable for all - disable only for my session, to check what would happen with the plan, and only then decide to proceed with disabling for all (or to drop it) ALTER is quite invasive way, even more than "UPDATE .. SET indisvalid = false ...". It would be good to do it via SET as it was proposed in the plantuner extension long ago.
samokhvalov 4y ago
There is an opinion that it's unsafe: https://twitter.com/petervgeoghegan/status/1599191964045672449 https://twitter.com/petervgeoghegan/status/15991919640456724... There was an extension from Teodor some time ago, plantuner, to disable indexes in a session or globally ("set plantuner.forbid_index='id_idx2';") – but it didn't make it to core, and even to contribs. Maybe because the functionality to disable indexes was mixed with planner hints there. It's a very old story, discussion from 2009: https://www.postgresql.org/message-id/flat/47E63672-972E-452C-AFBE-37C3C883D9FC%40decibel.org#bf15ae4127caff5cc6a70a97e53d625b https://www.postgresql.org/message-id/flat/47E63672-972E-452...

viraptor 4y ago

The completely unused indexes can be found through https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW https://www.postgresql.org/docs/current/monitoring-stats.htm...

haki 4y ago

You can utilize transactional DDL for that https://hakibenita.com/sql-tricks-application-dba#make-indexes-invisible https://hakibenita.com/sql-tricks-application-dba#make-index...