digoal
2020-11-10
PostgreSQL , 绑定变量 , 硬解析
https://commitfest.postgresql.org/30/2713/
https://github.com/MasaoFujii/pg_cheat_funcs#record-pg_cached_plan_sourcestmt-text
NTT的开源代码提供的关于统计SQL硬解析、软解析次数的统计.
关于generic和custom plan的解说
《PostgreSQL 11 preview - 增加强制custom plan GUC开关(plancache_mode)，对付倾斜》
《PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜》
> =# select * from pg_prepared_statements;
> -[ RECORD 1 ]---+--------------------------------------------
> name | p1
> statement | prepare p1 as select a from t where a = $1;
> prepare_time | 2020-05-21 15:41:50.419578+09
> parameter_types | {integer}
> from_sql | t
> calls | 7
> custom_calls | 5
> plan_generation | 6
> generic_cost | 4.3100000000000005
> custom_cost | 9.31
>
> Perhaps plan_generation is not needed there.
>
合并到pg_stat_statements中.
Attached a poc patch which exposes total, min, max, mean and
stddev time for both generic and custom plans.
=# SELECT * FROM =# SELECT * FROM pg_stat_statements;
-[ RECORD 1
]-------+---------------------------------------------------------
userid | 10
dbid | 12878
queryid | 4617094108938234366
query | PREPARE pr1 AS SELECT * FROM pg_class WHERE
relname = $1
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 6
total_exec_time | 0.46600699999999995
min_exec_time | 0.029376000000000003
max_exec_time | 0.237413
mean_exec_time | 0.07766783333333334
stddev_exec_time | 0.07254973134206326
generic_calls | 1
total_generic_time | 0.045334000000000006
min_generic_time | 0.045334000000000006
max_generic_time | 0.045334000000000006
mean_generic_time | 0.045334000000000006
stddev_generic_time | 0
custom_calls | 5
total_custom_time | 0.42067299999999996
min_custom_time | 0.029376000000000003
max_custom_time | 0.237413
mean_custom_time | 0.0841346
stddev_custom_time | 0.07787966226583164
...
In this patch, exposing new columns is mandatory, but I think
it's better to make it optional by adding a GUC something
like 'pgss.track_general_custom_plans.
I also feel it makes the number of columns too many.
Just adding the total time may be sufficient.
Any thoughts?
Regards,
--
Atsushi Torikoshi
Attachment Content-Type Size
0003-POC-add-plan-type-to-pgss.patch
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
