Re: Is there a way to temporarily disable a index

On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote:
> David G Johnston  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