Re: Disabling an index temporarily

On Tue, Dec 15, 2015 at 7:56 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> 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.

If someone wants to make "squirreling away the pg_get_indexdef"
easier, particularly for an entire table or an entire schema or an
entire database, I certainly wouldn't object. I am not a masochist.

But also, while loading 1.5 million records into a table with 250
million records is horribly, rebuilding all the indexes on a 251.5
million record table from scratch is even more horrible. I don't know
if suspending maintenance (either globally or just for one session)
and then doing a bulk fix-up would be less horrible, but would be
willing to give it a test run.

Cheers,

Jeff