public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shayon Mukherjee <[email protected]>
To: David Rowley <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)
Date: Wed, 16 Oct 2024 14:15:51 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CANqtF-oXKe0M=0QOih6H+sZRjE2BWAbkW_1+9nMEAMLxUJg5jA@mail.gmail.com>
<[email protected]>
<CA+TgmoZw+o3UpKVf-=-o-x4fC2A54buK6ogLwk-TZ4C6h2W_ww@mail.gmail.com>
<[email protected]>
<CAApHDvr1a4crBB2CwT6RoscGqwbp6s9_OakBTQg375eiPHg-RA@mail.gmail.com>
<CA+TgmoYGzg2tHJ=-HsCADnAFdDFYo5TdFCK4Hk0EE4v9xusqNA@mail.gmail.com>
<[email protected]>
<CA+TgmobJAAoYhAQdHzq7-oLW=w+b_cWONc0jZzK45ZGQNdnnjw@mail.gmail.com>
<CAApHDvq7iLSfNkxyMqGrqdync-3qCACBrcRD6vMKAEEoaEBn9g@mail.gmail.com>
<[email protected]>
> On Oct 16, 2024, at 12:19 PM, Shayon Mukherjee <[email protected]> wrote:
>
> - ALTER INDEX ... ENABLE/DISABLE performs an in-place update of the pg_index
> catalog to protect against indcheckxmin [2] (older unrelated thread).
Performing the in place update of the pg_index row from ATExecEnableDisableIndex using systable_inplace_update_begin was failing in CI weirdly but not on my local MacBook when running spec suite. I am also wondering what is causing the requirement [1] to update the row in-place vs say using CatalogTupleUpdate since using the later is working well locally + CI?
I have attached a v2 patch (following from the last v1 patch [1]) that uses CatalogTupleUpdate and local + CI [2] is passing.
[1] https://www.postgresql.org/message-id/[email protected]
[2] https://www.postgresql.org/message-id/EF2313B8-A017-4869-9B7F-A24EDD8795DE%40gmail.com
[3] https://github.com/shayonj/postgres/pull/1
Thanks
Shayon

