On Fri, Jul 11, 2014 at 12:12 PM, Michael Banck <mba...@gmx.net> wrote:
> On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote: > > David G Johnston <david.g.johns...@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....@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. David J.
