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

Commitd05b172

Browse files
committed
Add generic_plans and custom_plans fields into pg_prepared_statements.
There was no easy way to find how many times generic and custom planshave been executed for a prepared statement. This commit exposes thosenumbers of times in pg_prepared_statements view.Author: Atsushi Torikoshi, Kyotaro HoriguchiReviewed-by: Tatsuro Yamada, Masahiro Ikeda, Fujii MasaoDiscussion:https://postgr.es/m/CACZ0uYHZ4M=NZpofH6JuPHeX=__5xcDELF8hT8_2T+R55w4RQw@mail.gmail.com
1 parent044dc7b commitd05b172

File tree

8 files changed

+105
-16
lines changed

8 files changed

+105
-16
lines changed

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10841,6 +10841,24 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
1084110841
frontend/backend protocol
1084210842
</para></entry>
1084310843
</row>
10844+
10845+
<row>
10846+
<entry role="catalog_table_entry"><para role="column_definition">
10847+
<structfield>generic_plans</structfield> <type>int8</type>
10848+
</para>
10849+
<para>
10850+
Number of times generic plan was chosen
10851+
</para></entry>
10852+
</row>
10853+
10854+
<row>
10855+
<entry role="catalog_table_entry"><para role="column_definition">
10856+
<structfield>custom_plans</structfield> <type>int8</type>
10857+
</para>
10858+
<para>
10859+
Number of times custom plan was chosen
10860+
</para></entry>
10861+
</row>
1084410862
</tbody>
1084510863
</tgroup>
1084610864
</table>

‎src/backend/commands/prepare.c‎

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -694,7 +694,8 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
694694

695695
/*
696696
* This set returning function reads all the prepared statements and
697-
* returns a set of (name, statement, prepare_time, param_types, from_sql).
697+
* returns a set of (name, statement, prepare_time, param_types, from_sql,
698+
* generic_plans, custom_plans).
698699
*/
699700
Datum
700701
pg_prepared_statement(PG_FUNCTION_ARGS)
@@ -723,7 +724,7 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
723724
* build tupdesc for result tuples. This must match the definition of the
724725
* pg_prepared_statements view in system_views.sql
725726
*/
726-
tupdesc=CreateTemplateTupleDesc(5);
727+
tupdesc=CreateTemplateTupleDesc(7);
727728
TupleDescInitEntry(tupdesc, (AttrNumber)1,"name",
728729
TEXTOID,-1,0);
729730
TupleDescInitEntry(tupdesc, (AttrNumber)2,"statement",
@@ -734,6 +735,10 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
734735
REGTYPEARRAYOID,-1,0);
735736
TupleDescInitEntry(tupdesc, (AttrNumber)5,"from_sql",
736737
BOOLOID,-1,0);
738+
TupleDescInitEntry(tupdesc, (AttrNumber)6,"generic_plans",
739+
INT8OID,-1,0);
740+
TupleDescInitEntry(tupdesc, (AttrNumber)7,"custom_plans",
741+
INT8OID,-1,0);
737742

738743
/*
739744
* We put all the tuples into a tuplestore in one scan of the hashtable.
@@ -755,8 +760,8 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
755760
hash_seq_init(&hash_seq,prepared_queries);
756761
while ((prep_stmt=hash_seq_search(&hash_seq))!=NULL)
757762
{
758-
Datumvalues[5];
759-
boolnulls[5];
763+
Datumvalues[7];
764+
boolnulls[7];
760765

761766
MemSet(nulls,0,sizeof(nulls));
762767

@@ -766,6 +771,8 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
766771
values[3]=build_regtype_array(prep_stmt->plansource->param_types,
767772
prep_stmt->plansource->num_params);
768773
values[4]=BoolGetDatum(prep_stmt->from_sql);
774+
values[5]=Int64GetDatumFast(prep_stmt->plansource->num_generic_plans);
775+
values[6]=Int64GetDatumFast(prep_stmt->plansource->num_custom_plans);
769776

770777
tuplestore_putvalues(tupstore,tupdesc,values,nulls);
771778
}

‎src/backend/utils/cache/plancache.c‎

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -218,6 +218,7 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
218218
plansource->generation=0;
219219
plansource->generic_cost=-1;
220220
plansource->total_custom_cost=0;
221+
plansource->num_generic_plans=0;
221222
plansource->num_custom_plans=0;
222223

223224
MemoryContextSwitchTo(oldcxt);
@@ -285,6 +286,7 @@ CreateOneShotCachedPlan(RawStmt *raw_parse_tree,
285286
plansource->generation=0;
286287
plansource->generic_cost=-1;
287288
plansource->total_custom_cost=0;
289+
plansource->num_generic_plans=0;
288290
plansource->num_custom_plans=0;
289291

290292
returnplansource;
@@ -1213,12 +1215,14 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
12131215
{
12141216
/* Build a custom plan */
12151217
plan=BuildCachedPlan(plansource,qlist,boundParams,queryEnv);
1216-
/* Accumulate total costs of custom plans, but 'ware overflow */
1217-
if (plansource->num_custom_plans<INT_MAX)
1218-
{
1219-
plansource->total_custom_cost+=cached_plan_cost(plan, true);
1220-
plansource->num_custom_plans++;
1221-
}
1218+
/* Accumulate total costs of custom plans */
1219+
plansource->total_custom_cost+=cached_plan_cost(plan, true);
1220+
1221+
plansource->num_custom_plans++;
1222+
}
1223+
else
1224+
{
1225+
plansource->num_generic_plans++;
12221226
}
12231227

