From 99e02378b04d496698147c858c80477659fb34ad Mon Sep 17 00:00:00 2001 From: asotolongo Date: Thu, 21 Jul 2022 17:56:59 -0400 Subject: [PATCH v1] Here's a patch to add the counters of parallelism planned/executed by statements to pg_stat_statements --- contrib/pg_stat_statements/Makefile | 2 +- .../expected/oldextversions.out | 58 ++++++++++++++++ .../pg_stat_statements--1.10--1.11.sql | 69 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 58 ++++++++++++++-- .../pg_stat_statements.control | 2 +- .../pg_stat_statements/sql/oldextversions.sql | 5 ++ doc/src/sgml/pgstatstatements.sgml | 18 +++++ 7 files changed, 203 insertions(+), 9 deletions(-) create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index edc40c8bbf..0afb9060fa 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -6,7 +6,7 @@ OBJS = \ pg_stat_statements.o EXTENSION = pg_stat_statements -DATA = pg_stat_statements--1.4.sql \ +DATA = pg_stat_statements--1.4.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 \ diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out index efb2049ecf..f847127347 100644 --- a/contrib/pg_stat_statements/expected/oldextversions.out +++ b/contrib/pg_stat_statements/expected/oldextversions.out @@ -250,4 +250,62 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements; t (1 row) +-- New functions and views for pg_stat_statements in 1.11 +AlTER EXTENSION pg_stat_statements UPDATE TO '1.11'; +\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 | | | + blk_read_time | double precision | | | + 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 | | | + parallel_planed | bigint | | | + parallel_executed | bigint | | | + +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/pg_stat_statements--1.10--1.11.sql b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql new file mode 100644 index 0000000000..19d2bc13ce --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql @@ -0,0 +1,69 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.11'" 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 blk_read_time float8, + OUT 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 parallel_planed int8, + OUT parallel_executed int8 +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_statements_1_11' +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 049da9fe6d..a593227980 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -118,7 +118,8 @@ typedef enum pgssVersion PGSS_V1_3, PGSS_V1_8, PGSS_V1_9, - PGSS_V1_10 + PGSS_V1_10, + PGSS_V1_11 } pgssVersion; typedef enum pgssStoreKind @@ -200,6 +201,8 @@ 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_planed; /* # of times parallel planned */ + int64 parallel_executed; /* # of times parallel executed */ } Counters; /* @@ -312,6 +315,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_3); 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); PG_FUNCTION_INFO_V1(pg_stat_statements_info); @@ -342,7 +346,9 @@ static void pgss_store(const char *query, uint64 queryId, const BufferUsage *bufusage, const WalUsage *walusage, const struct JitInstrumentation *jitusage, - JumbleState *jstate); + JumbleState *jstate, + bool parallelplaned, + bool parallelexec); static void pg_stat_statements_internal(FunctionCallInfo fcinfo, pgssVersion api_version, bool showtext); @@ -857,7 +863,9 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate) NULL, NULL, NULL, - jstate); + jstate, + NULL, + NULL); } /* @@ -942,6 +950,8 @@ pgss_planner(Query *parse, &bufusage, &walusage, NULL, + NULL, + NULL, NULL); } else @@ -1061,7 +1071,9 @@ pgss_ExecutorEnd(QueryDesc *queryDesc) &queryDesc->totaltime->bufusage, &queryDesc->totaltime->walusage, queryDesc->estate->es_jit ? &queryDesc->estate->es_jit->instr : NULL, - NULL); + NULL, + queryDesc->plannedstmt->parallelModeNeeded, + queryDesc->estate->es_use_parallel_mode); } if (prev_ExecutorEnd) @@ -1179,6 +1191,8 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, &bufusage, &walusage, NULL, + NULL, + NULL, NULL); } else @@ -1213,7 +1227,9 @@ pgss_store(const char *query, uint64 queryId, const BufferUsage *bufusage, const WalUsage *walusage, const struct JitInstrumentation *jitusage, - JumbleState *jstate) + JumbleState *jstate, + bool parallelplaned, + bool parallelexec) { pgssHashKey key; pgssEntry *entry; @@ -1399,7 +1415,15 @@ pgss_store(const char *query, uint64 queryId, e->counters.jit_emission_count++; e->counters.jit_emission_time += INSTR_TIME_GET_MILLISEC(jitusage->emission_counter); } - + /* inc the parallel counters*/ + if (parallelplaned) + { + e->counters.parallel_planed += 1; + } + if (parallelexec) + { + e->counters.parallel_executed += 1; + } SpinLockRelease(&e->mutex); } @@ -1449,7 +1473,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) #define PG_STAT_STATEMENTS_COLS_V1_8 32 #define PG_STAT_STATEMENTS_COLS_V1_9 33 #define PG_STAT_STATEMENTS_COLS_V1_10 43 -#define PG_STAT_STATEMENTS_COLS 43 /* maximum of above */ +#define PG_STAT_STATEMENTS_COLS_V1_11 45 +#define PG_STAT_STATEMENTS_COLS 45 /* maximum of above */ /* * Retrieve statement statistics. @@ -1461,6 +1486,15 @@ 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_11(PG_FUNCTION_ARGS) +{ + bool showtext = PG_GETARG_BOOL(0); + pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext); + + return (Datum) 0; +} + Datum pg_stat_statements_1_10(PG_FUNCTION_ARGS) { @@ -1591,6 +1625,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, if (api_version != PGSS_V1_10) elog(ERROR, "incorrect number of output arguments"); break; + case PG_STAT_STATEMENTS_COLS_V1_11: + if (api_version != PGSS_V1_11) + elog(ERROR, "incorrect number of output arguments"); + break; default: elog(ERROR, "incorrect number of output arguments"); } @@ -1823,6 +1861,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, values[i++] = Int64GetDatumFast(tmp.jit_emission_count); values[i++] = Float8GetDatumFast(tmp.jit_emission_time); } + if (api_version >= PGSS_V1_11) + { + values[i++] = Int64GetDatumFast(tmp.parallel_planed); + values[i++] = Int64GetDatumFast(tmp.parallel_executed); + } Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 : api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 : @@ -1831,6 +1874,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 : 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 : -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 0747e48138..8a76106ec6 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.10' +default_version = '1.11' 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 e2a83106d4..62a666d710 100644 --- a/contrib/pg_stat_statements/sql/oldextversions.sql +++ b/contrib/pg_stat_statements/sql/oldextversions.sql @@ -48,4 +48,9 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.10'; \d pg_stat_statements SELECT count(*) > 0 AS has_data FROM pg_stat_statements; +-- New functions and views for pg_stat_statements in 1.11 +AlTER EXTENSION pg_stat_statements UPDATE TO '1.11'; +\d pg_stat_statements +SELECT count(*) > 0 AS has_data FROM pg_stat_statements; + DROP EXTENSION pg_stat_statements; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index ecf6cd6bf3..34d73584fb 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -473,6 +473,24 @@ Total time spent by the statement on emitting code, in milliseconds + + + + parallel_planed bigint + + + Number of times that the statement was planned to use parallelism + + + + + + parallel_executed bigint + + + Number of times that the statement was executed using parallelism + + base-commit: a4f09ef22981011020a5b7404dc2801751b15f64 -- 2.37.1