Subject: Re: Add parallel columns for seq scan and index scan on pg_stat_all_tables and _indexes

From: Guillaume Lelarge <[email-masked]>
Date: Thu, 5 Sep 2024 08:19:13 +0200

Le jeu. 5 sept. 2024 à 07:36, Bertrand Drouvot <[email-masked]>
a écrit :

> Hi,
>
> On Wed, Sep 04, 2024 at 04:37:19PM +0200, Guillaume Lelarge wrote:
> > Hi,
> >
> > Le mer. 4 sept. 2024 à 16:18, Bertrand Drouvot <
> [email-masked]>
> > a écrit :
> > > What about adding a comment instead of this extra check?
> > >
> > >
> > Done too in v3.
>
> Thanks!
>
> 1 ===
>
> +       /*
> +        * Don't check counts.parallelnumscans because counts.numscans
> includes
> +        * counts.parallelnumscans
> +        */
>
> "." is missing at the end of the comment.
>
>
Fixed in v4.

> 2 ===
>
> -               if (t > tabentry->lastscan)
> +               if (t > tabentry->lastscan && lstats->counts.numscans)
>
> The extra check on lstats->counts.numscans is not needed as it's already
> done
> a few lines before.
>
>
Fixed in v4.

> 3 ===
>
> +               if (t > tabentry->parallellastscan &&
> lstats->counts.parallelnumscans)
>
> This one makes sense.
>
> And now I'm wondering if the extra comment added in v3 is really worth it
> (and
> does not sound confusing)? I mean, the parallel check is done once we passe
> the initial test on counts.numscans. I think the code is clear enough
> without
> this extra comment, thoughts?
>
>
I'm not sure I understand you here. I kinda like the extra comment though.

> 4 ===
>
> What about adding a few tests? or do you want to wait a bit more to see if
> "
> there's an agreement on this patch" (as you stated at the start of this
> thread).
>
>
Guess I can start working on that now. It will take some time as I've never
done it before. Good thing I added the patch on the November commit fest :)

Thanks again.

Regards.

-- 
Guillaume.
From 6c92e70cd2698f24fe14069f675b7934e2f95bfe Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <[email-masked]>
Date: Wed, 28 Aug 2024 21:35:30 +0200
Subject: [PATCH v4] Add parallel columns for pg_stat_all_tables,indexes

pg_stat_all_tables gets 4 new columns: parallel_seq_scan,
last_parallel_seq_scan, parallel_idx_scan, last_parallel_idx_scan.

pg_stat_all_indexes gets 2 new columns: parallel_idx_scan,
last_parallel_idx_scan.
---
 doc/src/sgml/monitoring.sgml                 | 69 ++++++++++++++++++--
 src/backend/access/brin/brin.c               |  2 +-
 src/backend/access/gin/ginscan.c             |  2 +-
 src/backend/access/gist/gistget.c            |  4 +-
 src/backend/access/hash/hashsearch.c         |  2 +-
 src/backend/access/heap/heapam.c             |  2 +-
 src/backend/access/nbtree/nbtsearch.c        |  2 +-
 src/backend/access/spgist/spgscan.c          |  2 +-
 src/backend/catalog/system_views.sql         |  6 ++
 src/backend/utils/activity/pgstat_relation.c |  8 +++
 src/backend/utils/adt/pgstatfuncs.c          |  6 ++
 src/include/catalog/pg_proc.dat              |  8 +++
 src/include/pgstat.h                         | 17 +++--
 13 files changed, 112 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 933de6fe07..6886094095 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3773,7 +3773,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>seq_scan</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of sequential scans initiated on this table
+       Number of sequential scans (including parallel ones) initiated on this table
       </para></entry>
      </row>
 
@@ -3782,7 +3782,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type>
       </para>
       <para>
-       The time of the last sequential scan on this table, based on the
+       The time of the last sequential scan (including parallel ones) on this table, based on the
+       most recent transaction stop time
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>parallel_seq_scan</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of parallel sequential scans initiated on this table
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_parallel_seq_scan</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       The time of the last parallel sequential scan on this table, based on the
        most recent transaction stop time
       </para></entry>
      </row>
@@ -3801,7 +3820,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>idx_scan</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of index scans initiated on this table
+       Number of index scans (including parallel ones) initiated on this table
       </para></entry>
      </row>
 
@@ -3810,7 +3829,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type>
       </para>
       <para>
-       The time of the last index scan on this table, based on the
+       The time of the last index scan (including parallel ones) on this table, based on the
+       most recent transaction stop time
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>parallel_idx_scan</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of parallel index scans initiated on this table
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_parallel_idx_scan</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       The time of the last parallel index scan on this table, based on the
        most recent transaction stop time
       </para></entry>
      </row>
