v3-0001-amcheck-Optimize-speed-of-checking-for-unique-con.patch

From f801783ade88e524b73acfa19f1b06e5b54608df Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Fri, 10 May 2024 03:07:53 +0300
Subject: [PATCH v3 1/4] amcheck: Optimize speed of checking for unique
 constraint violation

Currently, when amcheck validates a unique constraint, it visits the heap for
each index tuple.  This commit implements skipping keys, which have only one
non-dedeuplicated index tuple (quite common case for unique indexes). That
gives substantial economy on index checking time.

Reported-by: Noah Misch
Discussion: https://postgr.es/m/20240325020323.fd.nmisch%40google.com
Author: Alexander Korotkov, Pavel Borisov
---
 contrib/amcheck/verify_nbtree.c | 35 +++++++++++++++++++++++++++++++--
 1 file changed, 33 insertions(+), 2 deletions(-)

diff --git a/contrib/amcheck/verify_nbtree.c b/contrib/amcheck/verify_nbtree.c
index 70f65b645a6..c7be785f88b 100644
--- a/contrib/amcheck/verify_nbtree.c
+++ b/contrib/amcheck/verify_nbtree.c
@@ -1429,6 +1429,13 @@ bt_target_page_check(BtreeCheckState *state)
 		bool		lowersizelimit;
 		ItemPointer scantid;
 
+		/*
+		 * True if we already called bt_entry_unique_check() for the current
+		 * item.  This helps to avoid visiting the heap for keys, which are
+		 * anyway presented only once and can't comprise a unique violation.
+		 */
+		bool		unique_checked = false;
+
 		CHECK_FOR_INTERRUPTS();
 
 		itemid = PageGetItemIdCareful(state, state->targetblock,
@@ -1771,13 +1778,19 @@ bt_target_page_check(BtreeCheckState *state)
 
 		/*
 		 * If the index is unique verify entries uniqueness by checking the
-		 * heap tuples visibility.
+		 * heap tuples visibility.  Immediately check posting tuples and
+		 * tuples with repeated keys.  Postpone check for keys, which have the
+		 * first appearance.
 		 */
 		if (state->checkunique && state->indexinfo->ii_Unique &&
-			P_ISLEAF(topaque) && !skey->anynullkeys)
+			P_ISLEAF(topaque) && !skey->anynullkeys &&
+			(BTreeTupleIsPosting(itup) || ItemPointerIsValid(lVis_tid)))
+		{
 			bt_entry_unique_check(state, itup, state->targetblock, offset,
 								  &lVis_i, &lVis_tid, &lVis_offset,
 								  &lVis_block);
+			unique_checked = true;
+		}
 
 		if (state->checkunique && state->indexinfo->ii_Unique &&
 			P_ISLEAF(topaque) && OffsetNumberNext(offset) <= max)
@@ -1796,6 +1809,9 @@ bt_target_page_check(BtreeCheckState *state)
 			 * data (whole index tuple or last posting in index tuple). Key
 			 * containing null value does not violate unique constraint and
 			 * treated as different to any other key.
+			 *
+			 * If the next key is the same as the previous one, do the
+			 * bt_entry_unique_check() call if it was postponed.
 			 */
 			if (_bt_compare(state->rel, skey, state->target,
 							OffsetNumberNext(offset)) != 0 || skey->anynullkeys)
@@ -1805,6 +1821,12 @@ bt_target_page_check(BtreeCheckState *state)
 				lVis_block = InvalidBlockNumber;
 				lVis_offset = InvalidOffsetNumber;
 			}
+			else if (!unique_checked)
+			{
+				bt_entry_unique_check(state, itup, state->targetblock, offset,
+									  &lVis_i, &lVis_tid, &lVis_offset,
+									  &lVis_block);
+			}
 			skey->scantid = scantid;	/* Restore saved scan key state */
 		}
 
@@ -1889,6 +1911,15 @@ bt_target_page_check(BtreeCheckState *state)
 				/* The first key on the next page is the same */
 				if (_bt_compare(state->rel, rightkey, state->target, max) == 0 && !rightkey->anynullkeys)
 				{
+					/*
+					 * Do the bt_entry_unique_check() call if it was
+					 * postponed.
+					 */
+					if (!unique_checked)
+						bt_entry_unique_check(state, itup, state->targetblock, offset,
+											  &lVis_i, &lVis_tid, &lVis_offset,
+											  &lVis_block);
+
 					elog(DEBUG2, "cross page equal keys");
 					state->target = palloc_btree_page(state,
 													  rightblock_number);
-- 
2.39.3 (Apple Git-145)