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
> (as linked to from the TODO wiki page), but the neighboring discussion
> was mostly about FK constraints.
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.