Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcf54a2c

Browse files
committed
pg_stat_statements: Add columns to track parallel worker activity
The view pg_stat_statements gains two columns:- parallel_workers_to_launch, the number of parallel workers planned tobe launched.- parallel_workers_launched, the number of parallel workers actuallylaunched.The ratio of both columns offers hints that parallel workers are lackingon a per-statement basis, requiring some tuning, in coordination with"calls", the number of times a query is executed.As of now, these numbers are tracked within Gather and GatherMergenodes. They could be extended to utilities that make use of parallelworkers (parallel btree and brin, VACUUM).The module is bumped to 1.12.Author: Guillaume LelargeDiscussion:https://postgr.es/m/CAECtzeWtTGOK0UgKXdDGpfTVSa5bd_VbUt6K6xn8P7X+_dZqKw@mail.gmail.com
1 parentde3a2ea commitcf54a2c

File tree

10 files changed

+281
-11
lines changed

10 files changed

+281
-11
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@ OBJS = \
77

88
EXTENSION = pg_stat_statements
99
DATA = pg_stat_statements--1.4.sql\
10-
pg_stat_statements--1.10--1.11.sql\
10+
pg_stat_statements--1.11--1.12.sql pg_stat_statements--1.10--1.11.sql\
1111
pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql\
1212
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql\
1313
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql\
@@ -19,8 +19,8 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
1919

