Hello, > On Jul 23, 2025, at 9:43 PM, Michael Paquier <mich...@paquier.xyz> wrote: > > On Tue, Jul 22, 2025 at 01:15:16PM -0500, Sami Imseih wrote: >> The GUC serves multiple purposes. For example,I can create an index as >> invisible >> and use it in a controlled way, which is helpful for experimenting >> with a new index. > > An in-core GUC to control the list of indexes that should be allowed > or disallowed is I think asking for trouble, adding schema-related > knowledge directly into the GUC machinery. This does not scale well, > even if you force all the entries to be specified down to the database > and the schema. And it makes harder to control what a "good" behavior > should be at query-level. > > My 2c.
+1 I wonder if there's a path to simplify things further here while still providing a way to gradually build confidence when disabling and then dropping an index. As a developer/DBA or person in a similar position, I think my journey for dropping an index in this new world would look something like this: 1. Research phase: Use `pg_stat_user_indexes`, query analysis to understand index usage 2. Experimentation phase: Use `pg_hint_plan` (or GUC?) for session-level testing and slower rollout from applications using feature flags - Up until a while ago, this step won't exist because once I had enough confidence from step 1, I'd go to step 3. Which is a huge improvement from jumping to Step 4 below. But the new discussions have made me think that this step is important. 3. Validation phase: Use `ALTER INDEX INVISIBLE` for final system-wide confidence building 4. Cleanup phase: `DROP INDEX` when certain Per this plan, this would mean that pg_hint_plan would need to support index-level hints, and it’s not a massive / impossible task. But it also means that both systems aren't fighting/overriding each other or making it difficult for users to understand when exactly an index is being used or not. Ultimately, this would also mean that `ALTER INDEX INVISIBLE` is a one-way door, and there is only one way to control index visibility in core, which makes sense to me. I think any pitfalls and guarantees can be communicated well through documentation both in core and in `pg_hint_plan`. What’s not clear to me here is, how common / intuitive of a workflow will this be and if it fits easily in the “common use case” path? There are some aspects of the GUC approach that I'd miss, also because as a developer I've used DDLs and GUCs more than pg_hint_plan, but it's probably just a tooling exposure thing perhaps. Curious what folks think. P.S. Still very happy to help with patches whenever that is. Thanks, Shayon
