Re: Invisible Indexes

On 06/18/2018 06:12 PM, Tom Lane wrote:
> Peter Geoghegan <pg(at)bowt(dot)ie> writes:
>> On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I think the actually desirable way to handle this sort of thing is through
>>> an "index advisor" sort of plugin, which can hide a given index from the
>>> planner without any globally visible side-effects.
>> The globally visible side-effects are the point, though. Some users
>> desire cheap insurance against dropping what turns out to be the wrong
>> index.
> Perhaps there are use-cases where you want globally visible effects,
> but the primary use-case Andrew cited (i.e. EXPLAIN experimentation)
> would not want that.
>
> Anyway, if we do it with a GUC, the user can control the scope of
> the effects.
>
>

Yeah, but Peter makes the case that people want it for global
experimentation. "We think we can safely drop this humungous index that
would take us days to rebuild, but before we do let's make it invisible
and run for a few days just to make sure." I guess we could do that with
a GUC, but it seems ugly.

To Andres' point about the fragility of pg_index, maybe we'd need a
separate_catalog (pg_invisible_index)?

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services