Re: Is there a way to temporarily disable a index

On Fri, Jul 11, 2014 at 12:12 PM, Michael Banck <mbanck(at)gmx(dot)net> wrote:

> On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote:
> > David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)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.

David J.