Обсуждение: Proposal to Enable/Disable Index using ALTER INDEX

On Tue, 10 Sept 2024 at 09:39, Shayon Mukherjee <shayonj@gmail.com> wrote:
> Adding and removing indexes is a common operation in PostgreSQL. On larger databases, however, these operations can
beresource-intensive. When evaluating the performance impact of one or more indexes, dropping them might not be ideal
sinceas a user you may want a quicker way to test their effects without fully committing to removing & adding them back
again.Which can be a time taking operation on larger tables. 
>
> Proposal:
> I propose adding an ALTER INDEX command that allows for enabling or disabling an index globally. This could look
somethinglike: 
>
> ALTER INDEX index_name ENABLE;
> ALTER INDEX index_name DISABLE;
>
> A disabled index would still receive updates and enforce constraints as usual but would not be used for queries. This
allowsusers to assess whether an index impacts query performance before deciding to drop it entirely. 

I personally think having some way to alter an index to stop it from
being used in query plans would be very useful for the reasons you
mentioned.  I don't have any arguments against the syntax you've
proposed.  We'd certainly have to clearly document that constraints
are still enforced. Perhaps there is some other syntax which would
self-document slightly better. I just can't think of it right now.

> Implementation:
> To keep this simple, I suggest toggling the indisvalid flag in pg_index during the enable/disable operation.

That's not a good idea as it would allow ALTER INDEX ... ENABLE; to be
used to make valid a failed concurrently created index.  I think this
would need a new flag and everywhere in the planner would need to be
adjusted to ignore indexes when that flag is false.

> Additional Considerations:
> - Keeping the index up-to-date while it’s disabled seems preferable, as it avoids the need to rebuild the index if
it’sre-enabled later. The alternative would be dropping and rebuilding the index upon re-enabling, which I believe
wouldintroduce additional overhead in terms of application logic & complexity. 

I think the primary use case here is to assist in dropping useless
indexes in a way that can very quickly be undone if the index is more
useful than thought. If you didn't keep the index up-to-date then that
would make the feature useless for that purpose.

If we get the skip scan feature for PG18, then there's likely going to
be lots of people with indexes that they might want to consider
removing after upgrading. Maybe this is a good time to consider this
feature as it possibly won't ever be more useful than it will be after
we get skip scans.

David

Hi,

On Tue, Sep 10, 2024 at 10:16:34AM +1200, David Rowley wrote:
> On Tue, 10 Sept 2024 at 09:39, Shayon Mukherjee <shayonj@gmail.com> wrote:
> > Adding and removing indexes is a common operation in PostgreSQL. On
> > larger databases, however, these operations can be
> > resource-intensive. When evaluating the performance impact of one or
> > more indexes, dropping them might not be ideal since as a user you
> > may want a quicker way to test their effects without fully
> > committing to removing & adding them back again. Which can be a time
> > taking operation on larger tables.
> >
> > Proposal:
> > I propose adding an ALTER INDEX command that allows for enabling or disabling an index globally. This could look
somethinglike:
 
> >
> > ALTER INDEX index_name ENABLE;
> > ALTER INDEX index_name DISABLE;
> >
> > A disabled index would still receive updates and enforce constraints
> > as usual but would not be used for queries. This allows users to
> > assess whether an index impacts query performance before deciding to
> > drop it entirely.
> 
> I personally think having some way to alter an index to stop it from
> being used in query plans would be very useful for the reasons you
> mentioned.  I don't have any arguments against the syntax you've
> proposed.  We'd certainly have to clearly document that constraints
> are still enforced. Perhaps there is some other syntax which would
> self-document slightly better. I just can't think of it right now.
> 
> > Implementation:
> > To keep this simple, I suggest toggling the indisvalid flag in
> > pg_index during the enable/disable operation.
> 
> That's not a good idea as it would allow ALTER INDEX ... ENABLE; to be
> used to make valid a failed concurrently created index.  I think this
> would need a new flag and everywhere in the planner would need to be
> adjusted to ignore indexes when that flag is false.

