v1-0001-Ability-to-enable-disable-indexes-through-GUC.patch

From 24daa35ee5a7ae803df2f588c67a40f61538774f Mon Sep 17 00:00:00 2001
From: Shayon Mukherjee <shayonj@gmail.com>
Date: Thu, 26 Sep 2024 13:37:07 -0400
Subject: [PATCH v1] Ability to enable/disable indexes through GUC

The patch introduces a new GUC parameter `disabled_indexes` that allows users to specify a comma-separated list of indexes to be ignored during query planning. Key aspects:

- Adds a new `isdisabled` attribute to the `IndexOptInfo` structure.
- Modifies `get_relation_info` in `plancat.c` to skip disabled indexes entirely, thus reducing the number of places we need to check if an index is disabled or not.
- Implements GUC hooks for parameter validation and assignment.
- Resets the plan cache when the `disabled_indexes` list is modified through `ResetPlanCache()`

I chose to modify the logic within `get_relation_info` as compared to, say, reducing the cost to make the planner not consider an index during planning, mostly to keep the number of changes being introduced to a minimum and also the logic itself being self-contained and easier to under perhaps (?).

As mentioned before, this does not impact the building of the index. That still happens.

I have added regression tests for:

- Basic single-column and multi-column indexes
- Partial indexes
- Expression indexes
- Join indexes
- GIN and GiST indexes
- Covering indexes
- Range indexes
- Unique indexes and constraints
---
 doc/src/sgml/config.sgml                   |  18 ++
 src/backend/optimizer/util/plancat.c       | 102 +++++++
 src/backend/utils/misc/guc_tables.c        |  12 +
 src/include/nodes/pathnodes.h              |   2 +
 src/include/optimizer/optimizer.h          |   6 +
 src/include/utils/guc_hooks.h              |   5 +
 src/test/regress/expected/create_index.out | 293 +++++++++++++++++++++
 src/test/regress/sql/create_index.sql      | 150 +++++++++++
 8 files changed, 588 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0aec11f443..789f286218 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -6375,6 +6375,24 @@ SELECT * FROM parent WHERE key = 2400;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-disabled-indexes" xreflabel="disabled_indexes">
+      <term><varname>disabled_indexes</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>disabled_indexes</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies a comma-separated list of index names that should be ignored
+        by the query planner. This allows for temporarily disabling specific
+        indexes without needing to drop them or rebuild them when enabling.
+        This can be useful for testing query performance with and without
+        certain indexes. It is a session-level parameter, allowing for easily managing
+        the list of disabled indexes.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
     </sect2>
    </sect1>
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index b913f91ff0..04d9313116 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -47,14 +47,18 @@
 #include "storage/bufmgr.h"
 #include "tcop/tcopprot.h"
 #include "utils/builtins.h"
+#include "utils/guc_hooks.h"
 #include "utils/lsyscache.h"
 #include "utils/partcache.h"
+#include "utils/plancache.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
+#include "utils/varlena.h"
 
 /* GUC parameter */
 int			constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION;
+char			*disabled_indexes = "";
 
 /* Hook for plugins to get control in get_relation_info() */
 get_relation_info_hook_type get_relation_info_hook = NULL;
@@ -295,6 +299,21 @@ 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 all together, 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 free the memory and close the relation before continuing
+			 * to the next index.
+			 */
+			info->isdisabled = is_index_disabled(RelationGetRelationName(indexRelation));
+			if (info->isdisabled)
+			{
+				pfree(info);
+				index_close(indexRelation, NoLock);
+				continue;
+			}
+
 			for (i = 0; i < ncolumns; i++)
 			{
 				info->indexkeys[i] = index->indkey.values[i];
@@ -2596,3 +2615,86 @@ set_baserel_partition_constraint(Relation relation, RelOptInfo *rel)
 		rel->partition_qual = partconstr;
 	}
 }
