v2-0001-Introduce-the-ability-to-enable-disable-indexes.patch

From 848b143d6ae1817602aa68710e9bcc033c061da9 Mon Sep 17 00:00:00 2001
From: Shayon Mukherjee <shayonj@gmail.com>
Date: Sun, 22 Sep 2024 14:01:45 -0400
Subject: [PATCH v2] Introduce the ability to enable/disable indexes

---
 doc/src/sgml/catalogs.sgml                 |  11 +
 doc/src/sgml/ref/alter_index.sgml          |  39 ++++
 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           |  94 +++++++-
 src/backend/optimizer/path/indxpath.c      |  12 +
 src/backend/optimizer/util/plancat.c       |   2 +
 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/catversion.h           |   2 +-
 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 | 250 +++++++++++++++++++++
 src/test/regress/sql/create_index.sql      | 140 ++++++++++++
 21 files changed, 671 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index bfb97865e1..61fbf5beb5 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4590,6 +4590,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</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..613d63b747 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,29 @@ 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, or for temporarily reducing the overhead of index maintenance
+      during bulk data loading operations.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
   </para>
 
@@ -300,6 +325,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 b2b3ecb524..1989cb8ce9 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 738bc46ae8..ce497eed58 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -324,7 +324,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 f99c2d2dee..7cd2041300 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1191,6 +1191,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 2d703aa22e..905a3f58a2 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
@@ -4546,6 +4546,8 @@ AlterTableGetLockLevel(List *cmds)
 			case AT_SetExpression:
 			case AT_DropExpression:
 			case AT_SetCompression:
+			case AT_EnableIndex:
+			case AT_DisableIndex:
 				cmd_lockmode = AccessExclusiveLock;
 				break;
 
@@ -5123,6 +5125,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);
@@ -5519,6 +5527,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);
@@ -6418,6 +6432,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:
@@ -20214,3 +20230,79 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * ATExecEnableDisableIndex function handles the execution of enabling or disabling an index.
+ * It performs an in-place update to preserve the pg_index row's xmin.
+ */
+static void
+ATExecEnableDisableIndex(Relation rel, bool enable)
+{
+		Oid         indexOid = RelationGetRelid(rel);
+		Relation    pg_index;
+		HeapTuple   indexTuple;
+		Form_pg_index indexForm;
+		bool        updated = false;
+
+		/* Open pg_index */
+		pg_index = table_open(IndexRelationId, RowExclusiveLock);
+
+		/* Fetch the index's pg_index tuple */
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexOid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexOid);
+
+		indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		/* Check if the index's current state differs from the desired state */
+		if (indexForm->indisenabled != enable)
+		{
+			HeapTuple copyTuple;
+
+			/* Create a modifiable copy of the tuple */
+			copyTuple = heap_copytuple(indexTuple);
+			indexForm = (Form_pg_index) GETSTRUCT(copyTuple);
+
+			indexForm->indisenabled = enable;
+
+			/* Perform an in-place update */
+			heap_inplace_update(pg_index, copyTuple);
+
+			/* Free the copy */
+			heap_freetuple(copyTuple);
+
+			updated = true;
+
+			/* Update relcache */
+			CacheInvalidateRelcache(rel);
+
+			/*
+				* Invalidate the relcache for the table, so that after we commit
+				* all sessions will refresh the table's index state before
+				* attempting to use this index.
+			*/
+			CacheInvalidateRelcache(rel);
+
+			ereport(NOTICE,
+				(errmsg("index \"%s\" is now %s",
+					RelationGetRelationName(rel),
+					enable ? "enabled" : "disabled")));
+		}
+		else
+		{
+			ereport(NOTICE,
+				(errmsg("index \"%s\" is already %s",
+					RelationGetRelationName(rel),
+					enable ? "enabled" : "disabled")));
+		}
+
+		/* Clean up */
+		ReleaseSysCache(indexTuple);
+		table_close(pg_index, RowExclusiveLock);
+
+		/* Invoke the object access hook if we updated the index */
+		if (updated)
+		{
+			InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0);
+		}
+}
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c0fcc7d78d..843237dadf 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -254,6 +254,10 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
 	{
 		IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
 
+		/* Skip disabled indexes */
+		if (!index->enabled)
+				continue;
+
 		/* Protect limited-size array in IndexClauseSets */
 		Assert(index->nkeycolumns <= INDEX_MAX_KEYS);
 
@@ -715,6 +719,10 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
 	bool		skip_nonnative_saop = false;
 	ListCell   *lc;
 
+	/* Skip disabled indexes */
+	if (!index->enabled)
+			return;
+
 	/*
 	 * Build simple index paths using the clauses.  Allow ScalarArrayOpExpr
 	 * clauses only if the index AM supports them natively.
@@ -823,6 +831,10 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 
 	Assert(skip_nonnative_saop != NULL || scantype == ST_BITMAPSCAN);
 
+	/* Skip disabled indexes */
+	if (!index->enabled)
+		return NIL;
+
 	/*
 	 * Check that index supports the desired scan type(s)
 	 */
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index b913f91ff0..30dc6353f6 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -192,6 +192,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 		}
 	}
 
