Skip to content

Commit 3357471

Browse files
michaelpqmichaelpq
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_calls These counters track how many times a prepared statement was executed using a generic or custom plan, respectively, providing a global equivalent at query level, for top and non-top levels, of pg_prepared_statements whose data is restricted to a single session. This commit builds upon e125e36. The module is bumped to version 1.13. PGSS_FILE_HEADER is bumped as well, something that the latest patches touching the on-disk format of the PGSS file did not actually bother 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 parent e125e36 commit 3357471

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+
parallel plancache cleanup 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;

‎contrib/pg_stat_statements/expected/plancache.out

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)