v1-0001-Improve-index-selection-for-REPLICA-IDENTITY-FULL.patch

From 3bfacc0428307f18f753bab11cf1400781caf09e Mon Sep 17 00:00:00 2001
From: Ethan Mertz <ethmertz@amazon.com>
Date: Thu, 21 May 2026 22:41:13 +0000
Subject: [PATCH v1] Improve index selection for REPLICA IDENTITY FULL

When multiple usable indexes exist for a relation with REPLICA IDENTITY
FULL, the subscriber now prefers unique indexes over non-unique ones
(since a unique index guarantees at most one tuple per index scan), and
among indexes of the same uniqueness, prefers those with fewer key
columns for a narrower, more efficient lookup.

Previously, the first eligible index found was returned without
considering whether a better candidate existed.  This could lead to
suboptimal index usage during logical replication apply, particularly
when both unique and non-unique indexes were available.

Regression tests are included to verify the selection logic.
---
 src/backend/replication/logical/relation.c    | 34 ++++++++--
 .../subscription/t/032_subscribe_use_index.pl | 64 +++++++++++++++++++
 2 files changed, 94 insertions(+), 4 deletions(-)

diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 0b1d80b5b0f..2334e0c049f 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -784,28 +784,54 @@ logicalrep_partition_open(LogicalRepRelMapEntry *root,
  * We expect to call this function when REPLICA IDENTITY FULL is defined for
  * the remote relation.
  *
+ * If multiple usable indexes exist, preference is given to unique indexes
+ * (since they guarantee at most one tuple per index scan), and among indexes
+ * of the same uniqueness, those with fewer key columns are preferred for a
+ * narrower, more efficient lookup.
+ *
  * If no suitable index is found, returns InvalidOid.
  */
 static Oid
 FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
 {
 	List	   *idxlist = RelationGetIndexList(localrel);
+	Oid			bestIdx = InvalidOid;
+	bool		bestIsUnique = false;
+	int			bestNKeyAtts = PG_INT32_MAX;
 
 	foreach_oid(idxoid, idxlist)
 	{
 		bool		isUsableIdx;
+		bool		isUnique;
+		int			nKeyAtts;
 		Relation	idxRel;
 
 		idxRel = index_open(idxoid, AccessShareLock);
 		isUsableIdx = IsIndexUsableForReplicaIdentityFull(idxRel, attrmap);
+		isUnique = idxRel->rd_index->indisunique;
+		nKeyAtts = idxRel->rd_index->indnkeyatts;
 		index_close(idxRel, AccessShareLock);
 
-		/* Return the first eligible index found */
-		if (isUsableIdx)
-			return idxoid;
+		if (!isUsableIdx)
+			continue;
+
+		/*
+		 * Prefer unique indexes over non-unique ones, since a unique index
+		 * guarantees at most one index scan per tuple match.  Among indexes
+		 * of the same uniqueness, prefer fewer key columns for a narrower,
+		 * more efficient lookup.
+		 */
+		if (bestIdx == InvalidOid ||
+			(isUnique && !bestIsUnique) ||
+			(isUnique == bestIsUnique && nKeyAtts < bestNKeyAtts))
+		{
+			bestIdx = idxoid;
+			bestIsUnique = isUnique;
+			bestNKeyAtts = nKeyAtts;
+		}
 	}
 
-	return InvalidOid;
+	return bestIdx;
 }
 
 /*
diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl
index c755c1a7518..3637b0e9aca 100644
--- a/src/test/subscription/t/032_subscribe_use_index.pl
+++ b/src/test/subscription/t/032_subscribe_use_index.pl
@@ -547,6 +547,70 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full");
 # Testcase end: Subscription can use hash index
 # =============================================================================
 
+# =============================================================================
+# Testcase start: Index selection prefers unique indexes and fewer key columns
+#
+
+# create tables pub and sub
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE test_idx_select (a int, b int, c int)");
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE test_idx_select REPLICA IDENTITY FULL");
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE test_idx_select (a int, b int, c int)");
+
+# create a non-unique index on (a, b, c) and a unique index on (a, b)
+# the unique index with fewer columns should be preferred
+$node_subscriber->safe_psql('postgres',
+	"CREATE INDEX test_idx_select_nonuniq ON test_idx_select(a, b, c)");
+$node_subscriber->safe_psql('postgres',
+	"CREATE UNIQUE INDEX test_idx_select_uniq ON test_idx_select(a, b)");
+
+# create pub/sub
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_idx_select FOR TABLE test_idx_select");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_idx_select CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_idx_select"
+);
+
+# wait for initial table synchronization to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, $appname);
+
+# insert and update a row
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO test_idx_select VALUES (1, 2, 3)");
+$node_publisher->safe_psql('postgres',
+	"UPDATE test_idx_select SET c = 4 WHERE a = 1");
+
+# wait for catchup and verify the unique index was used
+$node_publisher->wait_for_catchup($appname);
+$node_subscriber->poll_query_until('postgres',
+	q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname = 'test_idx_select_uniq';}
+  )
+  or die
+  "Timed out while waiting for unique index to be used";
+
+# verify the non-unique index was NOT used
+$result = $node_subscriber->safe_psql('postgres',
+	"select idx_scan from pg_stat_all_indexes where indexrelname = 'test_idx_select_nonuniq'");
+is($result, qq(0),
+	'non-unique index should not be used when unique index is available');
+
+# verify correct data
+$result = $node_subscriber->safe_psql('postgres',
+	"select c from test_idx_select where a = 1");
+is($result, qq(4),
+	'subscriber has correct data after update via preferred unique index');
+
+# cleanup
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_idx_select");
+$node_publisher->safe_psql('postgres', "DROP TABLE test_idx_select");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_idx_select");
+$node_subscriber->safe_psql('postgres', "DROP TABLE test_idx_select");
+
+# Testcase end: Index selection prefers unique indexes and fewer key columns
+# =============================================================================
+
 $node_subscriber->stop('fast');
 $node_publisher->stop('fast');
 
-- 
2.47.3