@@ -4080,7 +4118,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>idx_scan</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of index scans initiated on this index
+       Number of index scans (including parallel ones) initiated on this index
       </para></entry>
      </row>
 
@@ -4089,7 +4127,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type>
       </para>
       <para>
-       The time of the last scan on this index, based on the
+       The time of the last scan on this index(including parallel ones), based
+       on the most recent transaction stop time
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>parallel_idx_scan</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of parallel index scans initiated on this index
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_parallel_idx_scan</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       The time of the last parallel scan on this index, based on the
        most recent transaction stop time
       </para></entry>
      </row>
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 6467bed604..4b5557fcf7 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -580,7 +580,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
 
 	opaque = (BrinOpaque *) scan->opaque;
 	bdesc = opaque->bo_bdesc;
-	pgstat_count_index_scan(idxRel);
+	pgstat_count_index_scan(idxRel, false);
 
 	/*
 	 * We need to know the size of the table so that we know how long to
diff --git a/src/backend/access/gin/ginscan.c b/src/backend/access/gin/ginscan.c
index af24d38544..2926a4caf6 100644
--- a/src/backend/access/gin/ginscan.c
+++ b/src/backend/access/gin/ginscan.c
@@ -435,7 +435,7 @@ ginNewScanKey(IndexScanDesc scan)
 
 	MemoryContextSwitchTo(oldCtx);
 
-	pgstat_count_index_scan(scan->indexRelation);
+	pgstat_count_index_scan(scan->indexRelation, false);
 }
 
 void
diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
index b35b8a9757..7e89382ce5 100644
--- a/src/backend/access/gist/gistget.c
+++ b/src/backend/access/gist/gistget.c
@@ -624,7 +624,7 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir)
 		/* Begin the scan by processing the root page */
 		GISTSearchItem fakeItem;
 
-		pgstat_count_index_scan(scan->indexRelation);
+		pgstat_count_index_scan(scan->indexRelation, false);
 
 		so->firstCall = false;
 		so->curPageData = so->nPageData = 0;
@@ -749,7 +749,7 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
 	if (!so->qual_ok)
 		return 0;
 
-	pgstat_count_index_scan(scan->indexRelation);
+	pgstat_count_index_scan(scan->indexRelation, false);
 
 	/* Begin the scan by processing the root page */
 	so->curPageData = so->nPageData = 0;
diff --git a/src/backend/access/hash/hashsearch.c b/src/backend/access/hash/hashsearch.c
index 0d99d6abc8..a63edc8372 100644
--- a/src/backend/access/hash/hashsearch.c
+++ b/src/backend/access/hash/hashsearch.c
@@ -297,7 +297,7 @@ _hash_first(IndexScanDesc scan, ScanDirection dir)
 	HashPageOpaque opaque;
 	HashScanPosItem *currItem;
 
-	pgstat_count_index_scan(rel);
+	pgstat_count_index_scan(rel, false);
 
 	/*
 	 * We do not support hash scans with no index qualification, because we
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 91b20147a0..f21cf50e6e 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -409,7 +409,7 @@ initscan(HeapScanDesc scan, ScanKey key, bool keep_startblock)
 	 * and for sample scans we update stats for tuple fetches).
 	 */
 	if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
-		pgstat_count_heap_scan(scan->rs_base.rs_rd);
+		pgstat_count_heap_scan(scan->rs_base.rs_rd, (scan->rs_base.rs_parallel != NULL));
 }
 
 /*
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index 2551df8a67..ef50852199 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -896,7 +896,7 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
 
 	Assert(!BTScanPosIsValid(so->currPos));
 
-	pgstat_count_index_scan(rel);
+	pgstat_count_index_scan(rel, (scan->parallel_scan != NULL));
 
 	/*
 	 * Examine the scan keys and eliminate any redundant keys; also mark the
diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c
index 03293a7816..a78fa34570 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -422,7 +422,7 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys,
 	resetSpGistScanOpaque(so);
 
 	/* count an indexscan for stats */
-	pgstat_count_index_scan(scan->indexRelation);
+	pgstat_count_index_scan(scan->indexRelation, false);
 }
 
 void
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7fd5d256a1..54b1cd6b40 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -670,9 +670,13 @@ CREATE VIEW pg_stat_all_tables AS
             C.relname AS relname,
             pg_stat_get_numscans(C.oid) AS seq_scan,
             pg_stat_get_lastscan(C.oid) AS last_seq_scan,
