Re: Invisible Indexes

On Mon, Jun 18, 2018 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.