Attachments:
[application/octet-stream] v2-0001-Introduce-the-ability-to-enable-disable-indexes-u.patch (46.9K, ../[email protected]/3-v2-0001-Introduce-the-ability-to-enable-disable-indexes-u.patch)
download | inline diff:
From b7d8e240b6f7cbe873fad1595d2bc79ebe38464b Mon Sep 17 00:00:00 2001
From: Shayon Mukherjee <[email protected]>
Date: Wed, 16 Oct 2024 14:06:59 -0400
Subject: [PATCH v2] Introduce the ability to enable/disable indexes using
ALTER INDEX
---
doc/src/sgml/catalogs.sgml | 11 +
doc/src/sgml/ref/alter_index.sgml | 43 ++
doc/src/sgml/ref/create_index.sgml | 29 ++
src/backend/bootstrap/bootparse.y | 2 +
src/backend/catalog/index.c | 31 +-
src/backend/catalog/toasting.c | 2 +-
src/backend/commands/indexcmds.c | 4 +
src/backend/commands/tablecmds.c | 58 ++-
src/backend/optimizer/util/plancat.c | 13 +
src/backend/parser/gram.y | 48 +-
src/backend/parser/parse_utilcmd.c | 3 +
src/backend/utils/adt/ruleutils.c | 4 +
src/backend/utils/cache/relcache.c | 1 +
src/include/catalog/index.h | 2 +-
src/include/catalog/pg_index.h | 1 +
src/include/nodes/parsenodes.h | 3 +
src/include/nodes/pathnodes.h | 2 +
src/test/regress/expected/create_index.out | 494 +++++++++++++++++++++
src/test/regress/sql/create_index.sql | 181 ++++++++
19 files changed, 922 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 964c819a02..124ca3dc35 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4590,6 +4590,17 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
partial index.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indisenabled</structfield> <type>bool</type>
+ </para>
+ <para>
+ 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,
+ but is still maintained when the table is modified. Default is true.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index e26efec064..c5c13a3cb3 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -31,6 +31,8 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTE
SET STATISTICS <replaceable class="parameter">integer</replaceable>
ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ]
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ]
+ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ENABLE
+ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> DISABLE
</synopsis>
</refsynopsisdiv>
@@ -158,6 +160,33 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ENABLE</literal></term>
+ <listitem>
+ <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>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DISABLE</literal></term>
+ <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. This can be useful for testing query performance with and without
+ specific indexes, temporarily reducing the overhead of index maintenance
+ during bulk data loading operations, or verifying an index is not being used
+ before dropping it. If performance degrades after disabling an index, it can be
+ easily re-enabled. Before disabling, it's recommended to check
+ <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
+ to identify potentially unused indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
@@ -300,6 +329,20 @@ CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
</programlisting></para>
+ <para>
+ To enable an index:
+<programlisting>
+ALTER INDEX idx_name ENABLE;
+</programlisting>
+ </para>
+
+ <para>
+ To disable an index:
+<programlisting>
+ALTER INDEX idx_name DISABLE;
+</programlisting>
+ </para>
+
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 621bc0e253..f1eebfa250 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -28,6 +28,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
+ [ DISABLE ]
</synopsis>
</refsynopsisdiv>
@@ -380,6 +381,19 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>DISABLE</literal></term>
+ <listitem>
+ <para>
+ Creates the index in a disabled state (default enabled). A disabled index is not used by the
+ query planner for query optimization, but it is still maintained when the
+ underlying table data changes. This can be useful when you want to create
+ an index without immediately impacting query performance, allowing you to
+ enable it later at a more convenient time. The index can be enabled later
+ using <command>ALTER INDEX ... ENABLE</command>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
<refsect2 id="sql-createindex-storage-parameters" xreflabel="Index Storage Parameters">
@@ -701,6 +715,14 @@ Indexes:
partitioned index is a metadata only operation.
</para>
+ <para>
+ When creating an index with the <literal>DISABLE</literal> option, the index
+ will be created but not used for query planning. This can be useful for
+ preparing an index in advance of its use, or for testing purposes. The index
+ will still be maintained as the table is modified, so it can be enabled
+ later without needing to be rebuilt. By default all new indexes are enabled.
+ </para>
+
</refsect2>
</refsect1>
@@ -980,6 +1002,13 @@ SELECT * FROM points
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
</programlisting></para>
+ <para>
+ To create an index on the table <literal>test_table</literal> with the default
+ name, but have it initially disabled:
+<programlisting>
+CREATE INDEX ON test_table (col1) DISABLE;
+</programlisting>
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 73a7592fb7..6023d58490 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -302,6 +302,7 @@ Boot_DeclareIndexStmt:
stmt->concurrent = false;
stmt->if_not_exists = false;
stmt->reset_default_tblspc = false;
+ stmt->isenabled = true;
/* locks and races need not concern us in bootstrap mode */
relationId = RangeVarGetRelid(stmt->relation, NoLock,
@@ -355,6 +356,7 @@ Boot_DeclareUniqueIndexStmt:
stmt->concurrent = false;
stmt->if_not_exists = false;
stmt->reset_default_tblspc = false;
+ stmt->isenabled = true;
/* locks and races need not concern us in bootstrap mode */
relationId = RangeVarGetRelid(stmt->relation, NoLock,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 12822d0b14..15773ebb13 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -118,7 +118,8 @@ static void UpdateIndexRelation(Oid indexoid, Oid heapoid,
bool isexclusion,
bool immediate,
bool isvalid,
- bool isready);
+ bool isready,
+ bool isenabled);
static void index_update_stats(Relation rel,
bool hasindex,
double reltuples);
@@ -569,7 +570,8 @@ UpdateIndexRelation(Oid indexoid,
bool isexclusion,
bool immediate,
bool isvalid,
- bool isready)
+ bool isready,
+ bool isenabled)
{
int2vector *indkey;
oidvector *indcollation;
@@ -647,6 +649,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indisready - 1] = BoolGetDatum(isready);
values[Anum_pg_index_indislive - 1] = BoolGetDatum(true);
values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false);
+ values[Anum_pg_index_indisenabled - 1] = BoolGetDatum(isenabled);
values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey);
values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation);
values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass);
@@ -712,6 +715,8 @@ UpdateIndexRelation(Oid indexoid,
* already exists.
* INDEX_CREATE_PARTITIONED:
* create a partitioned index (table must be partitioned)
+ * INDEX_CREATE_DISABLED:
+* create the index as disabled if instructed, defaults to being enabled.
* constr_flags: flags passed to index_constraint_create
* (only if INDEX_CREATE_ADD_CONSTRAINT is set)
* allow_system_table_mods: allow table to be a system catalog
@@ -757,6 +762,7 @@ index_create(Relation heapRelation,
bool invalid = (flags & INDEX_CREATE_INVALID) != 0;
bool concurrent = (flags & INDEX_CREATE_CONCURRENT) != 0;
bool partitioned = (flags & INDEX_CREATE_PARTITIONED) != 0;
+ bool isenabled = (flags & INDEX_CREATE_ENABLED) != 0;
char relkind;
TransactionId relfrozenxid;
MultiXactId relminmxid;
@@ -1040,13 +1046,15 @@ index_create(Relation heapRelation,
* (Or, could define a rule to maintain the predicate) --Nels, Feb '92
* ----------------
*/
+
UpdateIndexRelation(indexRelationId, heapRelationId, parentIndexRelid,
indexInfo,
collationIds, opclassIds, coloptions,
isprimary, is_exclusion,
(constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) == 0,
!concurrent && !invalid,
- !concurrent);
+ !concurrent,
+ isenabled);
/*
* Register relcache invalidation on the indexes' heap relation, to
@@ -1315,6 +1323,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
List *indexColNames = NIL;
List *indexExprs = NIL;
List *indexPreds = NIL;
+ Form_pg_index indexForm;
+ bits16 createFlags;
indexRelation = index_open(oldIndexId, RowExclusiveLock);
@@ -1342,6 +1352,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
Anum_pg_index_indoption);
indcoloptions = (int2vector *) DatumGetPointer(colOptionDatum);
+ /* Get the enabled state of the original index */
+ indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+
/* Fetch reloptions of index if any */
classTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(oldIndexId));
if (!HeapTupleIsValid(classTuple))
@@ -1433,6 +1446,16 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
stattargets[i].isnull = isnull;
}
+ /*
+ * Determine the create flags for the new index.
+ * We always use SKIP_BUILD and CONCURRENT for concurrent reindexing.
+ * If the original index was enabled, we also set the ENABLED flag
+ * to maintain the same state in the new index.
+ */
+ createFlags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT;
+ if (indexForm->indisenabled)
+ createFlags |= INDEX_CREATE_ENABLED;
+
/*
* Now create the new index.
*
@@ -1456,7 +1479,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indcoloptions->values,
stattargets,
reloptionsDatum,
- INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT,
+ createFlags,
0,
true, /* allow table to be a system catalog? */
false, /* is_internal? */
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index ad3082c62a..dead993a74 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -325,7 +325,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
BTREE_AM_OID,
rel->rd_rel->reltablespace,
collationIds, opclassIds, NULL, coloptions, NULL, (Datum) 0,
- INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL);
+ INDEX_CREATE_IS_PRIMARY | INDEX_CREATE_ENABLED, 0, true, true, NULL);
table_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index e33ad81529..d0604e9a00 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1192,6 +1192,10 @@ DefineIndex(Oid tableId,
flags |= INDEX_CREATE_PARTITIONED;
if (stmt->primary)
flags |= INDEX_CREATE_IS_PRIMARY;
+ if (stmt->isenabled)
+ flags |= INDEX_CREATE_ENABLED;
+ else
+ flags &= ~INDEX_CREATE_ENABLED;
/*
* If the table is partitioned, and recursion was declined but partitions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1ccc80087c..7ccd15b06d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -664,7 +664,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, const char *compression);
static char GetAttributeStorage(Oid atttypid, const char *storagemode);
-
+static void ATExecEnableDisableIndex(Relation rel, bool enable);
/* ----------------------------------------------------------------
* DefineRelation
@@ -4558,6 +4558,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetExpression:
case AT_DropExpression:
case AT_SetCompression:
+ case AT_EnableIndex:
+ case AT_DisableIndex:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -5131,6 +5133,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_EnableIndex:
+ case AT_DisableIndex:
+ ATSimplePermissions(cmd->subtype, rel, ATT_INDEX | ATT_PARTITIONED_INDEX);
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -5527,6 +5535,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_DetachPartitionFinalize:
address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name);
break;
+ case AT_EnableIndex:
+ ATExecEnableDisableIndex(rel, true);
+ break;
+ case AT_DisableIndex:
+ ATExecEnableDisableIndex(rel, false);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -6426,6 +6440,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "DROP COLUMN";
case AT_AddIndex:
case AT_ReAddIndex:
+ case AT_EnableIndex:
+ case AT_DisableIndex:
return NULL; /* not real grammar */
case AT_AddConstraint:
case AT_ReAddConstraint:
@@ -20203,3 +20219,43 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
return cstorage;
}
+
+/*
+ * ATExecEnableDisableIndex
+ * Performs a catalog update to enable or disable an index in pg_index.
+ */
+static void
+ATExecEnableDisableIndex(Relation rel, bool enable)
+{
+ Oid indexOid = RelationGetRelid(rel);
+ Relation pg_index;
+ HeapTuple indexTuple;
+ Form_pg_index indexForm;
+ bool updated = false;
+
+ pg_index = table_open(IndexRelationId, RowExclusiveLock);
+
+ indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexOid));
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "could not find tuple for index %u", indexOid);
+
+ indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (indexForm->indisenabled != enable)
+ {
+ indexForm->indisenabled = enable;
+
+ CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
+ updated = true;
+ }
+
+ heap_freetuple(indexTuple);
+ table_close(pg_index, RowExclusiveLock);
+
+ if (updated)
+ {
+ CacheInvalidateRelcache(rel);
+ InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0);
+ CommandCounterIncrement();
+ }
+}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index b913f91ff0..eaa15a1f53 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -295,6 +295,19 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
info->opcintype = (Oid *) palloc(sizeof(Oid) * nkeycolumns);
info->canreturn = (bool *) palloc(sizeof(bool) * ncolumns);
+ /*
+ * Skip disabled indexes altogether, as they should not be considered
+ * for query planning. This builds the data structure for the planner's
+ * use and we make it part of IndexOptInfo since the index is already open.
+ * We also close the relation before continuing to the next index.
+ */
+ info->enabled = index->indisenabled;
+ if (!info->enabled)
+ {
+ index_close(indexRelation, NoLock);
+ continue;
+ }
+
for (i = 0; i < ncolumns; i++)
{
info->indexkeys[i] = index->indkey.values[i];
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4aa8646af7..c5d0d66445 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -333,7 +333,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> add_drop opt_asc_desc opt_nulls_order
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
- replica_identity partition_cmd index_partition_cmd
+ replica_identity partition_cmd index_partition_cmd index_alter_cmd
%type <list> alter_table_cmds alter_type_cmds
%type <list> alter_identity_column_option_list
%type <defelt> alter_identity_column_option
@@ -496,6 +496,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <boolean> opt_unique opt_verbose opt_full
%type <boolean> opt_freeze opt_analyze opt_default
%type <defelt> opt_binary copy_delimiter
+%type <boolean> opt_index_enabled
%type <boolean> copy_from opt_program
@@ -2168,6 +2169,24 @@ AlterTableStmt:
n->nowait = $13;
$$ = (Node *) n;
}
+ | ALTER INDEX qualified_name index_alter_cmd
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $3;
+ n->cmds = list_make1($4);
+ n->objtype = OBJECT_INDEX;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | ALTER INDEX IF_P EXISTS qualified_name index_alter_cmd
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = list_make1($6);
+ n->objtype = OBJECT_INDEX;
+ n->missing_ok = true;
+ $$ = (Node *) n;
+ }
| ALTER INDEX qualified_name alter_table_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
@@ -2393,6 +2412,21 @@ index_partition_cmd:
}
;
+index_alter_cmd:
+ /* ALTER INDEX <name> ENABLE|DISABLE */
+ ENABLE_P
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_EnableIndex;
+ $$ = (Node *) n;
+ }
+ | DISABLE_P
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DisableIndex;
+ $$ = (Node *) n;
+ }
+ ;
alter_table_cmd:
/* ALTER TABLE <name> ADD <coldef> */
ADD_P columnDef
@@ -8126,7 +8160,7 @@ defacl_privilege_target:
IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_enabled
{
IndexStmt *n = makeNode(IndexStmt);
@@ -8141,6 +8175,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
n->options = $14;
n->tableSpace = $15;
n->whereClause = $16;
+ n->isenabled = $17;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -8158,7 +8193,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_enabled
{
IndexStmt *n = makeNode(IndexStmt);
@@ -8173,6 +8208,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
n->options = $17;
n->tableSpace = $18;
n->whereClause = $19;
+ n->isenabled = $20;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -8195,6 +8231,12 @@ opt_unique:
| /*EMPTY*/ { $$ = false; }
;
+opt_index_enabled:
+ ENABLE_P { $$ = true; }
+ | DISABLE_P { $$ = false; }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
access_method_clause:
USING name { $$ = $2; }
| /*EMPTY*/ { $$ = DEFAULT_INDEX_TYPE; }
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 1e15ce10b4..32554612ed 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1588,6 +1588,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
index->concurrent = false;
index->if_not_exists = false;
index->reset_default_tblspc = false;
+ index->isenabled = idxrec->indisenabled;
/*
* We don't try to preserve the name of the source index; instead, just
@@ -2214,6 +2215,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
index->concurrent = false;
index->if_not_exists = false;
index->reset_default_tblspc = constraint->reset_default_tblspc;
+ /* Ensure indexes for constraints are created as enabled by default */
+ index->isenabled = true;
/*
* If it's ALTER TABLE ADD CONSTRAINT USING INDEX, look up the index and
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 2177d17e27..05b27ca232 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1559,6 +1559,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
else
appendStringInfo(&buf, " WHERE %s", str);
}
+
+ /* Add DISABLE clause if the index is disabled */
+ if (!idxrec->indisenabled)
+ appendStringInfoString(&buf, " DISABLE");
}
/* Clean up */
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index c326f687eb..b3b43df080 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2344,6 +2344,7 @@ RelationReloadIndexInfo(Relation relation)
relation->rd_index->indisready = index->indisready;
relation->rd_index->indislive = index->indislive;
relation->rd_index->indisreplident = index->indisreplident;
+ relation->rd_index->indisenabled = index->indisenabled;
/* Copy xmin too, as that is needed to make sense of indcheckxmin */
HeapTupleHeaderSetXmin(relation->rd_indextuple->t_data,
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 2dea96f47c..8a47cfd174 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -65,6 +65,7 @@ extern void index_check_primary_key(Relation heapRel,
#define INDEX_CREATE_IF_NOT_EXISTS (1 << 4)
#define INDEX_CREATE_PARTITIONED (1 << 5)
#define INDEX_CREATE_INVALID (1 << 6)
+#define INDEX_CREATE_ENABLED (1 << 7)
extern Oid index_create(Relation heapRelation,
const char *indexRelationName,
@@ -175,7 +176,6 @@ extern void RestoreReindexState(const void *reindexstate);
extern void IndexSetParentIndex(Relation partitionIdx, Oid parentOid);
-
/*
* itemptr_encode - Encode ItemPointer as int64/int8
*
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 6c89639a9e..b0c7e5f365 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -44,6 +44,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
bool indisready; /* is this index ready for inserts? */
bool indislive; /* is this index alive at all? */
bool indisreplident; /* is this index the identity for replication? */
+ bool indisenabled; /* is this index enabled for use by queries? */
/* variable-length fields start here, but we allow direct access to indkey */
int2vector indkey BKI_FORCE_NOT_NULL; /* column numbers of indexed cols,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index c92cef3d16..8b85e91985 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2414,6 +2414,8 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_EnableIndex, /* ENABLE INDEX */
+ AT_DisableIndex, /* DISABLE INDEX */
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -3392,6 +3394,7 @@ typedef struct IndexStmt
bool if_not_exists; /* just do nothing if index already exists? */
bool reset_default_tblspc; /* reset default_tablespace prior to
* executing */
+ bool isenabled; /* true if ENABLE (default), false if DISABLE */
} IndexStmt;
/* ----------------------
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 07e2415398..67a4d80d92 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1207,6 +1207,8 @@ struct IndexOptInfo
/* AM's cost estimator */
/* Rather than include amapi.h here, we declare amcostestimate like this */
void (*amcostestimate) () pg_node_attr(read_write_ignore);
+ /* true if this index is enabled */
+ bool enabled;
};
/*
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d3358dfc39..22513ae978 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2965,6 +2965,500 @@ ERROR: REINDEX SCHEMA cannot run inside a transaction block
END;
-- concurrently
REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test enable/disable functionality for indexes
+-- Setup
+CREATE TABLE index_test(
+ id INT PRIMARY KEY,
+ data TEXT,
+ num INT,
+ vector INT[],
+ range INT4RANGE
+);
+INSERT INTO index_test
+SELECT
+ g,
+ 'data ' || g,
+ g % 100,
+ ARRAY[g, g+1, g+2],
+ int4range(g, g+10)
+FROM generate_series(1, 1000) g;
+-- Function for testing
+CREATE FUNCTION get_data_length(text) RETURNS INT AS $$
+ SELECT length($1);
+$$ LANGUAGE SQL IMMUTABLE;
+-- Helper function to show index status
+CREATE OR REPLACE FUNCTION show_index_status(index_name text)
+RETURNS TABLE (
+ indexrelid regclass,
+ indisvalid boolean,
+ indisready boolean,
+ indislive boolean,
+ indisenabled boolean
+) AS $$
+BEGIN
+ RETURN QUERY EXECUTE format('
+ SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+ FROM pg_index
+ WHERE indexrelid = %L::regclass', index_name);
+END;
+$$ LANGUAGE plpgsql;
+-- Create and test each index type
+-- 1. Basic single-column index
+CREATE INDEX idx_single ON index_test(data);
+SELECT show_index_status('idx_single');
+ show_index_status
+----------------------
+ (idx_single,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ QUERY PLAN
+-----------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (data = 'data 500'::text)
+ -> Bitmap Index Scan on idx_single
+ Index Cond: (data = 'data 500'::text)
+(4 rows)
+
+ALTER INDEX idx_single DISABLE;
+SELECT show_index_status('idx_single');
+ show_index_status
+----------------------
+ (idx_single,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ QUERY PLAN
+-------------------------------------
+ Seq Scan on index_test
+ Filter: (data = 'data 500'::text)
+(2 rows)
+
+ALTER INDEX idx_single ENABLE;
+SELECT show_index_status('idx_single');
+ show_index_status
+----------------------
+ (idx_single,t,t,t,t)
+(1 row)
+
+-- 2. Multi-column index
+CREATE INDEX idx_multi ON index_test(num, data);
+SELECT show_index_status('idx_multi');
+ show_index_status
+---------------------
+ (idx_multi,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ QUERY PLAN
+----------------------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: ((num = 50) AND (data > 'data 500'::text))
+ -> Bitmap Index Scan on idx_multi
+ Index Cond: ((num = 50) AND (data > 'data 500'::text))
+(4 rows)
+
+ALTER INDEX idx_multi DISABLE;
+SELECT show_index_status('idx_multi');
+ show_index_status
+---------------------
+ (idx_multi,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ QUERY PLAN
+------------------------------------------------------
+ Seq Scan on index_test
+ Filter: ((data > 'data 500'::text) AND (num = 50))
+(2 rows)
+
+ALTER INDEX idx_multi ENABLE;
+SELECT show_index_status('idx_multi');
+ show_index_status
+---------------------
+ (idx_multi,t,t,t,t)
+(1 row)
+
+-- 3. Partial index
+CREATE INDEX idx_partial ON index_test(num) WHERE num < 50;
+SELECT show_index_status('idx_partial');
+ show_index_status
+-----------------------
+ (idx_partial,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ QUERY PLAN
+----------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 25)
+ -> Bitmap Index Scan on idx_partial
+ Index Cond: (num = 25)
+(4 rows)
+
+ALTER INDEX idx_partial DISABLE;
+SELECT show_index_status('idx_partial');
+ show_index_status
+-----------------------
+ (idx_partial,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ QUERY PLAN
+--------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 25)
+ -> Bitmap Index Scan on idx_multi
+ Index Cond: (num = 25)
+(4 rows)
+
+ALTER INDEX idx_partial ENABLE;
+SELECT show_index_status('idx_partial');
+ show_index_status
+-----------------------
+ (idx_partial,t,t,t,t)
+(1 row)
+
+-- 4. Expression index
+CREATE INDEX idx_expression ON index_test((lower(data)));
+SELECT show_index_status('idx_expression');
+ show_index_status
+--------------------------
+ (idx_expression,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ QUERY PLAN
+------------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (lower(data) = 'data 500'::text)
+ -> Bitmap Index Scan on idx_expression
+ Index Cond: (lower(data) = 'data 500'::text)
+(4 rows)
+
+ALTER INDEX idx_expression DISABLE;
+SELECT show_index_status('idx_expression');
+ show_index_status
+--------------------------
+ (idx_expression,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on index_test
+ Filter: (lower(data) = 'data 500'::text)
+(2 rows)
+
+ALTER INDEX idx_expression ENABLE;
+SELECT show_index_status('idx_expression');
+ show_index_status
+--------------------------
+ (idx_expression,t,t,t,t)
+(1 row)
+
+-- 5. GIN index
+CREATE INDEX idx_gin ON index_test USING gin(vector);
+SELECT show_index_status('idx_gin');
+ show_index_status
+-------------------
+ (idx_gin,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ QUERY PLAN
+----------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (vector @> '{500}'::integer[])
+ -> Bitmap Index Scan on idx_gin
+ Index Cond: (vector @> '{500}'::integer[])
+(4 rows)
+
+ALTER INDEX idx_gin DISABLE;
+SELECT show_index_status('idx_gin');
+ show_index_status
+-------------------
+ (idx_gin,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on index_test
+ Filter: (vector @> '{500}'::integer[])
+(2 rows)
+
+ALTER INDEX idx_gin ENABLE;
+SELECT show_index_status('idx_gin');
+ show_index_status
+-------------------
+ (idx_gin,t,t,t,t)
+(1 row)
+
+-- 6. GiST index
+CREATE INDEX idx_gist ON index_test USING gist(range);
+SELECT show_index_status('idx_gist');
+ show_index_status
+--------------------
+ (idx_gist,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ QUERY PLAN
+-------------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (range && '[100,110)'::int4range)
+ -> Bitmap Index Scan on idx_gist
+ Index Cond: (range && '[100,110)'::int4range)
+(4 rows)
+
+ALTER INDEX idx_gist DISABLE;
+SELECT show_index_status('idx_gist');
+ show_index_status
+--------------------
+ (idx_gist,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ QUERY PLAN
+---------------------------------------------
+ Seq Scan on index_test
+ Filter: (range && '[100,110)'::int4range)
+(2 rows)
+
+ALTER INDEX idx_gist ENABLE;
+SELECT show_index_status('idx_gist');
+ show_index_status
+--------------------
+ (idx_gist,t,t,t,t)
+(1 row)
+
+-- 7. Covering index
+CREATE INDEX idx_covering ON index_test(num) INCLUDE (data);
+SELECT show_index_status('idx_covering');
+ show_index_status
+------------------------
+ (idx_covering,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ QUERY PLAN
+-----------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 50)
+ -> Bitmap Index Scan on idx_covering
+ Index Cond: (num = 50)
+(4 rows)
+
+ALTER INDEX idx_covering DISABLE;
+SELECT show_index_status('idx_covering');
+ show_index_status
+------------------------
+ (idx_covering,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ QUERY PLAN
+--------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 50)
+ -> Bitmap Index Scan on idx_multi
+ Index Cond: (num = 50)
+(4 rows)
+
+ALTER INDEX idx_covering ENABLE;
+SELECT show_index_status('idx_covering');
+ show_index_status
+------------------------
+ (idx_covering,t,t,t,t)
+(1 row)
+
+-- 8. Unique index
+CREATE UNIQUE INDEX idx_unique ON index_test(id, data);
+SELECT show_index_status('idx_unique');
+ show_index_status
+----------------------
+ (idx_unique,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ QUERY PLAN
+----------------------------------------------------------
+ Index Scan using idx_unique on index_test
+ Index Cond: ((id = 500) AND (data = 'data 500'::text))
+(2 rows)
+
+ALTER INDEX idx_unique DISABLE;
+SELECT show_index_status('idx_unique');
+ show_index_status
+----------------------
+ (idx_unique,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using index_test_pkey on index_test
+ Index Cond: (id = 500)
+ Filter: (data = 'data 500'::text)
+(3 rows)
+
+ALTER INDEX idx_unique ENABLE;
+SELECT show_index_status('idx_unique');
+ show_index_status
+----------------------
+ (idx_unique,t,t,t,t)
+(1 row)
+
+-- 9. Function-based index
+CREATE INDEX idx_func ON index_test(get_data_length(data));
+SELECT show_index_status('idx_func');
+ show_index_status
+--------------------
+ (idx_func,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ QUERY PLAN
+-----------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (length(data) = 10)
+ -> Bitmap Index Scan on idx_func
+ Index Cond: (length(data) = 10)
+(4 rows)
+
+ALTER INDEX idx_func DISABLE;
+SELECT show_index_status('idx_func');
+ show_index_status
+--------------------
+ (idx_func,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ QUERY PLAN
+-------------------------------
+ Seq Scan on index_test
+ Filter: (length(data) = 10)
+(2 rows)
+
+ALTER INDEX idx_func ENABLE;
+SELECT show_index_status('idx_func');
+ show_index_status
+--------------------
+ (idx_func,t,t,t,t)
+(1 row)
+
+-- 10. Join index
+CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT);
+INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g;
+CREATE INDEX idx_join ON join_test(ref_id);
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+ QUERY PLAN
+---------------------------------------------------------------
+ Hash Join
+ Hash Cond: (it.num = jt.ref_id)
+ -> Seq Scan on index_test it
+ -> Hash
+ -> Bitmap Heap Scan on join_test jt
+ Recheck Cond: ((id >= 100) AND (id <= 200))
+ -> Bitmap Index Scan on join_test_pkey
+ Index Cond: ((id >= 100) AND (id <= 200))
+(8 rows)
+
+-- Disable all indexes to force seq scan
+ALTER INDEX idx_join DISABLE;
+ALTER INDEX join_test_pkey DISABLE;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,f)
+(1 row)
+
+SELECT show_index_status('join_test_pkey');
+ show_index_status
+--------------------------
+ (join_test_pkey,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+ QUERY PLAN
+-----------------------------------------------------
+ Hash Join
+ Hash Cond: (it.num = jt.ref_id)
+ -> Seq Scan on index_test it
+ -> Hash
+ -> Seq Scan on join_test jt
+ Filter: ((id >= 100) AND (id <= 200))
+(6 rows)
+
+ALTER INDEX idx_join ENABLE;
+ALTER INDEX join_test_pkey ENABLE;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,t)
+(1 row)
+
+SELECT show_index_status('join_test_pkey');
+ show_index_status
+--------------------------
+ (join_test_pkey,t,t,t,t)
+(1 row)
+
+-- Test REINDEX CONCURRENTLY
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,t)
+(1 row)
+
+ALTER INDEX idx_join DISABLE;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,f)
+(1 row)
+
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,f)
+(1 row)
+
+SELECT pg_get_indexdef('idx_join'::regclass);
+ pg_get_indexdef
+-----------------------------------------------------------------------------------
+ CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id) DISABLE
+(1 row)
+
+ALTER INDEX idx_join ENABLE;
+SELECT pg_get_indexdef('idx_join'::regclass);
+ pg_get_indexdef
+---------------------------------------------------------------------------
+ CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id)
+(1 row)
+
+-- Clean up
+DROP TABLE index_test;
+DROP TABLE join_test;
+DROP FUNCTION get_data_length;
+DROP FUNCTION show_index_status;
-- Failure for unauthorized user
CREATE ROLE regress_reindexuser NOLOGIN;
SET SESSION ROLE regress_reindexuser;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index fe162cc7c3..db547898aa 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1297,6 +1297,187 @@ END;
-- concurrently
REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test enable/disable functionality for indexes
+
+-- Setup
+CREATE TABLE index_test(
+ id INT PRIMARY KEY,
+ data TEXT,
+ num INT,
+ vector INT[],
+ range INT4RANGE
+);
+
+INSERT INTO index_test
+SELECT
+ g,
+ 'data ' || g,
+ g % 100,
+ ARRAY[g, g+1, g+2],
+ int4range(g, g+10)
+FROM generate_series(1, 1000) g;
+
+-- Function for testing
+CREATE FUNCTION get_data_length(text) RETURNS INT AS $$
+ SELECT length($1);
+$$ LANGUAGE SQL IMMUTABLE;
+
+-- Helper function to show index status
+CREATE OR REPLACE FUNCTION show_index_status(index_name text)
+RETURNS TABLE (
+ indexrelid regclass,
+ indisvalid boolean,
+ indisready boolean,
+ indislive boolean,
+ indisenabled boolean
+) AS $$
+BEGIN
+ RETURN QUERY EXECUTE format('
+ SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+ FROM pg_index
+ WHERE indexrelid = %L::regclass', index_name);
+END;
+$$ LANGUAGE plpgsql;
+
+-- Create and test each index type
+-- 1. Basic single-column index
+CREATE INDEX idx_single ON index_test(data);
+SELECT show_index_status('idx_single');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ALTER INDEX idx_single DISABLE;
+SELECT show_index_status('idx_single');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ALTER INDEX idx_single ENABLE;
+SELECT show_index_status('idx_single');
+
+-- 2. Multi-column index
+CREATE INDEX idx_multi ON index_test(num, data);
+SELECT show_index_status('idx_multi');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ALTER INDEX idx_multi DISABLE;
+SELECT show_index_status('idx_multi');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ALTER INDEX idx_multi ENABLE;
+SELECT show_index_status('idx_multi');
+
+-- 3. Partial index
+CREATE INDEX idx_partial ON index_test(num) WHERE num < 50;
+SELECT show_index_status('idx_partial');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ALTER INDEX idx_partial DISABLE;
+SELECT show_index_status('idx_partial');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ALTER INDEX idx_partial ENABLE;
+SELECT show_index_status('idx_partial');
+
+-- 4. Expression index
+CREATE INDEX idx_expression ON index_test((lower(data)));
+SELECT show_index_status('idx_expression');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ALTER INDEX idx_expression DISABLE;
+SELECT show_index_status('idx_expression');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ALTER INDEX idx_expression ENABLE;
+SELECT show_index_status('idx_expression');
+
+-- 5. GIN index
+CREATE INDEX idx_gin ON index_test USING gin(vector);
+SELECT show_index_status('idx_gin');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ALTER INDEX idx_gin DISABLE;
+SELECT show_index_status('idx_gin');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ALTER INDEX idx_gin ENABLE;
+SELECT show_index_status('idx_gin');
+
+-- 6. GiST index
+CREATE INDEX idx_gist ON index_test USING gist(range);
+SELECT show_index_status('idx_gist');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ALTER INDEX idx_gist DISABLE;
+SELECT show_index_status('idx_gist');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ALTER INDEX idx_gist ENABLE;
+SELECT show_index_status('idx_gist');
+
+-- 7. Covering index
+CREATE INDEX idx_covering ON index_test(num) INCLUDE (data);
+SELECT show_index_status('idx_covering');
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ALTER INDEX idx_covering DISABLE;
+SELECT show_index_status('idx_covering');
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ALTER INDEX idx_covering ENABLE;
+SELECT show_index_status('idx_covering');
+
+-- 8. Unique index
+CREATE UNIQUE INDEX idx_unique ON index_test(id, data);
+SELECT show_index_status('idx_unique');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ALTER INDEX idx_unique DISABLE;
+SELECT show_index_status('idx_unique');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ALTER INDEX idx_unique ENABLE;
+SELECT show_index_status('idx_unique');
+
+-- 9. Function-based index
+CREATE INDEX idx_func ON index_test(get_data_length(data));
+SELECT show_index_status('idx_func');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ALTER INDEX idx_func DISABLE;
+SELECT show_index_status('idx_func');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ALTER INDEX idx_func ENABLE;
+SELECT show_index_status('idx_func');
+
+-- 10. Join index
+CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT);
+INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g;
+CREATE INDEX idx_join ON join_test(ref_id);
+SELECT show_index_status('idx_join');
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+
+-- Disable all indexes to force seq scan
+ALTER INDEX idx_join DISABLE;
+ALTER INDEX join_test_pkey DISABLE;
+SELECT show_index_status('idx_join');
+SELECT show_index_status('join_test_pkey');
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+
+ALTER INDEX idx_join ENABLE;
+ALTER INDEX join_test_pkey ENABLE;
+SELECT show_index_status('idx_join');
+SELECT show_index_status('join_test_pkey');
+
+-- Test REINDEX CONCURRENTLY
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+
+ALTER INDEX idx_join DISABLE;
+SELECT show_index_status('idx_join');
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+
+SELECT pg_get_indexdef('idx_join'::regclass);
+ALTER INDEX idx_join ENABLE;
+SELECT pg_get_indexdef('idx_join'::regclass);
+
+-- Clean up
+DROP TABLE index_test;
+DROP TABLE join_test;
+DROP FUNCTION get_data_length;
+DROP FUNCTION show_index_status;
+
-- Failure for unauthorized user
CREATE ROLE regress_reindexuser NOLOGIN;
SET SESSION ROLE regress_reindexuser;
--
2.37.1 (Apple Git-137.1)
view thread (23+ messages) latest in thread
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], [email protected], [email protected]
Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)
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