2020
REGRESS_OPTS = --temp-config$(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
2121
REGRESS = select dml cursors utility level_tracking planning\
22-
user_activity wal entry_timestamp privileges extendedcleanup\
23-
oldextversions
22+
user_activity wal entry_timestamp privileges extended\
23+
parallel cleanupoldextversions
2424
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
2525
# which typical installcheck users do not have (e.g. buildfarm clients).
2626
NO_INSTALLCHECK = 1

‎contrib/pg_stat_statements/expected/oldextversions.out

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -342,4 +342,68 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
342342
t
343343
(1 row)
344344

345+
-- New functions and views for pg_stat_statements in 1.12
346+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.12';
347+
\d pg_stat_statements
348+
View "public.pg_stat_statements"
349+
Column | Type | Collation | Nullable | Default
350+
----------------------------+--------------------------+-----------+----------+---------
351+
userid | oid | | |
352+
dbid | oid | | |
353+
toplevel | boolean | | |
354+
queryid | bigint | | |
355+
query | text | | |
356+
plans | bigint | | |
357+
total_plan_time | double precision | | |
358+
min_plan_time | double precision | | |
359+
max_plan_time | double precision | | |
360+
mean_plan_time | double precision | | |
361+
stddev_plan_time | double precision | | |
362+
calls | bigint | | |
363+
total_exec_time | double precision | | |
364+
min_exec_time | double precision | | |
365+
max_exec_time | double precision | | |
366+
mean_exec_time | double precision | | |
367+
stddev_exec_time | double precision | | |
368+
rows | bigint | | |
369+
shared_blks_hit | bigint | | |
370+
shared_blks_read | bigint | | |
371+
shared_blks_dirtied | bigint | | |
372+
shared_blks_written | bigint | | |
373+
local_blks_hit | bigint | | |
374+
local_blks_read | bigint | | |
375+
local_blks_dirtied | bigint | | |
376+
local_blks_written | bigint | | |
377+
temp_blks_read | bigint | | |
378+
temp_blks_written | bigint | | |
379+
shared_blk_read_time | double precision | | |
380+
shared_blk_write_time | double precision | | |
381+
local_blk_read_time | double precision | | |
382+
local_blk_write_time | double precision | | |
383+
temp_blk_read_time | double precision | | |
384+
temp_blk_write_time | double precision | | |
385+
wal_records | bigint | | |
386+
wal_fpi | bigint | | |
387+
wal_bytes | numeric | | |
388+
jit_functions | bigint | | |
389+
jit_generation_time | double precision | | |
390+
jit_inlining_count | bigint | | |
391+
jit_inlining_time | double precision | | |
392+
jit_optimization_count | bigint | | |
393+
jit_optimization_time | double precision | | |
394+
jit_emission_count | bigint | | |
395+
jit_emission_time | double precision | | |
396+
jit_deform_count | bigint | | |
397+
jit_deform_time | double precision | | |
398+
parallel_workers_to_launch | bigint | | |
399+
parallel_workers_launched | bigint | | |
400+
stats_since | timestamp with time zone | | |
401+
minmax_stats_since | timestamp with time zone | | |
402+
403+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
404+
has_data
405+
----------
406+
t
407+
(1 row)
408+
345409
DROP EXTENSION pg_stat_statements;
Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
--
2+
-- Tests for parallel statistics
3+
--
4+
SET pg_stat_statements.track_utility = FALSE;
5+
-- encourage use of parallel plans
6+
SET parallel_setup_cost = 0;
7+
SET parallel_tuple_cost = 0;
8+
SET min_parallel_table_scan_size = 0;
9+
SET max_parallel_workers_per_gather = 2;
10+
CREATE TABLE pgss_parallel_tab (a int);
11+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
12+
t
13+
---
14+
t
15+
(1 row)
16+
17+
SELECT count(*) FROM pgss_parallel_tab;
18+
count
19+
-------
20+
0
21+
(1 row)
22+
23+
SELECT query,
24+
parallel_workers_to_launch > 0 AS has_workers_to_launch,
25+
parallel_workers_launched > 0 AS has_workers_launched
26+
FROM pg_stat_statements
27+
WHERE query ~ 'SELECT count'
28+
ORDER BY query COLLATE "C";
29+
query | has_workers_to_launch | has_workers_launched
30+
----------------------------------------+-----------------------+----------------------
31+
SELECT count(*) FROM pgss_parallel_tab | t | t
32+
(1 row)
33+
34+
DROP TABLE pgss_parallel_tab;

‎contrib/pg_stat_statements/meson.build

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements
2121
install_data(
2222
'pg_stat_statements.control',
2323
'pg_stat_statements--1.4.sql',
24+
'pg_stat_statements--1.11--1.12.sql',
2425
'pg_stat_statements--1.10--1.11.sql',
2526
'pg_stat_statements--1.9--1.10.sql',
2627
'pg_stat_statements--1.8--1.9.sql',
@@ -52,6 +53,7 @@ tests += {
5253
'entry_timestamp',
5354
'privileges',
5455
'extended',
56+
'parallel',
5557
'cleanup',
5658
'oldextversions',
5759
],
Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.11--1.12.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_stat_statements UPDATE TO '1.12'" to load this file. \quit
5+
6+
/* First we have to remove them from the extension*/
7+
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
8+
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
9+
10+
/* Then we can drop them*/
11+
DROPVIEW pg_stat_statements;
12+
DROPFUNCTION pg_stat_statements(boolean);
13+
14+
/* Now redefine*/
15+
CREATEFUNCTIONpg_stat_statements(IN showtextboolean,
16+
OUT useridoid,
17+
OUT dbidoid,
18+
OUT toplevel bool,
19+
OUT queryidbigint,
20+
OUT querytext,
21+
OUT plans int8,
22+
OUT total_plan_time float8,
23+
OUT min_plan_time float8,
24+
OUT max_plan_time float8,
25+
OUT mean_plan_time float8,
26+
OUT stddev_plan_time float8,
27+
OUT calls int8,
28+
OUT total_exec_time float8,
29+
OUT min_exec_time float8,
30+
OUT max_exec_time float8,
31+
OUT mean_exec_time float8,
32+
OUT stddev_exec_time float8,
33+
OUT rows int8,
34+
OUT shared_blks_hit int8,
35+
OUT shared_blks_read int8,
36+
OUT shared_blks_dirtied int8,
37+
OUT shared_blks_written int8,
38+
OUT local_blks_hit int8,
39+
OUT local_blks_read int8,
40+
OUT local_blks_dirtied int8,
41+
OUT local_blks_written int8,
42+
OUT temp_blks_read int8,
43+
OUT temp_blks_written int8,
44+
OUT shared_blk_read_time float8,
45+
OUT shared_blk_write_time float8,
46+
OUT local_blk_read_time float8,
47+
OUT local_blk_write_time float8,
48+
OUT temp_blk_read_time float8,
49+
OUT temp_blk_write_time float8,
50+
OUT wal_records int8,
51+
OUT wal_fpi int8,
52+
OUT wal_bytesnumeric,
53+
OUT jit_functions int8,
54+
OUT jit_generation_time float8,
55+
OUT jit_inlining_count int8,
56+
OUT jit_inlining_time float8,
57+
OUT jit_optimization_count int8,
58+
OUT jit_optimization_time float8,
59+
OUT jit_emission_count int8,
60+
OUT jit_emission_time float8,
61+
OUT jit_deform_count int8,
62+
OUT jit_deform_time float8,
63+
OUT parallel_workers_to_launch int8,
64+
OUT parallel_workers_launched int8,
65+
OUT stats_sincetimestamp with time zone,
66+
OUT minmax_stats_sincetimestamp with time zone
67+
)
68+
RETURNS SETOF record
69+
AS'MODULE_PATHNAME','pg_stat_statements_1_12'
70+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
71+
72+
CREATEVIEWpg_stat_statementsAS
73+
SELECT*FROM pg_stat_statements(true);
74+
75+
GRANTSELECTON pg_stat_statements TO PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 53 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,7 @@ typedef enum pgssVersion
113113
PGSS_V1_9,
114114
PGSS_V1_10,
115115
PGSS_V1_11,
116+
PGSS_V1_12,
116117
}pgssVersion;
117118

118119
typedefenumpgssStoreKind
@@ -204,6 +205,10 @@ typedef struct Counters
204205
int64jit_emission_count;/* number of times emission time has been
205206
* > 0 */
206207
doublejit_emission_time;/* total time to emit jit code */
208+
int64parallel_workers_to_launch;/* # of parallel workers planned
209+
* to be launched */
210+
int64parallel_workers_launched;/* # of parallel workers actually
211+
* launched */
207212
}Counters;
208213

