Re: Invisible Indexes

On 19.06.2018 01:11, Andres Freund wrote:
> On 2018-06-18 18:05:11 -0400, Tom Lane wrote:
>> Andres Freund <andres(at)anarazel(dot)de> writes:
>>> On 2018-06-18 17:57:04 -0400, Tom Lane 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.
>>> Although I'm a bit doubtful that just shoving this into an extension is
>>> really sufficient. This is an extremely common task.
>> Well, what I was thinking about was that this functionality already
>> exists (I think) in one or more "index advisor" plugins.
> They're doing the opposite, right? I.e. they return "hypothetical
> indexes", which then can be used by the planner. None of the ones I've
> seen currently mask out an existing index.
>

I think that "invisible" indexes are tightly related with "hypothetical"
indexes.
Both are used to estimate query execution cost if particular index
exists/not exists.
Certainly, in case of hypothetical indexes we can only calculate cost,
but not actually execute query using this index.
And "invisible" indexes allows to execute query without this index. But
the final goal of both in the same.
And if we are introducing some syntax for invisible indexes, may be it
is better to take in account also "hypothetical" indexes and let them to
be toggled by this syntax also.

I am not sure if it can be completely done at extension level. At least
definitely, altering grammar is not possible at extension level. But it
can be handled using index parameters.
Both invisible and hypothetical indexes seems to be really useful
things: steps forward to "zero administration" database. My point is
that we should consider them together.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company