Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snxm5-007ERL-Ld for pgsql-hackers@arkaria.postgresql.org; Tue, 10 Sep 2024 10:07:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1snxm4-00AncV-Gt for pgsql-hackers@arkaria.postgresql.org; Tue, 10 Sep 2024 10:07:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snxm4-00AncM-5w for pgsql-hackers@lists.postgresql.org; Tue, 10 Sep 2024 10:07:04 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1snxlz-000SG5-UL for pgsql-hackers@lists.postgresql.org; Tue, 10 Sep 2024 10:07:03 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-e1d0e74f484so5586554276.2 for ; Tue, 10 Sep 2024 03:07:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725962818; x=1726567618; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=A5IoW46IfjcCgU4n7gcoN4vEXprWhGumV+EYHbWqXfM=; b=P+jwQc9t/y/PpcrIC3WQZ5xfgUTnEEtZV0AfM+jbARZP4bdbD08SBc28+jB5YlZHtN sqlRgjYWaeRwlHyWHjTkwt1V0lgs1puwWl+lEEY5IZNtZNlNG4ARBJ/03lfJVpXb5mY5 sEkFZ+d7aynJ9j/Qa5uIvQQ1MfxHMPhWUtTKmYU15x5RrDj1w9aSn6MNSzhbb5EAvSTk luNaeVQYrVD2fWzTUWLHqgfKKu1PPlNpAtEJnQDvwEmldoQj52Wdy+W2Q8wqr3v8ZdKi NFF10uM40X3MgKxFnPE+jR3lo0o0nSGg+x9lBfwV4D6aA211TWZzT1YMj4oxoFZvWr6L 6y7w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725962818; x=1726567618; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=A5IoW46IfjcCgU4n7gcoN4vEXprWhGumV+EYHbWqXfM=; b=O6GimkE6pG0l43FaLkJtxnE8+VHY8aYGhmNuQTJc5kuElGUwQH5zvS5O8JZDtxByS8 IwHxWiZLbdSNtNfqHnWvvJOlCaH+B9UyZHVhcF4xs0Y0DH73GsoLGV3JIqSSQcon2/jK b8uIRtYjdiMkrDehBHIvahvHNSzmYBZFLOoCDwPiJHr7OA+3JUlrOSCUmFpXq4IXEqbx IMla6I8lmwKTaQ76WQmz4t6hiib3va8X5ts+nzNPqqtLKntdRHeTOuYylg1ILbQnZcVE /fyUGOWOhUn0JXOJq8VLuJ2yGYc/MGJVRrQADQ3A5OKz9w0OJoddV2zbrxiECYnGTlJR 7mbw== X-Forwarded-Encrypted: i=1; AJvYcCU/L4HZaRbmgAZgcvLwe9egdaHVvSWbCyvVR7l7ymvf0xegt/D4VWh+6dG6jKVnCvrC/h+SwjYUN+T/bJiq@lists.postgresql.org X-Gm-Message-State: AOJu0YxkEo5g7TWaXa74PpvQYtHh4KoMh/3oob9vgpzxiuHVtKEUKw4T awU7rWSnNzUvfGe86PChvlGZCZ/8DhRNFA2jPsTzYysKhvvvnOAxgHv5M2wIC2/9mLKyk8Jkjv+ LwUjAeYQ0s5Yuos3s3MYGPPmmPRtoMDJ03q0= X-Google-Smtp-Source: AGHT+IHgZsmdxnuym3iDXZmZ3krSVGpaKqGZWsRYupwcIc3ZSxusbMGF0zfokA3o9f9w9Ki1snXBiNVDAoaXlhfOmTg= X-Received: by 2002:a05:690c:dce:b0:632:c442:2316 with SMTP id 00721157ae682-6db44df4979mr151215477b3.3.1725962818569; Tue, 10 Sep 2024 03:06:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: wenhui qiu Date: Tue, 10 Sep 2024 18:06:47 +0800 Message-ID: Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX To: David Rowley Cc: Shayon Mukherjee , pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000b24ee40621c10805" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b24ee40621c10805 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Shayon Thank you for your work on this , I think it's great to have this feature implemented ,I checked the doucment on other databases,It seems both MySQL 8.0 and oracle supports it, sql server need to rebuild indexes after disabled,It seems disable the index, it's equivalent to deleting the index, except that the index's metadata is still retained: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/invisible-indexes.html https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact= -sql?view=3Dsql-server-ver16 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-I= NDEX.html ->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. MySQL 8.0 and oracle settings are not visible, index information is always updated, I would then suggest that the statement be changed to set the index invisible and visible. Thanks David Rowley =E4=BA=8E2024=E5=B9=B49=E6=9C=8810=E6= =97=A5=E5=91=A8=E4=BA=8C 06:17=E5=86=99=E9=81=93=EF=BC=9A > On Tue, 10 Sept 2024 at 09:39, Shayon Mukherjee wrote= : > > Adding and removing indexes is a common operation in PostgreSQL. On > larger databases, however, these operations can be resource-intensive. Wh= en > 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 the= ir > 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 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=E2=80=99s disabled seems prefer= able, as > it avoids the need to rebuild the index if it=E2=80=99s re-enabled later.= The > alternative would be dropping and rebuilding the index upon re-enabling, > which I believe would introduce additional overhead in terms of applicati= on > 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 > > > --000000000000b24ee40621c10805 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Shayon=C2=A0
=C2=A0 =C2=A0Thank you for =C2=A0your = work on this ,=C2=A0I think it's great to have this feature implemented= ,I checked the doucment =C2=A0on other databases,It seems both MySQL 8.0 = =C2=A0and oracle supports it, sql server need to rebuild indexes after =C2= =A0disabled,It seems =C2=A0disable the index, it's equivalent to deleti= ng the index, except that the index's metadata is still retained:
=
->A disabled index would still receive updates and en= force constraints as usual but would not be used for queries. This allows u= sers to assess -> ->whether an index impacts query performance before= deciding to drop it entirely.
MySQL 8.0 and oracle settings are = not visible, index information is always updated,=C2=A0I would then suggest= that the statement be changed to set the index invisible and visible.