209214
/*
@@ -317,6 +322,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
317322
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
318323
PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
319324
PG_FUNCTION_INFO_V1(pg_stat_statements_1_11);
325+
PG_FUNCTION_INFO_V1(pg_stat_statements_1_12);
320326
PG_FUNCTION_INFO_V1(pg_stat_statements);
321327
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
322328

@@ -347,7 +353,9 @@ static void pgss_store(const char *query, uint64 queryId,
347353
constBufferUsage*bufusage,
348354
constWalUsage*walusage,
349355
conststructJitInstrumentation*jitusage,
350-
JumbleState*jstate);
356+
JumbleState*jstate,
357+
intparallel_workers_to_launch,
358+
intparallel_workers_launched);
351359
staticvoidpg_stat_statements_internal(FunctionCallInfofcinfo,
352360
pgssVersionapi_version,
353361
boolshowtext);
@@ -867,7 +875,9 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
867875
NULL,
868876
NULL,
869877
NULL,
870-
jstate);
878+
jstate,
879+
0,
880+
0);
871881
}
872882

873883
/*
@@ -945,7 +955,9 @@ pgss_planner(Query *parse,
945955
&bufusage,
946956
&walusage,
947957
NULL,
948-
NULL);
958+
NULL,
959+
0,
960+
0);
949961
}
950962
else
951963
{
@@ -1078,7 +1090,9 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
10781090
&queryDesc->totaltime->bufusage,
10791091
&queryDesc->totaltime->walusage,
10801092
queryDesc->estate->es_jit ?&queryDesc->estate->es_jit->instr :NULL,
1081-
NULL);
1093+
NULL,
1094+
queryDesc->estate->es_parallel_workers_to_launch,
1095+
queryDesc->estate->es_parallel_workers_launched);
10821096
}
10831097

10841098
if (prev_ExecutorEnd)
@@ -1209,7 +1223,9 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
12091223
&bufusage,
12101224
&walusage,
12111225
NULL,
1212-
NULL);
1226+
NULL,
1227+
0,
1228+
0);
12131229
}
12141230
else
12151231
{
@@ -1270,7 +1286,9 @@ pgss_store(const char *query, uint64 queryId,
12701286
constBufferUsage*bufusage,
12711287
constWalUsage*walusage,
12721288
conststructJitInstrumentation*jitusage,
1273-
JumbleState*jstate)
1289+
JumbleState*jstate,
1290+
intparallel_workers_to_launch,
1291+
intparallel_workers_launched)
12741292
{
12751293
pgssHashKeykey;
12761294
pgssEntry*entry;
@@ -1473,6 +1491,10 @@ pgss_store(const char *query, uint64 queryId,
14731491
entry->counters.jit_emission_time+=INSTR_TIME_GET_MILLISEC(jitusage->emission_counter);
14741492
}
14751493

1494+
/* parallel worker counters */
1495+
entry->counters.parallel_workers_to_launch+=parallel_workers_to_launch;
1496+
entry->counters.parallel_workers_launched+=parallel_workers_launched;
1497+
14761498
SpinLockRelease(&entry->mutex);
14771499
}
14781500

