Hello, This patch was a bit discussed on [1], and with more details on [2]. It introduces four new columns in pg_stat_all_tables:
* parallel_seq_scan * last_parallel_seq_scan * parallel_idx_scan * last_parallel_idx_scan and two new columns in pg_stat_all_indexes: * parallel_idx_scan * last_parallel_idx_scan As Benoit said yesterday, the intent is to help administrators evaluate the usage of parallel workers in their databases and help configuring parallelization usage. A test script (test.sql) is attached. You can execute it with "psql -Xef test.sql your_database" (your_database should not contain a t1 table as it will be dropped and recreated). Here is its result, a bit commented: DROP TABLE IF EXISTS t1; DROP TABLE CREATE TABLE t1 (id integer); CREATE TABLE INSERT INTO t1 SELECT generate_series(1, 10_000_000); INSERT 0 10000000 VACUUM ANALYZE t1; VACUUM SELECT relname, seq_scan, last_seq_scan, parallel_seq_scan, last_parallel_seq_scan FROM pg_stat_user_tables WHERE relname='t1' -[ RECORD 1 ]----------+--- relname | t1 seq_scan | 0 last_seq_scan | parallel_seq_scan | 0 last_parallel_seq_scan | ==> no scan at all, the table has just been created SELECT * FROM t1 LIMIT 1; id ---- 1 (1 row) SELECT pg_sleep(1); SELECT relname, seq_scan, last_seq_scan, parallel_seq_scan, last_parallel_seq_scan FROM pg_stat_user_tables WHERE relname='t1' -[ RECORD 1 ]----------+------------------------------ relname | t1 seq_scan | 1 last_seq_scan | 2024-08-29 15:43:17.377182+02 parallel_seq_scan | 0 last_parallel_seq_scan | ==> one sequential scan, no parallelization SELECT count(*) FROM t1; count ---------- 10000000 (1 row) SELECT pg_sleep(1); SELECT relname, seq_scan, last_seq_scan, parallel_seq_scan, last_parallel_seq_scan FROM pg_stat_user_tables WHERE relname='t1' -[ RECORD 1 ]----------+------------------------------ relname | t1 seq_scan | 4 last_seq_scan | 2024-08-29 15:43:18.504533+02 parallel_seq_scan | 3 last_parallel_seq_scan | 2024-08-29 15:43:18.504533+02 ==> one parallel sequential scan ==> I use the default configuration, so parallel_leader_participation = on, max_parallel_workers_per_gather = 2 ==> meaning 3 parallel sequential scans (1 leader, two workers) ==> take note that seq_scan was also incremented... we didn't change the previous behaviour for this column CREATE INDEX ON t1(id); CREATE INDEX SELECT indexrelname,idx_scan,last_idx_scan,parallel_idx_scan,last_parallel_idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE relname='t1' -[ RECORD 1 ]----------+---------- indexrelname | t1_id_idx idx_scan | 0 last_idx_scan | parallel_idx_scan | 0 last_parallel_idx_scan | idx_tup_read | 0 idx_tup_fetch | 0 ==> no scan at all, the index has just been created SELECT * FROM t1 WHERE id=150000; id -------- 150000 (1 row) SELECT pg_sleep(1); SELECT indexrelname,idx_scan,last_idx_scan,parallel_idx_scan,last_parallel_idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE relname='t1' -[ RECORD 1 ]----------+------------------------------ indexrelname | t1_id_idx idx_scan | 1 last_idx_scan | 2024-08-29 15:43:22.020853+02 parallel_idx_scan | 0 last_parallel_idx_scan | idx_tup_read | 1 idx_tup_fetch | 0 ==> one index scan, no parallelization SELECT * FROM t1 WHERE id BETWEEN 100000 AND 400000; SELECT pg_sleep(1); pg_sleep ---------- (1 row) SELECT indexrelname,idx_scan,last_idx_scan,parallel_idx_scan,last_parallel_idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE relname='t1' -[ RECORD 1 ]----------+------------------------------ indexrelname | t1_id_idx idx_scan | 2 last_idx_scan | 2024-08-29 15:43:23.136665+02 parallel_idx_scan | 0 last_parallel_idx_scan | idx_tup_read | 300002 idx_tup_fetch | 0 ==> another index scan, no parallelization SELECT count(*) FROM t1 WHERE id BETWEEN 100000 AND 400000; count -------- 300001 (1 row) SELECT pg_sleep(1); SELECT indexrelname,idx_scan,last_idx_scan,parallel_idx_scan,last_parallel_idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE relname='t1' -[ RECORD 1 ]----------+----------------------------- indexrelname | t1_id_idx idx_scan | 5 last_idx_scan | 2024-08-29 15:43:24.16057+02 parallel_idx_scan | 3 last_parallel_idx_scan | 2024-08-29 15:43:24.16057+02 idx_tup_read | 600003 idx_tup_fetch | 0 ==> one parallel index scan ==> same thing, 3 parallel index scans (1 leader, two workers) ==> also, take note that idx_scan was also incremented... we didn't change the previous behaviour for this column First time I had to add new columns to a statistics catalog. I'm actually not sure that we were right to change pg_proc.dat manually. We'll probably have to fix this. Documentation is done, but maybe we should also add that seq_scan and idx_scan also include parallel scan. Yet to be done: tests. Once there's an agreement on this patch, we'll work on the tests. This has been a collective work with Benoit Lobréau, Jehan-Guillaume de Rorthais, and Franck Boudehen. Thanks. Regards. [1] https://www.postgresql.org/message-id/flat/b4220d15-2e21-0e98-921b-b9892543cc93%40dalibo.com [2] https://www.postgresql.org/message-id/flat/d657df20-c4bf-63f6-e74c-cb85a81d0383%40dalibo.com -- Guillaume.
test.sql
Description: application/sql
From 78fb5406c42c9ecd429e08314d9f3a0bfd112c6a Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <guillaume.lela...@dalibo.com>
Date: Wed, 28 Aug 2024 21:35:30 +0200
Subject: [PATCH] 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 | 57 ++++++++++++++++++++
src/backend/access/heap/heapam.c | 2 +
src/backend/access/nbtree/nbtsearch.c | 2 +
src/backend/catalog/system_views.sql | 6 +++
src/backend/utils/activity/pgstat_relation.c | 7 ++-
src/backend/utils/adt/pgstatfuncs.c | 6 +++
src/include/catalog/pg_proc.dat | 8 +++
src/include/pgstat.h | 13 +++++
8 files changed, 99 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 55417a6fa9..afd5a23528 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3714,6 +3714,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</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>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>seq_tup_read</structfield> <type>bigint</type>
@@ -3742,6 +3761,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</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>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>idx_tup_fetch</structfield> <type>bigint</type>
@@ -4021,6 +4059,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</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>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>idx_tup_read</structfield> <type>bigint</type>
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 91b20147a0..c2f1b8e25e 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -410,6 +410,8 @@ initscan(HeapScanDesc scan, ScanKey key, bool keep_startblock)
*/
if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
pgstat_count_heap_scan(scan->rs_base.rs_rd);
+ if (scan->rs_base.rs_parallel != NULL)
+ pgstat_count_parallel_heap_scan(scan->rs_base.rs_rd);
}
/*
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index 2551df8a67..e37ed32bb1 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -897,6 +897,8 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
Assert(!BTScanPosIsValid(so->currPos));
pgstat_count_index_scan(rel);
+ if (scan->parallel_scan != NULL)
+ pgstat_count_parallel_index_scan(rel);
/*
* Examine the scan keys and eliminate any redundant keys; also mark the
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 19cabc9a47..d78a121114 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..cfdc1d42bf 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -829,12 +829,15 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
tabentry = &shtabstats->stats;
tabentry->numscans += lstats->counts.numscans;
- if (lstats->counts.numscans)
+ tabentry->parallelnumscans += lstats->counts.parallelnumscans;
+ if (lstats->counts.numscans || lstats->counts.parallelnumscans)
{
TimestampTz t = GetCurrentTransactionStopTimestamp();
- if (t > tabentry->lastscan)
+ if (t > tabentry->lastscan && lstats->counts.numscans)
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 3221137123..8b9440ee3b 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 4abc6d9526..022c905ea7 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 f63159c55c..b5011a3d1b 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -190,6 +190,7 @@ typedef struct PgStat_BackendSubEntry
typedef struct PgStat_TableCounts
{
PgStat_Counter numscans;
+ PgStat_Counter parallelnumscans;
PgStat_Counter tuples_returned;
PgStat_Counter tuples_fetched;
@@ -430,6 +431,8 @@ typedef struct PgStat_StatTabEntry
{
PgStat_Counter numscans;
TimestampTz lastscan;
+ PgStat_Counter parallelnumscans;
+ TimestampTz parallellastscan;
PgStat_Counter tuples_returned;
PgStat_Counter tuples_fetched;
@@ -642,6 +645,11 @@ extern void pgstat_report_analyze(Relation rel,
if (pgstat_should_count_relation(rel)) \
(rel)->pgstat_info->counts.numscans++; \
} while (0)
+#define pgstat_count_parallel_heap_scan(rel) \
+ do { \
+ if (pgstat_should_count_relation(rel)) \
+ (rel)->pgstat_info->counts.parallelnumscans++; \
+ } while (0)
#define pgstat_count_heap_getnext(rel) \
do { \
if (pgstat_should_count_relation(rel)) \
@@ -657,6 +665,11 @@ extern void pgstat_report_analyze(Relation rel,
if (pgstat_should_count_relation(rel)) \
(rel)->pgstat_info->counts.numscans++; \
} while (0)
+#define pgstat_count_parallel_index_scan(rel) \
+ do { \
+ if (pgstat_should_count_relation(rel)) \
+ (rel)->pgstat_info->counts.parallelnumscans++; \
+ } while (0)
#define pgstat_count_index_tuples(rel, n) \
do { \
if (pgstat_should_count_relation(rel)) \
--
2.46.0
