Expose Parallelism counters planned/execute in pg_stat_statements
Hi all:
Here's a patch to add counters about planned/executed for parallelism
to pg_stat_statements, as a way to follow-up on if the queries are
planning/executing with parallelism, this can help to understand if you
have a good/bad configuration or if your hardware is enough
We decided to store information about the number of times is planned
and the number of times executed the parallelism by queries
Regards
Anthony
Attachments:
v1-0001-pg_stat_statements_counters-parallelism.patchtext/x-patch; charset=UTF-8; name=v1-0001-pg_stat_statements_counters-parallelism.patchDownload
From 99e02378b04d496698147c858c80477659fb34ad Mon Sep 17 00:00:00 2001
From: asotolongo <asotolongo@gmail.com>
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
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parallel_planed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times that the statement was planned to use parallelism
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parallel_executed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times that the statement was executed using parallelism
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
base-commit: a4f09ef22981011020a5b7404dc2801751b15f64
--
2.37.1
On Thu, Jul 21, 2022 at 06:26:58PM -0400, Anthony Sotolongo wrote:
Hi all:
Here's a patch to add counters about� planned/executed� for parallelism� to
pg_stat_statements, as a way to follow-up on if the queries are
planning/executing with parallelism, this can help to understand if you have
a good/bad configuration or if your hardware is enough
+1, I was missing something like this before, but it didn't occur to me to use
PSS:
/messages/by-id/20200310190142.GB29065@telsasoft.com
My hope is to answer to questions like these:
. is query (ever? usually?) using parallel paths?
. is query usefully using parallel paths?
. what queries are my max_parallel_workers(_per_process) being used for ?
. Are certain longrunning or frequently running queries which are using
parallel paths using all max_parallel_workers and precluding other queries
from using parallel query ? Or, are semi-short queries sometimes precluding
longrunning queries from using parallelism, when the long queries would
better benefit ?
This patch is storing the number of times the query was planned/executed using
parallelism, but not the number of workers. Would it make sense to instead
store the the *number* of workers launched/planned ? Otherwise, it might be
that a query is consistently planned to use a large number of workers, but then
runs with few. I'm referring to the fields shown in "explain/analyze". (Then,
the 2nd field should be renamed to "launched").
Workers Planned: 2
Workers Launched: 2
I don't think this is doing the right thing for prepared statements, like
PQprepare()/PQexecPrepared(), or SQL: PREPARE p AS SELECT; EXECUTE p;
Right now, the docs say that it shows the "number of times the statement was
planned to use parallelism", but the planning counter is incremented during
each execution. PSS already shows "calls" and "plans" separately. The
documentation doesn't mention prepared statements as a reason why they wouldn't
match, which seems like a deficiency.
This currently doesn't count parallel workers used by utility statements, such
as CREATE INDEX and VACUUM (see max_parallel_maintenance_workers). If that's
not easy to do, mention that in the docs as a limitation.
You should try to add some test to contrib/pg_stat_statements/sql, or add
parallelism test to an existing test. Note that the number of parallel workers
launched isn't stable, so you can't test that part..
You modified pgss_store() to take two booleans, but pass "NULL" instead of
"false". Curiously, of all the compilers in cirrusci, only MSVC complained ..
"planed" is actually spelled "planned", with two enns.
The patch has some leading/trailing whitespace (maybe shown by git log
depending on your configuration).
Please add this patch to the next commitfest.
https://commitfest.postgresql.org/39/
--
Justin
On 21-07-22 20:35, Justin Pryzby wrote:
On Thu, Jul 21, 2022 at 06:26:58PM -0400, Anthony Sotolongo wrote:
Hi all:
Here's a patch to add counters about planned/executed for parallelism to
pg_stat_statements, as a way to follow-up on if the queries are
planning/executing with parallelism, this can help to understand if you have
a good/bad configuration or if your hardware is enough+1, I was missing something like this before, but it didn't occur to me to use
PSS:
First of all, thanks for review the the patch and for the comments
/messages/by-id/20200310190142.GB29065@telsasoft.com
My hope is to answer to questions like these:
. is query (ever? usually?) using parallel paths?
. is query usefully using parallel paths?
. what queries are my max_parallel_workers(_per_process) being used for ?
. Are certain longrunning or frequently running queries which are using
parallel paths using all max_parallel_workers and precluding other queries
from using parallel query ? Or, are semi-short queries sometimes precluding
longrunning queries from using parallelism, when the long queries would
better benefit ?This patch is storing the number of times the query was planned/executed using
parallelism, but not the number of workers. Would it make sense to instead
store the the *number* of workers launched/planned ? Otherwise, it might be
that a query is consistently planned to use a large number of workers, but then
runs with few. I'm referring to the fields shown in "explain/analyze". (Then,
the 2nd field should be renamed to "launched").Workers Planned: 2
Workers Launched: 2
The main idea of the patch is to store the number of times the
statements were planned and executed in parallel, not the number of
workers used in the execution. Of course, what you mention can be
helpful, it will be given a review to see how it can be achieved
I don't think this is doing the right thing for prepared statements, like
PQprepare()/PQexecPrepared(), or SQL: PREPARE p AS SELECT; EXECUTE p;Right now, the docs say that it shows the "number of times the statement was
planned to use parallelism", but the planning counter is incremented during
each execution. PSS already shows "calls" and "plans" separately. The
documentation doesn't mention prepared statements as a reason why they wouldn't
match, which seems like a deficiency.
We will check it and see how fix it
This currently doesn't count parallel workers used by utility statements, such
as CREATE INDEX and VACUUM (see max_parallel_maintenance_workers). If that's
not easy to do, mention that in the docs as a limitation.
We will update the documentation with information related to this comment
You should try to add some test to contrib/pg_stat_statements/sql, or add
parallelism test to an existing test. Note that the number of parallel workers
launched isn't stable, so you can't test that part..You modified pgss_store() to take two booleans, but pass "NULL" instead of
"false". Curiously, of all the compilers in cirrusci, only MSVC complained .."planed" is actually spelled "planned", with two enns.
The patch has some leading/trailing whitespace (maybe shown by git log
depending on your configuration).
OK, we will fix it
Show quoted text
Please add this patch to the next commitfest.
https://commitfest.postgresql.org/39/
Hi,
On Fri, Jul 22, 2022 at 11:17:52AM -0400, Anthony Sotolongo wrote:
On 21-07-22 20:35, Justin Pryzby wrote:
On Thu, Jul 21, 2022 at 06:26:58PM -0400, Anthony Sotolongo wrote:
Hi all:
Here's a patch to add counters about� planned/executed� for parallelism� to
pg_stat_statements, as a way to follow-up on if the queries are
planning/executing with parallelism, this can help to understand if you have
a good/bad configuration or if your hardware is enough+1, I was missing something like this before, but it didn't occur to me to use
PSS:First of all, thanks for review the the patch and for the comments
/messages/by-id/20200310190142.GB29065@telsasoft.com
My hope is to answer to questions like these:
. is query (ever? usually?) using parallel paths?
. is query usefully using parallel paths?
. what queries are my max_parallel_workers(_per_process) being used for ?
. Are certain longrunning or frequently running queries which are using
parallel paths using all max_parallel_workers and precluding other queries
from using parallel query ? Or, are semi-short queries sometimes precluding
longrunning queries from using parallelism, when the long queries would
better benefit ?This patch is storing the number of times the query was planned/executed using
parallelism, but not the number of workers. Would it make sense to instead
store the the *number* of workers launched/planned ? Otherwise, it might be
that a query is consistently planned to use a large number of workers, but then
runs with few. I'm referring to the fields shown in "explain/analyze". (Then,
the 2nd field should be renamed to "launched").Workers Planned: 2
Workers Launched: 2The main idea of the patch is to store the number of times the statements
were planned and executed in parallel, not the number of workers used in the
execution. Of course, what you mention can be helpful, it will be given a
review to see how it can be achieved
I think you would need both information.
With your current patch it only says if the plan and execution had parallelism
enabled, but not if it could actually use with parallelism at all. It gives
some information, but it's not that useful on its own.
Also, a cumulated number of workers isn't really useful if you don't know what
fraction of the number of executions (or planning) they refer to.
That being said, I'm not sure how exactly the information about the number of
workers can be exposed, as there might be multiple gathers per plan and AKAIK
they can run at different part of the query execution. So in some case having
a total of 3 workers planned means that you ideally needed 3 workers available
at the same time, and in some other case it might be only 2 or even 1.
On 22-07-22 12:08, Julien Rouhaud wrote:
Hi,
On Fri, Jul 22, 2022 at 11:17:52AM -0400, Anthony Sotolongo wrote:
On 21-07-22 20:35, Justin Pryzby wrote:
On Thu, Jul 21, 2022 at 06:26:58PM -0400, Anthony Sotolongo wrote:
Hi all:
Here's a patch to add counters about planned/executed for parallelism to
pg_stat_statements, as a way to follow-up on if the queries are
planning/executing with parallelism, this can help to understand if you have
a good/bad configuration or if your hardware is enough+1, I was missing something like this before, but it didn't occur to me to use
PSS:First of all, thanks for review the the patch and for the comments
/messages/by-id/20200310190142.GB29065@telsasoft.com
My hope is to answer to questions like these:
. is query (ever? usually?) using parallel paths?
. is query usefully using parallel paths?
. what queries are my max_parallel_workers(_per_process) being used for ?
. Are certain longrunning or frequently running queries which are using
parallel paths using all max_parallel_workers and precluding other queries
from using parallel query ? Or, are semi-short queries sometimes precluding
longrunning queries from using parallelism, when the long queries would
better benefit ?This patch is storing the number of times the query was planned/executed using
parallelism, but not the number of workers. Would it make sense to instead
store the the *number* of workers launched/planned ? Otherwise, it might be
that a query is consistently planned to use a large number of workers, but then
runs with few. I'm referring to the fields shown in "explain/analyze". (Then,
the 2nd field should be renamed to "launched").Workers Planned: 2
Workers Launched: 2The main idea of the patch is to store the number of times the statements
were planned and executed in parallel, not the number of workers used in the
execution. Of course, what you mention can be helpful, it will be given a
review to see how it can be achievedI think you would need both information.
With your current patch it only says if the plan and execution had parallelism
enabled, but not if it could actually use with parallelism at all. It gives
some information, but it's not that useful on its own.
The original idea of this patch was identify when occurred some of the
circumstances under which it was impossible to execute that plan in
parallel at execution time
as mentioned on the documentation at [1]
For example:
Due to the different client configuration, the execution behavior can
be different , and can affect the performance:
As you can see in the above execution plan
From psql
-> Gather Merge (cost=779747.43..795700.62 rows=126492
width=40) (actual time=1109.515..1472.369 rows=267351 loops=1)
Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
Workers Planned: 6
Workers Launched: 6
-> Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=889.129..974.028 rows=38193 loops=7)
From jdbc (from dbeaver)
-> Gather Merge (cost=779747.43..795700.62 rows=126492
width=40) (actual time=4383.576..4385.856 rows=398 loops=1)
Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
Workers Planned: 6
Workers Launched: 0
-> Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=4383.574..4385.814 rows=398 loops=1)
This example was discussed also at this Thread [2]
With these PSS counters will be easily identified when some of these
causes are happening.
[1]
https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
Also, a cumulated number of workers isn't really useful if you don't know what
fraction of the number of executions (or planning) they refer to.
We will try to investigate how to do this.
Show quoted text
That being said, I'm not sure how exactly the information about the number of
workers can be exposed, as there might be multiple gathers per plan and AKAIK
they can run at different part of the query execution. So in some case having
a total of 3 workers planned means that you ideally needed 3 workers available
at the same time, and in some other case it might be only 2 or even 1.
Hi,
On Fri, Jul 22, 2022 at 02:11:35PM -0400, Anthony Sotolongo wrote:
On 22-07-22 12:08, Julien Rouhaud wrote:
With your current patch it only says if the plan and execution had parallelism
enabled, but not if it could actually use with parallelism at all. It gives
some information, but it's not that useful on its own.The original idea of this patch was� identify when occurred some of the
circumstances under which it was� impossible to execute that plan in
parallel at execution timeas mentioned on the documentation at [1]
For example:
Due to the different client configuration, the execution behavior can be�
different , and can affect the performance:As you can see in the above execution plan
From psql
�� �������� ->� Gather Merge� (cost=779747.43..795700.62 rows=126492
width=40) (actual time=1109.515..1472.369 rows=267351 loops=1)
�� �������������� Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
�� �������������� Workers Planned: 6
�� �������������� Workers Launched: 6
�� �������������� ->� Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=889.129..974.028 rows=38193 loops=7)From jdbc (from dbeaver)
�� �������� ->� Gather Merge� (cost=779747.43..795700.62 rows=126492
width=40) (actual time=4383.576..4385.856 rows=398 loops=1)
�� �������������� Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
�� �������������� Workers Planned: 6
�� �������������� Workers Launched: 0
�� �������������� ->� Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=4383.574..4385.814 rows=398 loops=1)This example was� discussed also at this Thread [2]
With these PSS counters will be easily identified when some of these causes
are happening.
I agree it can be hard to identify, but I don't think that your proposed
approach is enough to be able to do so. There's no guarantee of an exact 1:1
mapping between planning and execution, so you could totally see the same value
for parallel_planned and parallel_exec and still have the dbeaver behavior
happening.
If you want to be able to distinguish "plan was parallel but execution was
forced to disable it" from "plan wasn't parallel, so was the execution", you
need some specific counters for both situations.
On 23-07-22 00:03, Julien Rouhaud wrote:
Hi,
On Fri, Jul 22, 2022 at 02:11:35PM -0400, Anthony Sotolongo wrote:
On 22-07-22 12:08, Julien Rouhaud wrote:
With your current patch it only says if the plan and execution had parallelism
enabled, but not if it could actually use with parallelism at all. It gives
some information, but it's not that useful on its own.The original idea of this patch was identify when occurred some of the
circumstances under which it was impossible to execute that plan in
parallel at execution timeas mentioned on the documentation at [1]
For example:
Due to the different client configuration, the execution behavior can be
different , and can affect the performance:As you can see in the above execution plan
From psql
-> Gather Merge (cost=779747.43..795700.62 rows=126492
width=40) (actual time=1109.515..1472.369 rows=267351 loops=1)
Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
Workers Planned: 6
Workers Launched: 6
-> Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=889.129..974.028 rows=38193 loops=7)From jdbc (from dbeaver)
-> Gather Merge (cost=779747.43..795700.62 rows=126492
width=40) (actual time=4383.576..4385.856 rows=398 loops=1)
Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
Workers Planned: 6
Workers Launched: 0
-> Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=4383.574..4385.814 rows=398 loops=1)This example was discussed also at this Thread [2]
With these PSS counters will be easily identified when some of these causes
are happening.I agree it can be hard to identify, but I don't think that your proposed
approach is enough to be able to do so. There's no guarantee of an exact 1:1
mapping between planning and execution, so you could totally see the same value
for parallel_planned and parallel_exec and still have the dbeaver behavior
happening.If you want to be able to distinguish "plan was parallel but execution was
forced to disable it" from "plan wasn't parallel, so was the execution", you
need some specific counters for both situations.
Thanks for your time and feedback, yes we were missing some details, so
we need to rethink some points to continue
Hi,
El lun, 25 jul 2022 a la(s) 14:19, Anthony Sotolongo (asotolongo@gmail.com)
escribió:
On 23-07-22 00:03, Julien Rouhaud wrote:
Hi,
On Fri, Jul 22, 2022 at 02:11:35PM -0400, Anthony Sotolongo wrote:
On 22-07-22 12:08, Julien Rouhaud wrote:
With your current patch it only says if the plan and execution had
parallelism
enabled, but not if it could actually use with parallelism at all. It
gives
some information, but it's not that useful on its own.
The original idea of this patch was identify when occurred some of the
circumstances under which it was impossible to execute that plan in
parallel at execution timeas mentioned on the documentation at [1]
For example:
Due to the different client configuration, the execution behavior can be
different , and can affect the performance:As you can see in the above execution plan
From psql
-> Gather Merge (cost=779747.43..795700.62 rows=126492
width=40) (actual time=1109.515..1472.369 rows=267351 loops=1)
Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
Workers Planned: 6
Workers Launched: 6
-> Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=889.129..974.028 rows=38193 loops=7)From jdbc (from dbeaver)
-> Gather Merge (cost=779747.43..795700.62 rows=126492
width=40) (actual time=4383.576..4385.856 rows=398 loops=1)
Output: t.entity_node_id, t.configuration_id,
t.stream_def_id, t.run_type_id, t.state_datetime, (PARTIAL count(1))
Workers Planned: 6
Workers Launched: 0
-> Partial GroupAggregate (cost=778747.33..779327.09
rows=21082 width=40) (actual time=4383.574..4385.814 rows=398 loops=1)This example was discussed also at this Thread [2]
With these PSS counters will be easily identified when some of these
causes
are happening.
I agree it can be hard to identify, but I don't think that your proposed
approach is enough to be able to do so. There's no guarantee of anexact 1:1
mapping between planning and execution, so you could totally see the
same value
for parallel_planned and parallel_exec and still have the dbeaver
behavior
happening.
If you want to be able to distinguish "plan was parallel but execution
was
forced to disable it" from "plan wasn't parallel, so was the execution",
you
need some specific counters for both situations.
Thanks for your time and feedback, yes we were missing some details, so
we need to rethink some points to continue
We have rewritten the patch and added the necessary columns to have the
number of times a parallel query plan was not executed using parallelism.
We are investigating how to add more information related to the workers
created
by the Gather/GatherMerge nodes, but it is not a trivial task.
Regards.
Attachments:
v2-0001-Add-parallel-counters-to-pg_stat_statements.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Add-parallel-counters-to-pg_stat_statements.patchDownload
From e1462913894704f54fe1a8ab423157651b6358c0 Mon Sep 17 00:00:00 2001
From: Daymel Bonne <daymelbonne@gmail.com>
Date: Fri, 29 Jul 2022 13:19:07 +0000
Subject: [PATCH v2] 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 | 20 ++++++
src/backend/executor/execUtils.c | 1 +
src/backend/executor/nodeGather.c | 2 +
src/backend/executor/nodeGatherMerge.c | 2 +
src/include/nodes/execnodes.h | 1 +
13 files changed, 287 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 049da9fe6d..2915e06bcb 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..afb729d968 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
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>paral_planned_exec</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times that the statement was executed using parallelism
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>paral_planned_not_exec</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the statement was planned to use parallelism but
+ was not executed using parallelism. See the discussion of
+ <xref linkend="when-can-parallel-query-be-used"/>.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
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..9daefa45f8 100644
--- a/src/backend/executor/nodeGather.c
+++ b/src/backend/executor/nodeGather.c
@@ -186,6 +186,8 @@ ExecGather(PlanState *pstate)
LaunchParallelWorkers(pcxt);
/* We save # workers launched for the benefit of EXPLAIN */
node->nworkers_launched = pcxt->nworkers_launched;
+ 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..46e6cbd24d 100644
--- a/src/backend/executor/nodeGatherMerge.c
+++ b/src/backend/executor/nodeGatherMerge.c
@@ -227,6 +227,8 @@ ExecGatherMerge(PlanState *pstate)
LaunchParallelWorkers(pcxt);
/* We save # workers launched for the benefit of EXPLAIN */
node->nworkers_launched = pcxt->nworkers_launched;
+ 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: 59be1c942a47f6c8a4c47d242200fbbf4be59b88
--
2.30.2
Hi:
We have rewritten the patch and added the necessary columns to have the
number of times a parallel query plan was not executed using parallelism.
This version includes comments on the source code and documentation.
Regards
Attachments:
v3-0001-Add-parallel-counters-to-pg_stat_statements.patchapplication/octet-stream; name=v3-0001-Add-parallel-counters-to-pg_stat_statements.patchDownload
From 0d88ddd09f0f6f9df71f3d43af31205df58d63b5 Mon Sep 17 00:00:00 2001
From: Daymel Bonne <daymelbonne@gmail.com>
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
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>paral_planned_exec</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times that the statement was executed using parallelism.
+ See <xref linkend="pgstatstatements-paral-counter-limitations"/>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>paral_planned_not_exec</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the statement was planned to use parallelism but
+ was not executed using parallelism. See <xref linkend="pgstatstatements-paral-counter-limitations"/>
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -961,6 +981,20 @@ hit_percent |
</screen>
</sect2>
+ <sect2>
+ <title>Limitations</title>
+ <para>
+ <itemizedlist>
+ <listitem id="pgstatstatements-paral-counter-limitations" xreflabel="Parallel counters limitations">
+ <para>
+ <literal>paral_planned_exec</literal> and <literal>paral_planned_not_exec</literal> do not
+ include statistics from utility statements such as CREATE INDEX or VACUUM.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect2>
+
<sect2>
<title>Authors</title>
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
Hi,
On Fri, Jul 29, 2022 at 08:36:44AM -0500, Daymel Bonne Sol�s wrote:
We have rewritten the patch and added the necessary columns to have the
number of times a parallel query plan was not executed using parallelism.We are investigating how to add more information related to the workers
created
by the Gather/GatherMerge nodes, but it is not a trivial task.
As far as I can see the scope of the counters is now different. You said you
wanted to be able to identify when a parallel query plan cannot be executed
with parallelism, but what the fields are now showing is simply whether no
workers were launched at all. It could be because of the dbeaver behavior you
mentioned (the !es_use_parallel_mode case), but also if the executor did try to
launch parallel workers and didn't get any.
I don't think that's an improvement. With this patch if you see the
"paral_planned_not_exec" counter going up, you still don't know if this is
because of the !es_use_parallel_mode or if you simply have too many parallel
queries running at the same time, or both, and therefore can't do much with
that information. Both situations are different and in my opinion require
different (and specialized) counters to properly handle them.
Also, I don't think that paral_planned_exec and paral_planned_not_exec are good
column (and variable) names. Maybe something like
"parallel_exec_count" and "forced_non_parallel_exec_count" (assuming it's based
on a parallel plan and !es_use_parallel_mode).
On Tue, Aug 16, 2022 at 02:58:43PM +0800, Julien Rouhaud wrote:
I don't think that's an improvement. With this patch if you see the
"paral_planned_not_exec" counter going up, you still don't know if this is
because of the !es_use_parallel_mode or if you simply have too many parallel
queries running at the same time, or both, and therefore can't do much with
that information. Both situations are different and in my opinion require
different (and specialized) counters to properly handle them.
This thread has been idle for a few weeks now, and this feedback has
not been answered to. This CF entry has been marked as RwF.
--
Michael