Re: Disabling an index temporarily

On Sun, Dec 13, 2015 at 11:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > Not to hijack the thread even further in the wrong direction, but I
> > think what Corey really wants here is to stop maintaining the index at
> > retail while preserving the existing definition and existing index
> > data, and then to do a wholesale fix-up, like what is done in the 2nd
> > half of a create index concurrently, upon re-enabling it.
>
> Meh. Why not just drop the index? I mean, yeah, you might save a few
> keystrokes when and if you ever re-enable it, but this sure seems like
> a feature in search of a use-case.
>
> regards, tom lane
>

Sorry, I misread Tatsu's initial post. I thought the disabling was for the
purpose of reducing overhead on large DML operations, not plan
experimentation.

Jeff's suggestion is one use-case. The work of discovering what indexes
exist on a table (because it may have changed since you last wrote that
code), saving those names and definitions to an intermediate table,
disabling them, doing the big DML operation, and then re-enabling them is
tedious and error prone, both in the coding of it and the error handling.
Leaving the index definitions in the data dictionary is one way to avoid
all that.