How about the indislive flag instead? I haven't looked at the code, but
from the documentation ("If false, the index is in process of being
dropped, and
should be ignored for all purposes") it sounds like we made be able to
piggy-back on that instead?


Michael

On Tue, 10 Sept 2024 at 22:46, Michael Banck <mbanck@gmx.net> wrote:
> How about the indislive flag instead? I haven't looked at the code, but
> from the documentation ("If false, the index is in process of being
> dropped, and
> should be ignored for all purposes") it sounds like we made be able to
> piggy-back on that instead?

Doing that could cause an UPDATE which would ordinarily not be
eligible for a HOT-update to become a HOT-update. That would cause
issues if the index is enabled again as the index wouldn't have been
updated during the UPDATE.

I don't see the big deal with adding a new flag. There's even a free
padding byte to put this flag in after indisreplident, so we don't
have to worry about using more memory.

David

On Tue, Sep 10, 2024 at 10:16:34AM +1200, David Rowley wrote:
> I think the primary use case here is to assist in dropping useless
> indexes in a way that can very quickly be undone if the index is more
> useful than thought. If you didn't keep the index up-to-date then that
> would make the feature useless for that purpose.
> 
> If we get the skip scan feature for PG18, then there's likely going to
> be lots of people with indexes that they might want to consider
> removing after upgrading. Maybe this is a good time to consider this
> feature as it possibly won't ever be more useful than it will be after
> we get skip scans.

+1, this is something I've wanted for some time.  There was some past
discussion, too [0].

[0] https://postgr.es/m/flat/ed8c9ed7-bb5d-aaec-065b-ad4893645deb%402ndQuadrant.com

-- 
nathan

On Wed, 11 Sept 2024 at 03:12, Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Tue, Sep 10, 2024 at 10:16:34AM +1200, David Rowley wrote:
> > If we get the skip scan feature for PG18, then there's likely going to
> > be lots of people with indexes that they might want to consider
> > removing after upgrading. Maybe this is a good time to consider this
> > feature as it possibly won't ever be more useful than it will be after
> > we get skip scans.
>
> +1, this is something I've wanted for some time.  There was some past
> discussion, too [0].
>
> [0] https://postgr.es/m/flat/ed8c9ed7-bb5d-aaec-065b-ad4893645deb%402ndQuadrant.com

Thanks for digging that up. I'd forgotten about that.  I see there was
pushback from having this last time, which is now over 6 years ago.
In the meantime, we still have nothing to make this easy for people.

I think the most important point I read in that thread is [1]. Maybe
what I mentioned in [2] is a good workaround.

Additionally, I think there will need to be syntax in CREATE INDEX for
this. Without that pg_get_indexdef() might return SQL that does not
reflect the current state of the index. MySQL seems to use "CREATE
INDEX name ON table (col) [VISIBLE|INVISIBLE]".

David

[1] https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm%40alap3.anarazel.de
[2] https://www.postgresql.org/message-id/CAKJS1f_L7y_BTGESp5Qd6BSRHXP0mj3x9O9C_U27GU478UwpBw%40mail.gmail.com

On Mon, 23 Sept 2024 at 05:43, Shayon Mukherjee <shayonj@gmail.com> wrote:
> - Modified get_index_paths() and build_index_paths() to exclude disabled
>   indexes from consideration during query planning.

There are quite a large number of other places you also need to modify.

Here are 2 places where the index should be ignored but isn't:

1. expression indexes seem to still be used for statistical estimations:

create table b as select generate_series(1,1000)b;
create index on b((b%10));
analyze b;
explain select distinct b%10 from b;
-- HashAggregate  (cost=23.00..23.12 rows=10 width=4)

alter index b_expr_idx disable;
explain select distinct b%10 from b;
-- HashAggregate  (cost=23.00..23.12 rows=10 width=4) <-- should be 1000 rows

drop index b_expr_idx;
explain select distinct b%10 from b;
-- HashAggregate  (cost=23.00..35.50 rows=1000 width=4)

2. Indexes seem to still be used for join removals.

create table c (c int primary key);
explain select c1.* from c c1 left join c c2 on c1.c=c2.c; --
correctly removes join.
alter index c_pkey disable;
explain select c1.* from c c1 left join c c2 on c1.c=c2.c; -- should
not remove join.

Please carefully look over all places that RelOptInfo.indexlist is
looked at and consider skipping disabled indexes. Please also take
time to find SQL that exercises each of those places so you can verify
that the behaviour is correct after your change. This is also a good
way to learn exactly all cases where indexes are used. Using this
method would have led you to find places like
rel_supports_distinctness(), where you should be skipping disabled
indexes.

The planner should not be making use of disabled indexes for any
optimisations at all.

> - catversion.h is updated with a new CATALOG_VERSION_NO to reflect change in pg_index
>   schema.

Please leave that up to the committer. Patch authors doing this just
results in the patch no longer applying as soon as someone commits a
version bump.

Also, please get rid of these notices. The command tag serves that
purpose. It's not interesting that the index is already disabled.

# alter index a_pkey disable;
NOTICE:  index "a_pkey" is now disabled
ALTER INDEX
# alter index a_pkey disable;
NOTICE:  index "a_pkey" is already disabled
ALTER INDEX

I've only given the code a very quick glance. I don't quite understand
why you're checking the index is enabled in create_index_paths() and
get_index_paths(). I think the check should be done only in
create_index_paths().  Primarily, you'll find code such as "if
(index->indpred != NIL && !index->predOK)" in the locations you need
to consider skipping the disabled index. I think your new code should
be located very close to those places or perhaps within the same if
condition unless it makes it overly complex for the human reader.

I think the documents should also mention that disabling an index is a
useful way to verify an index is not being used before dropping it as
the index can be enabled again at the first sign that performance has
been effected. (It might also be good to mention that checking
pg_stat_user_indexes.idx_scan should be the first port of call when
checking for unused indexes)

David

Hi David,

Thank you so much for the review and pointers. I totally missed expression indexes. I am going to do another proper
passalong with your feedback and follow up with an updated patch and any questions.  

Excited to be learning so much about the internals.
Shayon

> On Sep 22, 2024, at 6:44 PM, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 23 Sept 2024 at 05:43, Shayon Mukherjee <shayonj@gmail.com> wrote:
>> - Modified get_index_paths() and build_index_paths() to exclude disabled
>>  indexes from consideration during query planning.
>
> There are quite a large number of other places you also need to modify.
>
> Here are 2 places where the index should be ignored but isn't:
>
> 1. expression indexes seem to still be used for statistical estimations:
>
> create table b as select generate_series(1,1000)b;
> create index on b((b%10));
> analyze b;
> explain select distinct b%10 from b;
> -- HashAggregate  (cost=23.00..23.12 rows=10 width=4)
>
> alter index b_expr_idx disable;
> explain select distinct b%10 from b;
> -- HashAggregate  (cost=23.00..23.12 rows=10 width=4) <-- should be 1000 rows
>
> drop index b_expr_idx;
> explain select distinct b%10 from b;
> -- HashAggregate  (cost=23.00..35.50 rows=1000 width=4)
>
> 2. Indexes seem to still be used for join removals.
>
> create table c (c int primary key);
> explain select c1.* from c c1 left join c c2 on c1.c=c2.c; --
> correctly removes join.
> alter index c_pkey disable;
> explain select c1.* from c c1 left join c c2 on c1.c=c2.c; -- should
> not remove join.
>
> Please carefully look over all places that RelOptInfo.indexlist is
> looked at and consider skipping disabled indexes. Please also take
> time to find SQL that exercises each of those places so you can verify
> that the behaviour is correct after your change. This is also a good
> way to learn exactly all cases where indexes are used. Using this
> method would have led you to find places like
> rel_supports_distinctness(), where you should be skipping disabled
> indexes.
>
> The planner should not be making use of disabled indexes for any
> optimisations at all.
>
>> - catversion.h is updated with a new CATALOG_VERSION_NO to reflect change in pg_index
>>  schema.
>
> Please leave that up to the committer. Patch authors doing this just
> results in the patch no longer applying as soon as someone commits a
> version bump.
>
> Also, please get rid of these notices. The command tag serves that
> purpose. It's not interesting that the index is already disabled.
>
> # alter index a_pkey disable;
> NOTICE:  index "a_pkey" is now disabled
> ALTER INDEX
> # alter index a_pkey disable;
> NOTICE:  index "a_pkey" is already disabled
> ALTER INDEX
>
> I've only given the code a very quick glance. I don't quite understand
> why you're checking the index is enabled in create_index_paths() and
> get_index_paths(). I think the check should be done only in
> create_index_paths().  Primarily, you'll find code such as "if
> (index->indpred != NIL && !index->predOK)" in the locations you need
> to consider skipping the disabled index. I think your new code should
> be located very close to those places or perhaps within the same if
> condition unless it makes it overly complex for the human reader.
>
> I think the documents should also mention that disabling an index is a
> useful way to verify an index is not being used before dropping it as
> the index can be enabled again at the first sign that performance has
> been effected. (It might also be good to mention that checking
> pg_stat_user_indexes.idx_scan should be the first port of call when
> checking for unused indexes)
>
> David

On 09.09.24 23:38, Shayon Mukherjee wrote:
> *Problem*:
> Adding and removing indexes is a common operation in PostgreSQL. On 
> larger databases, however, these operations can be resource-intensive. 
> When evaluating the performance impact of one or more indexes, dropping 
> them might not be ideal since as a user you may want a quicker way to 
> test their effects without fully committing to removing & adding them 
> back again. Which can be a time taking operation on larger tables.
> 
> *Proposal*:
> I propose adding an ALTER INDEX command that allows for enabling or 
> disabling an index globally. This could look something like:
> 
> ALTER INDEX index_name ENABLE;
> ALTER INDEX index_name DISABLE;
> 
> A disabled index would still receive updates and enforce constraints as 
> usual but would not be used for queries. This allows users to assess 
> whether an index impacts query performance before deciding to drop it 
> entirely.

I think a better approach would be to make the list of disabled indexes 
a GUC setting, which would then internally have an effect similar to 
enable_indexscan, meaning it would make the listed indexes unattractive 
to the planner.

This seems better than the proposed DDL command, because you'd be able 
to use this per-session, instead of forcing a global state, and even 
unprivileged users could use it.

(I think we have had proposals like this before, but I can't find the 
discussion I'm thinking of right now.)

That's a good point.

+1 for the idea of the GUC setting, especially since, as you mentioned, it allows unprivileged users to access it and
beingper-session.. 

I am happy to draft a patch for this as well. I think I have a working idea so far of where the necessary checks might
go.However if you don’t mind, can you elaborate further on how the effect would be similar to enable_indexscan?  

I was thinking we could introduce a new GUC option called `disabled_indexes` and perform a check against in all places
foreach index being considered with its OID via get_relname_relid through a helper function in the various places we
needto prompt the planner to not use the index (like in indxpath.c as an example). 

Curious to learn if you have a different approach in mind perhaps?

Thank you,
Shayon


> On Sep 23, 2024, at 11:14 AM, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 09.09.24 23:38, Shayon Mukherjee wrote:
>> *Problem*:
>> Adding and removing indexes is a common operation in PostgreSQL. On larger databases, however, these operations can
beresource-intensive. When evaluating the performance impact of one or more indexes, dropping them might not be ideal
sinceas a user you may want a quicker way to test their effects without fully committing to removing & adding them back
again.Which can be a time taking operation on larger tables. 
>> *Proposal*:
>> I propose adding an ALTER INDEX command that allows for enabling or disabling an index globally. This could look
somethinglike: 
>> ALTER INDEX index_name ENABLE;
>> ALTER INDEX index_name DISABLE;
>> A disabled index would still receive updates and enforce constraints as usual but would not be used for queries.
Thisallows users to assess whether an index impacts query performance before deciding to drop it entirely. 
>
> I think a better approach would be to make the list of disabled indexes a GUC setting, which would then internally
havean effect similar to enable_indexscan, meaning it would make the listed indexes unattractive to the planner. 
>
> This seems better than the proposed DDL command, because you'd be able to use this per-session, instead of forcing a
globalstate, and even unprivileged users could use it. 
>
> (I think we have had proposals like this before, but I can't find the discussion I'm thinking of right now.)
>

On Tue, 24 Sept 2024 at 03:14, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 09.09.24 23:38, Shayon Mukherjee wrote:
> > ALTER INDEX index_name ENABLE;
> > ALTER INDEX index_name DISABLE;
>
> I think a better approach would be to make the list of disabled indexes
> a GUC setting, which would then internally have an effect similar to
> enable_indexscan, meaning it would make the listed indexes unattractive
> to the planner.

I understand the last discussion went down that route too. For me, it
seems strange that adding some global variable is seen as cleaner than
storing the property in the same location as all the other index
properties.

How would you ensure no cached plans are still using the index after
changing the GUC?

> This seems better than the proposed DDL command, because you'd be able
> to use this per-session, instead of forcing a global state, and even
> unprivileged users could use it.

That's true.

> (I think we have had proposals like this before, but I can't find the
> discussion I'm thinking of right now.)

I think it's the one that was already linked by Nathan.  [1]? The GUC
seems to have been first suggested on the same thread in [2].

David

[1] https://www.postgresql.org/message-id/ed8c9ed7-bb5d-aaec-065b-ad4893645deb%402ndQuadrant.com
[2] https://www.postgresql.org/message-id/29800.1529359024%40sss.pgh.pa.us

On 23.09.24 22:51, Shayon Mukherjee wrote:
> I am happy to draft a patch for this as well. I think I have a working
> idea so far of where the necessary checks might go. However if you don’t
>   mind, can you elaborate further on how the effect would be similar to
> enable_indexscan?

Planner settings like enable_indexscan used to just add a large number 
(disable_cost) to the estimated plan node costs.  It's a bit more 
sophisticated in PG17.  But in any case, I imagine "disabling an index" 
could use the same mechanism.  Or maybe not, maybe the setting would 
just completely ignore the index.

On 24.09.24 02:30, David Rowley wrote:
> I understand the last discussion went down that route too. For me, it
> seems strange that adding some global variable is seen as cleaner than
> storing the property in the same location as all the other index
> properties.

It's arguably not actually a property of the index, it's a property of 
the user's session.  Like, kind of, the search path is a session 
property, not a property of a schema.

> How would you ensure no cached plans are still using the index after
> changing the GUC?

Something for the patch author to figure out. ;-)

Peter Eisentraut <peter@eisentraut.org> writes:
> On 23.09.24 22:51, Shayon Mukherjee wrote:
>> I am happy to draft a patch for this as well. I think I have a working
>> idea so far of where the necessary checks might go. However if you don’t
>> mind, can you elaborate further on how the effect would be similar to
>> enable_indexscan?

> Planner settings like enable_indexscan used to just add a large number 
> (disable_cost) to the estimated plan node costs.  It's a bit more 
> sophisticated in PG17.  But in any case, I imagine "disabling an index" 
> could use the same mechanism.  Or maybe not, maybe the setting would 
> just completely ignore the index.

Yeah, I'd be inclined to implement this by having create_index_paths
just not make any paths for rejected indexes.  Or you could back up
another step and keep plancat.c from building IndexOptInfos for them.
The latter might have additional effects, such as preventing the plan
from relying on a uniqueness condition enforced by the index.  Not
clear to me if that's desirable or not.

[ thinks... ]  One good reason for implementing it in plancat.c is
that you'd have the index relation open and be able to see its name
for purposes of matching to the filter.  Anywhere else, getting the
name would involve additional overhead.

            regards, tom lane

Thank you for the historical context and working, I understand what you were referring to before now.

Shayon

> On Sep 24, 2024, at 2:08 PM, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 23.09.24 22:51, Shayon Mukherjee wrote:
>> I am happy to draft a patch for this as well. I think I have a working
>> idea so far of where the necessary checks might go. However if you don’t
>>  mind, can you elaborate further on how the effect would be similar to
>> enable_indexscan?
>
> Planner settings like enable_indexscan used to just add a large number (disable_cost) to the estimated plan node
costs. It's a bit more sophisticated in PG17.  But in any case, I imagine "disabling an index" could use the same
mechanism. Or maybe not, maybe the setting would just completely ignore the index. 

On Mon, Sep 23, 2024 at 11:14 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> I think a better approach would be to make the list of disabled indexes
> a GUC setting, which would then internally have an effect similar to
> enable_indexscan, meaning it would make the listed indexes unattractive
> to the planner.
>
> This seems better than the proposed DDL command, because you'd be able
> to use this per-session, instead of forcing a global state, and even
> unprivileged users could use it.
>
> (I think we have had proposals like this before, but I can't find the
> discussion I'm thinking of right now.)

I feel like a given user could want either one of these things. If
you've discovered that a certain index is causing your production
application to pick the wrong index, disabling it and thereby
affecting all backends is what you want. If you're trying to
experiment with different query plans without changing anything for
other backends, being able to set some session-local state is better.
I don't understand the argument that one of these is categorically
better than the other.

--
Robert Haas
EDB: http://www.enterprisedb.com

> On Oct 7, 2024, at 4:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Sep 23, 2024 at 11:14 AM Peter Eisentraut <peter@eisentraut.org> wrote:
>> I think a better approach would be to make the list of disabled indexes
>> a GUC setting, which would then internally have an effect similar to
>> enable_indexscan, meaning it would make the listed indexes unattractive
>> to the planner.
>>
>> This seems better than the proposed DDL command, because you'd be able
>> to use this per-session, instead of forcing a global state, and even
>> unprivileged users could use it.
>>
>> (I think we have had proposals like this before, but I can't find the
>> discussion I'm thinking of right now.)
>
> I feel like a given user could want either one of these things. If
> you've discovered that a certain index is causing your production
> application to pick the wrong index, disabling it and thereby
> affecting all backends is what you want. If you're trying to
> experiment with different query plans without changing anything for
> other backends, being able to set some session-local state is better.
> I don't understand the argument that one of these is categorically
> better than the other.

Makes sense to me and it’s something I am somewhat split on as well. I suppose with a GUC you can still do some thing
like

ALTER USER foobar SET disabled_indexes to ‘idx_test_table_id’

[thinking…] This way all new sessions will start to not consider the index when query planning. Of course it does not
helpexisting sessions, so one may need to kill those backends, which could be heavy handed. 

Both these options clearly serve slightly different purposes with good pros and I am currently thinking if GUC is that
goodmiddle ground solution. 

Curious if someone has a stronger opinion on which one of these might make more sense perhaps :-D.

[thinking…] Unless - we try to do support both a GUC and the ALTER INDEX ENABLE/DISABLE grammar + isdisabled attribute
onpg_index? 

I can see that both implementations (GUC and the new attribute on pg_index via ALTER) have the primary logic managed by
`get_relation_info`in `plancat.c`. Here, we set `isdisabled` (new attribute) on `IndexOptInfo` and compare it against
`disabled_indexes`in the GUC (from the previous GUC patch). Similarly, for `pg_index`, which is already open in
`get_relation_info`,we can read from `pg_index.isdisabled` and accordingly update `IndexOptInfo.isdisabled`. 

[0] https://www.postgresql.org/message-id/6CE345C1-6FFD-4E4C-8775-45DA659C57CF@gmail.com

Thanks
Shayon

On Wed, 9 Oct 2024 at 20:07, Shayon Mukherjee <shayonj@gmail.com> wrote:
> [thinking…] Unless - we try to do support both a GUC and the ALTER INDEX ENABLE/DISABLE grammar + isdisabled
attributeon pg_index? 

I just wanted to explain my point of view on this.  This is my opinion
and is by no means authoritative.

I was interested in this patch when you proposed it as an ALTER INDEX
option. I know other committers seem interested, but I personally
don't have any interest in the GUC option.  I think the reason I
dislike it is that it's yet another not even half-baked take on
planner hints (the other one being enable* GUCs). I often thought that
if we ever did planner hints that it would be great to have multiple
ways to specify the hints. Ordinarily, I'd expect some special comment
type as the primary method to specify hints, but equally, it would be
nice to be able to specify them in other ways. e.g. a GUC to have them
apply to more than just 1 query. Useful for things such as "don't use
index X".

Now, I'm not suggesting you go off and code up planner hints. That's a
huge project. I'm just concerned that we've already got a fair bit of
cruft that will be left remaining if we ever get core planner hints
and a disabled_indexes GUC will just add to that. I don't feel like
the ALTER INDEX method would be leftover cruft from us gaining core
planner hints. Others might feel differently on that one. I feel the
ALTER INDEX option is less controversial.

I'll also stand by what I said earlier on this thread. If PeterG gets
index skip scans done for PG18, then it's likely there's going to be
lots of users considering if they still need a certain index or not
after upgrading to PG18.

David

On Wed, Oct 9, 2024 at 4:19 AM David Rowley <dgrowleyml@gmail.com> wrote:
> On Wed, 9 Oct 2024 at 20:07, Shayon Mukherjee <shayonj@gmail.com> wrote:
> > [thinking…] Unless - we try to do support both a GUC and the ALTER INDEX ENABLE/DISABLE grammar + isdisabled
attributeon pg_index? 
>
> I just wanted to explain my point of view on this.  This is my opinion
> and is by no means authoritative.
>
> I was interested in this patch when you proposed it as an ALTER INDEX
> option. I know other committers seem interested, but I personally
> don't have any interest in the GUC option.  I think the reason I
> dislike it is that it's yet another not even half-baked take on
> planner hints (the other one being enable* GUCs). I often thought that
> if we ever did planner hints that it would be great to have multiple
> ways to specify the hints. Ordinarily, I'd expect some special comment
> type as the primary method to specify hints, but equally, it would be
> nice to be able to specify them in other ways. e.g. a GUC to have them
> apply to more than just 1 query. Useful for things such as "don't use
> index X".

+1. A GUC can be done as a contrib module using existing hooks, and I
think that's already been done outside of core, perhaps multiple
times. That certainly doesn't mean we CAN'T add it as an in-core
feature, but I do think "yet another not even half-baked take on
planner hints" is a fair description. What I would personally like to
see is for us to ship one or possibly more than one contrib module
that let people do hint-like things in useful ways, and this could be
a part of that. But I think we need better infrastructure for
controlling the planner behavior first, hence the "allowing extensions
to control planner behavior" thread.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Sat, Oct 12, 2024 at 5:56 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> Thank you for sharing this Robert. I like the idea behind "allowing extensions to control planner behavior” overall
andI think it does help towards a powerful extension ecosystem too. I wonder if there is a reality where we can achieve
boththe outcomes here 
>
> - Support disabling of indexes [1] through ALTER command
> - While also building on "allowing extensions to control planner behavior” for the reasons above?
>
> [1] https://www.postgresql.org/message-id/ABD42A12-4DCF-4EE4-B903-4C657903CECE%40gmail.com

Yes, I think we can do both things.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Sat, 12 Oct 2024 at 22:41, Vinícius Abrahão <vinnix.bsd@gmail.com> wrote:
> You are going to disable the index but not the update of it? Why? Does it imply that when you are going to re-enable
ityou are going to recreate it? 

It might be worth you reading the discussion and proposed patches. I
think either of those would answer your questions.

I don't recall anyone ever proposing that re-enabling the index would
result in it having to be rebuilt. If that was a requirement, then I'd
say there does not seem much point in the feature. You might as well
just drop the index and recreate it if you change your mind.

David

On Wed, 16 Oct 2024 at 03:40, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Sat, Oct 12, 2024 at 5:56 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> > Thank you for sharing this Robert. I like the idea behind "allowing extensions to control planner behavior” overall
andI think it does help towards a powerful extension ecosystem too. I wonder if there is a reality where we can achieve
boththe outcomes here 
> >
> > - Support disabling of indexes [1] through ALTER command
> > - While also building on "allowing extensions to control planner behavior” for the reasons above?
> >
> > [1] https://www.postgresql.org/message-id/ABD42A12-4DCF-4EE4-B903-4C657903CECE%40gmail.com
>
> Yes, I think we can do both things.

I think so too.  I imagine there'd be cases where even hints global to
all queries running on the server wouldn't result in the index being
completely disabled.  For example, a physical replica might not be
privy to the hints defined on the primary and it might just be the
queries running on the physical replica that are getting the most use
out of the given index.  Having the change made in pg_index would mean
physical replicas have the index disabled too. For the primary use
case I have in mind (test disabling indexes you're considering
dropping), having the disabledness replicate would be very useful.

David

On 24.09.24 20:21, Tom Lane wrote:
> Peter Eisentraut <peter@eisentraut.org> writes:
>> On 23.09.24 22:51, Shayon Mukherjee wrote:
>>> I am happy to draft a patch for this as well. I think I have a working
>>> idea so far of where the necessary checks might go. However if you don’t
>>> mind, can you elaborate further on how the effect would be similar to
>>> enable_indexscan?
> 
>> Planner settings like enable_indexscan used to just add a large number
>> (disable_cost) to the estimated plan node costs.  It's a bit more
>> sophisticated in PG17.  But in any case, I imagine "disabling an index"
>> could use the same mechanism.  Or maybe not, maybe the setting would
>> just completely ignore the index.
> 
> Yeah, I'd be inclined to implement this by having create_index_paths
> just not make any paths for rejected indexes.  Or you could back up
> another step and keep plancat.c from building IndexOptInfos for them.
> The latter might have additional effects, such as preventing the plan
> from relying on a uniqueness condition enforced by the index.  Not
> clear to me if that's desirable or not.

Yes, I think you'd want that, because one of the purposes of this 
feature would be to test whether it's okay to drop an index.  So you 
don't want the planner to take any account of the index for its decisions.

On Wed, Oct 16, 2024 at 8:33 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> > Yeah, I'd be inclined to implement this by having create_index_paths
> > just not make any paths for rejected indexes.  Or you could back up
> > another step and keep plancat.c from building IndexOptInfos for them.
> > The latter might have additional effects, such as preventing the plan
> > from relying on a uniqueness condition enforced by the index.  Not
> > clear to me if that's desirable or not.
>
> Yes, I think you'd want that, because one of the purposes of this
> feature would be to test whether it's okay to drop an index.  So you
> don't want the planner to take any account of the index for its decisions.

I think this is right. I think we want to avoid invalidating the
index, so we still need to consider it in determining where HOT
updates must be performed, but we don't want to "improve" the plan
using the index if it's disabled.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Thu, Oct 17, 2024 at 9:59 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> My take away from whether or not an in-place update is needed on pg_index [1]
>
> - It’s unclear to me why it’s needed.
> - I understand the xmin would get incremented when using CatalogTupleUpdate to update indisenabled.
> - However, I haven’t been able to replicate any odd behavior locally or CI.
> - FWIW - REINDEX CONCURRENTLY (via index_swap),  index_constraint_create and few other places perform
CatalogTupleUpdateto update the relevant attributes as well. 
>
> Based on the above summary and after my testing I would like to propose a v3 of the patch. The only difference
betweenthis and v1 [2] is that the update of pg_index row happens via CatalogTupleUpdate. 
>
> [1]  https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm@alap3.anarazel.de
> [2] https://www.postgresql.org/message-id/EF2313B8-A017-4869-9B7F-A24EDD8795DE%40gmail.com

In-place updates are generally bad news, so I think this patch
shouldn't use them. However, someone will need to investigate whether
that breaks the indcheckxmin thing that Andres mentions in your [1],
and if it does, figure out what to do about it. Creating a test case
to show the breakage would probably be a good first step, to frame the
discussion.

--
Robert Haas
EDB: http://www.enterprisedb.com

> On Nov 5, 2024, at 10:55 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Oct 17, 2024 at 9:59 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
>> My take away from whether or not an in-place update is needed on pg_index [1]
>>
>> - It’s unclear to me why it’s needed.
>> - I understand the xmin would get incremented when using CatalogTupleUpdate to update indisenabled.
>> - However, I haven’t been able to replicate any odd behavior locally or CI.
>> - FWIW - REINDEX CONCURRENTLY (via index_swap),  index_constraint_create and few other places perform
CatalogTupleUpdateto update the relevant attributes as well. 
>>
>> Based on the above summary and after my testing I would like to propose a v3 of the patch. The only difference
betweenthis and v1 [2] is that the update of pg_index row happens via CatalogTupleUpdate. 
>>
>> [1]  https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm@alap3.anarazel.de
>> [2] https://www.postgresql.org/message-id/EF2313B8-A017-4869-9B7F-A24EDD8795DE%40gmail.com
>
> In-place updates are generally bad news, so I think this patch
> shouldn't use them. However, someone will need to investigate whether
> that breaks the indcheckxmin thing that Andres mentions in your [1],
> and if it does, figure out what to do about it. Creating a test case
> to show the breakage would probably be a good first step, to frame the
> discussion.

Hello,

Thank you for the guidance and tips. I was wondering if updating in-place is preferable or not, since my first instinct
wasto avoid it. I did not notice any breakage last time, unless I was looking in the wrong place (possibly?). I did
noticethe min update when a not-in-place update was performed, but I didn't notice any issues (as mentioned in [1])
fromit, via logs, index usage, or other common operations. Let me write up some more test cases to check if there is a
riskof indexcheckxmin running out or other issues, and I'll get back here. 

Thank you
Shayon

[1] https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm@alap3.anarazel.de

>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com

On Tue, 26 Nov 2024 at 11:34, Shayon Mukherjee <shayonj@gmail.com> wrote:
> Thank you for the guidance and tips. I was wondering if updating in-place is preferable or not, since my first
instinctwas to avoid it. I did not notice any breakage last time, unless I was looking in the wrong place (possibly?).
Idid notice the min update when a not-in-place update was performed, but I didn't notice any issues (as mentioned in
[1])from it, via logs, index usage, or other common operations. Let me write up some more test cases to check if there
isa risk of indexcheckxmin running out or other issues, and I'll get back here. 

Another safer option could be to disallow the enable/disable ALTER
TABLE if indcheckxmin is true. We do have and use
ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE for these sorts of issues.
There are other existing failure modes relating to indexes that can
depend on what another session has done, e.g. MarkInheritDetached()
can fail if another session detached an index concurrently. I could
respect an argument that this one might be worse than that as its
timing dependent rather than dependent on what another session has
done.

David

> Rebased with the latest master as well.

Hi,

This is a great, long needed feature. Thanks for doing this.

I am late to this thread, but I took a look at the current patch
and have some comments as I continue to look.

1/
instead of

+       If true, the index is currently enabled and should be used for queries.
+       If false, the index is disabled and should not be used for queries,

how about?

"If true, the index is currently enabled and may be used for queries.
If false, the index is disabled and may not be used for queries,"

"may" is more accurate than "should" in this context.

2/
instead of

+       but is still maintained when the table is modified. Default is true.

how about?

"but is still updated when the table is modified. Default is true."

"update" of an index is the current verb used. See bottom of
https://www.postgresql.org/docs/current/indexes-intro.html

3/
instead of saying "used by the query planner for query optimization",
can it just be "The index will be used for queries."

+     <para>
+      Enable the specified index. The index will be used by the query planner
+      for query optimization. This is the default state for newly
created indexes.
+     </para>

Same for

+    <listitem>
+     <para>
+      Disable the specified index. A disabled index is not used by
the query planner
+      for query optimization, but it is still maintained when the
underlying table
+      data changes and will still be used to enforce constraints
(such as UNIQUE,
+      or PRIMARY KEY constraints).

4/ Should documentation recommend a direct catalog update?

+      to identify potentially unused indexes. Note that if you want
to completely
+      prevent an index from being used, including for constraint
enforcement, you
+      would need to mark it as invalid using a direct update to the
system catalogs
+      (e.g., <literal>UPDATE pg_index SET indisvalid = false WHERE
indexrelid = 'index_name'::regclass</literal>).

"indisvalid" does not control constraint enforcement in this case. It will be
"indisready" being set to false that will.

But even then, this goes against the general principle ( also documnted )
of not updating the catalog directly. See [1]

I think this part should be removed.

5/

In a case of a prepared statement, disabling the index
has no effect.

postgres=# create table foo ( id int primary key );
CREATE TABLE
postgres=# prepare prp as select * from foo where id = 1;
PREPARE
postgres=# explain analyze execute prp;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1
width=4) (actual time=0.018..0.019 rows=0 loops=1)
   Index Cond: (id = 1)
   Heap Fetches: 0
   Buffers: shared hit=2
 Planning:
   Buffers: shared hit=15 read=7
 Planning Time: 2.048 ms
 Execution Time: 0.071 ms
(8 rows)

postgres=# alter index foo_pkey disable ;
ALTER INDEX
postgres=# explain analyze execute prp;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1
width=4) (actual time=0.035..0.036 rows=0 loops=1)
   Index Cond: (id = 1)
   Heap Fetches: 0
   Buffers: shared hit=2
 Planning Time: 0.012 ms
 Execution Time: 0.320 ms
(6 rows)

Should this not behave like if you drop (or create) an index
during a prepared statement? I have not yet looked closely at
this code to see what could be done.

Regards,

Sami Imseih
Amazon Web Services (AWS)


[1] https://www.postgresql.org/docs/current/catalogs.html

On Sun, Sep 22, 2024 at 3:45 PM David Rowley <dgrowleyml@gmail.com> wrote:

> I think the documents should also mention that disabling an index is a
> useful way to verify an index is not being used before dropping it as
> the index can be enabled again at the first sign that performance has
> been effected. (It might also be good to mention that checking
> pg_stat_user_indexes.idx_scan should be the first port of call when
> checking for unused indexes)

While reviewing Shayon's v14 patch, I had removed text (quoted below) from the
ALTER INDEX docs that did not feel right in a command reference. I thought
of reading up on the history/discussion of the patch, and now I see why Shayon
chose to include an advice in ALTER INDEX docs.

> + indexes. If performance degrades after making an index invisible, it can be easily
> + be made visible using <literal>VISIBLE</literal>. Before making an index invisible, it's recommended
> + to check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
> + to identify potentially unused indexes.

I feel ALTER INDEX command reference doc is not the right place for this kind of
operational advice. Is there a better place in documentation for this kind of
advice? Or maybe it needs to be reworded to fit the command reference style?

Best regards,
Gurjeet
http://Gurje.et

> > + indexes. If performance degrades after making an index invisible, it can be easily
> > + be made visible using <literal>VISIBLE</literal>. Before making an index invisible, it's recommended
> > + to check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
> > + to identify potentially unused indexes.
>
> I feel ALTER INDEX command reference doc is not the right place for this kind of
> operational advice. Is there a better place in documentation for this kind of
> advice? Or maybe it needs to be reworded to fit the command reference style?

I agree with you.

What about we add this wording in the following section [0]? This
section discusses techniques
for experimenting with indexes. It says,
".... A good deal of experimentation is often necessary. The rest of
this section gives some tips for that:...."

A discussion about invisible indexes as one of the tools for
experimentation can be added here.
What do you think?

[0] https://www.postgresql.org/docs/current/indexes-examine.html

--
Sami Imseih
Amazon Web Services (AWS)

On Wed Apr 2, 2025 at 6:58 PM PDT, Sami Imseih wrote:
>> > + indexes. If performance degrades after making an index invisible, it can be easily
>> > + be made visible using <literal>VISIBLE</literal>. Before making an index invisible, it's recommended
>> > + to check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
>> > + to identify potentially unused indexes.
>>
>> I feel ALTER INDEX command reference doc is not the right place for this kind of
>> operational advice. Is there a better place in documentation for this kind of
>> advice? Or maybe it needs to be reworded to fit the command reference style?
>
> I agree with you.
>
> What about we add this wording in the following section [0]? This
> section discusses techniques
> for experimenting with indexes. It says,
> ".... A good deal of experimentation is often necessary. The rest of
> this section gives some tips for that:...."
>
> A discussion about invisible indexes as one of the tools for
> experimentation can be added here.
> What do you think?
>
> [0] https://www.postgresql.org/docs/current/indexes-examine.html

That seems like a very good location for this advice. But the current
set of bullet points are all directed towards "... a general procedure
for determining which indexes to create". I propose that a new paragrph,
not a bullet point, be added towards the end of that section which
addresses the options of steps to take before dropping an index.
Something like the following:

Sometimes you may notice that an index is not being used anymore by the
application queries. In such cases, it is a good idea to investigate if
such an index can be dropped, because an index that is not being used
for query optimization still consumes resources and slows down INSERT,
UPDATE, and DELETE commands. To aid in such an investigation, look at
the pg_stat_user_indexes.idx_scan count for the index.

To determine the performance effects of dropping the index, without
actually dropping the said index, you may mark the index invisible to
the planner by using the ALTER INDEX ... INVISIIBLE command. If it turns
out that doing so causes a performance degradation, the index can be
quickly made visible to the planner for query optimization by using the
ALTER INDEX ... VISIBLE command.

Thoughts?

Best regards,
Gurjeet
http://Gurje.et

> That seems like a very good location for this advice. But the current
> set of bullet points are all directed towards "... a general procedure
> for determining which indexes to create". I propose that a new paragrph,
> not a bullet point, be added towards the end of that section which
> addresses the options of steps to take before dropping an index.
> Something like the following:

> Thoughts?

This new feature provides the ability to experiment with indexes to
create ( or drop ),
so I don't think it deviates from the purpose of this paragraphs.

This patch will provide the ability for the user to create an index as initially
invisible and a GUC, use_invisible_index if set to TRUE to experiment with
the new index to see if it improves performance. So, I think providing this
pattern to experiment with a new index will fit nicely as a new bulletpoint.

--
Sami Imseih
Amazon Web Services (AWS)

Thanks for the update!

The changes in v15 look good to me. The patch does need to be rebased,
and I also think you should add a tab-complete for CREATE INDEX


simseih@bcd07415af92 postgresql % git diff
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8e2eb50205e..f1853a68ccc 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3434,6 +3434,8 @@ match_previous_words(int pattern_id,
                         !TailMatches("POLICY", MatchAny, MatchAny,
MatchAny, MatchAny, MatchAny) &&
                         !TailMatches("FOR", MatchAny, MatchAny, MatchAny))
                COMPLETE_WITH("(");
+       else if (TailMatches("*)"))
+               COMPLETE_WITH("VISIBLE", "INVISIBLE");

        /* CREATE OR REPLACE */
        else if (Matches("CREATE", "OR"))

IMO, with the above in place, this patch is RFC.

--
Sami Imseih
Amazon Web Services (AWS)

> Attached v16 with feedback and rebased.

Thanks, and I realized the original tab-complete I proposed
was not entirely correct. I fixed it and also shortened the
commit message.


--
Sami Imseih
Amazon Web Services (AWS)

hi.
The following is a review of version 17.

ATExecSetIndexVisibility
    if (indexForm->indisvisible != visible)
    {
        indexForm->indisvisible = visible;
        CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
        CacheInvalidateRelcache(heapRel);
        InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0);
        CommandCounterIncrement();
    }
I am slightly confused. if we already used
CommandCounterIncrement();
then we don't need CacheInvalidateRelcache?


doc/src/sgml/ref/alter_index.sgml
  <para>
   <command>ALTER INDEX</command> changes the definition of an existing index.
   There are several subforms described below. Note that the lock level required
   may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal>
lock is held
   unless explicitly noted. When multiple subcommands are listed, the lock
   held will be the strictest one required from any subcommand.

per the above para, we need mention that ALTER INDEX SET INVISIBLE|INVISIBLE
only use ShareUpdateExclusiveLock?


index_create is called in several places,
most of the time, we use INDEX_CREATE_VISIBLE.
if we define it as INDEX_CREATE_INVISIBLE rather than INDEX_CREATE_VISIBLE
then argument flags required code changes would be less, (i didn't try
it myself)


Similar to get_index_isclustered,
We can place GetIndexVisibility in
src/backend/utils/cache/lsyscache.c,
make it an extern function, so others can use it;
to align with other function names,
maybe rename it as get_index_visibility.


create index v2_idx on v1(data) visible;
is allowed,
doc/src/sgml/ref/create_index.sgml
<synopsis> section need to change to
[ VISIBLE | INVISIBLE ]

?

hi, two more minor issues.

src/bin/pg_dump/pg_dump.c
if (fout->remoteVersion >= 180000)
need change to
if (fout->remoteVersion >= 190000)


+-- Test index visibility with partitioned tables
+CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100);
+CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200);
+INSERT INTO part_tbl
+SELECT g, 'data ' || g
+FROM generate_series(1, 199) g;
+CREATE INDEX idx_part_tbl ON part_tbl(data);
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+   relname    | indisvisible
+--------------+--------------
+ idx_part_tbl | t
+(1 row)
+

