v20250709-0006-add-prefetch-info-to-explain.patch

From 34a2e9e21ac6635cf893c9dc69603e50ac5cf284 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas@vondra.me>
Date: Fri, 25 Apr 2025 16:07:14 +0200
Subject: [PATCH v20250709 6/6] add prefetch info to explain

show whether prefetch is enabled and average distance
---
 src/backend/access/gist/gistget.c   |  9 +++++
 src/backend/access/gist/gistscan.c  |  2 ++
 src/backend/access/hash/hash.c      | 12 +++++++
 src/backend/access/nbtree/nbtree.c  | 12 +++++++
 src/backend/access/spgist/spgscan.c | 14 ++++++++
 src/backend/commands/explain.c      | 56 +++++++++++++++++++++++++++++
 src/include/access/relscan.h        |  5 +++
 7 files changed, 110 insertions(+)

diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
index 7e292ebb442..c5b4ec94616 100644
--- a/src/backend/access/gist/gistget.c
+++ b/src/backend/access/gist/gistget.c
@@ -830,6 +830,15 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir)
 
 				so->curPageData++;
 
+				if (scan->xs_rs)
+				{
+					if (so->streamPageData != (OffsetNumber) -1)
+					{
+						scan->xs_rs_count++;
+						scan->xs_rs_distance += abs(so->curPageData - so->streamPageData);
+					}
+				}
+
 				return true;
 			}
 
diff --git a/src/backend/access/gist/gistscan.c b/src/backend/access/gist/gistscan.c
index df05f282aa1..3fe630a87c0 100644
--- a/src/backend/access/gist/gistscan.c
+++ b/src/backend/access/gist/gistscan.c
@@ -323,6 +323,8 @@ gistbeginscan(Relation heap, Relation index, int nkeys, int norderbys)
 													 gist_ordered_stream_read_next,
 													 scan,
 													 0);
+		scan->xs_rs_count = 0;
+		scan->xs_rs_distance = 0;
 	}
 
 	return scan;
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index 0884f0e05d9..65b4bba0682 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -333,6 +333,15 @@ hashgettuple(IndexScanDesc scan, ScanDirection dir)
 		res = _hash_next(scan, dir);
 	}
 
+	if (scan->xs_rs)
+	{
+		if (so->currPos.streamIndex != -1)
+		{
+			scan->xs_rs_count++;
+			scan->xs_rs_distance += (so->currPos.streamIndex - so->currPos.itemIndex);
+		}
+	}
+
 	return res;
 }
 
@@ -485,6 +494,9 @@ hashbeginscan(Relation heap, Relation index, int nkeys, int norderbys)
 												 hash_stream_read_next,
 												 scan,
 												 0);
+
+		scan->xs_rs_count = 0;
+		scan->xs_rs_distance = 0;
 	}
 
 	return scan;
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index 0fa4af79dac..d7242405803 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -280,6 +280,15 @@ btgettuple(IndexScanDesc scan, ScanDirection dir)
 		/* ... otherwise see if we need another primitive index scan */
 	} while (so->numArrayKeys && _bt_start_prim_scan(scan, dir));
 
+	if (scan->xs_rs)
+	{
+		if (so->currPos.streamIndex != -1)
+		{
+			scan->xs_rs_count++;
+			scan->xs_rs_distance += (so->currPos.streamIndex - so->currPos.itemIndex);
+		}
+	}
+
 	return res;
 }
 
@@ -503,6 +512,9 @@ btbeginscan(Relation heap, Relation index, int nkeys, int norderbys)
 												 bt_stream_read_next,
 												 scan,
 												 0);
+
+		scan->xs_rs_count = 0;
+		scan->xs_rs_distance = 0;
 	}
 
 	return scan;
diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c
index c90703a522e..9c87d364391 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -483,6 +483,9 @@ spgbeginscan(Relation heap, Relation index, int keysz, int orderbysz)
 												 spg_stream_read_next,
 												 scan,
 												 0);
+
+		scan->xs_rs_count = 0;
+		scan->xs_rs_distance = 0;
 	}
 
 	return scan;
@@ -1214,6 +1217,17 @@ spggettuple(IndexScanDesc scan, ScanDirection dir)
 													 so->distances[so->iPtr],
 													 so->recheckDistances[so->iPtr]);
 			so->iPtr++;
