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.