public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Eisentraut <[email protected]>
To: Shayon Mukherjee <[email protected]>
To: [email protected]
Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX
Date: Mon, 23 Sep 2024 17:14:15 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANqtF-oXKe0M=0QOih6H+sZRjE2BWAbkW_1+9nMEAMLxUJg5jA@mail.gmail.com>
References: <CANqtF-oXKe0M=0QOih6H+sZRjE2BWAbkW_1+9nMEAMLxUJg5jA@mail.gmail.com>

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

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox