On Fri, Jul 11, 2014 at 12:12 PM, Michael Banck <mbanck@gmx.net> wrote:
On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote: > David G Johnston <david.g.johnston@gmail.com> writes: > > Benedikt Grundmann wrote > >> That is it possible to tell the planner that index is off limits > >> i.e. > >> don't ever generate a plan using it? > > > Catalog hacking could work but not recommended (nor do I know the > > proper > > commands and limitations). Do you need the database/table to accept > > writes > > during the testing period? > > Hacking pg_index.indisvalid could work, given a reasonably recent PG. > I would not try it in production until I'd tested it ;-)
I wonder whether this should be exposed at the SQL level? Hacking pg_index is left to superusers, but the creator of an index (or the owner of the schema) might want to experiment with disabling indices while debugging query plans as well.
Turns out this is already in the TODO, Steve Singer has requested this (in particular, "ALTER TABLE ... ENABLE|DISABLE INDEX ...") in http://www.postgresql.org/message-id/87hbegz5ir.fsf@cbbrowne.afilias-int.info (as linked to from the TODO wiki page), but the neighboring discussion was mostly about FK constraints.
Thoughts?
Michael
Apparently work is ongoing on to allow EXPLAIN to calculate the impact a particular index has on table writes. What is needed is a mechanism to temporarily facilitate the remove impact of specific indexes on reads without having to disable the index for writing. Ideally on a per-query basis so altering the catalog doesn't make sense. I know we do not want traditional planner hints but in the spirit of the existing enable_indexscan GUC there should be a " disable_readofindex='table1.index1,table1.index2,table2.index1' " GUC capability that would allow for session, user, or system-level control of which indexes are to be used during table reads.
