From 0d88ddd09f0f6f9df71f3d43af31205df58d63b5 Mon Sep 17 00:00:00 2001 From: Daymel Bonne Date: Sat, 13 Aug 2022 20:23:04 +0000 Subject: [PATCH v3] Add parallel counters to pg_stat_statements --- contrib/pg_stat_statements/Makefile | 2 +- .../expected/oldextversions.out | 58 ++++++++++++++++ .../expected/pg_stat_statements.out | 48 +++++++++++++ .../pg_stat_statements--1.10--1.11.sql | 69 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 62 ++++++++++++++--- .../pg_stat_statements.control | 2 +- .../pg_stat_statements/sql/oldextversions.sql | 5 ++ .../sql/pg_stat_statements.sql | 25 +++++++ doc/src/sgml/pgstatstatements.sgml | 34 +++++++++ src/backend/executor/execUtils.c | 1 + src/backend/executor/nodeGather.c | 7 ++ src/backend/executor/nodeGatherMerge.c | 7 ++ src/include/nodes/execnodes.h | 1 + 13 files changed, 311 insertions(+), 10 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..410e7963e0 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) +-- Added parallel counters 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 | | | + paral_planned_exec | bigint | | | + paral_planned_not_exec | 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/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index ff0166fb9d..0e09d8bc57 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -1102,4 +1102,52 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; 2 (1 row) +-- 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=4; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +CREATE TABLE pgss_test as select generate_series(1, 1000) i; +SELECT COUNT(1) from pgss_test WHERE i % 2 = 0; + count +------- + 500 +(1 row) + +SELECT paral_planned_exec = 1, paral_planned_not_exec = 0 from pg_stat_statements WHERE query LIKE '%COUNT%'; + ?column? | ?column? +----------+---------- + t | t +(1 row) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SET max_parallel_workers = 0; +SELECT COUNT(1) from pgss_test WHERE i % 2 = 0; + count +------- + 500 +(1 row) + +SELECT paral_planned_exec = 0, paral_planned_not_exec = 1 from pg_stat_statements WHERE query LIKE '%COUNT%'; + ?column? | ?column? +----------+---------- + t | t +(1 row) + +DROP TABLE pgss_test; +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; 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..902f86657f --- /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 paral_planned_exec int8, + OUT paral_planned_not_exec 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 ba868f0de9..43fead4fdd 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,9 @@ 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 paral_planned_exec; /* # of times was executed using parallelism */ + int64 paral_planned_not_exec; /* # of times was planned to use parallelism + * but was not executed using parallelism */ } Counters; /* @@ -312,6 +316,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 +347,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 paral_executed, + bool paral_planned); static void pg_stat_statements_internal(FunctionCallInfo fcinfo, pgssVersion api_version, bool showtext); @@ -857,7 +864,9 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate) NULL, NULL, NULL, - jstate); + jstate, + false, + false); } /* @@ -942,7 +951,9 @@ pgss_planner(Query *parse, &bufusage, &walusage, NULL, - NULL); + NULL, + false, + false); } else { @@ -1061,7 +1072,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_mode_exec, + queryDesc->plannedstmt->parallelModeNeeded); } if (prev_ExecutorEnd) @@ -1179,7 +1192,9 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, &bufusage, &walusage, NULL, - NULL); + NULL, + false, + false); } else { @@ -1213,7 +1228,9 @@ pgss_store(const char *query, uint64 queryId, const BufferUsage *bufusage, const WalUsage *walusage, const struct JitInstrumentation *jitusage, - JumbleState *jstate) + JumbleState *jstate, + bool paral_executed, + bool paral_planned) { pgssHashKey key; pgssEntry *entry; @@ -1399,6 +1416,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 parallel counters */ + if (paral_executed) + { + e->counters.paral_planned_exec += 1; + } + if (paral_planned && !paral_executed) + { + e->counters.paral_planned_not_exec += 1; + } SpinLockRelease(&e->mutex); } @@ -1449,7 +1475,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 +1488,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 +1627,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 +1863,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.paral_planned_exec); + values[i++] = Int64GetDatumFast(tmp.paral_planned_not_exec); + } 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 +1876,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..381f5c396d 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; +-- Added parallel counters 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/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index a01f183727..856f8034ff 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -442,4 +442,29 @@ SELECT ( SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; +-- 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=4; + +SELECT pg_stat_statements_reset(); +CREATE TABLE pgss_test as select generate_series(1, 1000) i; + +SELECT COUNT(1) from pgss_test WHERE i % 2 = 0; +SELECT paral_planned_exec = 1, paral_planned_not_exec = 0 from pg_stat_statements WHERE query LIKE '%COUNT%'; + +SELECT pg_stat_statements_reset(); +SET max_parallel_workers = 0; + +SELECT COUNT(1) from pgss_test WHERE i % 2 = 0; +SELECT paral_planned_exec = 0, paral_planned_not_exec = 1 from pg_stat_statements WHERE query LIKE '%COUNT%'; + +DROP TABLE pgss_test; + +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; + DROP EXTENSION pg_stat_statements; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index ecf6cd6bf3..a658c813b7 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -473,6 +473,26 @@ Total time spent by the statement on emitting code, in milliseconds + + + + paral_planned_exec bigint + + + Number of times that the statement was executed using parallelism. + See + + + + + + paral_planned_not_exec bigint + + + Number of times the statement was planned to use parallelism but + was not executed using parallelism. See + + @@ -961,6 +981,20 @@ hit_percent | + + Limitations + + + + + paral_planned_exec and paral_planned_not_exec do not + include statistics from utility statements such as CREATE INDEX or VACUUM. + + + + + + Authors diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index 9df1f81ea8..b3d178e53f 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -155,6 +155,7 @@ CreateExecutorState(void) estate->es_sourceText = NULL; estate->es_use_parallel_mode = false; + estate->es_parallel_mode_exec = false; estate->es_jit_flags = 0; estate->es_jit = NULL; diff --git a/src/backend/executor/nodeGather.c b/src/backend/executor/nodeGather.c index 1283d5b737..5524783848 100644 --- a/src/backend/executor/nodeGather.c +++ b/src/backend/executor/nodeGather.c @@ -187,6 +187,13 @@ ExecGather(PlanState *pstate) /* We save # workers launched for the benefit of EXPLAIN */ node->nworkers_launched = pcxt->nworkers_launched; + /* + * We save that the plan used parallelism if at least one + * of the nodes was able to launch a worker. + */ + if(pcxt->nworkers_launched > 0) + estate->es_parallel_mode_exec = true; + /* Set up tuple queue readers to read the results. */ if (pcxt->nworkers_launched > 0) { diff --git a/src/backend/executor/nodeGatherMerge.c b/src/backend/executor/nodeGatherMerge.c index 3b1007f352..d71eb597c4 100644 --- a/src/backend/executor/nodeGatherMerge.c +++ b/src/backend/executor/nodeGatherMerge.c @@ -228,6 +228,13 @@ ExecGatherMerge(PlanState *pstate) /* We save # workers launched for the benefit of EXPLAIN */ node->nworkers_launched = pcxt->nworkers_launched; + /* + * We save that the plan used parallelism if at least one + * of the nodes was able to launch a worker. + */ + if(pcxt->nworkers_launched > 0) + estate->es_parallel_mode_exec = true; + /* Set up tuple queue readers to read the results. */ if (pcxt->nworkers_launched > 0) { diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 01b1727fc0..0d4f081051 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -676,6 +676,7 @@ typedef struct EState struct EPQState *es_epq_active; bool es_use_parallel_mode; /* can we use parallel workers? */ + bool es_parallel_mode_exec; /* was executed in parallel */ /* The per-query shared memory area to use for parallel execution. */ struct dsa_area *es_query_dsa; base-commit: adee0df127e088a47c58801fc5a412392297e1da -- 2.30.2