Re: Disabling an index temporarily

On 12/13/15 9:27 PM, Tom Lane wrote:
> Corey Huinker<corey(dot)huinker(at)gmail(dot)com> writes:
>> >So, I'd propose we following syntax:
>> >ALTER INDEX foo SET DISABLED
>> >-- does the SET indisvalid = false shown earlier.
> This is exactly*not* what Tatsuo-san was after, though; he was asking
> for a session-local disable, which I would think would be by far the more
> common use-case. It's hard for me to see much of a reason to disable an
> index globally while still paying all the cost to maintain it. Seems to
> me the typical work flow would be more like "disable index in a test
> session, try all your queries and see how well they work, if you conclude
> you don't need the index then drop it".

Both have value.

Sometimes the only realistic way to test this is to disable the index
server-wide and see if anything blows up. Actually, in my experience,
that's far more common than having some set of queries you can test
against and call it good.

FWIW, I also don't see the use case for disabling maintenance on an
index. Just drop it and if you know you'll want to recreate it squirrel
away pg_get_indexdef() before you do.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com