@@ -1539,7 +1561,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
15391561
#definePG_STAT_STATEMENTS_COLS_V1_933
15401562
#definePG_STAT_STATEMENTS_COLS_V1_1043
15411563
#definePG_STAT_STATEMENTS_COLS_V1_1149
1542-
#definePG_STAT_STATEMENTS_COLS49/* maximum of above */
1564+
#definePG_STAT_STATEMENTS_COLS_V1_1251
1565+
#definePG_STAT_STATEMENTS_COLS51/* maximum of above */
15431566

15441567
/*
15451568
* Retrieve statement statistics.
@@ -1551,6 +1574,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
15511574
* expected API version is identified by embedding it in the C name of the
15521575
* function. Unfortunately we weren't bright enough to do that for 1.1.
15531576
*/
1577+
Datum
1578+
pg_stat_statements_1_12(PG_FUNCTION_ARGS)
1579+
{
1580+
boolshowtext=PG_GETARG_BOOL(0);
1581+
1582+
pg_stat_statements_internal(fcinfo,PGSS_V1_12,showtext);
1583+
1584+
return (Datum)0;
1585+
}
1586+
15541587
Datum
15551588
pg_stat_statements_1_11(PG_FUNCTION_ARGS)
15561589
{
@@ -1695,6 +1728,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
16951728
if (api_version!=PGSS_V1_11)
16961729
elog(ERROR,"incorrect number of output arguments");
16971730
break;
1731+
casePG_STAT_STATEMENTS_COLS_V1_12:
1732+
if (api_version!=PGSS_V1_12)
1733+
elog(ERROR,"incorrect number of output arguments");
1734+
break;
16981735
default:
16991736
elog(ERROR,"incorrect number of output arguments");
17001737
}
@@ -1932,6 +1969,14 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
19321969
{
19331970
values[i++]=Int64GetDatumFast(tmp.jit_deform_count);
19341971
values[i++]=Float8GetDatumFast(tmp.jit_deform_time);
1972+
}
1973+
if (api_version >=PGSS_V1_12)
1974+
{
1975+
values[i++]=Int64GetDatumFast(tmp.parallel_workers_to_launch);
1976+
values[i++]=Int64GetDatumFast(tmp.parallel_workers_launched);
1977+
}
1978+
if (api_version >=PGSS_V1_11)
1979+
{
19351980
values[i++]=TimestampTzGetDatum(stats_since);
19361981
values[i++]=TimestampTzGetDatum(minmax_stats_since);
19371982
}
@@ -1944,6 +1989,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
19441989
api_version==PGSS_V1_9 ?PG_STAT_STATEMENTS_COLS_V1_9 :
19451990
api_version==PGSS_V1_10 ?PG_STAT_STATEMENTS_COLS_V1_10 :
19461991
api_version==PGSS_V1_11 ?PG_STAT_STATEMENTS_COLS_V1_11 :
1992+
api_version==PGSS_V1_12 ?PG_STAT_STATEMENTS_COLS_V1_12 :
19471993
-1/* fail if you forget to update this assert */ ));
19481994

19491995
tuplestore_putvalues(rsinfo->setResult,rsinfo->setDesc,values,nulls);
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_stat_statements extension
22
comment = 'track planning and execution statistics of all SQL statements executed'
3-
default_version = '1.11'
3+
default_version = '1.12'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

‎contrib/pg_stat_statements/sql/oldextversions.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,4 +58,9 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
5858
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
5959
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
6060

61+
-- New functions and views for pg_stat_statements in 1.12
62+
AlTER EXTENSION pg_stat_statementsUPDATE TO'1.12';
63+
\d pg_stat_statements
64+
SELECTcount(*)>0AS has_dataFROM pg_stat_statements;
65+
6166
DROP EXTENSION pg_stat_statements;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp