I went back to look at this patch and a few things. I noticed it did
not have correct
indentation, so I ran pgindent. I also removed some extra lines added and made
some slight adjustments to the docs. Attached my edited patch as a txt. If you
agree, please revise into a v14.
I also noticed that between v12 and v13, the GUC use_invisible_index
was removed,
but I don't see a discussion as to why. I feel it's a good GUC to have
[0], and we should
at least have it as a separate patch as part of this set.
I will continue reviewing the patch, but i feel this may be close to
be marked RFC, although
not sure if it will get a committer review before code freeze.
[0]
https://www.postgresql.org/message-id/flat/CAA5RZ0udzydObMDi65C59-oq54B9ZmjSZ1wVH3h%2Bv4XiVm6QDA%40mail.gmail.com#cfea240ffd73e947f9edd1ef1c762dae
--
Sami Imseih
Amazon Web Services (AWS)
From c8886d5ac14f31897a7f8058a1caab3d9213993e Mon Sep 17 00:00:00 2001
From: Sami Imseih <sims...@amazon.com>
Date: Tue, 1 Apr 2025 12:27:23 -0500
Subject: [PATCH 1/1] 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 | 66 +++
src/backend/optimizer/util/plancat.c | 14 +
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 | 1 +
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 | 505 +++++++++++++++++++++
src/test/regress/sql/create_index.sql | 192 ++++++++
19 files changed, 954 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fb050635551..123fcb04892 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4618,6 +4618,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 1d42d05d858..04b3b0b9bfe 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>
@@ -159,6 +161,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>
@@ -301,6 +330,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 208389e8006..d7a2f7df852 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
+ is 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, allowing it to 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 9833f52c1be..9eea080ab5b 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -303,6 +303,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,
@@ -356,6 +357,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 739a92bdcc1..510755a4c0b 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -119,7 +119,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);
@@ -571,7 +572,8 @@ UpdateIndexRelation(Oid indexoid,
bool isexclusion,
bool immediate,
bool isvalid,
- bool isready)
+ bool isready,
+ bool isenabled)
{
int2vector *indkey;
oidvector *indcollation;
@@ -649,6 +651,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);
@@ -714,6 +717,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
@@ -759,6 +764,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;
@@ -1042,13 +1048,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
@@ -1317,6 +1325,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);
@@ -1344,6 +1354,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))
@@ -1435,6 +1448,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.
*
@@ -1458,7 +1481,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 874a8fc89ad..a612a0a3de1 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 33c2106c17c..90f9b111e57 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1222,6 +1222,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 10624353b0a..58d3d29d565 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -719,6 +719,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);
/* ----------------------------------------------------------------
@@ -4685,6 +4686,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetExpression:
case AT_DropExpression:
case AT_SetCompression:
+ case AT_EnableIndex:
+ case AT_DisableIndex:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -5249,6 +5252,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);
@@ -5645,6 +5654,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);
@@ -6591,6 +6606,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:
@@ -21466,3 +21483,52 @@ 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->indcheckxmin)
+ {
+ heap_freetuple(indexTuple);
+ table_close(pg_index, RowExclusiveLock);
+ ereport(ERROR,
+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot enable/disable index while
indcheckxmin is true"),
+ errhint("Wait for all transactions that might
see inconsistent HOT chains to complete")));
+ }
+
+ 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 0489ad36644..08167df3f8b 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -295,6 +295,20 @@ 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 0fc502a3a40..414a830ba1f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -324,7 +324,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
@@ -491,6 +491,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
@@ -2157,6 +2158,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);
@@ -2382,6 +2401,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
@@ -8185,7 +8219,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);
@@ -8200,6 +8234,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;
@@ -8217,7 +8252,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);
@@ -8232,6 +8267,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;
@@ -8254,6 +8290,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 9c1541e1fea..488de757bc2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1745,6 +1745,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
@@ -2366,6 +2367,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 9e90acedb91..3f6a89efaf4 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1565,6 +1565,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 9f54a9e72b7..5e59a73d655 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2318,6 +2318,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 4daa8bef5ee..a390ab76789 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,
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 4392b9d221d..766a52193cb 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 df331b1c0d9..aeb9ac844a1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2472,6 +2472,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 AlterTableCmd /* one subcommand of an ALTER TABLE */
@@ -3468,6 +3470,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 c24a1fc8514..a9833fe2a64 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1239,6 +1239,8 @@ struct IndexOptInfo
/* AM's cost estimator */
/* Rather than include amapi.h here, we declare amcostestimate like
this */
void (*amcostestimate) (struct PlannerInfo *, struct
IndexPath *, double, Cost *, Cost *, Selectivity *, double *, double *)
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 15be0043ad4..84461f234b4 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -3394,6 +3394,511 @@ ERROR: REINDEX SCHEMA cannot run inside a transaction
block
END;
-- concurrently
REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test enable/disable functionality for indexes
+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)
+
+-- Test enable/disable index with indcheckxmin being true
+CREATE TABLE indcheckxmin_test(id int PRIMARY KEY);
+INSERT INTO indcheckxmin_test VALUES (1), (2), (3);
+CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id);
+BEGIN;
+-- Simulate indcheckxmin being true
+UPDATE pg_index SET indcheckxmin = true WHERE indexrelid =
'idx_checkxmin'::regclass;
+ALTER INDEX idx_checkxmin DISABLE; -- expect fail
+ERROR: cannot enable/disable index while indcheckxmin is true
+HINT: Wait for all transactions that might see inconsistent HOT chains to
complete
+ROLLBACK;
+-- Clean up
+DROP TABLE index_test;
+DROP TABLE join_test;
+DROP FUNCTION get_data_length;
+DROP FUNCTION show_index_status;
+DROP TABLE indcheckxmin_test CASCADE;
-- 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 6b3852dddd8..48569a0924a 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1435,6 +1435,198 @@ END;
-- concurrently
REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test enable/disable functionality for indexes
+
+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);
+
+-- Test enable/disable index with indcheckxmin being true
+
+CREATE TABLE indcheckxmin_test(id int PRIMARY KEY);
+INSERT INTO indcheckxmin_test VALUES (1), (2), (3);
+CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id);
+BEGIN;
+-- Simulate indcheckxmin being true
+UPDATE pg_index SET indcheckxmin = true WHERE indexrelid =
'idx_checkxmin'::regclass;
+ALTER INDEX idx_checkxmin DISABLE; -- expect fail
+ROLLBACK;
+
+-- Clean up
+DROP TABLE index_test;
+DROP TABLE join_test;
+DROP FUNCTION get_data_length;
+DROP FUNCTION show_index_status;
+DROP TABLE indcheckxmin_test CASCADE;
+
-- Failure for unauthorized user
CREATE ROLE regress_reindexuser NOLOGIN;
SET SESSION ROLE regress_reindexuser;
--
2.39.5 (Apple Git-154)