12241228
Assert(plan!=NULL);
@@ -1574,6 +1578,7 @@ CopyCachedPlan(CachedPlanSource *plansource)
15741578
/* We may as well copy any acquired cost knowledge */
15751579
newsource->generic_cost=plansource->generic_cost;
15761580
newsource->total_custom_cost=plansource->total_custom_cost;
1581+
newsource->num_generic_plans=plansource->num_generic_plans;
15771582
newsource->num_custom_plans=plansource->num_custom_plans;
15781583

15791584
MemoryContextSwitchTo(oldcxt);

‎src/include/catalog/pg_proc.dat‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7755,9 +7755,9 @@
77557755
{ oid => '2510', descr => 'get the prepared statements for this session',
77567756
proname => 'pg_prepared_statement', prorows => '1000', proretset => 't',
77577757
provolatile => 's', proparallel => 'r', prorettype => 'record',
7758-
proargtypes => '', proallargtypes => '{text,text,timestamptz,_regtype,bool}',
7759-
proargmodes => '{o,o,o,o,o}',
7760-
proargnames => '{name,statement,prepare_time,parameter_types,from_sql}',
7758+
proargtypes => '', proallargtypes => '{text,text,timestamptz,_regtype,bool,int8,int8}',
7759+
proargmodes => '{o,o,o,o,o,o,o}',
7760+
proargnames => '{name,statement,prepare_time,parameter_types,from_sql,generic_plans,custom_plans}',
77617761
prosrc => 'pg_prepared_statement' },
77627762
{ oid => '2511', descr => 'get the open cursors for this session',
77637763
proname => 'pg_cursor', prorows => '1000', proretset => 't',

‎src/include/utils/plancache.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -130,7 +130,8 @@ typedef struct CachedPlanSource
130130
/* State kept to help decide whether to use custom or generic plans: */
131131
doublegeneric_cost;/* cost of generic plan, or -1 if not known */
132132
doubletotal_custom_cost;/* total cost of custom plans so far */
133-
intnum_custom_plans;/* number of plans included in total */
133+
int64num_custom_plans;/* # of custom plans included in total */
134+
int64num_generic_plans;/* # of generic plans */
134135
}CachedPlanSource;
135136