+
+/*
+ * is_index_disabled
+ * Checks if the given index is in the list of disabled indexes.
+ */
+bool
+is_index_disabled(const char *indexName)
+{
+	List	   *namelist;
+	ListCell   *l;
+	char	   *rawstring;
+	bool		result = false;
+
+	if (disabled_indexes == NULL || disabled_indexes[0] == '\0' || indexName == NULL)
+		return false;
+
+	rawstring = pstrdup(disabled_indexes);
+
+	if (!SplitIdentifierString(rawstring, ',', &namelist))
+	{
+		pfree(rawstring);
+		list_free(namelist);
+		return false;
+	}
+
+	foreach(l, namelist)
+	{
+		if (strcmp(indexName, (char *) lfirst(l)) == 0)
+		{
+			result = true;
+			break;
+		}
+	}
+
+	list_free(namelist);
+	pfree(rawstring);
+
+	return result;
+}
+
+/*
+ * assign_disabled_indexes
+ * GUC assign_hook for "disabled_indexes" GUC variable.
+ * Updates the disabled_indexes value and resets the plan cache if the value has changed.
+ */
+void
+assign_disabled_indexes(const char *newval, void *extra)
+{
+	if (disabled_indexes == NULL || strcmp(disabled_indexes, newval) != 0)
+	{
+		disabled_indexes = guc_strdup(ERROR, newval);
+		ResetPlanCache();
+	}
+}
+
+/*
+ * check_disabled_indexes
+ * GUC check_hook for "disabled_indexes" GUC variable.
+ * Validates the new value for disabled_indexes.
+ */
+bool
+check_disabled_indexes(char **newval, void **extra, GucSource source)
+{
+	List	   *namelist = NIL;
+	char	   *rawstring;
+
+	if (*newval == NULL || strcmp(*newval, "") == 0)
+		return true;
+
+	rawstring = pstrdup(*newval);
+
+	if (!SplitIdentifierString(rawstring, ',', &namelist))
+	{
+		pfree(rawstring);
+		list_free(namelist);
+		return false;
+	}
+
+	pfree(rawstring);
+	list_free(namelist);
+
+	return true;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 686309db58..3f19af566c 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -4783,6 +4783,18 @@ struct config_string ConfigureNamesString[] =
 		check_restrict_nonsystem_relation_kind, assign_restrict_nonsystem_relation_kind, NULL
 	},
 
+	{
+		{"disabled_indexes", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Sets the list of indexes to be disabled for query planning."),
+			NULL,
+			GUC_LIST_INPUT | GUC_NOT_IN_SAMPLE | GUC_EXPLAIN
+		},
+		&disabled_indexes,
+		"",
+		check_disabled_indexes, assign_disabled_indexes, NULL
+	},
+
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 07e2415398..d65fad121c 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1207,6 +1207,8 @@ struct IndexOptInfo
 	/* AM's cost estimator */
 	/* Rather than include amapi.h here, we declare amcostestimate like this */
 	void		(*amcostestimate) () pg_node_attr(read_write_ignore);
+	/* true if this index is asked to be disabled */
+	bool		isdisabled;
 };
 
 /*
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 93e3dc719d..f008ff98af 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -203,4 +203,10 @@ extern List *pull_var_clause(Node *node, int flags);
 extern Node *flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node);
 extern Node *flatten_group_exprs(PlannerInfo *root, Query *query, Node *node);
 
+/*
+ * GUC variable for specifying indexes to be ignored by the query planner.
+ * Contains a comma-separated list of index names.
+ */
+extern PGDLLIMPORT char *disabled_indexes;
+
 #endif							/* OPTIMIZER_H */
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 5813dba0a2..4b8172fa00 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -175,4 +175,9 @@ extern bool check_synchronized_standby_slots(char **newval, void **extra,
 											 GucSource source);
 extern void assign_synchronized_standby_slots(const char *newval, void *extra);
 
+
+extern void assign_disabled_indexes(const char *newval, void *extra);
+extern bool is_index_disabled(const char *indexName);
+extern bool check_disabled_indexes(char **newval, void **extra, GucSource source);
+
 #endif							/* GUC_HOOKS_H */
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d3358dfc39..4f0b0eb59d 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2965,6 +2965,299 @@ ERROR:  REINDEX SCHEMA cannot run inside a transaction block
 END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test enabling/disabling of indexes
