0003-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-performance_bug2.patch

From 982d721013abf04b5eba36ce1abab350894883b2 Mon Sep 17 00:00:00 2001
From: spoondla <s_poondla@apple.com>
Date: Mon, 2 Mar 2026 11:23:17 -0800
Subject: [PATCH v3] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY performance
 with nullable indexed columns

Issue:
When a materialized view has a unique index on a nullable column, REFRESH
MATERIALIZED VIEW CONCURRENTLY included that column in the FULL OUTER JOIN
condition used to detect changes.  This caused severe performance degradation
because standard equality (col = col) evaluates to NULL when either side is
NULL, causing unchanged rows to appear as both deleted and re-inserted.

Example:
A materialized view with 1M rows where a nullable indexed column
contains all NULLs. When data is unchanged, the refresh should produce an
empty diff (0 rows). However, because NULL = NULL evaluates to NULL (not TRUE),
the join fails to match any rows. The diff incorrectly shows all 1M rows as
deleted and all 1M rows as newly inserted, so 2M changes in total, causing unnecessary
use of resources which drastically increased the time for "refresh materialized view concurrently" operation.

Fix:
Skip nullable columns when building the FULL OUTER JOIN equality conditions.
Only include columns with NOT NULL constraints from unique indexes in the join
predicate.
This is semantically correct because nullable columns in unique
indexes do not guarantee row uniqueness for NULL values (multiple NULLs are allowed).
The record equality operator (*=), which is always appended to the
join predicate, already handles all columns including nullable ones (i.e treating NULL as equal to NULL.)

The fix adds a new variable 'addedAnyQuals' to track whether any column conditions
were actually added to the query, separate from 'foundUniqueIndex' which tracks
whether a usable unique index exists (for validation).
This ensures correct SQL syntax when all columns in an index are nullable.
---
 src/backend/commands/matview.c | 30 +++++++++++++++++++++++++++---
 1 file changed, 27 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 81a55a33ef2..236c233914c 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -602,6 +602,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 	char	   *nsp;
 	TupleDesc	tupdesc;
 	bool		foundUniqueIndex;
+	bool		addedAnyQuals;
 	List	   *indexoidlist;
 	ListCell   *indexoidscan;
 	int16		relnatts;
@@ -715,6 +716,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 	tupdesc = matviewRel->rd_att;
 	opUsedForQual = palloc0_array(Oid, relnatts);
 	foundUniqueIndex = false;
+	addedAnyQuals = false;
 
 	indexoidlist = RelationGetIndexList(matviewRel);
 
@@ -732,6 +734,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 			Datum		indclassDatum;
 			int			i;
 
+			foundUniqueIndex = true;
+
 			/* Must get indclass the hard way. */
 			indclassDatum = SysCacheGetAttrNotNull(INDEXRELID,
 												   indexRel->rd_indextuple,
@@ -753,6 +757,18 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 				const char *leftop;
 				const char *rightop;
 
+				/*
+				 * Skip nullable columns.  Nullable columns in unique indexes
+				 * don't provide row uniqueness for NULL values (multiple NULLs
+				 * are allowed), so they cannot reliably identify matching rows.
+				 * Including them in the join condition causes NULL = NULL
+				 * comparisons which evaluate to NULL, making unchanged rows
+				 * appear different.  The record equality operator (*=) appended
+				 * below handles all columns including nullable ones.
+				 */
+				if (!attr->attnotnull)
+					continue;
+
 				/*
 				 * Identify the equality operator associated with this index
 				 * column.  First we need to look up the column's opclass.
@@ -788,7 +804,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 				/*
 				 * Actually add the qual, ANDed with any others.
 				 */
-				if (foundUniqueIndex)
+				if (addedAnyQuals)
 					appendStringInfoString(&querybuf, " AND ");
 
 				leftop = quote_qualified_identifier("newdata",
@@ -801,7 +817,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 										 op,
 										 rightop, attrtype);
 
-				foundUniqueIndex = true;
+				addedAnyQuals = true;
 			}
 		}
 
@@ -826,8 +842,16 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 				errmsg("could not find suitable unique index on materialized view \"%s\"",
 					   RelationGetRelationName(matviewRel)));
 
+	/*
+	 * The record equality operator (*=) is always included in the join
+	 * predicate.  It handles all columns correctly including nullable ones
+	 * that were skipped above, since *=  treats NULL as equal to NULL.
+	 */
+	if (addedAnyQuals)
+		appendStringInfoString(&querybuf, " AND ");
+
 	appendStringInfoString(&querybuf,
-						   " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) "
+						   "newdata.* OPERATOR(pg_catalog.*=) mv.*) "
 						   "WHERE newdata.* IS NULL OR mv.* IS NULL "
 						   "ORDER BY tid");
 
-- 
2.39.5 (Apple Git-154)