This test seems not that good?
"idx_part_tbl" is the partitioned index, we also need to show each
partition index
pg_index.indisvisible value?

we can change it to
--------
CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id);
CREATE TABLE part_tbl_1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100);
CREATE TABLE part_tbl_2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200);
CREATE INDEX ON part_tbl(data);
SELECT c.relname, i.indisvisible
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relname LIKE 'part_tbl%'
ORDER BY c.relname;
-----

On Mon, Apr 28, 2025 at 7:23 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> On Thu, Apr 24, 2025 at 12:45 AM jian he <jian.universality@gmail.com> wrote:
>>
> Thank you for the feedback. I have also updated the feedback from [1] as well. Few extra notes:
>
> - Attached v18
> - Rebased against master
> - Updated the commit message
> - Updated the target remote version to now be fout->remoteVersion >= 190000
> - Using a UNION ALL query to show all indexes from part_tbl partitioned tables in the specs as noted in [1]. The
querysuggested in [1] wasn't encompassing all the indexes, hence the UNION ALL for WHERE i.indrelid =
'part_tbl'::regclass::oid.
>

Having looked at this patch, I'm a bit surprised that it would be
considered for commit; not that the work hasn't been done with rigor,
but the implementation seems extremely obtuse for the common use cases
that have been envisioned.