+            pg_stat_get_parallelnumscans(C.oid) AS parallel_seq_scan,
+            pg_stat_get_parallellastscan(C.oid) AS last_parallel_seq_scan,
             pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
             sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
             max(pg_stat_get_lastscan(I.indexrelid)) AS last_idx_scan,
+            sum(pg_stat_get_parallelnumscans(I.indexrelid))::bigint AS parallel_idx_scan,
+            max(pg_stat_get_parallellastscan(I.indexrelid)) AS last_parallel_idx_scan,
             sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
             pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
             pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
@@ -792,6 +796,8 @@ CREATE VIEW pg_stat_all_indexes AS
             I.relname AS indexrelname,
             pg_stat_get_numscans(I.oid) AS idx_scan,
             pg_stat_get_lastscan(I.oid) AS last_idx_scan,
+            pg_stat_get_parallelnumscans(I.oid) AS parallel_idx_scan,
+            pg_stat_get_parallellastscan(I.oid) AS last_parallel_idx_scan,
             pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
             pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
     FROM pg_class C JOIN
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 8a3f7d434c..766c56524e 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -829,12 +829,20 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
 	tabentry = &shtabstats->stats;
 
 	tabentry->numscans += lstats->counts.numscans;
+	tabentry->parallelnumscans += lstats->counts.parallelnumscans;
+
+	/*
+	 * Don't check counts.parallelnumscans because counts.numscans includes
+	 * counts.parallelnumscans.
+	 */
 	if (lstats->counts.numscans)
 	{
 		TimestampTz t = GetCurrentTransactionStopTimestamp();
 
 		if (t > tabentry->lastscan)
 			tabentry->lastscan = t;
+		if (t > tabentry->parallellastscan && lstats->counts.parallelnumscans)
+			tabentry->parallellastscan = t;
 	}
 	tabentry->tuples_returned += lstats->counts.tuples_returned;
 	tabentry->tuples_fetched += lstats->counts.tuples_fetched;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 97dc09ac0d..30a3849e3d 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -82,6 +82,9 @@ PG_STAT_GET_RELENTRY_INT64(mod_since_analyze)
 /* pg_stat_get_numscans */
 PG_STAT_GET_RELENTRY_INT64(numscans)
 
+/* pg_stat_get_parallelnumscans */
+PG_STAT_GET_RELENTRY_INT64(parallelnumscans)
+
 /* pg_stat_get_tuples_deleted */
 PG_STAT_GET_RELENTRY_INT64(tuples_deleted)
 
@@ -140,6 +143,9 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time)
 /* pg_stat_get_lastscan */
 PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan)
 
+/* pg_stat_get_parallellastscan */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(parallellastscan)
+
 Datum
 pg_stat_get_function_calls(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ff5436acac..1cce03a6d2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5391,6 +5391,14 @@
   proname => 'pg_stat_get_lastscan', provolatile => 's', proparallel => 'r',
   prorettype => 'timestamptz', proargtypes => 'oid',
   prosrc => 'pg_stat_get_lastscan' },
