Re: Invisible Indexes

Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> writes:
> This is a MySQL feature, where an index is not considered by the
> planner. Implementing it should be fairly straightforward, adding a new
> boolean to pg_index, and options to CREATE INDEX and ALTER INDEX. I
> guess VISIBLE would become a new unreserved keyword.

> The most obvious use case is to see what the planner does when the index
> is not visible, for example which other index(es) it might use. There
> are probably other cases where we might want an index to enforce a
> constraint but not to be used in query planning.

Traditionally the way to do the former is

begin;
drop index unwanted;
explain ....;
rollback;

Admittedly, this isn't great in a production environment, but neither
would be disabling the index in the way you suggest.

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.

I'm not sure about the "enforce constraint only" argument --- that
sounds like a made-up use-case to me. It's pretty hard to imagine
a case where a unique index applies to a query and yet you don't want
to use it.

> So, do we want this feature? If we do I'll go ahead and prepare a patch.

On the whole I'm not excited about it, at least not with this approach.
Have you considered an extension or GUC with only local side effects?

regards, tom lane