As a primary example, imagine you have 2 indexes and you want to test
if one index can handle the load via skip scans. With this feature, in
order to do that SAFELY, you would need to first figure out how to
ensure that the `force_invisible_index` GUC has been set to true
across all possible backends, even though there seems like a general
agreement that there isn't an easy way to do this (see comments around
cached plans), and to make it more complicated, this needs to occur
across all downstream replicas. Only then would it be safe to run the
alter index to set your index invisible, at which point you could then
test at the query/session level to determine which queries will be
supportable without the risk of having your server(s) tank due to
overload when you start getting hundreds of queries who plan has gone
sideways. Ideally you would be able to do this in the opposite
fashion; start with a session level guc that allows you to test in a
controlled manner, and then if that works you start to roll that out
across multiple sessions, and then to multiple servers, before
eventually dropping the index.

But that isn't the only gap; imagine if you want to test across 3 or
more indexes; with this implementation, the "use invisible" flag is
all or nothing, which again makes it difficult to work with;
especially if you have multiple cases within the system that might
make use of this feature (and people will surely run invisible indexes
for weeks in production to ensure some random monthly report doesn't
come along and cause trouble). I'm also skeptical of the idea that
users need a way to add invisible indexes they can then test to see if
they are useful because 1) this is basically how indexes already work,
meaning if you add an index and it isn't useful, it doesn't get used,
and 2) we have an extension (hypopg) which arguably provides this
functionality without causing a bunch of i/o, and there isn't nearly
the clamor to add this functionality in to core as there is for having
a way to "soft drop" indexes. TBH, with this implementation, I can see
people running with all indexes set invisible and
force_invisible_index set to true, just to enable simple granular
control when they need it.

I know this thread is rather old and there doesn't seem to be full
agreement on the ALTER vs GUC implementation idea, and even though I
agree with the sentiment that the GUC system is little more than the
"half-baked take on planner hints", the upside of GUC first
implementations is that they tend to provide better usability than
most grammer related implementations. Consider that any implementation
which requires the use of ALTER statements (which this one does)
undercuts its own usefulness because it adds significant operational
risk in any attempt to use it just by the nature of ALTER leading to
system-wide (including multi-server) changes, and while it feels like
we often dismiss operational risk, those are exactly the folks who
need this feature the most.

P.S. I really do want to thank Shayon for sticking with this; I
thought about saying that up front but it felt cliche, but I do think
it is important to say it.

Robert Treat
https://xzilla.net

On Fri, 6 Jun 2025 at 08:14, Robert Treat <rob@xzilla.net> wrote:
> I know this thread is rather old and there doesn't seem to be full
> agreement on the ALTER vs GUC implementation idea, and even though I
> agree with the sentiment that the GUC system is little more than the
> "half-baked take on planner hints", the upside of GUC first
> implementations is that they tend to provide better usability than
> most grammer related implementations. Consider that any implementation
> which requires the use of ALTER statements (which this one does)
> undercuts its own usefulness because it adds significant operational
> risk in any attempt to use it just by the nature of ALTER leading to
> system-wide (including multi-server) changes, and while it feels like
> we often dismiss operational risk, those are exactly the folks who
> need this feature the most.

Thanks for weighing in.

In my mind, this feature is for "I'm almost 100% certain this index
isn't needed, I want to make sure I'm 100% right in a way that I can
quickly fix the ensuing chaos if I'm wrong".  It sounds like in your
mind it's "I want to run some experiments to see if this index is
needed or not". I think both have merit, but I think the former gets
you closer to 100% certainty, as it'll be replicated to physical
replica servers.

I'd personally be looking at something like pg_stat_all_indexes
instead of playing around with session-level GUC setting to figure out
if an index was being used or not and I'd be looking to the ALTER
TABLE once I'd seen nothing changing in pg_stat_all_indexes for some
time period. I mean, what am I really going to do in session-level
GUC? -- Run all possible queries that the application runs and check
they're still fast? If I could do that, then I could equally just not
use the GUC and look at EXPLAIN on all those queries to see if the
index is picked anywhere.

Maybe we need to hear from a few more people who have recently faced
the dilemma of removing a seemingly unused index on a critical
application.

For me, I have been in this situation before. The database wasn't
massive. I could likely have put the index back in 10 mins or so.
However, it'd still have been nice to have something else to try
before trying DROP INDEX. It's quite easy to imagine your finger
hovering over the [Enter] key for a while before typing that statement
when the index is large.

> P.S. I really do want to thank Shayon for sticking with this;

+1

David

> Thanks for weighing in.
+1

> In my mind, this feature is for "I'm almost 100% certain this index
> isn't needed, I want to make sure I'm 100% right in a way that I can
> quickly fix the ensuing chaos if I'm wrong".

This is the primary use-case. A user performs an ALTER INDEX...
INVISIBLE, and they monitor the workload and pg_stat_all_indexes
( on primary and hot standbys ) until they feel confident enough
to fully commit to dropping the index. This is the case that many
users out there want. The bonus is the locking acquired to flip
the VISIBLE/INVISIBLE flag is a ShareUpdateExclusiveLock
on the index, so this operation can only be blocked by VACUUM
or other ALTERs, etc,

> I'm also skeptical of the idea that
> users need a way to add invisible indexes they can then test to see if
> they are useful because 1) this is basically how indexes already work,
> meaning if you add an index and it isn't useful, it doesn't get used,

The GUC will be useful for experimentation or for the safer rollout of
new indexes. For example, an index can be created as INVISIBLE initially,
and with use_invisible_index, one can observe how the index may impact
various queries before fully committing to enabling it. Also, if we allow an
index to be INVISIBLE initially, we need to provide the user with this
GUC; otherwise, I can’t see why a user would want to make an
index INVISIBLE initially.

> and 2) we have an extension (hypopg) which arguably provides this
> functionality without causing a bunch of i/o, and there isn't nearly
> the clamor to add this functionality in to core as there is for having
> a way to "soft drop" indexes.

I have not worked much with HypoPG, but from what I understand,
it works only at the EXPLAIN level. It is purely an experimentation tool.
However, the proposed GUC can also be used in more places,
including, pg_hint_plan ( at least with the SET hint without any changes
to pg_hint_plan).

> > P.S. I really do want to thank Shayon for sticking with this;

> +1
+1

--
Sami Imseih
Amazon Web Services (AWS)

> Don't get me wrong, it would be an improvement to have some type of
> mechanism that can move you from almost 100% to 100%, but the real
> problem is how do you SAFELY get to almost 100% in the first place?

This big use case is precisely the "almost 100% to 100%"  confidence problem.
Usually, users have done their homework, they've analyzed
workloads, tuned queries and maybe created a better index. Now, they see some
indexes that are unused or underused. In the current state, the only
option is to drop the
index. But if that turns out to be a mistake, they have to rebuild it, which
can be slow and disruptive. With this feature, If making the index
invisible causes
problems, they can quickly make it visible again without needing to
rebuild anything.

Also, users coming from other databases, both commercial and open source, are
already used to this kind of setup: an ALTER command for visibility, plus a
parameter to control whether invisible indexes are used on a per session level.
So we're not inventing something new here; we're following a well-known and
useful pattern that makes life easier, especially for users migrating to
Postgres.

I am still trying to understand. Do you think the ALTER command is not useful?
or, do you think the GUC is all we need and it should be more granular?
or maybe something different?

--
Sami

On Fri, 6 Jun 2025 at 14:32, Robert Treat <rob@xzilla.net> wrote:
> In production, you aren't watching to see what happen with pg_stat_all_indexes, because you will first be watching
pg_stat_activityto see if the plans have flipped in some way that leads to an overloaded server (extra latency, poor
cachingeffects, extra buffers usage, etc). And the replicated bit? Sadly someone launched some big DML operation so
you'rewaiting for that to finish so the "quick rollback" can actually get to those other servers. 

I think you've misunderstood when you'd be looking at
pg_stat_all_indexes. The time when you'd want to look at
pg_stat_all_indexes is *before* you DROP INDEX and before you ALTER
TABLE INVISIBLE the index. What you'd likely want to look for there
are indexes that have the last_idx_scan set to something far in the
past or set to NULL.

I'm curious to know if you've ever had to drop an index out of
production before? What did you think about when you'd just typed the
DROP INDEX command and were contemplating your future? How long did
you pause before pressing [Enter]?

Can you list your proposed series of steps you'd recommend to a DBA
wishing to remove an index, assuming this feature exists in core as
you'd like it to?

David

On Fri, Jun 6, 2025 at 8:04 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Fri, 6 Jun 2025 at 14:32, Robert Treat <rob@xzilla.net> wrote:
> > In production, you aren't watching to see what happen with pg_stat_all_indexes, because you will first be watching
pg_stat_activityto see if the plans have flipped in some way that leads to an overloaded server (extra latency, poor
cachingeffects, extra buffers usage, etc). And the replicated bit? Sadly someone launched some big DML operation so
you'rewaiting for that to finish so the "quick rollback" can actually get to those other servers. 
>
> I think you've misunderstood when you'd be looking at
> pg_stat_all_indexes. The time when you'd want to look at
> pg_stat_all_indexes is *before* you DROP INDEX and before you ALTER
> TABLE INVISIBLE the index. What you'd likely want to look for there
> are indexes that have the last_idx_scan set to something far in the
> past or set to NULL.
>

I guess you have never heard of the TREAT method of index management? :-D
- Test for duplicate indexes
- Reindex bloated indexes
- Eliminate unused indexes
- Add missing indexes
- Tune indexes for generic queries

The easy part of figuring out what to change, the hard part
(sometimes) is getting those changes into production safely; that's
the part I am focused on.

> I'm curious to know if you've ever had to drop an index out of
> production before? What did you think about when you'd just typed the
> DROP INDEX command and were contemplating your future? How long did
> you pause before pressing [Enter]?
>

ROFL... Uh... yes, I have had to do it at least a few times.

So, years ago I used to say things like "I wish we had a way to make
indexes invisible like they do in Oracle" on the regular; but as I
worked through several different implementations and their potential
effects, and had more and more exposure to more demanding Postgres
installations, my thinking evolved. I spoke with Sami a bit about this
off-list and he walked me through some of the Oracle documentation on
this (I had, at best, forgot the specifics), which I think was helpful
to better understand some of the allure of the alter index/guc method
for many people who are used to it (and this current version of the
implementation is very Oracle like), but it also crystalized my
feeling that an Oracle-style implementation would be a red herring
that can keep us from a better solution.

> Can you list your proposed series of steps you'd recommend to a DBA
> wishing to remove an index, assuming this feature exists in core as
> you'd like it to?
>

Well, the series of steps differs depending on the nature of the
system being managed. If you are running on a single node with normal
traffic and resources, you just set the GUC to include the index you
want to be invisible, wait for a few days (maybe no one runs monthly
reports on this system?), take a quick look at your monitoring/stats
to make sure things seem copacetic, and then you drop the index and
reset the GUC.

But of course the people who I am most worried about are the ones who
are operating on high scale, high transaction, high connection,
"mission critical" systems... ie. people operating in high risk
environments, where things can go very bad very fast. Where safety
considerations are a critical part of every deployment.

In that type of environment, the GUC-only method enables you to
control changes at very precise levels, so you can do things like:
- run it ad-hoc at the session level to confirm that the explain plans
you get in production match your expectations.
- you can stay ad-hoc at the session level and run explain analyze and
confirm acceptable performance within your workload, and see what kind
of buffer impact you are going to have (typically overlooked, but a
potential landmine for outages, but I'll come back to this)
- because we are operating at the session level, we can then add this
on a per query basis at the application level, and in really high
traffic scenarios, you can use canary releases and/or feature flags to
ramp up those new queries into the live system.
- depending on how much risk you are concerned about, you can use this
session level method across queries individually, or at some point
roll it up to a user/application level. And again, we can roll it out
to different users at different times if you want.
- at some point when you feel confident that you have covered enough
angles, you set the GUC globally and let that marinate for a few more
weeks as needed.

And the funny thing is, at this point, once you have the guc put in
globally, and it's run for some number of weeks or months and everyone
is confident, you don't actually need the ALTER INDEX part any more;
you can just drop the index and be done with it. Now of course if you
aren't running at this kind of scale or don't have this level of risk,
you can speed run this a bit and go directly to the user level or skip
right to adding it globally, so the ease of use is on par with using
ALTER. But in any case where you do have elevated levels of risk, this
is actually less steps (and less risk) that having to use the
ALTER/guc method.

Earlier I mentioned the idea of monitoring buffer impact; let's talk
about that. I often hear people say that you should be doing things
like confirming your explain plans in development or have some type of
staging system where you do these kind of "experiments", as if a test
on a secondary system could really give you absolute confidence when
deploying to a system that automatically updates its settings (ie
pg_stats) at semi-random times with randomly sampled values; but in
any case, most people will at least agree that there is no way to
match up buffer usage across machines. That means if we are making
production changes that might have a significant impact on buffers, we
are doing something inherently dangerous. Well, dropping an index is
one of those things.

Imagine a scenario where you have a large index on a column and a
similar partial index on the same column, which are both used in
production for different queries, and therefore taking up some amount
of space within the buffer pool. When you make the partial index
invisible, the index is still maintained, and therefore it likely
still needs to maintain pages within the buffer pool to stay updated.
However, with queries now shifting to the full index, the full index
may very well need to pull in additional pages into the buffer pool
that it didn't need before, and this action can cause other pages from
some unknown object to get evicted. If you are lucky, this all works
itself and nothing bad happens, if you aren't, you may end up with a
server overloaded by latency in queries that aren't even related to
the indexes you're working on. (If you have a hard time seeing it with
partial indexes, the same can happen with consolidating indexes with
different INCLUDE statements, and certainly will be a scenario when
people look to drop indexes by way of skip-scan based plans). Now, is
it possible to handle this with the ALTER/guc method? Well, you can
mitigate it somewhat, but ironically to do so requires pushing out the
guc part of the ALTER/guc to all the places you would have pushed out
the GUC-only method, and that has to have been done BEFORE running
ALTER INDEX, so what does it really buy you?

