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

Commit3357471

Browse files
committed
pg_stat_statements: Add counters for generic and custom plans
This patch adds two new counters to pg_stat_statements:- generic_plan_calls- custom_plan_callsThese counters track how many times a prepared statement was executedusing a generic or custom plan, respectively, providing a globalequivalent at query level, for top and non-top levels, ofpg_prepared_statements whose data is restricted to a single session.This commit builds upone125e36. The module is bumped to version1.13. PGSS_FILE_HEADER is bumped as well, something that the latestpatches touching the on-disk format of the PGSS file did not actuallybother with since 2022..Author: Sami Imseih <samimseih@gmail.com>Reviewed-by: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>Reviewed-by: Michael Paquier <michael@paquier.xyz>Reviewed-by: Nikolay Samokhvalov <nik@postgres.ai>Discussion:https://postgr.es/m/CAA5RZ0uFw8Y9GCFvafhC=OA8NnMqVZyzXPfv_EePOt+iv1T-qQ@mail.gmail.com
1 parente125e36 commit3357471

File tree

10 files changed

+536
-10
lines changed

10 files changed

+536
-10
lines changed

‎contrib/pg_stat_statements/Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,7 @@ OBJS = \
77

88
EXTENSION = pg_stat_statements
99
DATA = pg_stat_statements--1.4.sql\
10+
pg_stat_statements--1.12--1.13.sql\
1011
pg_stat_statements--1.11--1.12.sql pg_stat_statements--1.10--1.11.sql\
1112
pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql\
1213
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql\
@@ -20,7 +21,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
2021
REGRESS_OPTS = --temp-config$(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
2122
REGRESS = select dml cursors utility level_tracking planning\
2223
user_activity wal entry_timestamp privileges extended\
23-
parallel cleanup oldextversions squashing
24+
parallelplancachecleanup oldextversions squashing
2425
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
2526
# which typical installcheck users do not have (e.g. buildfarm clients).
2627
NO_INSTALLCHECK = 1

‎contrib/pg_stat_statements/expected/oldextversions.out‎

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -407,4 +407,71 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
407407
t
408408
(1 row)
409409

410+
-- New functions and views for pg_stat_statements in 1.13
411+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.13';
412+
\d pg_stat_statements
413+
View "public.pg_stat_statements"
414+
Column | Type | Collation | Nullable | Default
415+
----------------------------+--------------------------+-----------+----------+---------
416+
userid | oid | | |
417+
dbid | oid | | |
418+
toplevel | boolean | | |
419+
queryid | bigint | | |
420+
query | text | | |
421+
plans | bigint | | |
422+
total_plan_time | double precision | | |
423+
min_plan_time | double precision | | |
424+
max_plan_time | double precision | | |
425+
mean_plan_time | double precision | | |
426+
stddev_plan_time | double precision | | |
427+
calls | bigint | | |
428+
total_exec_time | double precision | | |
429+
min_exec_time | double precision | | |
430+
max_exec_time | double precision | | |
431+
mean_exec_time | double precision | | |
432+
stddev_exec_time | double precision | | |
433+
rows | bigint | | |
434+
shared_blks_hit | bigint | | |
435+
shared_blks_read | bigint | | |
436+
shared_blks_dirtied | bigint | | |
437+
shared_blks_written | bigint | | |
438+
local_blks_hit | bigint | | |
439+
local_blks_read | bigint | | |
440+
local_blks_dirtied | bigint | | |
441+
local_blks_written | bigint | | |
442+
temp_blks_read | bigint | | |
443+
temp_blks_written | bigint | | |
444+
shared_blk_read_time | double precision | | |
445+
shared_blk_write_time | double precision | | |
446+
local_blk_read_time | double precision | | |
447+
local_blk_write_time | double precision | | |
448+
temp_blk_read_time | double precision | | |
449+
temp_blk_write_time | double precision | | |
450+
wal_records | bigint | | |
451+
wal_fpi | bigint | | |
452+
wal_bytes | numeric | | |
453+
wal_buffers_full | bigint | | |
454+
jit_functions | bigint | | |
455+
jit_generation_time | double precision | | |
456+
jit_inlining_count | bigint | | |
457+
jit_inlining_time | double precision | | |
458+
jit_optimization_count | bigint | | |
459+
jit_optimization_time | double precision | | |
460+
jit_emission_count | bigint | | |
461+
jit_emission_time | double precision | | |
462+
jit_deform_count | bigint | | |
463+
jit_deform_time | double precision | | |
464+
parallel_workers_to_launch | bigint | | |
465+
parallel_workers_launched | bigint | | |
466+
generic_plan_calls | bigint | | |
467+
custom_plan_calls | bigint | | |
468+
stats_since | timestamp with time zone | | |
469+
minmax_stats_since | timestamp with time zone | | |
470+
471+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
472+
has_data
473+
----------
474+
t
475+
(1 row)
476+
410477
DROP EXTENSION pg_stat_statements;
Lines changed: 224 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,224 @@
1+
--
2+
-- Tests with plan cache
3+
--
4+
-- Setup
5+
CREATE OR REPLACE FUNCTION select_one_func(int) RETURNS VOID AS $$
6+
DECLARE
7+
ret INT;
8+
BEGIN
9+
SELECT $1 INTO ret;
10+
END;
11+
$$ LANGUAGE plpgsql;
12+
CREATE OR REPLACE PROCEDURE select_one_proc(int) AS $$
13+
DECLARE
14+
ret INT;
15+
BEGIN
16+
SELECT $1 INTO ret;
17+
END;
18+
$$ LANGUAGE plpgsql;
19+
-- Prepared statements
20+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
21+
t
22+
---
23+
t
24+
(1 row)
25+
26+
PREPARE p1 AS SELECT $1 AS a;
27+
SET plan_cache_mode TO force_generic_plan;
28+
EXECUTE p1(1);
29+
a
30+
---
31+
1
32+
(1 row)
33+
34+
SET plan_cache_mode TO force_custom_plan;
35+
EXECUTE p1(1);
36+
a
37+
---
38+
1
39+
(1 row)
40+
41+
SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements
42+
ORDER BY query COLLATE "C";
43+
calls | generic_plan_calls | custom_plan_calls | query
44+
-------+--------------------+-------------------+----------------------------------------------------
45+
2 | 1 | 1 | PREPARE p1 AS SELECT $1 AS a
46+
1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
47+
2 | 0 | 0 | SET plan_cache_mode TO $1
48+
(3 rows)
49+
50+
DEALLOCATE p1;
51+
-- Extended query protocol
52+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
53+
t
54+
---
55+
t
56+
(1 row)
57+
58+
SELECT $1 AS a \parse p1
59+
SET plan_cache_mode TO force_generic_plan;
60+
\bind_named p1 1
61+
;
62+
a
63+
---
64+
1
65+
(1 row)
66+
67+
SET plan_cache_mode TO force_custom_plan;
68+
\bind_named p1 1
69+
;
70+
a
71+
---
72+
1
73+
(1 row)
74+
75+
SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements
76+
ORDER BY query COLLATE "C";
77+
calls | generic_plan_calls | custom_plan_calls | query
78+
-------+--------------------+-------------------+----------------------------------------------------
79+
2 | 1 | 1 | SELECT $1 AS a
80+
1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
81+
2 | 0 | 0 | SET plan_cache_mode TO $1
82+
(3 rows)
83+
84+
\close_prepared p1
85+
-- EXPLAIN [ANALYZE] EXECUTE
86+
SET pg_stat_statements.track = 'all';
87+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
88+
t
89+
---
90+
t
91+
(1 row)
92+
93+
PREPARE p1 AS SELECT $1;
94+
SET plan_cache_mode TO force_generic_plan;
95+
EXPLAIN (COSTS OFF) EXECUTE p1(1);
96+
QUERY PLAN
97+
------------
98+
Result
99+
(1 row)
100+
101+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) EXECUTE p1(1);
102+
QUERY PLAN
103+
-----------------------------------
104+
Result (actual rows=1.00 loops=1)
105+
(1 row)
106+
107+
SET plan_cache_mode TO force_custom_plan;
108+
EXPLAIN (COSTS OFF) EXECUTE p1(1);
109+
QUERY PLAN
110+
------------
111+
Result
112+
(1 row)
113+
114+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) EXECUTE p1(1);
115+
QUERY PLAN
116+
-----------------------------------
117+
Result (actual rows=1.00 loops=1)
118+
(1 row)
119+
120+
SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements
121+
ORDER BY query COLLATE "C";
122+
calls | generic_plan_calls | custom_plan_calls | toplevel | query
123+
-------+--------------------+-------------------+----------+----------------------------------------------------------------------------------
124+
2 | 0 | 0 | t | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) EXECUTE p1(1)
125+
2 | 0 | 0 | t | EXPLAIN (COSTS OFF) EXECUTE p1(1)
126+
4 | 2 | 2 | f | PREPARE p1 AS SELECT $1
127+
1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
128+
2 | 0 | 0 | t | SET plan_cache_mode TO $1
129+
(5 rows)
130+
131+
RESET pg_stat_statements.track;
132+
DEALLOCATE p1;
133+
-- Functions/procedures
134+
SET pg_stat_statements.track = 'all';
135+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
136+
t
137+
---
138+
t
139+
(1 row)
140+
141+
SET plan_cache_mode TO force_generic_plan;
142+
SELECT select_one_func(1);
143+
select_one_func
144+
-----------------
145+
146+
(1 row)
147+
148+
CALL select_one_proc(1);
149+
SET plan_cache_mode TO force_custom_plan;
150+
SELECT select_one_func(1);
151+
select_one_func
152+
-----------------
153+
154+
(1 row)
155+
156+
CALL select_one_proc(1);
157+
SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements
158+
ORDER BY query COLLATE "C";
159+
calls | generic_plan_calls | custom_plan_calls | toplevel | query
160+
-------+--------------------+-------------------+----------+----------------------------------------------------
161+
2 | 0 | 0 | t | CALL select_one_proc($1)
162+
4 | 2 | 2 | f | SELECT $1
163+
1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
164+
2 | 0 | 0 | t | SELECT select_one_func($1)
165+
2 | 0 | 0 | t | SET plan_cache_mode TO $1
166+
(5 rows)
167+
168+
--
169+
-- EXPLAIN [ANALYZE] EXECUTE + functions/procedures
170+
--
171+
SET pg_stat_statements.track = 'all';
172+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
173+
t
174+
---
175+
t
176+
(1 row)
177+
178+
SET plan_cache_mode TO force_generic_plan;
179+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func(1);
180+
QUERY PLAN
181+
-----------------------------------
182+
Result (actual rows=1.00 loops=1)
183+
(1 row)
184+
185+
EXPLAIN (COSTS OFF) SELECT select_one_func(1);
186+
QUERY PLAN
187+
------------
188+
Result
189+
(1 row)
190+
191+
CALL select_one_proc(1);
192+
SET plan_cache_mode TO force_custom_plan;
193+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func(1);
194+
QUERY PLAN
195+
-----------------------------------
196+
Result (actual rows=1.00 loops=1)
197+
(1 row)
198+
199+
EXPLAIN (COSTS OFF) SELECT select_one_func(1);
200+
QUERY PLAN
201+
------------
202+
Result
203+
(1 row)
204+
205+
CALL select_one_proc(1);
206+
SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements
207+
ORDER BY query COLLATE "C", toplevel;
208+
calls | generic_plan_calls | custom_plan_calls | toplevel | query
209+
-------+--------------------+-------------------+----------+------------------------------------------------------------------------------------------------
210+
2 | 0 | 0 | t | CALL select_one_proc($1)
211+
2 | 0 | 0 | t | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func($1)
212+
4 | 0 | 0 | f | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func($1);
213+
2 | 0 | 0 | t | EXPLAIN (COSTS OFF) SELECT select_one_func($1)
214+
4 | 2 | 2 | f | SELECT $1
215+
1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
216+
2 | 0 | 0 | t | SET plan_cache_mode TO $1
217+
(7 rows)
218+
219+
RESET pg_stat_statements.track;
220+
--
221+
-- Cleanup
222+
--
223+
DROP FUNCTION select_one_func(int);
224+
DROP PROCEDURE select_one_proc(int);

‎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.12--1.13.sql',
2425
'pg_stat_statements--1.11--1.12.sql',
2526
'pg_stat_statements--1.10--1.11.sql',
2627
'pg_stat_statements--1.9--1.10.sql',
@@ -54,6 +55,7 @@ tests += {
5455
'privileges',
5556
'extended',
5657
'parallel',
58+
'plancache',
5759
'cleanup',
5860
'oldextversions',
5961
'squashing',

0 commit comments

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