Thanks
<= /div>
D= avid Rowley <dgrowleyml@gmail.co= m> =E4=BA=8E2024=E5=B9=B49=E6=9C=8810=E6=97=A5=E5=91=A8=E4=BA=8C 06:= 17=E5=86=99=E9=81=93=EF=BC=9A
On Tue, 10 Sept 202= 4 at 09:39, Shayon Mukherjee <shayonj@gmail.com> wrote:
> Adding and removing indexes is a common operation in PostgreSQL. On la= rger databases, however, these operations can be resource-intensive. When e= valuating the performance impact of one or more indexes, dropping them migh= t not be ideal since as a user you may want a quicker way to test their eff= ects without fully committing to removing & adding them back again. Whi= ch can be a time taking operation on larger tables.
>
> Proposal:
> I propose adding an ALTER INDEX command that allows for enabling or di= sabling 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 a= s usual but would not be used for queries. This allows users to assess whet= her 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.=C2=A0 I don't have any arguments against the syntax you'= ve
proposed.=C2=A0 We'd certainly have to clearly document that constraint= s
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_inde= x during the enable/disable operation.

That's not a good idea as it would allow ALTER INDEX ... ENABLE; to be<= br> used to make valid a failed concurrently created index.=C2=A0 I think this<= br> 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=E2=80=99s disabled seems prefe= rable, as it avoids the need to rebuild the index if it=E2=80=99s re-enable= d later. The alternative would be dropping and rebuilding the index upon re= -enabling, which I believe would introduce 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<= br> would make the feature useless for that purpose.

If we get the skip scan feature for PG18, then there's likely going to<= br> 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<= br> we get skip scans.

David


--000000000000b24ee40621c10805--