I suppose while we're here, let me also make some observations about
how these methods differ when dealing with replica clusters. You
mentioned that one of the things you liked about the ALTER/guc method
is that it replicates the changes across all systems which makes it
easy to revert, however I believe that thinking is flawed. For
starters, any change that has to occur across the WAL stream is not
something that can be relied on to happen quickly; there are too many
other items that traverse that space that could end up blocking a
rollback from being applied in a timely fashion. The more complex the
replica cluster, the worse this is. One very common use case is to run
different workloads on different nodes, with the ALTER/guc method, you
are forcing users to make changes on a primary when they want to
target a workload that only runs on a replica. This means I have to
account for all potential workloads on all clusters before I can
safely start making changes, and to the degree that the ALTER/guc
gives me a safety net, that safety net is... to deploy a guc globally,
one at a time, on each individual server.

I feel like this email is already long, and tbh I could go on even
more, but hopefully I've covered enough to help explain some of the
issues that are involved here. I'm not trying to say that GUC-only is
a perfect solution, but I do think it handles every use case on par
with ALTER/guc, and enables some use cases ALTER/guc can't, especially
for people who have to operate in risk-first environments. And I get
it that some people are going to want a thing that looks very simple
or is familiar to how Oracle did it, but I can't help but think this
is one of those cases like how people used to always ask us to
implement UPSERT because that's what MySQL had, but instead we gave
them INSERT ON CONFLICT because it was the better solution to the
problem they (actually) had.


Robert Treat
https://xzilla.net