+-- Create tables
+CREATE TABLE basic_table (id serial PRIMARY KEY, value integer, text_col text);
+CREATE TABLE io_table (id serial PRIMARY KEY, value integer, category char(1));
+CREATE TABLE join_table (id serial PRIMARY KEY, basic_id integer, io_id integer);
+-- Create various types of indexes
+CREATE INDEX basic_value_idx ON basic_table (value);
+CREATE INDEX io_value_idx ON io_table (value);
+CREATE INDEX basic_multi_col_idx ON basic_table (value, text_col);
+CREATE INDEX io_partial_idx ON io_table (value) WHERE category = 'A';
+CREATE INDEX basic_expr_idx ON basic_table ((lower(text_col)));
+CREATE INDEX join_idx ON join_table (basic_id, io_id);
+-- Insert sample data
+INSERT INTO basic_table (value, text_col)
+SELECT i, 'Text ' || i FROM generate_series(1, 10000) i;
+INSERT INTO io_table (value, category)
+SELECT i, CASE WHEN i % 2 = 0 THEN 'A' ELSE 'B' END
+FROM generate_series(1, 10000) i;
+INSERT INTO join_table (basic_id, io_id)
+SELECT i % 10000 + 1, i % 10000 + 1 FROM generate_series(1, 20000) i;
+ANALYZE basic_table, io_table, join_table;
+-- Test queries with all indexes enabled
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Index Scan using basic_multi_col_idx on basic_table
+   Index Cond: (value = 50)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM io_table WHERE value BETWEEN 40 AND 60 AND category = 'A';
+                   QUERY PLAN                    
+-------------------------------------------------
+ Index Scan using io_partial_idx on io_table
+   Index Cond: ((value >= 40) AND (value <= 60))
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE lower(text_col) = 'text 100';
+                     QUERY PLAN                     
+----------------------------------------------------
+ Index Scan using basic_expr_idx on basic_table
+   Index Cond: (lower(text_col) = 'text 100'::text)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table b JOIN join_table j ON b.id = j.basic_id WHERE b.value = 500;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Nested Loop
+   ->  Index Scan using basic_multi_col_idx on basic_table b
+         Index Cond: (value = 500)
+   ->  Bitmap Heap Scan on join_table j
+         Recheck Cond: (b.id = basic_id)
+         ->  Bitmap Index Scan on join_idx
+               Index Cond: (basic_id = b.id)
+(7 rows)
+
+-- Disable single-column indexes
+SET disabled_indexes = 'basic_value_idx,io_value_idx';
+-- Test queries with single-column indexes disabled
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Index Scan using basic_multi_col_idx on basic_table
+   Index Cond: (value = 50)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM io_table WHERE value BETWEEN 40 AND 60 AND category = 'A';
+                   QUERY PLAN                    
+-------------------------------------------------
+ Index Scan using io_partial_idx on io_table
+   Index Cond: ((value >= 40) AND (value <= 60))
+(2 rows)
+
+-- Disable all custom indexes
+SET disabled_indexes = 'basic_value_idx,io_value_idx,basic_multi_col_idx,io_partial_idx,basic_expr_idx,join_idx';
+-- Test queries with all custom indexes disabled
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+       QUERY PLAN        
+-------------------------
+ Seq Scan on basic_table
+   Filter: (value = 50)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM io_table WHERE value BETWEEN 40 AND 60 AND category = 'A';
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Seq Scan on io_table
+   Filter: ((value >= 40) AND (value <= 60) AND (category = 'A'::bpchar))
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE lower(text_col) = 'text 100';
+                   QUERY PLAN                   
+------------------------------------------------
+ Seq Scan on basic_table
+   Filter: (lower(text_col) = 'text 100'::text)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table b JOIN join_table j ON b.id = j.basic_id WHERE b.value = 500;
+              QUERY PLAN               
+---------------------------------------
+ Hash Join
+   Hash Cond: (j.basic_id = b.id)
+   ->  Seq Scan on join_table j
+   ->  Hash
+         ->  Seq Scan on basic_table b
+               Filter: (value = 500)
+(6 rows)
+
+-- Enable all indexes again
+SET disabled_indexes = '';
+-- Test with a non-existent index name
+SET disabled_indexes = 'non_existent_idx';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Index Scan using basic_multi_col_idx on basic_table
+   Index Cond: (value = 50)
+(2 rows)
+
+-- Test disabled indexes with mixed case index names
+CREATE INDEX Mixed_Case_Idx ON basic_table (value);
+SET disabled_indexes = 'Mixed_Case_Idx';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Index Scan using basic_multi_col_idx on basic_table
+   Index Cond: (value = 50)
+(2 rows)
+
+-- Clean up
+DROP TABLE basic_table, io_table, join_table;
+-- Test more complex index types
+CREATE TABLE multi_purpose (
+    id serial PRIMARY KEY,
+    value integer,
+    text_col text,
+    ts_col tsvector,
+    point_col point
+);
+CREATE TABLE range_table (
+    id serial PRIMARY KEY,
+    range_col int4range
+);
+CREATE INDEX multi_expr_idx ON multi_purpose ((value % 10));
+CREATE INDEX multi_covering_idx ON multi_purpose (value) INCLUDE (text_col);
+CREATE INDEX multi_ts_idx ON multi_purpose USING GIN (ts_col);
+CREATE INDEX multi_point_idx ON multi_purpose USING GIST (point_col);
+CREATE INDEX range_idx ON range_table USING GIST (range_col);
+INSERT INTO multi_purpose (value, text_col, ts_col, point_col)
+SELECT
+    i,
+    'Text ' || i,
+    to_tsvector('english', 'Text ' || i || ' is a sample'),
+    point(i % 100, i % 100)
+FROM generate_series(1, 10000) i;
+INSERT INTO range_table (range_col)
+SELECT int4range(i, i+10) FROM generate_series(1, 1000) i;
+ANALYZE multi_purpose, range_table;
+-- Test queries with all indexes enabled
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE value % 10 = 5;
+                QUERY PLAN                 
+-------------------------------------------
+ Bitmap Heap Scan on multi_purpose
+   Recheck Cond: ((value % 10) = 5)
+   ->  Bitmap Index Scan on multi_expr_idx
+         Index Cond: ((value % 10) = 5)
+(4 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE to_tsquery('english', 'text & sample') @@ ts_col;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Seq Scan on multi_purpose
+   Filter: ('''text'' & ''sampl'''::tsquery @@ ts_col)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE point_col <@ box '((0,0),(50,50))';
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Bitmap Heap Scan on multi_purpose
+   Recheck Cond: (point_col <@ '(50,50),(0,0)'::box)
+   ->  Bitmap Index Scan on multi_point_idx
+         Index Cond: (point_col <@ '(50,50),(0,0)'::box)
+(4 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM range_table WHERE range_col && int4range(5, 15);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Bitmap Heap Scan on range_table
+   Recheck Cond: (range_col && '[5,15)'::int4range)
+   ->  Bitmap Index Scan on range_idx
+         Index Cond: (range_col && '[5,15)'::int4range)
+(4 rows)
+
+-- Disable indexes
+SET disabled_indexes = 'multi_expr_idx,multi_covering_idx,multi_ts_idx,multi_point_idx,range_idx';
+-- Test queries with indexes disabled
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE value % 10 = 5;
+          QUERY PLAN          
+------------------------------
+ Seq Scan on multi_purpose
+   Filter: ((value % 10) = 5)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE to_tsquery('english', 'text & sample') @@ ts_col;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Seq Scan on multi_purpose
+   Filter: ('''text'' & ''sampl'''::tsquery @@ ts_col)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE point_col <@ box '((0,0),(50,50))';
+                  QUERY PLAN                   
+-----------------------------------------------
+ Seq Scan on multi_purpose
+   Filter: (point_col <@ '(50,50),(0,0)'::box)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM range_table WHERE range_col && int4range(5, 15);
+                  QUERY PLAN                  
+----------------------------------------------
+ Seq Scan on range_table
+   Filter: (range_col && '[5,15)'::int4range)
+(2 rows)
+
+-- Enable all indexes again
+SET disabled_indexes = '';
+-- Clean up
+DROP TABLE multi_purpose, range_table;
+-- Test disabled indexes with unique constraints
+CREATE TABLE dual_index_test (id int, value text);
+CREATE UNIQUE INDEX uniq_dual_index_test_id_idx ON dual_index_test (id);
+CREATE INDEX dual_index_test_value_idx ON dual_index_test (value);
+INSERT INTO dual_index_test VALUES (1, 'one'), (2, 'two'), (3, 'three');
+-- Test with both indexes enabled
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE id = 1;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Index Scan using uniq_dual_index_test_id_idx on dual_index_test
+   Index Cond: (id = 1)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE value = 'two';
+                      QUERY PLAN                      
+------------------------------------------------------
+ Bitmap Heap Scan on dual_index_test
+   Recheck Cond: (value = 'two'::text)
+   ->  Bitmap Index Scan on dual_index_test_value_idx
+         Index Cond: (value = 'two'::text)
+(4 rows)
+
+-- Disable the unique index
+SET disabled_indexes TO 'uniq_dual_index_test_id_idx';
+-- Try to insert a duplicate value
+INSERT INTO dual_index_test VALUES (1, 'duplicate');
+ERROR:  duplicate key value violates unique constraint "uniq_dual_index_test_id_idx"
+DETAIL:  Key (id)=(1) already exists.
+-- Check query plans
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE id = 1;
+         QUERY PLAN          
+-----------------------------
+ Seq Scan on dual_index_test
+   Filter: (id = 1)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE value = 'two';
+                      QUERY PLAN                      
+------------------------------------------------------
+ Bitmap Heap Scan on dual_index_test
+   Recheck Cond: (value = 'two'::text)
+   ->  Bitmap Index Scan on dual_index_test_value_idx
+         Index Cond: (value = 'two'::text)
+(4 rows)
+
+-- Disable both indexes
+SET disabled_indexes TO 'uniq_dual_index_test_id_idx,dual_index_test_value_idx';
+-- Check query plans again
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE id = 1;
+         QUERY PLAN          
+-----------------------------
+ Seq Scan on dual_index_test
+   Filter: (id = 1)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE value = 'two';
+           QUERY PLAN            
+---------------------------------
+ Seq Scan on dual_index_test
+   Filter: (value = 'two'::text)
+(2 rows)
+
+-- Reset disabled_indexes
+SET disabled_indexes TO '';
+-- Clean up
+DROP TABLE dual_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..a21f855828 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1297,6 +1297,156 @@ END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
 
+-- Test enabling/disabling of indexes
+-- Create tables
+CREATE TABLE basic_table (id serial PRIMARY KEY, value integer, text_col text);
+CREATE TABLE io_table (id serial PRIMARY KEY, value integer, category char(1));
+CREATE TABLE join_table (id serial PRIMARY KEY, basic_id integer, io_id integer);
+
+-- Create various types of indexes
+CREATE INDEX basic_value_idx ON basic_table (value);
+CREATE INDEX io_value_idx ON io_table (value);
+CREATE INDEX basic_multi_col_idx ON basic_table (value, text_col);
+CREATE INDEX io_partial_idx ON io_table (value) WHERE category = 'A';
+CREATE INDEX basic_expr_idx ON basic_table ((lower(text_col)));
+CREATE INDEX join_idx ON join_table (basic_id, io_id);
+
+-- Insert sample data
+INSERT INTO basic_table (value, text_col)
+SELECT i, 'Text ' || i FROM generate_series(1, 10000) i;
+INSERT INTO io_table (value, category)
+SELECT i, CASE WHEN i % 2 = 0 THEN 'A' ELSE 'B' END
+FROM generate_series(1, 10000) i;
+INSERT INTO join_table (basic_id, io_id)
+SELECT i % 10000 + 1, i % 10000 + 1 FROM generate_series(1, 20000) i;
+
+ANALYZE basic_table, io_table, join_table;
+
+-- Test queries with all indexes enabled
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+EXPLAIN (COSTS OFF) SELECT * FROM io_table WHERE value BETWEEN 40 AND 60 AND category = 'A';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE lower(text_col) = 'text 100';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table b JOIN join_table j ON b.id = j.basic_id WHERE b.value = 500;
+
+-- Disable single-column indexes
+SET disabled_indexes = 'basic_value_idx,io_value_idx';
+
+-- Test queries with single-column indexes disabled
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+EXPLAIN (COSTS OFF) SELECT * FROM io_table WHERE value BETWEEN 40 AND 60 AND category = 'A';
+
+-- Disable all custom indexes
+SET disabled_indexes = 'basic_value_idx,io_value_idx,basic_multi_col_idx,io_partial_idx,basic_expr_idx,join_idx';
+
+-- Test queries with all custom indexes disabled
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+EXPLAIN (COSTS OFF) SELECT * FROM io_table WHERE value BETWEEN 40 AND 60 AND category = 'A';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE lower(text_col) = 'text 100';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table b JOIN join_table j ON b.id = j.basic_id WHERE b.value = 500;
+
+-- Enable all indexes again
+SET disabled_indexes = '';
+
+-- Test with a non-existent index name
+SET disabled_indexes = 'non_existent_idx';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+
+-- Test disabled indexes with mixed case index names
+CREATE INDEX Mixed_Case_Idx ON basic_table (value);
+SET disabled_indexes = 'Mixed_Case_Idx';
+EXPLAIN (COSTS OFF) SELECT * FROM basic_table WHERE value = 50;
+
+-- Clean up
+DROP TABLE basic_table, io_table, join_table;
+
+-- Test more complex index types
+CREATE TABLE multi_purpose (
+    id serial PRIMARY KEY,
+    value integer,
+    text_col text,
+    ts_col tsvector,
+    point_col point
+);
+
+CREATE TABLE range_table (
+    id serial PRIMARY KEY,
+    range_col int4range
+);
+
+CREATE INDEX multi_expr_idx ON multi_purpose ((value % 10));
+CREATE INDEX multi_covering_idx ON multi_purpose (value) INCLUDE (text_col);
+CREATE INDEX multi_ts_idx ON multi_purpose USING GIN (ts_col);
+CREATE INDEX multi_point_idx ON multi_purpose USING GIST (point_col);
+CREATE INDEX range_idx ON range_table USING GIST (range_col);
+
+INSERT INTO multi_purpose (value, text_col, ts_col, point_col)
+SELECT
+    i,
+    'Text ' || i,
+    to_tsvector('english', 'Text ' || i || ' is a sample'),
+    point(i % 100, i % 100)
+FROM generate_series(1, 10000) i;
+
+INSERT INTO range_table (range_col)
+SELECT int4range(i, i+10) FROM generate_series(1, 1000) i;
+
+ANALYZE multi_purpose, range_table;
+
+-- Test queries with all indexes enabled
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE value % 10 = 5;
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE to_tsquery('english', 'text & sample') @@ ts_col;
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE point_col <@ box '((0,0),(50,50))';
+EXPLAIN (COSTS OFF) SELECT * FROM range_table WHERE range_col && int4range(5, 15);
+
+-- Disable indexes
+SET disabled_indexes = 'multi_expr_idx,multi_covering_idx,multi_ts_idx,multi_point_idx,range_idx';
+
+-- Test queries with indexes disabled
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE value % 10 = 5;
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE to_tsquery('english', 'text & sample') @@ ts_col;
+EXPLAIN (COSTS OFF) SELECT * FROM multi_purpose WHERE point_col <@ box '((0,0),(50,50))';
+EXPLAIN (COSTS OFF) SELECT * FROM range_table WHERE range_col && int4range(5, 15);
+
+-- Enable all indexes again
+SET disabled_indexes = '';
+
+-- Clean up
+DROP TABLE multi_purpose, range_table;
+
+-- Test disabled indexes with unique constraints
+CREATE TABLE dual_index_test (id int, value text);
+CREATE UNIQUE INDEX uniq_dual_index_test_id_idx ON dual_index_test (id);
+CREATE INDEX dual_index_test_value_idx ON dual_index_test (value);
+
+INSERT INTO dual_index_test VALUES (1, 'one'), (2, 'two'), (3, 'three');
+
+-- Test with both indexes enabled
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE id = 1;
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE value = 'two';
+
+-- Disable the unique index
+SET disabled_indexes TO 'uniq_dual_index_test_id_idx';
+
+-- Try to insert a duplicate value
+INSERT INTO dual_index_test VALUES (1, 'duplicate');
+
+-- Check query plans
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE id = 1;
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE value = 'two';
+
+-- Disable both indexes
+SET disabled_indexes TO 'uniq_dual_index_test_id_idx,dual_index_test_value_idx';
+
+-- Check query plans again
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE id = 1;
+EXPLAIN (COSTS OFF) SELECT * FROM dual_index_test WHERE value = 'two';
+
+-- Reset disabled_indexes
+SET disabled_indexes TO '';
+
+-- Clean up
+DROP TABLE dual_index_test;
+
 -- Failure for unauthorized user
 CREATE ROLE regress_reindexuser NOLOGIN;
 SET SESSION ROLE regress_reindexuser;
-- 
2.37.1 (Apple Git-137.1)