From a28475ff5b1e17ee7295a3daabc79111c30b96d3 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Tue, 8 Oct 2024 15:56:22 +0900 Subject: [PATCH v4 2/3] Add parallel columns to pg_stat_statements --- doc/src/sgml/pgstatstatements.sgml | 18 +++++ contrib/pg_stat_statements/Makefile | 6 +- .../expected/oldextversions.out | 64 ++++++++++++++++ .../pg_stat_statements/expected/parallel.out | 34 +++++++++ contrib/pg_stat_statements/meson.build | 2 + .../pg_stat_statements--1.11--1.12.sql | 75 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 60 +++++++++++++-- .../pg_stat_statements.control | 2 +- .../pg_stat_statements/sql/oldextversions.sql | 5 ++ contrib/pg_stat_statements/sql/parallel.sql | 26 +++++++ 10 files changed, 281 insertions(+), 11 deletions(-) create mode 100644 contrib/pg_stat_statements/expected/parallel.out create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.11--1.12.sql create mode 100644 contrib/pg_stat_statements/sql/parallel.sql diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 9b0aff73b1..501b468e9a 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -527,6 +527,24 @@ + + + parallel_workers_to_launch bigint + + + Number of parallel workers planned to be launched + + + + + + parallel_workers_launched bigint + + + Number of parallel workers actually launched + + + stats_since timestamp with time zone diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index 1622b43ded..241c02587b 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -7,7 +7,7 @@ OBJS = \ EXTENSION = pg_stat_statements DATA = pg_stat_statements--1.4.sql \ - pg_stat_statements--1.10--1.11.sql \ + pg_stat_statements--1.11--1.12.sql pg_stat_statements--1.10--1.11.sql \ pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \ pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \ pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \ @@ -19,8 +19,8 @@ LDFLAGS_SL += $(filter -lm, $(LIBS)) REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf REGRESS = select dml cursors utility level_tracking planning \ - user_activity wal entry_timestamp privileges extended cleanup \ - oldextversions + user_activity wal entry_timestamp privileges extended \ + parallel cleanup oldextversions # Disabled because these tests require "shared_preload_libraries=pg_stat_statements", # which typical installcheck users do not have (e.g. buildfarm clients). NO_INSTALLCHECK = 1 diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out index 5842c930e5..0c60fc8127 100644 --- a/contrib/pg_stat_statements/expected/oldextversions.out +++ b/contrib/pg_stat_statements/expected/oldextversions.out @@ -342,4 +342,68 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; t (1 row) +-- New functions and views for pg_stat_statements in 1.12 +AlTER EXTENSION pg_stat_statements UPDATE TO '1.12'; +\d pg_stat_statements + View "public.pg_stat_statements" + Column | Type | Collation | Nullable | Default +----------------------------+--------------------------+-----------+----------+--------- + userid | oid | | | + dbid | oid | | | + toplevel | boolean | | | + queryid | bigint | | | + query | text | | | + plans | bigint | | | + total_plan_time | double precision | | | + min_plan_time | double precision | | | + max_plan_time | double precision | | | + mean_plan_time | double precision | | | + stddev_plan_time | double precision | | | + calls | bigint | | | + total_exec_time | double precision | | | + min_exec_time | double precision | | | + max_exec_time | double precision | | | + mean_exec_time | double precision | | | + stddev_exec_time | double precision | | | + rows | bigint | | | + shared_blks_hit | bigint | | | + shared_blks_read | bigint | | | + shared_blks_dirtied | bigint | | | + shared_blks_written | bigint | | | + local_blks_hit | bigint | | | + local_blks_read | bigint | | | + local_blks_dirtied | bigint | | | + local_blks_written | bigint | | | + temp_blks_read | bigint | | | + temp_blks_written | bigint | | | + shared_blk_read_time | double precision | | | + shared_blk_write_time | double precision | | | + local_blk_read_time | double precision | | | + local_blk_write_time | double precision | | | + temp_blk_read_time | double precision | | | + temp_blk_write_time | double precision | | | + wal_records | bigint | | | + wal_fpi | bigint | | | + wal_bytes | numeric | | | + jit_functions | bigint | | | + jit_generation_time | double precision | | | + jit_inlining_count | bigint | | | + jit_inlining_time | double precision | | | + jit_optimization_count | bigint | | | + jit_optimization_time | double precision | | | + jit_emission_count | bigint | | | + jit_emission_time | double precision | | | + jit_deform_count | bigint | | | + jit_deform_time | double precision | | | + parallel_workers_to_launch | bigint | | | + parallel_workers_launched | bigint | | | + stats_since | timestamp with time zone | | | + minmax_stats_since | timestamp with time zone | | | + +SELECT count(*) > 0 AS has_data FROM pg_stat_statements; + has_data +---------- + t +(1 row) + DROP EXTENSION pg_stat_statements; diff --git a/contrib/pg_stat_statements/expected/parallel.out b/contrib/pg_stat_statements/expected/parallel.out new file mode 100644 index 0000000000..8af3bd2c91 --- /dev/null +++ b/contrib/pg_stat_statements/expected/parallel.out @@ -0,0 +1,34 @@ +-- +-- Tests for parallel statistics +-- +SET pg_stat_statements.track_utility = FALSE; +-- encourage use of parallel plans +SET parallel_setup_cost = 0; +SET parallel_tuple_cost = 0; +SET min_parallel_table_scan_size = 0; +SET max_parallel_workers_per_gather = 2; +CREATE TABLE pgss_parallel_tab (a int); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT count(*) FROM pgss_parallel_tab; + count +------- + 0 +(1 row) + +SELECT query, + parallel_workers_to_launch > 0 AS has_workers_to_launch, + parallel_workers_launched > 0 AS has_workers_launched + FROM pg_stat_statements + WHERE query ~ 'SELECT count' + ORDER BY query COLLATE "C"; + query | has_workers_to_launch | has_workers_launched +----------------------------------------+-----------------------+---------------------- + SELECT count(*) FROM pgss_parallel_tab | t | t +(1 row) + +DROP TABLE pgss_parallel_tab; diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index e14669ca15..e659b5e2b7 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements install_data( 'pg_stat_statements.control', 'pg_stat_statements--1.4.sql', + 'pg_stat_statements--1.11--1.12.sql', 'pg_stat_statements--1.10--1.11.sql', 'pg_stat_statements--1.9--1.10.sql', 'pg_stat_statements--1.8--1.9.sql', @@ -52,6 +53,7 @@ tests += { 'entry_timestamp', 'privileges', 'extended', + 'parallel', 'cleanup', 'oldextversions', ], diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.11--1.12.sql b/contrib/pg_stat_statements/pg_stat_statements--1.11--1.12.sql new file mode 100644 index 0000000000..80e6be2544 --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.11--1.12.sql @@ -0,0 +1,75 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.11--1.12.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.12'" to load this file. \quit + +/* First we have to remove them from the extension */ +ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements; +ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean); + +/* Then we can drop them */ +DROP VIEW pg_stat_statements; +DROP FUNCTION pg_stat_statements(boolean); + +/* Now redefine */ +CREATE FUNCTION pg_stat_statements(IN showtext boolean, + OUT userid oid, + OUT dbid oid, + OUT toplevel bool, + OUT queryid bigint, + OUT query text, + OUT plans int8, + OUT total_plan_time float8, + OUT min_plan_time float8, + OUT max_plan_time float8, + OUT mean_plan_time float8, + OUT stddev_plan_time float8, + OUT calls int8, + OUT total_exec_time float8, + OUT min_exec_time float8, + OUT max_exec_time float8, + OUT mean_exec_time float8, + OUT stddev_exec_time float8, + OUT rows int8, + OUT shared_blks_hit int8, + OUT shared_blks_read int8, + OUT shared_blks_dirtied int8, + OUT shared_blks_written int8, + OUT local_blks_hit int8, + OUT local_blks_read int8, + OUT local_blks_dirtied int8, + OUT local_blks_written int8, + OUT temp_blks_read int8, + OUT temp_blks_written int8, + OUT shared_blk_read_time float8, + OUT shared_blk_write_time float8, + OUT local_blk_read_time float8, + OUT local_blk_write_time float8, + OUT temp_blk_read_time float8, + OUT temp_blk_write_time float8, + OUT wal_records int8, + OUT wal_fpi int8, + OUT wal_bytes numeric, + OUT jit_functions int8, + OUT jit_generation_time float8, + OUT jit_inlining_count int8, + OUT jit_inlining_time float8, + OUT jit_optimization_count int8, + OUT jit_optimization_time float8, + OUT jit_emission_count int8, + OUT jit_emission_time float8, + OUT jit_deform_count int8, + OUT jit_deform_time float8, + OUT parallel_workers_to_launch int8, + OUT parallel_workers_launched int8, + OUT stats_since timestamp with time zone, + OUT minmax_stats_since timestamp with time zone +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_statements_1_12' +LANGUAGE C STRICT VOLATILE PARALLEL SAFE; + +CREATE VIEW pg_stat_statements AS + SELECT * FROM pg_stat_statements(true); + +GRANT SELECT ON pg_stat_statements TO PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 5765ef49b4..773b821911 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -113,6 +113,7 @@ typedef enum pgssVersion PGSS_V1_9, PGSS_V1_10, PGSS_V1_11, + PGSS_V1_12, } pgssVersion; typedef enum pgssStoreKind @@ -204,6 +205,10 @@ typedef struct Counters int64 jit_emission_count; /* number of times emission time has been * > 0 */ double jit_emission_time; /* total time to emit jit code */ + int64 parallel_workers_to_launch; /* # of parallel workers planned to + * be launched */ + int64 parallel_workers_launched; /* # of parallel workers actually + * launched */ } Counters; /* @@ -317,6 +322,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_8); PG_FUNCTION_INFO_V1(pg_stat_statements_1_9); PG_FUNCTION_INFO_V1(pg_stat_statements_1_10); PG_FUNCTION_INFO_V1(pg_stat_statements_1_11); +PG_FUNCTION_INFO_V1(pg_stat_statements_1_12); PG_FUNCTION_INFO_V1(pg_stat_statements); PG_FUNCTION_INFO_V1(pg_stat_statements_info); @@ -347,7 +353,9 @@ static void pgss_store(const char *query, uint64 queryId, const BufferUsage *bufusage, const WalUsage *walusage, const struct JitInstrumentation *jitusage, - JumbleState *jstate); + JumbleState *jstate, + int parallel_workers_to_launch, + int parallel_workers_launched); static void pg_stat_statements_internal(FunctionCallInfo fcinfo, pgssVersion api_version, bool showtext); @@ -867,7 +875,9 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate) NULL, NULL, NULL, - jstate); + jstate, + 0, + 0); } /* @@ -945,7 +955,9 @@ pgss_planner(Query *parse, &bufusage, &walusage, NULL, - NULL); + NULL, + 0, + 0); } else { @@ -1078,7 +1090,9 @@ pgss_ExecutorEnd(QueryDesc *queryDesc) &queryDesc->totaltime->bufusage, &queryDesc->totaltime->walusage, queryDesc->estate->es_jit ? &queryDesc->estate->es_jit->instr : NULL, - NULL); + NULL, + queryDesc->estate->es_parallel_workers_to_launch, + queryDesc->estate->es_parallel_workers_launched); } if (prev_ExecutorEnd) @@ -1209,7 +1223,9 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, &bufusage, &walusage, NULL, - NULL); + NULL, + 0, + 0); } else { @@ -1270,7 +1286,9 @@ pgss_store(const char *query, uint64 queryId, const BufferUsage *bufusage, const WalUsage *walusage, const struct JitInstrumentation *jitusage, - JumbleState *jstate) + JumbleState *jstate, + int parallel_workers_to_launch, + int parallel_workers_launched) { pgssHashKey key; pgssEntry *entry; @@ -1473,6 +1491,10 @@ pgss_store(const char *query, uint64 queryId, entry->counters.jit_emission_time += INSTR_TIME_GET_MILLISEC(jitusage->emission_counter); } + /* parallel worker counters */ + entry->counters.parallel_workers_to_launch += parallel_workers_to_launch; + entry->counters.parallel_workers_launched += parallel_workers_launched; + SpinLockRelease(&entry->mutex); } @@ -1539,7 +1561,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) #define PG_STAT_STATEMENTS_COLS_V1_9 33 #define PG_STAT_STATEMENTS_COLS_V1_10 43 #define PG_STAT_STATEMENTS_COLS_V1_11 49 -#define PG_STAT_STATEMENTS_COLS 49 /* maximum of above */ +#define PG_STAT_STATEMENTS_COLS_V1_12 51 +#define PG_STAT_STATEMENTS_COLS 51 /* maximum of above */ /* * Retrieve statement statistics. @@ -1551,6 +1574,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) * expected API version is identified by embedding it in the C name of the * function. Unfortunately we weren't bright enough to do that for 1.1. */ +Datum +pg_stat_statements_1_12(PG_FUNCTION_ARGS) +{ + bool showtext = PG_GETARG_BOOL(0); + + pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext); + + return (Datum) 0; +} + Datum pg_stat_statements_1_11(PG_FUNCTION_ARGS) { @@ -1695,6 +1728,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, if (api_version != PGSS_V1_11) elog(ERROR, "incorrect number of output arguments"); break; + case PG_STAT_STATEMENTS_COLS_V1_12: + if (api_version != PGSS_V1_12) + elog(ERROR, "incorrect number of output arguments"); + break; default: elog(ERROR, "incorrect number of output arguments"); } @@ -1932,6 +1969,14 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, { values[i++] = Int64GetDatumFast(tmp.jit_deform_count); values[i++] = Float8GetDatumFast(tmp.jit_deform_time); + } + if (api_version >= PGSS_V1_12) + { + values[i++] = Int64GetDatumFast(tmp.parallel_workers_to_launch); + values[i++] = Int64GetDatumFast(tmp.parallel_workers_launched); + } + if (api_version >= PGSS_V1_11) + { values[i++] = TimestampTzGetDatum(stats_since); values[i++] = TimestampTzGetDatum(minmax_stats_since); } @@ -1944,6 +1989,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 : api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 : api_version == PGSS_V1_11 ? PG_STAT_STATEMENTS_COLS_V1_11 : + api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 : -1 /* fail if you forget to update this assert */ )); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control index 8a76106ec6..d45ebc12e3 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.control +++ b/contrib/pg_stat_statements/pg_stat_statements.control @@ -1,5 +1,5 @@ # pg_stat_statements extension comment = 'track planning and execution statistics of all SQL statements executed' -default_version = '1.11' +default_version = '1.12' module_pathname = '$libdir/pg_stat_statements' relocatable = true diff --git a/contrib/pg_stat_statements/sql/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql index 38d5505d0d..13b8ca2858 100644 --- a/contrib/pg_stat_statements/sql/oldextversions.sql +++ b/contrib/pg_stat_statements/sql/oldextversions.sql @@ -58,4 +58,9 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements; SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc); SELECT pg_stat_statements_reset() IS NOT NULL AS t; +-- New functions and views for pg_stat_statements in 1.12 +AlTER EXTENSION pg_stat_statements UPDATE TO '1.12'; +\d pg_stat_statements +SELECT count(*) > 0 AS has_data FROM pg_stat_statements; + DROP EXTENSION pg_stat_statements; diff --git a/contrib/pg_stat_statements/sql/parallel.sql b/contrib/pg_stat_statements/sql/parallel.sql new file mode 100644 index 0000000000..4ce1573d13 --- /dev/null +++ b/contrib/pg_stat_statements/sql/parallel.sql @@ -0,0 +1,26 @@ +-- +-- Tests for parallel statistics +-- + +SET pg_stat_statements.track_utility = FALSE; + +-- encourage use of parallel plans +SET parallel_setup_cost = 0; +SET parallel_tuple_cost = 0; +SET min_parallel_table_scan_size = 0; +SET max_parallel_workers_per_gather = 2; + +CREATE TABLE pgss_parallel_tab (a int); + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +SELECT count(*) FROM pgss_parallel_tab; + +SELECT query, + parallel_workers_to_launch > 0 AS has_workers_to_launch, + parallel_workers_launched > 0 AS has_workers_launched + FROM pg_stat_statements + WHERE query ~ 'SELECT count' + ORDER BY query COLLATE "C"; + +DROP TABLE pgss_parallel_tab; -- 2.45.2