+
 	/*
 	 * Estimate relation size --- unless it's an inheritance parent, in which
 	 * case the size we want is not the rel's own size but the size of its
@@ -459,6 +460,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			info->unique = index->indisunique;
 			info->immediate = index->indimmediate;
 			info->hypothetical = false;
+			info->enabled = index->indisenabled;
 
 			/*
 			 * Estimate the index size.  If it's not a partial index, we lock
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ab304ca989..7e47541541 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
 
@@ -2144,6 +2145,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);
@@ -2369,6 +2388,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
@@ -8102,7 +8136,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);
 
@@ -8117,6 +8151,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;
@@ -8134,7 +8169,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);
 
@@ -8149,6 +8184,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;
@@ -8171,6 +8207,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 5b6b7b809c..7cd5902ad7 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/catversion.h b/src/include/catalog/catversion.h
index 10bb26f2e4..a20af83da4 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202409211
+#define CATALOG_VERSION_NO	202409220
 
 #endif
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 e62ce1b753..e5125895b0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2413,6 +2413,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
@@ -3378,6 +3380,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..4096c4b797 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1188,6 +1188,8 @@ struct IndexOptInfo
 	bool		immediate;
 	/* true if index doesn't really exist */
 	bool		hypothetical;
+	/* is index enable? */
+  bool		enabled;
 
 	/*
 	 * Remaining fields are copied from the index AM's API struct
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d3358dfc39..57add757aa 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2965,6 +2965,256 @@ 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 enable_disable_test(id int primary key, data text);
+INSERT INTO enable_disable_test SELECT g, 'data ' || g FROM generate_series(1, 1000) g;
+-- CREATE INDEX with ENABLED/DISABLED
+CREATE INDEX enable_disable_idx1 ON enable_disable_test(data) DISABLE;
+CREATE INDEX enable_disable_idx2 ON enable_disable_test(data);
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | f
+ enable_disable_idx2      | t          | t          | t         | t
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+-- ALTER INDEX ... ENABLE/DISABLE
+-- Before
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | f
+ enable_disable_idx2      | t          | t          | t         | t
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+ALTER INDEX enable_disable_idx2 DISABLE;
+NOTICE:  index "enable_disable_idx2" is now disabled
+-- After
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | f
+ enable_disable_idx2      | t          | t          | t         | f
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+-- Enable all indexes
+ALTER INDEX enable_disable_idx2 ENABLE;
+NOTICE:  index "enable_disable_idx2" is now enabled
+ALTER INDEX enable_disable_idx1 ENABLE;
+NOTICE:  index "enable_disable_idx1" is now enabled
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | t
+ enable_disable_idx2      | t          | t          | t         | t
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+-- REINDEX TABLE
+REINDEX TABLE enable_disable_test;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | t
+ enable_disable_idx2      | t          | t          | t         | t
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+-- REINDEX INDEX with enable/disable
+ALTER INDEX enable_disable_idx1 DISABLE;
+NOTICE:  index "enable_disable_idx1" is now disabled
+REINDEX INDEX enable_disable_idx1;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | f
+ enable_disable_idx2      | t          | t          | t         | t
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+-- REINDEX INDEX CONCURRENTLY with enable/disable
+ALTER INDEX enable_disable_idx1 ENABLE;
+NOTICE:  index "enable_disable_idx1" is now enabled
+REINDEX INDEX enable_disable_idx1;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid | indisready | indislive | indisenabled 
+--------------------------+------------+------------+-----------+--------------
+ enable_disable_idx1      | t          | t          | t         | t
+ enable_disable_idx2      | t          | t          | t         | t
+ enable_disable_test_pkey | t          | t          | t         | t
+(3 rows)
+
+-- Test ENABLE/DISABLE on TOAST index
+CREATE TABLE toast_test (id int primary key, data text);
+INSERT INTO toast_test SELECT g, repeat('long text ', 1000) FROM generate_series(1, 10) g;
+-- Check initial state of TOAST index
+SELECT indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indexrelid = (SELECT indexrelid FROM pg_index WHERE indrelid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'toast_test'::regclass));
+ indisvalid | indisready | indislive | indisenabled 
+------------+------------+-----------+--------------
+ t          | t          | t         | t
+(1 row)
+
+-- Disable TOAST index
+ALTER INDEX pg_toast.pg_toast_16385_index DISABLE;
+ERROR:  relation "pg_toast.pg_toast_16385_index" does not exist
+-- Check state after disabling TOAST index
+SELECT indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indexrelid = (SELECT indexrelid FROM pg_index WHERE indrelid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'toast_test'::regclass));
+ indisvalid | indisready | indislive | indisenabled 
+------------+------------+-----------+--------------
+ t          | t          | t         | t
+(1 row)
+
+-- Enable TOAST index
+ALTER INDEX pg_toast.pg_toast_16385_index ENABLE;
+ERROR:  relation "pg_toast.pg_toast_16385_index" does not exist
+-- Check state after enabling TOAST index
+SELECT indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indexrelid = (SELECT indexrelid FROM pg_index WHERE indrelid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'toast_test'::regclass));
+ indisvalid | indisready | indislive | indisenabled 
+------------+------------+-----------+--------------
+ t          | t          | t         | t
+(1 row)
+
+-- Test CREATE TABLE with UNIQUE constraint
+CREATE TABLE unique_constraint_test (id int UNIQUE, data text);
+INSERT INTO unique_constraint_test SELECT g, 'data ' || g FROM generate_series(1, 1000) g;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'unique_constraint_test'::regclass;
+          indexrelid           | indisvalid | indisready | indislive | indisenabled 
+-------------------------------+------------+------------+-----------+--------------
+ unique_constraint_test_id_key | t          | t          | t         | t
+(1 row)
+
+-- Test that the unique constraint index is used
+EXPLAIN (COSTS OFF) SELECT * FROM unique_constraint_test WHERE id = 500;
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Index Scan using unique_constraint_test_id_key on unique_constraint_test
+   Index Cond: (id = 500)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM unique_constraint_test WHERE id IN (100, 200, 300);
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Bitmap Heap Scan on unique_constraint_test
+   Recheck Cond: (id = ANY ('{100,200,300}'::integer[]))
+   ->  Bitmap Index Scan on unique_constraint_test_id_key
+         Index Cond: (id = ANY ('{100,200,300}'::integer[]))
+(4 rows)
+
+-- Test CREATE TABLE with INDEX
+CREATE TABLE index_test (id int, data text);
+INSERT INTO index_test SELECT g, 'data ' || g FROM generate_series(1, 1000) g;
+CREATE INDEX ON index_test (data);
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'index_test'::regclass;
+     indexrelid      | indisvalid | indisready | indislive | indisenabled 
+---------------------+------------+------------+-----------+--------------
+ index_test_data_idx | t          | t          | t         | t
+(1 row)
+
+-- Test that the index is used
+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 index_test_data_idx
+         Index Cond: (data = 'data 500'::text)
+(4 rows)
+
+-- Test index usage with joins
+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;
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.id
+WHERE jt.id BETWEEN 100 AND 200;
+                          QUERY PLAN                           
+---------------------------------------------------------------
+ Hash Join
+   Hash Cond: (it.id = 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)
+
+-- Test index usage with ORDER BY
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM index_test
+ORDER BY data
+LIMIT 10;
+                        QUERY PLAN                        
+----------------------------------------------------------
+ Limit
+   ->  Index Scan using index_test_data_idx on index_test
+(2 rows)
+
+-- Test disabling an index and its effect on query plan
+ALTER INDEX index_test_data_idx DISABLE;
+NOTICE:  index "index_test_data_idx" is now disabled
+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)
+
+-- Re-enable the index
+ALTER INDEX index_test_data_idx ENABLE;
+NOTICE:  index "index_test_data_idx" is now enabled
+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 index_test_data_idx
+         Index Cond: (data = 'data 500'::text)
+(4 rows)
+
+-- Clean up
+DROP TABLE enable_disable_test;
+DROP TABLE toast_test;
+DROP TABLE unique_constraint_test;
+DROP TABLE join_test;
+DROP TABLE index_test;
 -- 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..d599717c6f 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1297,6 +1297,146 @@ END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
 
+-- Test enable/disable functionality for indexes
+
+-- Setup
+CREATE TABLE enable_disable_test(id int primary key, data text);
+INSERT INTO enable_disable_test SELECT g, 'data ' || g FROM generate_series(1, 1000) g;
+
+-- CREATE INDEX with ENABLED/DISABLED
+CREATE INDEX enable_disable_idx1 ON enable_disable_test(data) DISABLE;
+CREATE INDEX enable_disable_idx2 ON enable_disable_test(data);
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+-- ALTER INDEX ... ENABLE/DISABLE
+-- Before
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+ALTER INDEX enable_disable_idx2 DISABLE;
+-- After
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+-- Enable all indexes
+ALTER INDEX enable_disable_idx2 ENABLE;
+ALTER INDEX enable_disable_idx1 ENABLE;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+-- REINDEX TABLE
+REINDEX TABLE enable_disable_test;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+-- REINDEX INDEX with enable/disable
+ALTER INDEX enable_disable_idx1 DISABLE;
+REINDEX INDEX enable_disable_idx1;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+-- REINDEX INDEX CONCURRENTLY with enable/disable
+ALTER INDEX enable_disable_idx1 ENABLE;
+REINDEX INDEX enable_disable_idx1;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'enable_disable_test'::regclass
+ORDER BY indexrelid::regclass::text;
+
+-- Test ENABLE/DISABLE on TOAST index
+CREATE TABLE toast_test (id int primary key, data text);
+INSERT INTO toast_test SELECT g, repeat('long text ', 1000) FROM generate_series(1, 10) g;
+
+-- Check initial state of TOAST index
+SELECT indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indexrelid = (SELECT indexrelid FROM pg_index WHERE indrelid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'toast_test'::regclass));
+
+-- Disable TOAST index
+ALTER INDEX pg_toast.pg_toast_16385_index DISABLE;
+
+-- Check state after disabling TOAST index
+SELECT indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indexrelid = (SELECT indexrelid FROM pg_index WHERE indrelid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'toast_test'::regclass));
+
+-- Enable TOAST index
+ALTER INDEX pg_toast.pg_toast_16385_index ENABLE;
+
+-- Check state after enabling TOAST index
+SELECT indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indexrelid = (SELECT indexrelid FROM pg_index WHERE indrelid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'toast_test'::regclass));
+
+-- Test CREATE TABLE with UNIQUE constraint
+CREATE TABLE unique_constraint_test (id int UNIQUE, data text);
+INSERT INTO unique_constraint_test SELECT g, 'data ' || g FROM generate_series(1, 1000) g;
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'unique_constraint_test'::regclass;
+
+-- Test that the unique constraint index is used
+EXPLAIN (COSTS OFF) SELECT * FROM unique_constraint_test WHERE id = 500;
+EXPLAIN (COSTS OFF) SELECT * FROM unique_constraint_test WHERE id IN (100, 200, 300);
+
+-- Test CREATE TABLE with INDEX
+CREATE TABLE index_test (id int, data text);
+INSERT INTO index_test SELECT g, 'data ' || g FROM generate_series(1, 1000) g;
+CREATE INDEX ON index_test (data);
+SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled
+FROM pg_index
+WHERE indrelid = 'index_test'::regclass;
+
+-- Test that the index is used
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+
+-- Test index usage with joins
+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;
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.id
+WHERE jt.id BETWEEN 100 AND 200;
+
+-- Test index usage with ORDER BY
+EXPLAIN (COSTS OFF)
+SELECT *
+FROM index_test
+ORDER BY data
+LIMIT 10;
+
+-- Test disabling an index and its effect on query plan
+ALTER INDEX index_test_data_idx DISABLE;
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+
+-- Re-enable the index
+ALTER INDEX index_test_data_idx ENABLE;
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+
+-- Clean up
+DROP TABLE enable_disable_test;
+DROP TABLE toast_test;
+DROP TABLE unique_constraint_test;
+DROP TABLE join_test;
+DROP TABLE index_test;
+
 -- Failure for unauthorized user
 CREATE ROLE regress_reindexuser NOLOGIN;
 SET SESSION ROLE regress_reindexuser;
-- 
2.37.1 (Apple Git-137.1)