> In that type of environment, the GUC-only method enables you to
> control changes at very precise levels, so you can do things like:
> - run it ad-hoc at the session level to confirm that the explain plans
> you get in production match your expectations.
> - you can stay ad-hoc at the session level and run explain analyze and
> confirm acceptable performance within your workload, and see what kind
> of buffer impact you are going to have (typically overlooked, but a
> potential landmine for outages, but I'll come back to this)
> - because we are operating at the session level, we can then add this
> on a per query basis at the application level, and in really high
> traffic scenarios, you can use canary releases and/or feature flags to
> ramp up those new queries into the live system.
> - depending on how much risk you are concerned about, you can use this
> session level method across queries individually, or at some point
> roll it up to a user/application level. And again, we can roll it out
> to different users at different times if you want.
> - at some point when you feel confident that you have covered enough
> angles, you set the GUC globally and let that marinate for a few more
> weeks as needed.

Do we need this level of granular control in core, or should this be
delegated to other tools in the ecosystem, like pg_hint_plan? The de
facto tool for influencing planning.
There is probably some work that must happen in that extension to make
the use-cases above work, but it is something to consider.

With that said, I am not really opposed to a multi-value GUC that takes
in a list of index names, but I do have several concerns with that
approach being available in core:

1. The list of indexes getting too long, and the potential performance
impact of having to translate the index name to a relid to find which
index to make "invisible". I don't think a list of index relids will
be good from a usability perspective either.

2. A foot-gun such as adding an index name to my list, dropping the
index, recreating it with the same name, and now my new index is not
being used.

3. not sync'd up with the replica, so manual work is required there. That
could be seen as a positive aspect of this approach as well.

4. The above points speak on the level of maintenance required for this.

> You mentioned that one of the things you liked about the ALTER/guc method
> is that it replicates the changes across all systems which makes it
> easy to revert, however I believe that thinking is flawed. For
> starters, any change that has to occur across the WAL stream is not
> something that can be relied on to happen quickly; there are too many
> other items that traverse that space that could end up blocking a
> rollback from being applied in a timely fashion.

This is not going to be unique to this feature though. Other critical
DDLs will be blocked, so this is a different problem, IMO.

> but it also crystalized my
> feeling that an Oracle-style implementation would be a red herring
> that can keep us from a better solution.

Going back to this point, I still think that the ALTER option is useful
after the user's confidence is near 100% and they are ready to drop
the index for good, and which also gets replicated.

The GUC is useful for experimentation or for users that want to do a
slow rollout of dropping an index. We can discuss whether this should
be a multi-value setting or a boolean in core, or if it should be
delegated to an extension.

Essentially, I don't think we need to choose one or the other, but
perhaps we can improve upon the GUC.


--
Sami

On Sun, 8 Jun 2025 at 01:35, Robert Treat <rob@xzilla.net> wrote:
>
> On Fri, Jun 6, 2025 at 8:04 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > Can you list your proposed series of steps you'd recommend to a DBA
> > wishing to remove an index, assuming this feature exists in core as
> > you'd like it to?
> >
>
> Well, the series of steps differs depending on the nature of the
> system being managed. If you are running on a single node with normal
> traffic and resources, you just set the GUC to include the index you
> want to be invisible, wait for a few days (maybe no one runs monthly
> reports on this system?), take a quick look at your monitoring/stats
> to make sure things seem copacetic, and then you drop the index and
> reset the GUC.

Thanks for explaining.

What are your thoughts on cached plans? In this scenario, do you
assume that waiting a few days means that connections get reset and
prepared statements will have been replanned? Or do you think cached
plans don't matter in this scenario?

David

On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Sun, 8 Jun 2025 at 01:35, Robert Treat <rob@xzilla.net> wrote:
> > On Fri, Jun 6, 2025 at 8:04 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > > Can you list your proposed series of steps you'd recommend to a DBA
> > > wishing to remove an index, assuming this feature exists in core as
> > > you'd like it to?
> > >
> >
> > Well, the series of steps differs depending on the nature of the
> > system being managed. If you are running on a single node with normal
> > traffic and resources, you just set the GUC to include the index you
> > want to be invisible, wait for a few days (maybe no one runs monthly
> > reports on this system?), take a quick look at your monitoring/stats
> > to make sure things seem copacetic, and then you drop the index and
> > reset the GUC.
>
> Thanks for explaining.
>
> What are your thoughts on cached plans? In this scenario, do you
> assume that waiting a few days means that connections get reset and
> prepared statements will have been replanned? Or do you think cached
> plans don't matter in this scenario?
>

Heh; I did say that the GUC model wasn't perfect, so good on you for
getting right to one of the more wonky parts. In practice, I actually
don't think it matters as much as one might think; IME there is a sort
of inverse relationship were the more sensitive you are to production
changes and/or running at high scale, the more likely you are going to
want to slow deploy / ramp up these changes, and doing things like
adding the GUC at the session level will likely require a connection
recycle anyway. Also keeping invisible indexes in place for days or
weeks is likely to be a common scenario, and again we don't normally
expect connections, or cached plans, to stay alive for weeks at a
time. Of course you can't dismiss this; you'd definitely have to
document that if they are worried about queries with cached plans the
best solution would be to recycle any connections that might have
existed before setting the guc in place. That may not sound ideal, but
I think in practice it is no worse than the practical effects of
thinking that ANALYZE will help keep your queries fast; sure it keeps
your statistics up to date, but if you are running cached plans for
indefinite periods of time, you wouldn't actually pick those up those
statistics changes*, which means cached plans are already susceptible
to degrading over time, and we are expecting people to recycle
connections regularly even if we don't say it very loud.

* As an aside, I once looked into implementing some kind of
pg_invalidate_cached_plans() function that would send a signal to all
backend to dump their plans; kind of like a global DISCARD ALL, but it
always seemed scarier than just recycling connections, so I gave up on
it pretty quick; maybe some would find that useful though?


Robert Treat
https://xzilla.net

On Mon, 9 Jun 2025 at 06:53, Robert Treat <rob@xzilla.net> wrote:
>
> On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > What are your thoughts on cached plans? In this scenario, do you
> > assume that waiting a few days means that connections get reset and
> > prepared statements will have been replanned? Or do you think cached
> > plans don't matter in this scenario?
> >
>
> Heh; I did say that the GUC model wasn't perfect, so good on you for
> getting right to one of the more wonky parts. In practice, I actually
> don't think it matters as much as one might think; IME there is a sort
> of inverse relationship were the more sensitive you are to production
> changes and/or running at high scale, the more likely you are going to
> want to slow deploy / ramp up these changes, and doing things like
> adding the GUC at the session level will likely require a connection
> recycle anyway. Also keeping invisible indexes in place for days or
> weeks is likely to be a common scenario, and again we don't normally
> expect connections, or cached plans, to stay alive for weeks at a
> time. Of course you can't dismiss this; you'd definitely have to
> document that if they are worried about queries with cached plans the
> best solution would be to recycle any connections that might have
> existed before setting the guc in place. That may not sound ideal, but
> I think in practice it is no worse than the practical effects of
> thinking that ANALYZE will help keep your queries fast; sure it keeps
> your statistics up to date, but if you are running cached plans for
> indefinite periods of time, you wouldn't actually pick those up those
> statistics changes*, which means cached plans are already susceptible
> to degrading over time, and we are expecting people to recycle
> connections regularly even if we don't say it very loud.

I agree that it doesn't seem ideal. I feel like if we're adding a
feature that we have to list a bunch of caveats in the documentation,
then we're doing something wrong. BTW, the ALTER INDEX will correctly
invalidate cached plans and does not suffer from the same issue.

My thoughts on this are that extensions are a better place to keep
solutions that work most of the time. Once you start committing quirky
things to Postgres, you sentence yourself to answering the same
question for possibly a few decades in the -bugs or -general mailing
list. I do my best to avoid that and feel we have enough of that
already, so I'm -1 on the GUC solution for this. I know there are a
few other people that are for it, so feel free to listen to them
instead.

Personally, I'd rather see us getting query hints in core and having
some method to specify a global hint to hint "not using index X". I'm
not holding my breath for that one.

David

On Sun, Jun 8, 2025 at 9:37 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Mon, 9 Jun 2025 at 06:53, Robert Treat <rob@xzilla.net> wrote:
> > On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > > What are your thoughts on cached plans? In this scenario, do you
> > > assume that waiting a few days means that connections get reset and
> > > prepared statements will have been replanned? Or do you think cached
> > > plans don't matter in this scenario?
> > >
> >
> > Heh; I did say that the GUC model wasn't perfect, so good on you for
> > getting right to one of the more wonky parts. In practice, I actually
> > don't think it matters as much as one might think; IME there is a sort
> > of inverse relationship were the more sensitive you are to production
> > changes and/or running at high scale, the more likely you are going to
> > want to slow deploy / ramp up these changes, and doing things like
> > adding the GUC at the session level will likely require a connection
> > recycle anyway. Also keeping invisible indexes in place for days or
> > weeks is likely to be a common scenario, and again we don't normally
> > expect connections, or cached plans, to stay alive for weeks at a
> > time. Of course you can't dismiss this; you'd definitely have to
> > document that if they are worried about queries with cached plans the
> > best solution would be to recycle any connections that might have
> > existed before setting the guc in place. That may not sound ideal, but
> > I think in practice it is no worse than the practical effects of
> > thinking that ANALYZE will help keep your queries fast; sure it keeps
> > your statistics up to date, but if you are running cached plans for
> > indefinite periods of time, you wouldn't actually pick those up those
> > statistics changes*, which means cached plans are already susceptible
> > to degrading over time, and we are expecting people to recycle
> > connections regularly even if we don't say it very loud.
>
> I agree that it doesn't seem ideal. I feel like if we're adding a
> feature that we have to list a bunch of caveats in the documentation,
> then we're doing something wrong. BTW, the ALTER INDEX will correctly
> invalidate cached plans and does not suffer from the same issue.
>

While the ALTER INDEX provides a simple way to do cache invalidation,
for practical application you still have most of the same issues and
need to jump through many of the same guc hoops with
force_invisible_index, which is a large part of why this is such a red
herring.

> My thoughts on this are that extensions are a better place to keep
> solutions that work most of the time. Once you start committing quirky
> things to Postgres, you sentence yourself to answering the same
> question for possibly a few decades in the -bugs or -general mailing
> list. I do my best to avoid that and feel we have enough of that
> already, so I'm -1 on the GUC solution for this. I know there are a
> few other people that are for it, so feel free to listen to them
> instead.
>

I hear you wrt explaining quirky things to users; you wouldn't believe
the level of confusion I got when I started explaining
"plan_cache_mode" to users when v12 rolled out. I'd guess the vast
majority of users have still never heard of this guc and have no idea
that Postgres behaves like this, which is another reason why I'd
rather not optimize for a very small segment of the user base at the
expense of a much larger set of users.

And to be clear, this isn't a case of a GUC solution vs an ALTER
solution. There is a reason that the proposed ALTER solution contains
a GUC as well, and why Oracle had to make use of a session flag in
their implementation. You are going to have a guc either way, which
means you are going to have to explain a bunch of these different
caveats in BOTH solutions. It's just that in one of the solutions, you
are further entangling the usage with DDL changes (and the additional
caveats that come with that).


Robert Treat
https://xzilla.net

On Tue, 10 Jun 2025 at 04:40, Robert Treat <rob@xzilla.net> wrote:
> You are going to have a guc either way, which
> means you are going to have to explain a bunch of these different
> caveats in BOTH solutions. It's just that in one of the solutions, you
> are further entangling the usage with DDL changes (and the additional
> caveats that come with that).

IMO, having this GUC to force the use of invisible indexes is quite
strange. In my view, it detracts from the guarantees that you're meant
to get from disabling indexes. What if some connection has
use_invisible_index set to true? The DBA might assume all is well
after having seen nobody complain and then drop the index. The user
might then complain.

David

Hi Shayon,

On Sat, Jun 21, 2025 at 9:38 PM Shayon Mukherjee <shayonj@gmail.com> wrote:
>
>
>
> On Jun 11, 2025, at 9:00 AM, Sami Imseih <samimseih@gmail.com> wrote:
>
>
>> IMO, having this GUC to force the use of invisible indexes is quite
>> strange. In my view, it detracts from the guarantees that you're meant
>> to get from disabling indexes. What if some connection has
>> use_invisible_index set to true? The DBA might assume all is well
>> after having seen nobody complain and then drop the index. The user
>> might then complain.
>
>
> Sure, this may occur. I can also imagine cases where an index is made
> visible only for certain workloads, intentionally. But such efforts should
> be coordinated by application teams and DBAs. Someone would need to modify
> this GUC at the connection level, alter the database, or change the session
> via application code. An ad-hoc connection enabling this GUC is unlikely to
> be an issue.
>
> I don't see how we could provide the INVISIBLE index DDL without also
> providing this boolean GUC. If a user creates an index that is initially
> INVISIBLE, they need a GUC to try it out before deciding to make it
> visible.
>
> It was also pointed out in the thread above that this GUC can serve as a
> backstop for replicas if the DDL to make an index visible is delayed.
>
>
> Hello,
>
> Thank you everyone for all the discussions and also to Robert Treat for feedback and the operational considerations.
>
> It seems like there are multiple ways to solve this problem, which is encouraging. From the discussion, there appears
tobe consensus on few things as well, including the DDL approach, which I personally am a proponent for as well. 
>
> I believe this is a valuable feature for DBAs and engineers working with large databases. Esp since it provides the
confidenceto "turn off" an index to observe the impact through their observability tools and make an informed decision
aboutwhether to drop it. If they're wrong, they can quickly rollback by making the index visible again, rather than
waitingfor a full index rebuild that can take 30 minutes to hours. 
>
> The primary use case I have in mind is for helping engineers (ones not so seasoned like DBAs) decide whether to drop
*existing*indexes. For new indexes, I expect most users would create them in visible mode (the default). Or so has been
myexperience so far. 
>
> The GUC component opens the door for additional workflows, such as creating an index as initially invisible (like
Samipoints out) and testing its performance before making it visible. I originally wasn't thinking it this way, but
thisdemonstrates the flexibility of the feature and accommodates different development approaches. 
>
> As Robert noted, both approaches have trade-offs around operational safety and granular control. However, I think the
DDLapproach provides the right balance of simplicity and system-wide consistency that most users need, while the GUC
stillenables experimentation for those who want it. 
>
> I'm very much committed to iterating on this patch to address any remaining feedback and help make progress on this.
Isthere something we can do here in the essence of "start small, think big", perhaps? 
>
> Thanks
> Shayon
>

Based on your analysis, I think the patch could be split into two
parts: one focusing on the DDL approach and the other on the
additional GUC control.

From reading the discussions, it seems that the GUC control
depends on the DDL approach (eg. creating an index as initially
invisible and making it visible later).

Therefore, maybe the DDL approach can be committed first
and extend the GUC control later as needed?

I read the v18 patch, I think the following changes should not
be included:

diff --git a/src/interfaces/ecpg/test/regression.diffs
b/src/interfaces/ecpg/test/regression.diffs
new file mode 100644
index 0000000000..e69de29bb2
diff --git a/src/interfaces/ecpg/test/regression.out
b/src/interfaces/ecpg/test/regression.out
new file mode 100644
index 0000000000..cb633f4d71
--- /dev/null
+++ b/src/interfaces/ecpg/test/regression.out
@@ -0,0 +1,55 @@
+# initializing database system by copying initdb template
+# using temp instance on port 65312 with PID 30031
+ok 1         - compat_informix/dec_test                  563 ms
+ok 2         - compat_informix/charfuncs                 255 ms
+ok 3         - compat_informix/rfmtdate                  355 ms


--
Regards
Junwang Zhao

On Sat, Jun 21, 2025 at 10:59 AM Merlin Moncure <mmoncure@gmail.com> wrote:
> On Sat, Jun 21, 2025 at 8:38 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
>> The primary use case I have in mind is for helping engineers (ones not so seasoned like DBAs) decide whether to drop
*existing*indexes. For new indexes, I expect most users would create them in visible mode (the default). Or so has been
myexperience so far. 
>

+1

>
> What I would be using this for is when the server is choosing the wrong index, often in multi column index scenarios.
Theserver can be obtuse in those situations.  So I see this as a query optimization aid rather than a 'should I drop
this?'Given that there are several ways to do that already.  I can see scenarios  where I'd want the index backed
constraintto never be used for some/all queries. 
>
> ALTER driving this seems ok.  It seems more of a planner directive to me but having potential permanent configuration
(vsmostly temporary needs) tips the scale IMO. 
>

If your use case falls along the lines of modifying planner decisions,
a DDL based interface is really the wrong interface for that; it
forces system wide impact and provides no ability to work in a per
query/connection/role/etc type manner, and is the most susceptible to
having rollback issues.  These types of issues have always been
resolved through GUCs, which again, fits the use case here as well. I
guess I'll caveat that with the note that your use case is already
addressable using pg_hint_plan, which operates using sql comments, but
I think we're trying to not mention query hints in this thread :-)


Robert Treat
https://xzilla.net

On Sat, Jun 21, 2025 at 7:37 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> On Jun 11, 2025, at 9:00 AM, Sami Imseih <samimseih@gmail.com> wrote:
>> IMO, having this GUC to force the use of invisible indexes is quite
>> strange. In my view, it detracts from the guarantees that you're meant
>> to get from disabling indexes. What if some connection has
>> use_invisible_index set to true? The DBA might assume all is well
>> after having seen nobody complain and then drop the index. The user
>> might then complain.
>
>
> Sure, this may occur. I can also imagine cases where an index is made
> visible only for certain workloads, intentionally. But such efforts should
> be coordinated by application teams and DBAs. Someone would need to modify
> this GUC at the connection level, alter the database, or change the session
> via application code. An ad-hoc connection enabling this GUC is unlikely to
> be an issue.
>
> I don't see how we could provide the INVISIBLE index DDL without also
> providing this boolean GUC. If a user creates an index that is initially
> INVISIBLE, they need a GUC to try it out before deciding to make it
> visible.
>
> It was also pointed out in the thread above that this GUC can serve as a
> backstop for replicas if the DDL to make an index visible is delayed.
>
>
> Hello,
>
> Thank you everyone for all the discussions and also to Robert Treat for feedback and the operational considerations.
>
> It seems like there are multiple ways to solve this problem, which is encouraging. From the discussion, there appears
tobe consensus on few things as well, including the DDL approach, which I personally am a proponent for as well. 
>
> I believe this is a valuable feature for DBAs and engineers working with large databases. Esp since it provides the
confidenceto "turn off" an index to observe the impact through their observability tools and make an informed decision
aboutwhether to drop it. If they're wrong, they can quickly rollback by making the index visible again, rather than
waitingfor a full index rebuild that can take 30 minutes to hours. 
>
> The primary use case I have in mind is for helping engineers (ones not so seasoned like DBAs) decide whether to drop
*existing*indexes. For new indexes, I expect most users would create them in visible mode (the default). Or so has been
myexperience so far. 
>
> The GUC component opens the door for additional workflows, such as creating an index as initially invisible (like
Samipoints out) and testing its performance before making it visible. I originally wasn't thinking it this way, but
thisdemonstrates the flexibility of the feature and accommodates different development approaches. 
>
> As Robert noted, both approaches have trade-offs around operational safety and granular control. However, I think the
DDLapproach provides the right balance of simplicity and system-wide consistency that most users need, while the GUC
stillenables experimentation for those who want it. 
>
> I'm very much committed to iterating on this patch to address any remaining feedback and help make progress on this.
Isthere something we can do here in the essence of "start small, think big", perhaps? 
>

Glad to hear you are still interested, slightly disheartened by the
general lack of concern around operational safety in this thread. I
actually think what you have done covers a lot of the ground for
multiple implementations, so I'm optimistic we can get something for
19.

I was thinking about this some more over the weekend and it does seem
like you can't get away from doing something with DDL; even though it
is the wrong mental model... like when your AC is running but you
don't think it is cool enough, so you turn it down farther, as if it
would blow colder air... but that isn't how AC actually work... it
seems you can't eliminate the desire for this mental model entirely.
Which to be clear, I am not against, it's just a bad tool for the hard
cases, but not in every case. Anyway, if I were picking this up, I
would separate out the two ideas; as I laid out in my email to David,
the GUC solution can stand on it's own without the DDL implementation,
and I would do that first, and then add a simplified DDL
implementation after the fact. Of course it could be done the other
way around, but I think you're more likely to land on the correct GUC
implementation if it isn't mixed up with DDL, and the best way to
assure that is by not having the DDL for the initial patch. Just my
.02, but happy to help spec it out further.


Robert Treat
https://xzilla.net

On Tue, Jul 15, 2025 at 8:19 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> On Jun 23, 2025, at 10:14 AM, Robert Treat <rob@xzilla.net> wrote:
> Glad to hear you are still interested, slightly disheartened by the
> general lack of concern around operational safety in this thread. I
> actually think what you have done covers a lot of the ground for
> multiple implementations, so I'm optimistic we can get something for
> 19.
>
> Just for my own learning and mental model - what would be a good way to understand the change that wasn’t
operationallysafe? 
>

Generally speaking, the two biggest factors for operational safety are
the ability to slowly ramp up changes in a controlled fashion, and
conversely the ability to quickly reverse them. On its surface, the
ALTER feature looks like it passes both of these tests because (in
simple cases) it appears better than drop/create index process alone;
indeed, the ability to "turn off" an index before dropping it feels
like a slower roll out than dropping it, and the ability to "turn it
back on" seems like a much quicker reversal than having to recreate
the index. Our problem is that this only gives the appearance of
safety without having provided any significant improvement in system
safety, especially in more complex and/or demanding setups. With
regards to roll out specifically, the ALTER method is no safer than
drop index because both use DDL which means they are both open to
blocking or being blocked by conflicting queries, which increase
operational risk within the system. Similarly, the nature of the DDL
change also requires that all sessions be impacted everywhere at once;
there is no way to slowly roll the change to some segment of the
database or some specific workload within the system. So it fails the
first test. With regards to the ability to quickly reverse the change,
it does beat the need to rebuild an index, but that only helps in a
very small subset of the typical use cases for this feature; ie where
you are concerned that your server might get "swamped" by poorly
performing queries while the index rebuilds. But that's a pretty low
level version of the problem; on very busy systems and/or system with
delicately balanced buffer caching, even a small pause measured in
seconds could be enough to bring a system down, and again our use of
DDL opens us up to delays from conflicting queries, untimely
wraparound vacuums, concurrent WAL traffic in the case of wanting to
do this across replica trees (which you can't not do). So we generally
fail the second test for a large portion of the use cases involved.
And maybe that would be ok if we didn't have a way to solve this
problem that doesn't fail these tests, but we do, which is through
using a GUC.

> I was thinking about this some more over the weekend and it does seem
> like you can't get away from doing something with DDL; even though it
> is the wrong mental model... like when your AC is running but you
> don't think it is cool enough, so you turn it down farther, as if it
> would blow colder air... but that isn't how AC actually work... it
> seems you can't eliminate the desire for this mental model entirely.
> Which to be clear, I am not against, it's just a bad tool for the hard
> cases, but not in every case. Anyway, if I were picking this up, I
> would separate out the two ideas; as I laid out in my email to David,
> the GUC solution can stand on it's own without the DDL implementation,
> and I would do that first, and then add a simplified DDL
> implementation after the fact. Of course it could be done the other
> way around, but I think you're more likely to land on the correct GUC
> implementation if it isn't mixed up with DDL, and the best way to
> assure that is by not having the DDL for the initial patch. Just my
> .02, but happy to help spec it out further.
>
>
> I am happy to split this into two, however I think starting with GUC first may not achieve a lot of cases that David
andI were talking about earlier in the thread, perhaps? Where, if you want quick feedback without needing to make
application/ session / connection level changes (i.e GUC) then you can quickly do it via the ALTER statement. Happy to
redothe patch and just keep ALTER for v1 accordingly, if it still makes sense. 
>

I think it is much more the other way around; the GUC handles far more
of the potential use cases that you might want to use the ALTER for,
and the ALTER clearly falls short of what the GUC can do. (Side note,
remember you can modify the GUC at the database level. And if you
really want to get ambitious, GUCs can be extended to work through
ALTER TABLE).

> Would folks have any preference between the two approaches?
>

Contrary to how it sounds, I'm not actually opposed to having both :-)
But I am very concerned that an implementation which does ALTER first
sets a sort of anchoring bias that would affect how the GUC feature
gets implemented, which is how I suspect Oracle ended up with their
crappy implementation. I don't think this happens in reverse; ie. the
GUC first implementation handles most of the heavy lifting so the
ALTER only needs to cover the suite spot of the use cases that it can
actually help with.


Robert Treat
https://xzilla.net

On Wed, 16 Jul 2025 at 05:59, Robert Treat <rob@xzilla.net> wrote:
> operational risk within the system. Similarly, the nature of the DDL
> change also requires that all sessions be impacted everywhere at once;
> there is no way to slowly roll the change to some segment of the
> database or some specific workload within the system.

IMO, sounds like your method for safety here is to slowly walk your
bull into the china shop. Wouldn't it be much better to learn where or
confirm the index isn't being used before you go turning it off for
various queries? I'm stumped at why your method for removing the index
amounts to closing your eyes and doing your best to narrow the blast
radius of the trial and error method.

> regards to roll out specifically, the ALTER method is no safer than
> drop index because both use DDL which means they are both open to
> blocking or being blocked by conflicting queries, which increase

Aside from not having to recreate the index, I agree with this part.
It's a genuine concern. If some query switches to a Seq Scan and the
queries to that table start taking a week to execute, then it'll be a
long wait before you can get an AccessExclusiveLock on the table
again. I think our mental models for this differ, however. In my
imagination, I've checked that the index is unused before I disable
it. It seems like in your model, you're going to disable it and
measure the yield of the resulting explosion.

The latest patch seems to be using a ShareUpdateExclusiveLock, so it
looks like those concurrent seq scans won't block making the index
visible again.

My concern with the GUC approach is that:

1. It'll be yet another crappy way to hint what you want the planner
to do. (The other way being enable_* GUCs)
2. There's no plan cache invalidation when changing the GUC.
3. Standby servers may get forgotten about
4. It encourages trial and error methodology for removing indexes
5. All the committers who showed any hints at liking this method have
disappeared from the thread.

My concern with #1 is that when we one day do get query hints, we'll
be left with a bunch of redundant ways to influence planner behaviour.

Maybe you could get the behaviour you want by some additions to
pg_hint_plan. Looking at [1], if query_id could be NULL to apply to
all queries and there was some way of doing "No IndexScan(*
index_name)", would that get you what you want?

David

[1] https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_table.md

My concern with #1 is that when we one day do get query hints, we'll
be left with a bunch of redundant ways to influence planner behaviour.

I’d argue we should not provide the ALTER option without the GUC, for
more granular control. 

On Fri, 18 Jul 2025 at 14:25, Sami Imseih <samimseih@gmail.com> wrote:
> I’d argue we should not provide the ALTER option without the GUC, for
> more granular control.

If you mean the use_invisible_index GUC, then for transparency here,
I'm not in favour of that. I do understand that this might be useful
when trying to get a lagged hot-standby which is desperately
performing Seq Scans back up and running again while waiting on the
replay of the ALTER TABLE VISIBLE, but I just don't feel comfortable
being the committer/forever-owner of having a GUC that overwrites
something that's explicitly written in the system catalogue tables
that is disabled. It's just too magical for my liking. I don't think
we have anything like that today.

Other committers might feel differently, so if the general consensus
is ALTER TABLE+GUC, then I'll leave it to them. I'm by no means saying
this to try and influence the discussion here. If the ALTER TABLE
alone is not seen as useful and I'm the only one who thinks it would
be useful by itself, then I'll just back away from this and let
someone else pick it up.

David

> Unless someone is willing to try and get “The PostgreSQL team’s blessed guide to index management”
>  into the documentation

I really doubt we can agree on one set of index management guidelines.
If anything, this thread has proven there are many ways to bake this
cake :) and all approaches have merit.