136137
/*

‎src/test/regress/expected/plancache.out‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -284,7 +284,15 @@ insert into test_mode select 1 from generate_series(1,1000) union all select 2;
284284
create index on test_mode (a);
285285
analyze test_mode;
286286
prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
287+
select name, generic_plans, custom_plans from pg_prepared_statements
288+
where name = 'test_mode_pp';
289+
name | generic_plans | custom_plans
290+
--------------+---------------+--------------
291+
test_mode_pp | 0 | 0
292+
(1 row)
293+
287294
-- up to 5 executions, custom plan is used
295+
set plan_cache_mode to auto;
288296
explain (costs off) execute test_mode_pp(2);
289297
QUERY PLAN
290298
----------------------------------------------------------
@@ -293,6 +301,13 @@ explain (costs off) execute test_mode_pp(2);
293301
Index Cond: (a = 2)
294302
(3 rows)
295303

304+
select name, generic_plans, custom_plans from pg_prepared_statements
305+
where name = 'test_mode_pp';
306+
name | generic_plans | custom_plans
307+
--------------+---------------+--------------
308+
test_mode_pp | 0 | 1
309+
(1 row)
310+
296311
-- force generic plan
297312
set plan_cache_mode to force_generic_plan;
298313
explain (costs off) execute test_mode_pp(2);
@@ -303,6 +318,13 @@ explain (costs off) execute test_mode_pp(2);
303318
Filter: (a = $1)
304319
(3 rows)
305320

321+
select name, generic_plans, custom_plans from pg_prepared_statements
322+
where name = 'test_mode_pp';
323+
name | generic_plans | custom_plans
324+
--------------+---------------+--------------
325+
test_mode_pp | 1 | 1
326+
(1 row)
327+
306328
-- get to generic plan by 5 executions
307329
set plan_cache_mode to auto;
308330
execute test_mode_pp(1); -- 1x
@@ -329,12 +351,26 @@ execute test_mode_pp(1); -- 4x
329351
1000
330352
(1 row)
331353

354+
select name, generic_plans, custom_plans from pg_prepared_statements
355+
where name = 'test_mode_pp';
356+
name | generic_plans | custom_plans
357+
--------------+---------------+--------------
358+
test_mode_pp | 1 | 5
359+
(1 row)
360+
332361
execute test_mode_pp(1); -- 5x
333362
count
334363
-------
335364
1000
336365
(1 row)
337366

367+
select name, generic_plans, custom_plans from pg_prepared_statements
368+
where name = 'test_mode_pp';
369+
name | generic_plans | custom_plans
370+
--------------+---------------+--------------
371+
test_mode_pp | 2 | 5
372+
(1 row)
373+
338374
-- we should now get a really bad plan
339375
explain (costs off) execute test_mode_pp(2);
340376
QUERY PLAN
@@ -354,4 +390,11 @@ explain (costs off) execute test_mode_pp(2);
354390
Index Cond: (a = 2)
355391
(3 rows)
356392

393+
select name, generic_plans, custom_plans from pg_prepared_statements
394+
where name = 'test_mode_pp';
395+
name | generic_plans | custom_plans
396+
--------------+---------------+--------------
397+
test_mode_pp | 3 | 6
398+
(1 row)
399+
357400
drop table test_mode;

‎src/test/regress/expected/rules.out‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1428,8 +1428,10 @@ pg_prepared_statements| SELECT p.name,
14281428
p.statement,
14291429
p.prepare_time,
14301430
p.parameter_types,
1431-
p.from_sql
1432-
FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
1431+
p.from_sql,
1432+
p.generic_plans,
1433+
p.custom_plans
1434+
FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, generic_plans, custom_plans);
14331435
pg_prepared_xacts| SELECT p.transaction,
14341436
p.gid,
14351437
p.prepared,

‎src/test/regress/sql/plancache.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -186,27 +186,40 @@ create index on test_mode (a);
186186
analyze test_mode;
187187

188188
prepare test_mode_pp (int)asselectcount(*)from test_modewhere a= $1;
189+
select name, generic_plans, custom_plansfrom pg_prepared_statements
190+
where name='test_mode_pp';
189191

190192
-- up to 5 executions, custom plan is used
193+
set plan_cache_mode to auto;
191194
explain (costs off) execute test_mode_pp(2);
195+
select name, generic_plans, custom_plansfrom pg_prepared_statements
196+
where name='test_mode_pp';
192197

193198
-- force generic plan
194199
set plan_cache_mode to force_generic_plan;
195200
explain (costs off) execute test_mode_pp(2);
201+
select name, generic_plans, custom_plansfrom pg_prepared_statements
202+
where name='test_mode_pp';
196203

197204
-- get to generic plan by 5 executions
198205
set plan_cache_mode to auto;
199206
execute test_mode_pp(1);-- 1x
200207
execute test_mode_pp(1);-- 2x
201208
execute test_mode_pp(1);-- 3x
202209
execute test_mode_pp(1);-- 4x
210+
select name, generic_plans, custom_plansfrom pg_prepared_statements
211+
where name='test_mode_pp';
203212
execute test_mode_pp(1);-- 5x
213+
select name, generic_plans, custom_plansfrom pg_prepared_statements
214+
where name='test_mode_pp';
204215

205216
-- we should now get a really bad plan
206217
explain (costs off) execute test_mode_pp(2);
207218

208219
-- but we can force a custom plan
209220
set plan_cache_mode to force_custom_plan;
210221
explain (costs off) execute test_mode_pp(2);
222+
select name, generic_plans, custom_plansfrom pg_prepared_statements
223+
where name='test_mode_pp';
211224

212225
droptable test_mode;

0 commit comments

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