Invisible Indexes

On Mon, Jun 18, 2018 at 2:36 PM, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:

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.

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

I know that it's definitely a feature that I want. Haven't thought
about the syntax, though.

--
Peter Geoghegan

On 18 June 2018 at 16:36, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:

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.

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

should pg_index.indisvalid works for this? in that case you only need
the syntax for it...

--
Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 06/18/2018 05:46 PM, Jaime Casanova wrote:

On 18 June 2018 at 16:36, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:

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.

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

should pg_index.indisvalid works for this? in that case you only need
the syntax for it...

I thought about that. But I think these are more or less orthogonal.  I
doubt it will involve lots of extra code, though.

cheers

andrew

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

On 06/18/2018 05:44 PM, Peter Geoghegan wrote:

On Mon, Jun 18, 2018 at 2:36 PM, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:

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.
So, do we want this feature? If we do I'll go ahead and prepare a patch.

I know that it's definitely a feature that I want.

Well, that's encouraging ;-)

Haven't thought
about the syntax, though.

I envisioned:

CREATE INDEX .... [NOT VISIBLE] ...;
ALTER INDEX ... [SET [NOT] VISIBLE] ...;

Let the bikeshedding begin :-)

cheers

andrew

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

On 2018-06-18 17:50:44 -0400, Andrew Dunstan wrote:

On 06/18/2018 05:46 PM, Jaime Casanova wrote:

On 18 June 2018 at 16:36, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:

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.

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

should pg_index.indisvalid works for this? in that case you only need
the syntax for it...

I thought about that. But I think these are more or less orthogonal.� I
doubt it will involve lots of extra code, though.

Be careful about that - currently it's not actually trivially possible
to ever update pg_index rows. No, I'm not kidding
you. pg_index.indexcheckxmin relies on the pg_index row's xmin. If you
have ALTER do a non inplace update, you'll break things.

Greetings,

Andres Freund

Andrew Dunstan <andrew.dunstan@2ndquadrant.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

Hi,

On 2018-06-18 17:57:04 -0400, Tom Lane wrote:

Andrew Dunstan <andrew.dunstan@2ndquadrant.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.

Yea, I don't think a global action - which'll at least take a something
like a share-update-exclusive lock - is a suitable approach for this
kinda thing.

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.

Greetings,

Andres Freund

On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

The globally visible side-effects are the point, though. Some users
desire cheap insurance against dropping what turns out to be the wrong
index.

FWIW, this isn't just a MySQL feature. Oracle has a similar feature.

--
Peter Geoghegan

Andres Freund <andres@anarazel.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. It's possible
that they've all bit-rotted for lack of support, which would not speak
highly of the demand for the feature. But if we feel this is worth
pulling into core, I think something along the lines of a GUC listing
indexes to ignore for planning purposes might be a better design.
It'd certainly dodge the issues you mentioned about lack of mutability
of pg_index entries.

regards, tom lane

On 2018-06-18 18:05:11 -0400, Tom Lane wrote:

Andres Freund <andres@anarazel.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.

It's possible that they've all bit-rotted for lack of support, which
would not speak highly of the demand for the feature.

IDK, the DBA / developer crowd hitting issues like this isn't the same
as the crowd willing to update an external plugin that doesn't even do
quite what you want, and was more experimental than anything.

Greetings,

Andres Freund

Peter Geoghegan <pg@bowt.ie> writes:

On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.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.

regards, tom lane

On Tue, Jun 19, 2018 at 12:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, what I was thinking about was that this functionality already
exists (I think) in one or more "index advisor" plugins. It's possible
that they've all bit-rotted for lack of support, which would not speak
highly of the demand for the feature. But if we feel this is worth
pulling into core, I think something along the lines of a GUC listing
indexes to ignore for planning purposes might be a better design.
It'd certainly dodge the issues you mentioned about lack of mutability
of pg_index entries.

I know only one extension which does exactly that:
https://github.com/postgrespro/plantuner

It seems that it's still maintained.

Andres Freund <andres@anarazel.de> writes:

On 2018-06-18 18:05:11 -0400, Tom Lane wrote:

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 had the idea that some of them could also hide existing indexes.
It's been awhile, so maybe my memory is faulty, but the hook we
provide is capable of that:

/*
* Allow a plugin to editorialize on the info we obtained from the
* catalogs. Actions might include altering the assumed relation size,
* removing an index, or adding a hypothetical index to the indexlist.
*/
if (get_relation_info_hook)
(*get_relation_info_hook) (root, relationObjectId, inhparent, rel);

regards, tom lane

On Mon, Jun 18, 2018 at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

I had imagined that those use cases would be the most common. Dropping
an index in production because it very much looks like it is unused is
always a bit nerve-wracking in my experience. It's often hard to be
100% sure due to factors like replicas, the possible loss of statistic
collector stats masking a problem, the possibility that there are very
important queries that do use the index but are only run very
infrequently, and so on.

--
Peter Geoghegan

On 06/18/2018 06:12 PM, Tom Lane wrote:

Peter Geoghegan <pg@bowt.ie> writes:

On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.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

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:

On 06/18/2018 06:12 PM, Tom Lane wrote:

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.

I find it hard to believe that it's uglier than what you suggested...
and it also does more, and is easier to implement.

regards, tom lane

On Mon, Jun 18, 2018 at 3:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

But if we feel this is worth
pulling into core, I think something along the lines of a GUC listing
indexes to ignore for planning purposes might be a better design.
It'd certainly dodge the issues you mentioned about lack of mutability
of pg_index entries.

​While adding a mutable column to pg_index​ is probably ideal having a
pg_index_visible table related one-to-one (optional?) with pg_index. MySQL
has, and we would probably want, a GUC to control whether to check the
table for visibility.

Reading the MySQL description for this one use case posited is a DBA
wanting to remove an index and see which queries appear in their duration
limit log (probably in combination with auto-explain).

An SQL interface to the feature seems desirable. On that front VISIBLE and
INVISIBLE are the pre-existing keywords for MySQL.

As long as BEGIN-ALTER INDEX-ROLLBACK works as expected I wouldn't see any
need for a GUC accepting text inputs. That said, somehow making "ALTER
INDEX LOCAL name INVISIBLE" work and having it auto-revert back to visible
as transaction end would provide for the one major advantage of an
in-session SET.

David J.

On Mon, Jun 18, 2018 at 6:11 PM, Andres Freund <andres@anarazel.de> wrote:

On 2018-06-18 18:05:11 -0400, Tom Lane wrote:

Andres Freund <andres@anarazel.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.

It's possible that they've all bit-rotted for lack of support, which
would not speak highly of the demand for the feature.

IDK, the DBA / developer crowd hitting issues like this isn't the same
as the crowd willing to update an external plugin that doesn't even do
quite what you want, and was more experimental than anything.

Indeed. ISTR a conversation I had with someone on slack earlier this
year about the validity of just manually updating indisvalid as a
means for determining if an index could be safely removed (for the
record, I did not recommend it ;-)

DBA's are often willing to weedwhacker at things in SQL when the
alternative is to learn C.

Robert Treat
http://xzilla.net

On Mon, Jun 18, 2018 at 3:17 PM, Andrew Dunstan <
andrew.dunstan@2ndquadrant.com> wrote:

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.

​On that front what's the proposed behavior for cached plans using said
index?

IIUC with a GUC you'd have to force clients to establish new sessions if
you wanted all queries to be affected by the new setting whereas using
cache invalidation you can affect existing sessions with a catalog update.

David J.
​