+
+
+	if (scan->xs_rs)
+	{
+		if (so->sPtr != -1)
+		{
+			scan->xs_rs_count++;
+			scan->xs_rs_distance += abs(so->iPtr - so->sPtr);
+		}
+	}
+
 			return true;
 		}
 
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7e2792ead71..d978d79e0f6 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -14,6 +14,7 @@
 #include "postgres.h"
 
 #include "access/xact.h"
+#include "access/relscan.h"
 #include "catalog/pg_type.h"
 #include "commands/createas.h"
 #include "commands/defrem.h"
@@ -136,6 +137,7 @@ static void show_memoize_info(MemoizeState *mstate, List *ancestors,
 							  ExplainState *es);
 static void show_hashagg_info(AggState *aggstate, ExplainState *es);
 static void show_indexsearches_info(PlanState *planstate, ExplainState *es);
+static void show_index_prefetch_info(PlanState *planstate, ExplainState *es);
 static void show_tidbitmap_info(BitmapHeapScanState *planstate,
 								ExplainState *es);
 static void show_instrumentation_count(const char *qlabel, int which,
@@ -1966,6 +1968,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			show_indexsearches_info(planstate, es);
+			show_index_prefetch_info(planstate, es);
 			break;
 		case T_IndexOnlyScan:
 			show_scan_qual(((IndexOnlyScan *) plan)->indexqual,
@@ -1983,6 +1986,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				ExplainPropertyFloat("Heap Fetches", NULL,
 									 planstate->instrument->ntuples2, 0, es);
 			show_indexsearches_info(planstate, es);
+			show_index_prefetch_info(planstate, es);
 			break;
 		case T_BitmapIndexScan:
 			show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig,
@@ -3889,6 +3893,58 @@ show_indexsearches_info(PlanState *planstate, ExplainState *es)
 	ExplainPropertyUInteger("Index Searches", NULL, nsearches, es);
 }
 
+
+static void
+show_index_prefetch_info(PlanState *planstate, ExplainState *es)
+{
+	Plan	   *plan = planstate->plan;
+	bool		prefetch = false;
+	float		distance = 0;
+
+	if (!es->analyze)
+		return;
+
+	if (!es->verbose)
+		return;
+
+	/* Initialize counters with stats from the local process first */
+	switch (nodeTag(plan))
+	{
+		case T_IndexScan:
+			{
+				IndexScanState *indexstate = ((IndexScanState *) planstate);
+
+				if (indexstate->iss_ScanDesc)
+				{
+					prefetch = (indexstate->iss_ScanDesc->xs_rs != NULL);
+
+					if (indexstate->iss_ScanDesc->xs_rs)
+						distance = (indexstate->iss_ScanDesc->xs_rs_distance / (float) Max(1, indexstate->iss_ScanDesc->xs_rs_count));
+				}
+				break;
+			}
+		case T_IndexOnlyScan:
+			{
+				IndexOnlyScanState *indexstate = ((IndexOnlyScanState *) planstate);
+
+				if (indexstate->ioss_ScanDesc)
+				{
+					prefetch = (indexstate->ioss_ScanDesc->xs_rs != NULL);
+
+					if (indexstate->ioss_ScanDesc->xs_rs)
+						distance = (indexstate->ioss_ScanDesc->xs_rs_distance / (float) Max(1, indexstate->ioss_ScanDesc->xs_rs_count));
+				}
+				break;
+			}
+		default:
+			break;
+	}
+
+	/* Next get the sum of the counters set within each and every process */
+	ExplainPropertyBool("Index Prefetch", prefetch, es);
+	ExplainPropertyFloat("Index Distance", NULL, distance, 1, es);
+}
+
 /*
  * Show exact/lossy pages for a BitmapHeapScan node
  */
diff --git a/src/include/access/relscan.h b/src/include/access/relscan.h
index 56e6c6245e5..ab524bd9b18 100644
--- a/src/include/access/relscan.h
+++ b/src/include/access/relscan.h
@@ -176,8 +176,13 @@ typedef struct IndexScanDescData
 	bool		xs_heap_continue;	/* T if must keep walking, potential
 									 * further results */
 	IndexFetchTableData *xs_heapfetch;
+
 	ReadStream *xs_rs;			/* read_stream (if supported by the AM) */
 
+	/* stats for explain */
+	int			xs_rs_distance;
+	int			xs_rs_count;
+
 	bool		xs_recheck;		/* T means scan keys must be rechecked */
 
 	/*
-- 
2.50.0