v1-0001-Proof-of-Concept-Ability-to-enable-disable-indexe.patch

From e796e322ffbba563b1d31f9c0024a96bcc81a430 Mon Sep 17 00:00:00 2001
From: Shayon Mukherjee <shayonj@gmail.com>
Date: Tue, 24 Sep 2024 09:16:14 -0400
Subject: [PATCH v1] Proof of Concept: Ability to enable/disable indexes
 through GUC

---
 src/backend/optimizer/path/indxpath.c | 52 +++++++++++++++++++++++++++
 src/backend/utils/adt/selfuncs.c      |  3 ++
 src/backend/utils/misc/guc_tables.c   | 14 ++++++++
 src/include/optimizer/optimizer.h     |  8 +++++
 4 files changed, 77 insertions(+)

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c0fcc7d78d..5b88aeecd4 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -35,6 +35,8 @@
 #include "utils/lsyscache.h"
 #include "utils/selfuncs.h"
 
+#include "utils/plancache.h"
+#include "utils/varlena.h"
 
 /* XXX see PartCollMatchesExprColl */
 #define IndexCollMatchesExprColl(idxcollation, exprcollation) \
@@ -265,6 +267,10 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
 		if (index->indpred != NIL && !index->predOK)
 			continue;
 
+		// Ignore if index is asked to be disabled
+		if (is_index_disabled(index->indexoid))
+			continue;
+
 		/*
 		 * Identify the restriction clauses that can match the index.
 		 */
@@ -3757,3 +3763,49 @@ is_pseudo_constant_for_index(PlannerInfo *root, Node *expr, IndexOptInfo *index)
 		return false;			/* no good, volatile comparison value */
 	return true;
 }
+
+
+bool
+is_index_disabled(Oid indexId)
+{
+		char       *rawnames;
+		List       *namelist;
+		ListCell   *l;
+
+		if (!disabled_indexes || disabled_indexes[0] == '\0')
+			return false;
+
+		rawnames = pstrdup(disabled_indexes);
+		if (!SplitIdentifierString(rawnames, ',', &namelist))
+		{
+			/* syntax error in name list */
+			pfree(rawnames);
+			list_free(namelist);
+			return false;
+		}
+
+		foreach(l, namelist)
+		{
+			char	*curname = (char *) lfirst(l);
+			Oid		indexOid;
+
+			indexOid = get_relname_relid(curname, get_rel_namespace(indexId));
+			if (indexOid == indexId)
+				{
+					pfree(rawnames);
+					list_free(namelist);
+					return true;
+				}
+		}
+
+		pfree(rawnames);
+		list_free(namelist);
+		return false;
+}
+
+void
+assign_disabled_indexes(const char *newval, void *extra)
+{
+	if (disabled_indexes != newval)
+		ResetPlanCache();
+}
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 03d7fb5f48..7491d4a768 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5152,6 +5152,9 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
 
 			for (pos = 0; pos < index->ncolumns; pos++)
 			{
+				if (is_index_disabled(index->indexoid))
+					continue;
+
 				if (index->indexkeys[pos] == 0)
 				{
 					Node	   *indexkey;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 686309db58..dd588b7f8c 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -547,6 +547,7 @@ int			tcp_keepalives_interval;
 int			tcp_keepalives_count;
 int			tcp_user_timeout;
 
+
 /*
  * SSL renegotiation was been removed in PostgreSQL 9.5, but we tolerate it
  * being set to zero (meaning never renegotiate) for backward compatibility.
@@ -3500,6 +3501,7 @@ struct config_int ConfigureNamesInt[] =
 		check_autovacuum_work_mem, NULL, NULL
 	},
 
+
 	{
 		{"tcp_keepalives_idle", PGC_USERSET, CONN_AUTH_TCP,
 			gettext_noop("Time between issuing TCP keepalives."),
@@ -4783,6 +4785,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
+		},
+		&disabled_indexes,
+		"",
+		NULL, assign_disabled_indexes, NULL
+	},
+
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 93e3dc719d..d96ee3e210 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -203,4 +203,12 @@ 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 support for disabled indexes
+/* Comma-separated list of indexes to be disabled for the planner */
+extern PGDLLIMPORT char *disabled_indexes;
+char 		*disabled_indexes;
+
+extern void assign_disabled_indexes(const char *newval, void *extra);
+extern bool is_index_disabled(Oid indexoid);
+
 #endif							/* OPTIMIZER_H */
-- 
2.37.1 (Apple Git-137.1)