wip_for_optimize_index_column_match.diff

From 56bab0ff114ec1bff28434cfac17c8db02d8fe7a Mon Sep 17 00:00:00 2001
From: Onder Kalaci <onderkalaci@gmail.com>
Date: Thu, 9 Mar 2023 13:45:22 +0300
Subject: [PATCH 1/2] WIP: Skip index usage when the index do not contain any
 of the relevant columns

---
 src/backend/replication/logical/relation.c    | 29 +++++++++++++++++--
 .../subscription/t/032_subscribe_use_index.pl |  6 ++--
 2 files changed, 29 insertions(+), 6 deletions(-)

diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 5943617d87..1616bd5712 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -750,6 +750,27 @@ IsIndexOnlyOnExpression(IndexInfo *indexInfo)
 	return true;
 }
 
+
+/*
+ * Returns true if the index contains any of the columns remoterel
+ * has.
+ */
+static bool
+IndexContainsAnyRemoteColumn(IndexInfo  *indexInfo,
+							 LogicalRepRelation  *remoterel)
+{
+	for (int i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
+	{
+		int			keycol = indexInfo->ii_IndexAttrNumbers[i];
+
+		if (AttributeNumberIsValid(keycol) &&
+			bms_is_member( keycol- 1, remoterel->attkeys))
+			return true;
+	}
+
+	return false;
+}
+
 /*
  * Returns the oid of an index that can be used by the apply worker to scan
  * the relation. The index must be btree, non-partial, and have at least
@@ -775,7 +796,7 @@ IsIndexOnlyOnExpression(IndexInfo *indexInfo)
  * If no suitable index is found, returns InvalidOid.
  */
 static Oid
-FindUsableIndexForReplicaIdentityFull(Relation localrel)
+FindUsableIndexForReplicaIdentityFull(Relation localrel, LogicalRepRelation *remoterel)
 {
 	List	   *indexlist = RelationGetIndexList(localrel);
 	Oid			usableIndex = InvalidOid;
@@ -785,14 +806,16 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel)
 	{
 		Oid			idxoid = lfirst_oid(lc);
 		bool		isUsableIndex;
+		bool		indexContainsAnyRemoteColumn;
 		Relation	indexRelation = index_open(idxoid, AccessShareLock);
 		IndexInfo  *indexInfo = BuildIndexInfo(indexRelation);
 
 		isUsableIndex = IsIndexUsableForReplicaIdentityFull(indexInfo);
+		indexContainsAnyRemoteColumn = IndexContainsAnyRemoteColumn(indexInfo, remoterel);
 
 		index_close(indexRelation, AccessShareLock);
 
-		if (isUsableIndex)
+		if (isUsableIndex && indexContainsAnyRemoteColumn)
 		{
 			/* we found one eligible index, don't need to continue */
 			usableIndex = idxoid;
@@ -891,7 +914,7 @@ FindLogicalRepLocalIndex(Relation localrel, LogicalRepRelation *remoterel)
 		 * long run or use the full-fledged planner which could cause
 		 * overhead.
 		 */
-		return FindUsableIndexForReplicaIdentityFull(localrel);
+		return FindUsableIndexForReplicaIdentityFull(localrel, remoterel);
 	}
 
 	return InvalidOid;
diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl
index 1d6c5e6415..2863fc547a 100644
--- a/src/test/subscription/t/032_subscribe_use_index.pl
+++ b/src/test/subscription/t/032_subscribe_use_index.pl
@@ -771,7 +771,7 @@ $node_publisher->safe_psql('postgres',
 $node_subscriber->safe_psql('postgres',
 	"CREATE TABLE test_replica_id_full (x int, y int)");
 $node_subscriber->safe_psql('postgres',
-	"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full(y)");
+	"CREATE INDEX test_replica_id_full_idy1 ON test_replica_id_full(y)");
 
 # insert some initial data
 $node_publisher->safe_psql('postgres',
@@ -793,9 +793,9 @@ $node_publisher->safe_psql('postgres',
 	"UPDATE test_replica_id_full SET x = x + 1 WHERE x = 15;");
 $node_publisher->wait_for_catchup($appname);
 
-# wait until the index is used on the subscriber
+# show that the index is not used on the subscriber
 $node_subscriber->poll_query_until(
-	'postgres', q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+	'postgres', q{select (idx_scan = 0) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idy1';}
 ) or die "Timed out while waiting for check subscriber tap_sub_rep_full updates one row via index";
 
 # make sure that the subscriber has the correct data
-- 
2.34.1