> we should probably just accept this will be a bit tool belt approach and
> there will be tools that for one person’s approach are not useful.

+1

> but I just don't feel comfortable
> being the committer/forever-owner of having a GUC that overwrites
> something that's explicitly written in the system catalogue tables
> that is disabled.

That's fair

> Other committers might feel differently, so if the general consensus
> is ALTER TABLE+GUC, then I'll leave it to them. I'm by no means saying
> this to try and influence the discussion here. If the ALTER TABLE
> alone is not seen as useful

If we only go with the ALTER, my concern is there is really no way an extension
( i.e. pg_hint_plan ) can even provide the behavior of this GUC. If the value is
'invisible' in the catalog, the index is no longer available to extensions via
RelOptInfo->indexlist, and it cannot be forced to be considered for planning by
the extension. So, unless we provide the GUC in-core, it will not be
possible for it
to be achieved by extensions. Maybe someone can prove me wrong here.

--
Sami

On Thu, Jul 17, 2025 at 9:49 PM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Wed, 16 Jul 2025 at 05:59, Robert Treat <rob@xzilla.net> wrote:
> > operational risk within the system. Similarly, the nature of the DDL
> > change also requires that all sessions be impacted everywhere at once;
> > there is no way to slowly roll the change to some segment of the
> > database or some specific workload within the system.
>
> IMO, sounds like your method for safety here is to slowly walk your
> bull into the china shop. Wouldn't it be much better to learn where or
> confirm the index isn't being used before you go turning it off for
> various queries? I'm stumped at why your method for removing the index
> amounts to closing your eyes and doing your best to narrow the blast
> radius of the trial and error method.
>
> > regards to roll out specifically, the ALTER method is no safer than
> > drop index because both use DDL which means they are both open to
> > blocking or being blocked by conflicting queries, which increase
>
> Aside from not having to recreate the index, I agree with this part.
> It's a genuine concern. If some query switches to a Seq Scan and the
> queries to that table start taking a week to execute, then it'll be a
> long wait before you can get an AccessExclusiveLock on the table
> again. I think our mental models for this differ, however. In my
> imagination, I've checked that the index is unused before I disable
> it. It seems like in your model, you're going to disable it and
> measure the yield of the resulting explosion.
>

The whole premise of this feature is that there is no way to have
certainty that an index is truly unused. I can assure you that I've
done just as much due diligence as you have (perhaps more) to
determine that the index is unused, but if that were enough to ensure
safety, we wouldn't need invisible indexes in the first place; we
could just drop the index.

Once we admit that neither of us have operational safety, the question
becomes just how close can we get people to certainty, and how can we
most limit the fallout from being wrong. In my world, I'd never have
to worry about a plan flipping to sequential scan causing a query to
hold locks for a week because the server will have certainly crashed
within minutes from the overwhelming level of traffic said sequential
scan brings, so if the solution requires heavy locks and global
application (like alter would), it will still be extremely risky in
heavy production workloads. In short, we're both walking a bull
through the china shop, but it would seem mine is much more
temperamental than yours.


Robert Treat
https://xzilla.net

> it will still be extremely risky in
> heavy production workloads. In short, we're both walking a bull
> through the china shop, but it would seem mine is much more
> temperamental than yours.

Robert, Could you describe the GUC you would like to see?

Also, I'd like to ask. what would be the argument against offering both options,
ALTER and a GUC to override the catalog, as currently proposed in the patch?

This conversation has been mainly GUC is better than ALTER, or vice versa.

It is clear, at least to me, there are merits in both approaches, so
what would be
the argument against making both options available ( maybe with a GUC that
could be more useful than a simple boolean )?

--

Sami

On Mon, Jul 21, 2025 at 1:17 PM Sami Imseih <samimseih@gmail.com> wrote:
>
> > it will still be extremely risky in
> > heavy production workloads. In short, we're both walking a bull
> > through the china shop, but it would seem mine is much more
> > temperamental than yours.
>
> Robert, Could you describe the GUC you would like to see?
>
> Also, I'd like to ask. what would be the argument against offering both options,
> ALTER and a GUC to override the catalog, as currently proposed in the patch?
>
> This conversation has been mainly GUC is better than ALTER, or vice versa.
>
> It is clear, at least to me, there are merits in both approaches, so
> what would be
> the argument against making both options available ( maybe with a GUC that
> could be more useful than a simple boolean )?
>

Just to reiterate, I am not against having both a GUC and ALTER
option, if implemented correctly. Like David, I don't have good
feelings about the ALTER / use_invisible_index GUC overwriting
behavior that is explicitly written in the catalog, and I see no
reason to settle for a technically awkward solution when I think it
also delivers a poor user interface that will be hard to reason about
and/or debug in production.

So I think the "right" interface looks something like a GUC that would
be something like "ignore_index_planning" which takes a csv list of
index names that the planner would ignore. On its own, this provides
as much flexibility as we can offer when attempting to change index
visibility, since it would be set global/local/etc, and could be set
on some, none, or some combo thereof within replica tree environments.
You can make that convoluted, but it is operating like other GUCs.

This also seems compatible with the implementation approach discussed
by PeterE, Tom, and Haas earlier upthread (1)(2) with regard to
providing a list of names and filtering them out. (There could be
other ways of implementing it, but this certainly seems to cover a lot
of the ground we'd want covered).

I know one concern of this method is that this could introduce some
parsing overhead if people choose to use large lists of indexes, but I
think that's generally ok as long as it is documented. Our typical use
case is expected to be one or maybe a few at most, indexes, but if
people feel strongly they need to run with dozens and dozens of
indexes listed, there will be a trade off, similar to other GUCs/tools
(think track_activity_query_size or adding pg_stat_statements, or even
wildly long search_paths).

This also covers some of the more esoteric use cases, such as wanting
to "turn off" indexes for mixed workload replica trees, and covers the
often mentioned use case of allowing an index to be created
"invisible" by default (just add the proposed index name to the list
before creation).

And I'll also mention that this seems like the method least likely to
conflict with an ALTER INDEX implementation if we want to add one down
the line (I think there is an argument for it), since I imagine that
you could create such a thing with a boolean catalog flag that mimics
the gucs behavior, so that the GUC or catalog aren't trying to
override each other. Of course I'm tempted to say you could maybe
implement this like an index storage parameter, but that might be a
bridge too far... still if we make the GUC first, that would certainly
be an interesting idea to explore.


1) https://www.postgresql.org/message-id/15238d97-f667-48df-8319-ab73b37d4511%40eisentraut.org
2) https://www.postgresql.org/message-id/3465209.1727202064%40sss.pgh.pa.us


Robert Treat
https://xzilla.net

> > > it will still be extremely risky in
> > > heavy production workloads. In short, we're both walking a bull
> > > through the china shop, but it would seem mine is much more
> > > temperamental than yours.
> >
> > Robert, Could you describe the GUC you would like to see?
> >
> > Also, I'd like to ask. what would be the argument against offering both options,
> > ALTER and a GUC to override the catalog, as currently proposed in the patch?
> >
> > This conversation has been mainly GUC is better than ALTER, or vice versa.
> >
> > It is clear, at least to me, there are merits in both approaches, so
> > what would be
> > the argument against making both options available ( maybe with a GUC that
> > could be more useful than a simple boolean )?
> >
>
> Just to reiterate, I am not against having both a GUC and ALTER
> option, if implemented correctly.

Thanks. This got lost, at least to me, in the thread above.

> Like David, I don't have good
> feelings about the ALTER / use_invisible_index GUC overwriting
> behavior that is explicitly written in the catalog,

OK, although I don't necessarily think this is something to be frowned
upon. I mean, if we end up with an ALTER/GUC combo, I can't see
how we can avoid such behavior.

> and I see no
> reason to settle for a technically awkward solution when I think it
> also delivers a poor user interface that will be hard to reason about
> and/or debug in production.

This is already an established pattern has been used by other
RDBMS's. Having worked with such interface in the past, a combo of
ALTER and GUC, I never thought it was awkward and it's quite simple to
understand/maintain. But that is subjective.

> So I think the "right" interface looks something like a GUC that would
> be something like "ignore_index_planning" which takes a csv list of
> index names that the planner would ignore.

A few years back, I explored this idea, and I did not really like the parsing
overhead for every execution. You will need to supply a list of fully-qualified
( dbname.schemaname.indexname) names or carefully manage the GUC
per database. Also, if you drop an index, you now must go cleanup the list,
and especially if at some point you recreate the index with the same name.
There is also that you have to push this GUC to all your standbys manually.

This never sounded good to me as a core feature, or do I think it's a really
friendly interface, and I think you can get in more trouble trying to deal with
such a GUC that requires such management.

--
Sami

On Mon, Jul 21, 2025 at 5:24 PM Sami Imseih <samimseih@gmail.com> wrote:
>
> > > > it will still be extremely risky in
> > > > heavy production workloads. In short, we're both walking a bull
> > > > through the china shop, but it would seem mine is much more
> > > > temperamental than yours.
> > >
> > > Robert, Could you describe the GUC you would like to see?
> > >
> > > Also, I'd like to ask. what would be the argument against offering both options,
> > > ALTER and a GUC to override the catalog, as currently proposed in the patch?
> > >
> > > This conversation has been mainly GUC is better than ALTER, or vice versa.
> > >
> > > It is clear, at least to me, there are merits in both approaches, so
> > > what would be
> > > the argument against making both options available ( maybe with a GUC that
> > > could be more useful than a simple boolean )?
> > >
> >
> > Just to reiterate, I am not against having both a GUC and ALTER
> > option, if implemented correctly.
>
> Thanks. This got lost, at least to me, in the thread above.
>
> > Like David, I don't have good
> > feelings about the ALTER / use_invisible_index GUC overwriting
> > behavior that is explicitly written in the catalog,
>
> OK, although I don't necessarily think this is something to be frowned
> upon. I mean, if we end up with an ALTER/GUC combo, I can't see
> how we can avoid such behavior.
>

I laid out two potential options in my previous email; if we implement
this like a storage option, then you are setting the guc at the index
level, which would resolve any opposing behavior. Even if you didn't
do that, if the catalog only indicates that index should be ignored,
rather than trying to control yes/no of index ignoring, then there is
no conflict. If the flag says ignore, you ignore. If the GUC says
ignore, you ignore. If you don't find either, you don't ignore
(default).

> > and I see no
> > reason to settle for a technically awkward solution when I think it
> > also delivers a poor user interface that will be hard to reason about
> > and/or debug in production.
>
> This is already an established pattern has been used by other
> RDBMS's. Having worked with such interface in the past, a combo of
> ALTER and GUC, I never thought it was awkward and it's quite simple to
> understand/maintain. But that is subjective.
>

It's amazing what people are willing to put up with if they are first
conditioned to believe it is the right way :-)

What stands out to me in the Oracle implementation is that they don't
sell it as a way to safely verify that indexes are unused before
dropping, but that it provides a way to safely create an index without
it being used. Both use cases are valid, but the former certainly
seems like the far more desired feature, and yet they seem to shy away
from showing the extra hoop jumping to make that work, I think
precisely because it is awkward to work with. I think we should try to
make the most common use case easier.

> > So I think the "right" interface looks something like a GUC that would
> > be something like "ignore_index_planning" which takes a csv list of
> > index names that the planner would ignore.
>
> A few years back, I explored this idea, and I did not really like the parsing
> overhead for every execution. You will need to supply a list of fully-qualified
> ( dbname.schemaname.indexname) names or carefully manage the GUC
> per database.

I think I'd agree that you may need to be careful, but that's true of
most things. I'm less sure of the need to use fully qualified names;
pg_hint_plan does not have that restriction, and arguably there are
use cases against wanting to do that (imagine a multi-tenant scenario
where you want a specific role to ignore an index regardless of which
database it connects to, as all databases have the same schema).

> Also, if you drop an index, you now must go cleanup the list,
> and especially if at some point you recreate the index with the same name.
> There is also that you have to push this GUC to all your standbys manually.
>
> This never sounded good to me as a core feature, or do I think it's a really
> friendly interface, and I think you can get in more trouble trying to deal with
> such a GUC that requires such management.
>

It isn't clear to me which option you are speaking about here. If you
believe you need a GUC (which you have said up-thread), then there is
going to be some GUC management for any such implementation. If you
set a role with a boolean "ignore_disabled_indexes" because you are
dropping an index, you'll certainly want to clean that up once the
index is dropped. There might be more bookkeeping for the DBA with a
csv list, but only because it allows the DBA more flexibility in how
it is implemented. If you stick to managing one index at a time, the
bookkeeping is basically the same.


Robert Treat
https://xzilla.net

> > This is already an established pattern has been used by other
> > RDBMS's. Having worked with such interface in the past, a combo of
> > ALTER and GUC, I never thought it was awkward and it's quite simple to
> > understand/maintain. But that is subjective.
> >
>
> It's amazing what people are willing to put up with if they are first
> conditioned to believe it is the right way :-)

Well, it works and serves its purpose (or even multiple purposes).

Also, whichever direction we go in will ultimately become the method
our users adopt. That’s just how these things work.

So, I respectfully disagree with your view :)

> What stands out to me in the Oracle implementation is that they don't
> sell it as a way to safely verify that indexes are unused before
> dropping, but that it provides a way to safely create an index without
> it being used.