+{ oid => '9000', descr => 'statistics: number of parallel scans done for table/index',
+  proname => 'pg_stat_get_parallelnumscans', provolatile => 's', proparallel => 'r',
+  prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_parallelnumscans' },
+{ oid => '9001', descr => 'statistics: time of the last parallel scan for table/index',
+  proname => 'pg_stat_get_parallellastscan', provolatile => 's', proparallel => 'r',
+  prorettype => 'timestamptz', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_parallellastscan' },
 { oid => '1929', descr => 'statistics: number of tuples read by seqscan',
   proname => 'pg_stat_get_tuples_returned', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index be2c91168a..ceca8bff7d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -192,6 +192,7 @@ typedef struct PgStat_BackendSubEntry
 typedef struct PgStat_TableCounts
 {
 	PgStat_Counter numscans;
+	PgStat_Counter parallelnumscans;
 
 	PgStat_Counter tuples_returned;
 	PgStat_Counter tuples_fetched;
@@ -433,6 +434,8 @@ typedef struct PgStat_StatTabEntry
 {
 	PgStat_Counter numscans;
 	TimestampTz lastscan;
+	PgStat_Counter parallelnumscans;
+	TimestampTz parallellastscan;
 
 	PgStat_Counter tuples_returned;
 	PgStat_Counter tuples_fetched;
@@ -640,10 +643,13 @@ extern void pgstat_report_analyze(Relation rel,
 
 /* nontransactional event counts are simple enough to inline */
 
-#define pgstat_count_heap_scan(rel)									\
+#define pgstat_count_heap_scan(rel, parallel)						\
 	do {															\
-		if (pgstat_should_count_relation(rel))						\
+		if (pgstat_should_count_relation(rel)) {					\
 			(rel)->pgstat_info->counts.numscans++;					\
+			if (parallel)											\
+				(rel)->pgstat_info->counts.parallelnumscans++;		\
+		}															\
 	} while (0)
 #define pgstat_count_heap_getnext(rel)								\
 	do {															\
@@ -655,10 +661,13 @@ extern void pgstat_report_analyze(Relation rel,
 		if (pgstat_should_count_relation(rel))						\
 			(rel)->pgstat_info->counts.tuples_fetched++;			\
 	} while (0)
-#define pgstat_count_index_scan(rel)								\
+#define pgstat_count_index_scan(rel, parallel)						\
 	do {															\
-		if (pgstat_should_count_relation(rel))						\
+		if (pgstat_should_count_relation(rel)) {					\
 			(rel)->pgstat_info->counts.numscans++;					\
+			if (parallel)											\
+				(rel)->pgstat_info->counts.parallelnumscans++;		\
+		}															\
 	} while (0)
 #define pgstat_count_index_tuples(rel, n)							\
 	do {															\
-- 
2.46.0

---

From: Guillaume Lelarge <[email-masked]>
Date: Sun, 6 Oct 2024 21:53:32 +0200

Hello,

Le jeu. 5 sept. 2024 à 08:19, Guillaume Lelarge <[email-masked]> a
écrit :

> Le jeu. 5 sept. 2024 à 07:36, Bertrand Drouvot <
> [email-masked]> a écrit :
>
>> Hi,
>>
>> What about adding a few tests? or do you want to wait a bit more to see
>> if "
> Guess I can start working on that now. It will take some time as I've
> never done it before. Good thing I added the patch on the November commit
> fest :)
>
>

Finally found some time to work on this. Tests added on v5 patch (attached).

Regards.

-- 
Guillaume.
From 92474720b3178f74517958fededcf6797de58552 Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <[email-masked]>
Date: Sun, 6 Oct 2024 21:50:17 +0200
Subject: [PATCH v5] Add parallel columns for pg_stat_all_tables,indexes

pg_stat_all_indexes gets 2 new columns: parallel_idx_scan,
last_parallel_idx_scan.
---
 doc/src/sgml/monitoring.sgml                 |  69 ++++++-
 src/backend/access/brin/brin.c               |   2 +-
 src/backend/access/gin/ginscan.c             |   2 +-
 src/backend/access/gist/gistget.c            |   4 +-
 src/backend/access/hash/hashsearch.c         |   2 +-
 src/backend/access/heap/heapam.c             |   2 +-
 src/backend/access/nbtree/nbtsearch.c        |  12 +-
 src/backend/access/spgist/spgscan.c          |   2 +-
 src/backend/catalog/system_views.sql         |   6 +
 src/backend/utils/activity/pgstat_relation.c |   8 +
 src/backend/utils/adt/pgstatfuncs.c          |   6 +
 src/include/catalog/pg_proc.dat              |   8 +
 src/include/pgstat.h                         |  17 +-
 src/test/regress/expected/rules.out          |  18 ++
 src/test/regress/expected/stats.out          | 194 +++++++++++++++++++
 src/test/regress/sql/stats.sql               |  92 +++++++++
 16 files changed, 421 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 331315f8d3..aeaabb0ffe 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3803,7 +3803,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>seq_scan</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of sequential scans initiated on this table
+       Number of sequential scans (including parallel ones) initiated on this table
       </para></entry>
      </row>
 
@@ -3812,7 +3812,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
@@ -3831,7 +3850,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
@@ -3840,7 +3859,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
@@ -4110,7 +4148,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
@@ -4119,7 +4157,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
index c0b978119a..0fffa67301 100644
@@ -584,7 +584,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)

 	/*
index f2fd62afbb..d2001d6054 100644

 
 void

 		so->firstCall = false;

 
 
 	/*
index da5e656a08..099b08242f 100644
@@ -400,7 +400,7 @@ initscan(HeapScanDesc scan, ScanKey key, bool keep_startblock)
 
 /*
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index fff7c89ead..8115b0fa2d 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -912,6 +912,12 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
 		return false;
 	}
 
+	/*
+	 * Count an indexscan for stats, now that we know that we'll call
+	 * _bt_search/_bt_endpoint below
+	 */
+	pgstat_count_index_scan(rel, (scan->parallel_scan != NULL));
+
 	/*
 	 * For parallel scans, get the starting page from shared state. If the
 	 * scan has not started, proceed to find out first leaf page in the usual
@@ -958,12 +964,6 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
 		_bt_start_array_keys(scan, dir);
 	}
 
-	/*
-	 * Count an indexscan for stats, now that we know that we'll call
-	 * _bt_search/_bt_endpoint below
-	 */
-	pgstat_count_index_scan(rel);
-
 	/*----------
 	 * Examine the scan keys to discover where we need to start the scan.
 	 *
diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c
index 3017861859..fe3c9979df 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -420,7 +420,7 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys,
 	resetSpGistScanOpaque(so);

 void
index 3456b821bc..b062af32fb 100644

index f7b50e0b5a..2bb2e7bdbc 100644

 
index 77f54a79e6..e92a924dd2 100644
@@ -5443,6 +5443,14 @@
index df53fa2d4f..55dbeefd24 100644
 
@@ -435,6 +436,8 @@ typedef struct PgStat_StatTabEntry
 
 	PgStat_Counter tuples_returned;
 	PgStat_Counter tuples_fetched;
@@ -642,10 +645,13 @@ extern void pgstat_report_analyze(Relation rel,

-#define pgstat_count_heap_scan(rel)									\
+#define pgstat_count_heap_scan(rel, parallel)						\
 	do {															\
-		if (pgstat_should_count_relation(rel))						\
+		if (pgstat_should_count_relation(rel)) {					\
 			(rel)->pgstat_info->counts.numscans++;					\
+			if (parallel)											\
+				(rel)->pgstat_info->counts.parallelnumscans++;		\
+		}															\
 	} while (0)
 #define pgstat_count_heap_getnext(rel)								\
 	do {															\
@@ -657,10 +663,13 @@ extern void pgstat_report_analyze(Relation rel,
 		if (pgstat_should_count_relation(rel))						\
 			(rel)->pgstat_info->counts.tuples_fetched++;			\
 	} while (0)
-#define pgstat_count_index_scan(rel)								\
+#define pgstat_count_index_scan(rel, parallel)						\
 	do {															\
-		if (pgstat_should_count_relation(rel))						\
+		if (pgstat_should_count_relation(rel)) {					\
 			(rel)->pgstat_info->counts.numscans++;					\
+			if (parallel)											\
+				(rel)->pgstat_info->counts.parallelnumscans++;		\
+		}															\
 	} while (0)
 #define pgstat_count_index_tuples(rel, n)							\
 	do {															\
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2b47013f11..a8c390fd89 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1772,6 +1772,8 @@ pg_stat_all_indexes| SELECT c.oid AS relid,
     i.relname AS indexrelname,
     pg_stat_get_numscans(i.oid) AS idx_scan,
     pg_stat_get_lastscan(i.oid) AS last_idx_scan,
+    pg_stat_get_parallelnumscans(i.oid) AS parallel_idx_scan,
+    pg_stat_get_parallellastscan(i.oid) AS last_parallel_idx_scan,
     pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
     pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
    FROM (((pg_class c
@@ -1784,9 +1786,13 @@ pg_stat_all_tables| SELECT c.oid AS relid,
     c.relname,
     pg_stat_get_numscans(c.oid) AS seq_scan,
     pg_stat_get_lastscan(c.oid) AS last_seq_scan,
+    pg_stat_get_parallelnumscans(c.oid) AS parallel_seq_scan,
+    pg_stat_get_parallellastscan(c.oid) AS last_parallel_seq_scan,
     pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,
     (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,
     max(pg_stat_get_lastscan(i.indexrelid)) AS last_idx_scan,
+    (sum(pg_stat_get_parallelnumscans(i.indexrelid)))::bigint AS parallel_idx_scan,
+    max(pg_stat_get_parallellastscan(i.indexrelid)) AS last_parallel_idx_scan,
     ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch,
     pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
     pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
@@ -2157,6 +2163,8 @@ pg_stat_sys_indexes| SELECT relid,
     indexrelname,
     idx_scan,
     last_idx_scan,
+    parallel_idx_scan,
+    last_parallel_idx_scan,
     idx_tup_read,
     idx_tup_fetch
    FROM pg_stat_all_indexes
@@ -2166,9 +2174,13 @@ pg_stat_sys_tables| SELECT relid,
     relname,
     seq_scan,
     last_seq_scan,
+    parallel_seq_scan,
+    last_parallel_seq_scan,
     seq_tup_read,
     idx_scan,
     last_idx_scan,
+    parallel_idx_scan,
+    last_parallel_idx_scan,
     idx_tup_fetch,
     n_tup_ins,
     n_tup_upd,
@@ -2205,6 +2217,8 @@ pg_stat_user_indexes| SELECT relid,
     indexrelname,
     idx_scan,
     last_idx_scan,
+    parallel_idx_scan,
+    last_parallel_idx_scan,
     idx_tup_read,
     idx_tup_fetch
    FROM pg_stat_all_indexes
@@ -2214,9 +2228,13 @@ pg_stat_user_tables| SELECT relid,
     relname,
     seq_scan,
     last_seq_scan,
+    parallel_seq_scan,
+    last_parallel_seq_scan,
     seq_tup_read,
     idx_scan,
     last_idx_scan,
+    parallel_idx_scan,
+    last_parallel_idx_scan,
     idx_tup_fetch,
     n_tup_ins,
     n_tup_upd,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 56771f83ed..2fe229dd5a 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -764,6 +764,200 @@ FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
         2 | t      |        3 | t
 (1 row)
 
+-----
+-- Test that parallel_seq_scan, last_parallel_seq_scan, parallel_idx_scan, last_parallel_idx_scan are correctly maintained
+--
+-- We can't use a temporary table for parallel test. So, perform test using a permanent table,
+-- but disable autovacuum on it. That way autovacuum etc won't
+-- interfere. To be able to check that timestamps increase, we sleep for 100ms
+-- between tests, assuming that there aren't systems with a coarser timestamp
+-- granularity.
+-----
+BEGIN;
+CREATE TABLE test_parallel_scan(idx_col int primary key, noidx_col int) WITH (autovacuum_enabled = false);
+INSERT INTO test_parallel_scan(idx_col, noidx_col) SELECT i, i FROM generate_series(1, 10_000) i;
+COMMIT;
+VACUUM ANALYZE test_parallel_scan;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT parallel_seq_scan, last_parallel_seq_scan, parallel_idx_scan, last_parallel_idx_scan
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+ parallel_seq_scan | last_parallel_seq_scan | parallel_idx_scan | last_parallel_idx_scan 
+-------------------+------------------------+-------------------+------------------------
+                 0 |                        |                 0 | 
+(1 row)
+
+-- ensure we start out with exactly one parallel index and parallel sequential scan
+BEGIN;
+SET LOCAL parallel_setup_cost TO 0;
+SET LOCAL min_parallel_table_scan_size TO '100kB';
+SET LOCAL min_parallel_index_scan_size TO '100kB';
+SET LOCAL enable_seqscan TO on;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_bitmapscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Aggregate
+   ->  Gather
+         Workers Planned: 2
+         ->  Parallel Seq Scan on test_parallel_scan
+               Filter: (noidx_col = 1)
+(5 rows)
+
+SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+ count 
+-------
+     1
+(1 row)
+
+SET LOCAL enable_seqscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+                                        QUERY PLAN                                         
+-------------------------------------------------------------------------------------------
+ Finalize Aggregate
+   ->  Gather
+         Workers Planned: 1
+         ->  Partial Aggregate
+               ->  Parallel Index Scan using test_parallel_scan_pkey on test_parallel_scan
+                     Index Cond: ((idx_col >= 1) AND (idx_col <= 6000))
+(6 rows)
+
+SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+ count 
+-------
+  6000
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+COMMIT;
+-- fetch timestamps from before the next test
+SELECT parallel_seq_scan, parallel_idx_scan
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+ parallel_seq_scan | parallel_idx_scan 
+-------------------+-------------------
+                 3 |                 2
+(1 row)
+
+SELECT last_parallel_seq_scan AS test_last_seq, last_parallel_idx_scan AS test_last_idx
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass \gset
+SELECT pg_sleep(0.1); -- assume a minimum timestamp granularity of 100ms
+ pg_sleep 
+----------
+ 
+(1 row)
+
+-- cause one parallel sequential scan
+BEGIN;
+SET LOCAL parallel_setup_cost TO 0;
+SET LOCAL min_parallel_table_scan_size TO '100kB';
+SET LOCAL min_parallel_index_scan_size TO '100kB';
+SET LOCAL enable_seqscan TO on;
+SET LOCAL enable_indexscan TO off;
+SET LOCAL enable_bitmapscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Aggregate
+   ->  Gather
+         Workers Planned: 2
+         ->  Parallel Seq Scan on test_parallel_scan
+               Filter: (noidx_col = 1)
+(5 rows)
+
+SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+ count 
+-------
+     1
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+COMMIT;
+-- check that just sequential scan stats were incremented
+SELECT parallel_seq_scan, :'test_last_seq' < last_parallel_seq_scan AS seq_ok,
+       parallel_idx_scan, :'test_last_idx' = last_parallel_idx_scan AS idx_ok
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+ parallel_seq_scan | seq_ok | parallel_idx_scan | idx_ok 
+-------------------+--------+-------------------+--------
+                 6 | t      |                 2 | t
+(1 row)
+
+-- fetch timestamps from before the next test
+SELECT last_parallel_seq_scan AS test_last_seq, last_parallel_idx_scan AS test_last_idx
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass \gset
+SELECT pg_sleep(0.1);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+-- cause one parallel index scan
+BEGIN;
+SET LOCAL parallel_setup_cost TO 0;
+SET LOCAL min_parallel_table_scan_size TO '100kB';
+SET LOCAL min_parallel_index_scan_size TO '100kB';
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_bitmapscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+                                        QUERY PLAN                                         
+-------------------------------------------------------------------------------------------
+ Finalize Aggregate
+   ->  Gather
+         Workers Planned: 1
+         ->  Partial Aggregate
+               ->  Parallel Index Scan using test_parallel_scan_pkey on test_parallel_scan
+                     Index Cond: ((idx_col >= 1) AND (idx_col <= 6000))
+(6 rows)
+
+SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+ count 
+-------
+  6000
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+COMMIT;
+-- check that just index scan stats were incremented
+SELECT parallel_seq_scan, :'test_last_seq' = last_parallel_seq_scan AS seq_ok,
+       parallel_idx_scan, :'test_last_idx' < last_parallel_idx_scan AS idx_ok
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+ parallel_seq_scan | seq_ok | parallel_idx_scan | idx_ok 
+-------------------+--------+-------------------+--------
+                 6 | t      |                 4 | t
+(1 row)
+
+-- fetch timestamps from before the next test
+SELECT last_parallel_seq_scan AS test_last_seq, last_parallel_idx_scan AS test_last_idx
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass \gset
+SELECT pg_sleep(0.1);
+ pg_sleep 
+----------
+ 
+(1 row)
+
 -----
 -- Test reset of some stats for shared table
 -----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 7147cc2f89..5eb92fbf55 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -376,6 +376,98 @@ COMMIT;
 SELECT seq_scan, :'test_last_seq' = last_seq_scan AS seq_ok, idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok
 FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
 
+-----
+-- Test that parallel_seq_scan, last_parallel_seq_scan, parallel_idx_scan, last_parallel_idx_scan are correctly maintained
+--
+-- We can't use a temporary table for parallel test. So, perform test using a permanent table,
+-- but disable autovacuum on it. That way autovacuum etc won't
+-- interfere. To be able to check that timestamps increase, we sleep for 100ms
+-- between tests, assuming that there aren't systems with a coarser timestamp
+-- granularity.
+-----
+
+BEGIN;
+CREATE TABLE test_parallel_scan(idx_col int primary key, noidx_col int) WITH (autovacuum_enabled = false);
+INSERT INTO test_parallel_scan(idx_col, noidx_col) SELECT i, i FROM generate_series(1, 10_000) i;
+COMMIT;
+
+VACUUM ANALYZE test_parallel_scan;
+
+SELECT pg_stat_force_next_flush();
+
+SELECT parallel_seq_scan, last_parallel_seq_scan, parallel_idx_scan, last_parallel_idx_scan
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+
+-- ensure we start out with exactly one parallel index and parallel sequential scan
+BEGIN;
+SET LOCAL parallel_setup_cost TO 0;
+SET LOCAL min_parallel_table_scan_size TO '100kB';
+SET LOCAL min_parallel_index_scan_size TO '100kB';
+SET LOCAL enable_seqscan TO on;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_bitmapscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+SET LOCAL enable_seqscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+SELECT pg_stat_force_next_flush();
+COMMIT;
+
+-- fetch timestamps from before the next test
+SELECT parallel_seq_scan, parallel_idx_scan
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+SELECT last_parallel_seq_scan AS test_last_seq, last_parallel_idx_scan AS test_last_idx
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass \gset
+SELECT pg_sleep(0.1); -- assume a minimum timestamp granularity of 100ms
+
+-- cause one parallel sequential scan
+BEGIN;
+SET LOCAL parallel_setup_cost TO 0;
+SET LOCAL min_parallel_table_scan_size TO '100kB';
+SET LOCAL min_parallel_index_scan_size TO '100kB';
+SET LOCAL enable_seqscan TO on;
+SET LOCAL enable_indexscan TO off;
+SET LOCAL enable_bitmapscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+SELECT count(*) FROM test_parallel_scan WHERE noidx_col = 1;
+SELECT pg_stat_force_next_flush();
+COMMIT;
+-- check that just sequential scan stats were incremented
+SELECT parallel_seq_scan, :'test_last_seq' < last_parallel_seq_scan AS seq_ok,
+       parallel_idx_scan, :'test_last_idx' = last_parallel_idx_scan AS idx_ok
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+
+-- fetch timestamps from before the next test
+SELECT last_parallel_seq_scan AS test_last_seq, last_parallel_idx_scan AS test_last_idx
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass \gset
+SELECT pg_sleep(0.1);
+
+-- cause one parallel index scan
+BEGIN;
+SET LOCAL parallel_setup_cost TO 0;
+SET LOCAL min_parallel_table_scan_size TO '100kB';
+SET LOCAL min_parallel_index_scan_size TO '100kB';
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_bitmapscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+SELECT count(*) FROM test_parallel_scan WHERE idx_col BETWEEN 1 AND 6000;
+SELECT pg_stat_force_next_flush();
+COMMIT;
+-- check that just index scan stats were incremented
+SELECT parallel_seq_scan, :'test_last_seq' = last_parallel_seq_scan AS seq_ok,
+       parallel_idx_scan, :'test_last_idx' < last_parallel_idx_scan AS idx_ok
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass;
+
+-- fetch timestamps from before the next test
+SELECT last_parallel_seq_scan AS test_last_seq, last_parallel_idx_scan AS test_last_idx
+FROM pg_stat_all_tables WHERE relid = 'test_parallel_scan'::regclass \gset
+SELECT pg_sleep(0.1);
+
 -----
 -- Test reset of some stats for shared table
 -----
-- 
2.46.2

---

From: Alena Rybakina <[email-masked]>
Date: Mon, 7 Oct 2024 00:43:18 +0300

Hi!

> Finally found some time to work on this. Tests added on v5 patch 
Maybe I'm not aware of the whole context of the thread and maybe my 
questions will seem a bit stupid, but honestly
it's not entirely clear to me how this statistics will help to adjust 
the number of parallel workers.
We may have situations when during overestimation of the cardinality 
during query optimization a several number of parallel workers were 
unjustifiably generated and vice versa -
due to a high workload only a few number of workers were generated.
How do we identify such cases so as not to increase or decrease the 
number of parallel workers when it is not necessary?

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Michael Paquier <[email-masked]>
Date: Mon, 7 Oct 2024 09:41:36 +0900

On Mon, Oct 07, 2024 at 12:43:18AM +0300, Alena Rybakina wrote:
> it's not entirely clear to me how this statistics will help to adjust the
> number of parallel workers.
> We may have situations when during overestimation of the cardinality during
> query optimization a several number of parallel workers were unjustifiably
> generated and vice versa -
> How do we identify such cases so as not to increase or decrease the number
> of parallel workers when it is not necessary?

Well.  For spiky workloads, only these numbers are not going to help.
If you can map them with the number of times a query related to these
tables has been called, something that pg_stat_statements would be
able to show more information about.

FWIW, I have doubts that these numbers attached to this portion of the
system are always useful.  For OLTP workloads, parallel workers would
unlikely be spawned because even with JOINs we won't work with a high
number of tuples that require them.  This could be interested with
analytics, however complex query sequences mean that we'd still need
to look at all the plans involving the relations where there is an
unbalance of planned/spawned workers, because these can usually
involve quite a few gather nodes.  At the end of the day, it seems to
me that we would still need data that involves statements to track
down specific plans that are starving.  If your application does not
have that many statements, looking at individial plans is OK, but if
you have hundreds of them to dig into, this is time-consuming and
stats at table/index level don't offer data in terms of stuff that
stands out and needs adjustments.

And this is without the argument of bloating more the stats entries
for each table, even if it matters less now that these stats are in
shmem lately.
--
Michael

---

From: Guillaume Lelarge <[email-masked]>
Date: Mon, 7 Oct 2024 08:34:58 +0200

Le lun. 7 oct. 2024 à 02:41, Michael Paquier <[email-masked]> a écrit :

> during
> number

We need granularity because we have granularity in the config. There is
pg_stat_database because it gives the whole picture and it is easier to
monitor. And then, there is pg_stat_statements to analyze problematic
statements. And finally there is pg_stat_all* because you can set
parallel_workers on a specific table.

Anyway, offering various ways of getting the same information is not
unheard of. Pretty much like temp_files/temp_bytes in pg_stat_database,
temp_blks_read/temp_blks_written in pg_stat_statements and log_temp_files
in log files if you ask me :)

-- 
Guillaume.

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 8 Oct 2024 18:20:17 +0300

> for each table, even if it matters less now that these stats are in

To be honest, it’s not entirely clear to me how these statistics will 
help in setting up parallel workers.

As I understand, we need additional tools for analytics, which are 
available in pg_stat_statements, but how then does it work? maybe you 
have the opportunity to demonstrate this?

-- 
Regards,
Alena Rybakina
Postgres Professional