Re: Invisible Indexes

On 06/19/2018 02:05 PM, Robert Haas wrote:
> On Mon, Jun 18, 2018 at 6:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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, I agree that a GUC seems more powerful and easier to roll out.
> A downside is that there could be cached plans still using that old
> index. If we did DDL on the index we could be sure they all got
> invalidated, but otherwise how do we know?
>
> BTW, like you, I seem to remember somebody writing an extension that
> did added a GUC that did exactly this, and demoing it at a conference.
> Maybe Oleg or Teodor?
>

A major downside to a GUC is that you have to be aware of the current
setting, since we're not going to have one settoing for each invisible
index. Doing it at the SQL level you can treat each index separately. A
GUC will actually involve more code, I suspect.

cheers

andrew

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