Ultimately, the ALTER command guarantees that the index is not being used,
since it applies a global change.

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.
I can also make an index visible only to certain workloads, let's say
the replicas only.
Also, If part of my workload suffers because I made the index is
invisible, I can selectively
make the index visible again using this GUC whileI figure things out.
In that case, it acts as a
safety measure against the global change, without having to roll it
back everywhere.
I think it’s quite versatile in its application.

> Both use cases are valid, but the former certainly
> seems like the far more desired feature, and yet they seem to shy away
> from showing the extra hoop jumping to make that work

I'm not following your point about how it's awkward.

> > > So I think the "right" interface looks something like a GUC that would
> > > be something like "ignore_index_planning" which takes a csv list of
> > > index names that the planner would ignore.
> >
> > A few years back, I explored this idea, and I did not really like the parsing
> > overhead for every execution. You will need to supply a list of fully-qualified
> > ( dbname.schemaname.indexname) names or carefully manage the GUC
> > per database.
>
> I think I'd agree that you may need to be careful, but that's true of
> most things. I'm less sure of the need to use fully qualified names;
> pg_hint_plan does not have that restriction,

pg_hint_plan works at the query level, and the hints are resolved based on
aliases, if I recall correctly. This is quite different from a GUC, which can
be applied at multiple levels, including the cluster level.

> There might be more bookkeeping for the DBA with a
> csv list, but only because it allows the DBA more flexibility in how
> it is implemented. If you stick to managing one index at a time, the
> bookkeeping is basically the same.

Sure, that's a fair point, and I don't disagree with the flexibility that such
a GUC provides. As I said, when I first started thinking about this problem,
I found the flexibility of a list-based GUC to be desirable, but I couldn't
rationalize the performance and maintenance trade-offs it incurs.

I'm definitely open to having my mind changed again on this topic. But I
don’t see this GUC as an opposing feature to the ALTER command, which I
still believe we should have.

In my view, the real question we are now debating is about how we
should implement the GUC.

--
Sami

On Tue, 22 Jul 2025 at 05:16, Sami Imseih <samimseih@gmail.com> wrote:
> Also, I'd like to ask. what would be the argument against offering both options,
> ALTER and a GUC to override the catalog, as currently proposed in the patch?

For me, the reason I don't like ALTER TABLE + the use_invisible_index
/ force_invisible_index (the v18 patch seems to be confused about the
name of that GUC) is because it puts into question what "invisible"
means. It's going to be a pretty useless feature for use cases where a
DBA wants to ensure a certain index is *never* used, but does not want
to drop it. A DBA might want to disable a certain index to investigate
certain forms of index corruption and it might not be good if people
can just overwrite that to bypass the DBA's choice.

It might be a slightly more flexible feature if there were 3 possible
states and one of those states could be clearly defined to mean that
users can overwrite the disabledness of all indexes by setting a GUC.
I'm still struggling to like that, however.

Now wondering if it would be better to spend the effort looking at
pg_hint_plan and seeing how hard it would be to get global hints added
which are applied to all queries, and then add a way to disable use of
a named index. (I don't have any experience with that extension other
than looking at the documentation)

David

> Now wondering if it would be better to spend the effort looking at
> pg_hint_plan and seeing how hard it would be to get global hints added
> which are applied to all queries, and then add a way to disable use of
> a named index. (I don't have any experience with that extension other
> than looking at the documentation)

Regardless of what comes out of this thread, pg_hint_plan supporting
a NoIndexScan hint that takes in an index name, and not only a relname
is needed. I plan on taking that up there.

--

Sami

On Tue, Jul 22, 2025 at 6:50 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Tue, 22 Jul 2025 at 05:16, Sami Imseih <samimseih@gmail.com> wrote:
> > Also, I'd like to ask. what would be the argument against offering both options,
> > ALTER and a GUC to override the catalog, as currently proposed in the patch?
>
> For me, the reason I don't like ALTER TABLE + the use_invisible_index
> / force_invisible_index (the v18 patch seems to be confused about the
> name of that GUC) is because it puts into question what "invisible"
> means. It's going to be a pretty useless feature for use cases where a
> DBA wants to ensure a certain index is *never* used, but does not want
> to drop it. A DBA might want to disable a certain index to investigate
> certain forms of index corruption and it might not be good if people
> can just overwrite that to bypass the DBA's choice.
>

Thanks for elaborating on this, you said it better than me.

So I'll note that in my proposal, the hypothetical catalog update
("alter index set guc" or whatever) is a one way door; if the dba (or
whomever) sets that, then the index is ignored everywhere, since the
session level guc can only also suggest the index be ignored from
planning. That is enough to allow people to both slow roll out OR slow
roll in new indexes, as needed, which I think covers enough ground
without the complexity going too far (which your below example clearly
shows is possible).

> It might be a slightly more flexible feature if there were 3 possible
> states and one of those states could be clearly defined to mean that
> users can overwrite the disabledness of all indexes by setting a GUC.
> I'm still struggling to like that, however.
>
> Now wondering if it would be better to spend the effort looking at
> pg_hint_plan and seeing how hard it would be to get global hints added
> which are applied to all queries, and then add a way to disable use of
> a named index. (I don't have any experience with that extension other
> than looking at the documentation)
>

I'd be interested in your evaluation of this, but the GUC I've
outlined would accomplish most of the use cases here automagically.


Robert Treat
https://xzilla.net

On Tue, Jul 22, 2025 at 06:04:50PM -0500, Sami Imseih wrote:
> Regardless of what comes out of this thread, pg_hint_plan supporting
> a NoIndexScan hint that takes in an index name, and not only a relname
> is needed. I plan on taking that up there.

Patches are welcome upstream.
--
Michael

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.
--
Michael

> On Jul 15, 2025, at 1:58 PM, Robert Treat <rob@xzilla.net> wrote:
>
> On Tue, Jul 15, 2025 at 8:19 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
>> On Jun 23, 2025, at 10:14 AM, Robert Treat <rob@xzilla.net> wrote:
>> Glad to hear you are still interested, slightly disheartened by the
>> general lack of concern around operational safety in this thread. I
>> actually think what you have done covers a lot of the ground for
>> multiple implementations, so I'm optimistic we can get something for
>> 19.
>>
>> Just for my own learning and mental model - what would be a good way to understand the change that wasn’t
operationallysafe? 
>>
>
> Generally speaking, the two biggest factors for operational safety are
> the ability to slowly ramp up changes in a controlled fashion, and
> conversely the ability to quickly reverse them. On its surface, the
> ALTER feature looks like it passes both of these tests because (in
> simple cases) it appears better than drop/create index process alone;
> indeed, the ability to "turn off" an index before dropping it feels
> like a slower roll out than dropping it, and the ability to "turn it
> back on" seems like a much quicker reversal than having to recreate
> the index. Our problem is that this only gives the appearance of
> safety without having provided any significant improvement in system
> safety, especially in more complex and/or demanding setups. With
> regards to roll out specifically, the ALTER method is no safer than
> drop index because both use DDL which means they are both open to
> blocking or being blocked by conflicting queries, which increase
> operational risk within the system. Similarly, the nature of the DDL
> change also requires that all sessions be impacted everywhere at once;
> there is no way to slowly roll the change to some segment of the
> database or some specific workload within the system. So it fails the
> first test. With regards to the ability to quickly reverse the change,
> it does beat the need to rebuild an index, but that only helps in a
> very small subset of the typical use cases for this feature; ie where
> you are concerned that your server might get "swamped" by poorly
> performing queries while the index rebuilds. But that's a pretty low
> level version of the problem; on very busy systems and/or system with
> delicately balanced buffer caching, even a small pause measured in
> seconds could be enough to bring a system down, and again our use of
> DDL opens us up to delays from conflicting queries, untimely
> wraparound vacuums, concurrent WAL traffic in the case of wanting to
> do this across replica trees (which you can't not do). So we generally
> fail the second test for a large portion of the use cases involved.
> And maybe that would be ok if we didn't have a way to solve this
> problem that doesn't fail these tests, but we do, which is through
> using a GUC.
>
>> I was thinking about this some more over the weekend and it does seem
>> like you can't get away from doing something with DDL; even though it
>> is the wrong mental model... like when your AC is running but you
>> don't think it is cool enough, so you turn it down farther, as if it
>> would blow colder air... but that isn't how AC actually work... it
>> seems you can't eliminate the desire for this mental model entirely.
>> Which to be clear, I am not against, it's just a bad tool for the hard
>> cases, but not in every case. Anyway, if I were picking this up, I
>> would separate out the two ideas; as I laid out in my email to David,
>> the GUC solution can stand on it's own without the DDL implementation,
>> and I would do that first, and then add a simplified DDL
>> implementation after the fact. Of course it could be done the other
>> way around, but I think you're more likely to land on the correct GUC
>> implementation if it isn't mixed up with DDL, and the best way to
>> assure that is by not having the DDL for the initial patch. Just my
>> .02, but happy to help spec it out further.
>>
>>
>> I am happy to split this into two, however I think starting with GUC first may not achieve a lot of cases that David
andI were talking about earlier in the thread, perhaps? Where, if you want quick feedback without needing to make
application/ session / connection level changes (i.e GUC) then you can quickly do it via the ALTER statement. Happy to
redothe patch and just keep ALTER for v1 accordingly, if it still makes sense. 
>>
>
> I think it is much more the other way around; the GUC handles far more
> of the potential use cases that you might want to use the ALTER for,
> and the ALTER clearly falls short of what the GUC can do. (Side note,
> remember you can modify the GUC at the database level. And if you
> really want to get ambitious, GUCs can be extended to work through
> ALTER TABLE).
>
>> Would folks have any preference between the two approaches?
>>
>
> Contrary to how it sounds, I'm not actually opposed to having both :-)
> But I am very concerned that an implementation which does ALTER first
> sets a sort of anchoring bias that would affect how the GUC feature
> gets implemented, which is how I suspect Oracle ended up with their
> crappy implementation. I don't think this happens in reverse; ie. the
> GUC first implementation handles most of the heavy lifting so the
> ALTER only needs to cover the suite spot of the use cases that it can
> actually help with.
>
>
>


I know the thread has gotten quite long and I'm a little late to the party, but thank you for taking the time to walk
methrough the operational safety considerations you were referencing. I have a much better mental model of what you
meannow. 

Just like David mentioned earlier in this thread, I was approaching this problem from the perspective of being 99%
certainthe index isn't used, and thinking that a DDL to enable/disable indexes without needing a rebuild would be
useful.However, I understand your point that not everyone may approach using the DDL in the same way, or may not have
donetheir due diligence, which could negatively impact busy systems. Though I think that's partly the responsibility of
theDBA/developer as well - for example, we have `pg_stat_user_indexes`, and I'd typically use the stats in that table
tounderstand index usage before trying to disable it. At the same time, I do agree with your point about making common
workflowseasy and safe for users. There is def something there. 

The whole thread has made it very clear that there are pros and cons to all the approaches mentioned so far, and I'll
refrainfrom extending this tangent any further :D. I'll reply on the main thread with some thoughts additional
thoughts.I just wanted to express my appreciation for you taking the time to walk me through your perspective in more
depth.

P.S. I think we're approaching the 11-month anniversary of this thread (started on September 9, 2024, by yours truly)
andI am very humbled, and impressed by the community's rigor here :). I personally would love to see something land in
core,and I'm confident we'll get there. 

Thanks
Shayon

Hello,

> On Jul 23, 2025, at 9:43 PM, Michael Paquier <michael@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
whendisabling and then dropping an index. As a developer/DBA or person in a similar position, I think my journey for
droppingan 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
usingfeature 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.
Whichis 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 /
impossibletask. But it also means that both systems aren't fighting/overriding each other or making it difficult for
usersto 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’snot clear to me here is, how common / intuitive of a workflow will this be and if it fits easily in the “common
usecase” 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

On Sat, Aug 02, 2025 at 03:09:19PM -0400, Shayon Mukherjee wrote:
> 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.

Sami has proposed a patch for pg_hint_plan that goes in the direction
of a DisableIndex hint, which is doable even if it requires some work
inside the extension code like some refactoring:
https://github.com/ossc-db/pg_hint_plan/issues/226

So I cannot say much about this proposal for core, but for
pg_hint_plan I'm looking at adding that for the v18 release of the
module, which is planned for the end of August/beginning of September.
--
Michael

On Mon, Aug 04, 2025 at 01:19:46PM +0900, Michael Paquier wrote:
> On Sat, Aug 02, 2025 at 03:09:19PM -0400, Shayon Mukherjee wrote:
> > 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.
>
> Sami has proposed a patch for pg_hint_plan that goes in the direction
> of a DisableIndex hint, which is doable even if it requires some work
> inside the extension code like some refactoring:
> https://github.com/ossc-db/pg_hint_plan/issues/226
>
> So I cannot say much about this proposal for core, but for
> pg_hint_plan I'm looking at adding that for the v18 release of the
> module, which is planned for the end of August/beginning of September.

FWIW if anyone actually needs it, that feature had existed for decades in
plantuner: http://www.sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=summary

On Mon, Aug 04, 2025 at 12:34:14PM +0800, Julien Rouhaud wrote:
> FWIW if anyone actually needs it, that feature had existed for decades in
> plantuner: http://www.sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=summary

Your link does not work here, this one does:
https://github.com/postgrespro/plantuner

Thanks for mentioning that, so it's a set of GUCs that rely on
get_relation_info_hook_type for the index filtering.
--
Michael

On Mon, Aug 04, 2025 at 01:19:46PM +0900, Michael Paquier wrote:
> Sami has proposed a patch for pg_hint_plan that goes in the direction
> of a DisableIndex hint, which is doable even if it requires some work
> inside the extension code like some refactoring:
> https://github.com/ossc-db/pg_hint_plan/issues/226
>
> So I cannot say much about this proposal for core, but for
> pg_hint_plan I'm looking at adding that for the v18 release of the
> module, which is planned for the end of August/beginning of September.

FWIW, I have been able to merge the patch to add support for this
DisableIndex hint in pg_hint_plan.  So at least this will give people
something to toy with even without this in-core feature.  That will be
included in next week's release, available in the version of the
module compatible with v18.
--
Michael

On Mon, Sep 08, 2025 at 09:37:39AM -0400, Robert Treat wrote:
> Thanks for your work on this. For those who may not be aware, Sami did
> implement a version of this in pg_hint_plan{1}, so that is helpful. I think
> it may be worth revisiting this in core, but perhaps once we seen this new
> implementation in action for a bit. (?)
>
> {1}
> https://github.com/ossc-db/pg_hint_plan/commit/d2cfd2f2c1fd18f55123b12c2250a384ccfaefaf

A side note on this one: it may make sense to implement regexp
handling in this new hint, where all the indexes matching the regexp
become ignored by the planner.  We have not done that because we
wanted to see the impact of the initial feature first.  I don't think
that this would increase the complexity footprint by a lot.
--
Michael