常量和绑定变量执行计划一致
方案概述
PG awr报告中SQL
select odr.order_no, odr.order_title,server_ip,schema_name, tenant_name, file_name , odr.order_result, odr.exec_num , odr.ctime
from audit.audit_order odr, audit.test_script_svn svn
where odr.order_no = svn.dir_name and odr.order_title=$1;PG中没有类似oracle 中dba_hist_plan, dba_hist_sqlbind. 无法知道SQL 的历史执行计划，
也不能查看SQL的绑定变量的值
EXPLAIN
select odr.order_no, odr.order_title,server_ip,schema_name, tenant_name, file_name , odr.order_result, odr.exec_num , odr.ctime
from audit.audit_order odr, audit.test_script_svn svn
where odr.order_no = svn.dir_name and odr.order_title=$1;
> ERROR: there is no parameter $1
LINE 5: and odr.order_title=$1;
> 查询时间: 0.006s这也为我们给 SQL调优带来不便。 此时我们需要查询、评估、甚至询问开发绑定变量的值才能获取SQL的执行计划
使用PREPARE语句生成通用执行计划
prepare sql1 as
select odr.order_no, odr.order_title,server_ip,schema_name, tenant_name, file_name , odr.order_result, odr.exec_num , odr.ctime
from audit.audit_order odr, audit.test_script_svn svn
where odr.order_no = svn.dir_name
and odr.order_title=$1;
> OK
> 查询时间: 0.024s带入测试值得到执行计划：
explain (analyse, buffers, costs ) execute sql1('供应商XXX-测XXX境-20230705-01-XXX组');
Hash Join (cost=141.31..356.46 rows=1 width=288) (actual time=7.071..7.130 rows=2 loops=1)
Hash Cond: ((svn.dir_name)::text = (odr.order_no)::text)
Buffers: shared read=251 dirtied=2
I/O Timings: read=2.435
-> Seq Scan on test_script_svn svn (cost=0.00..202.84 rows=4684 width=189) (actual time=0.085..3.832 rows=4674 loops=1)
Buffers: shared read=156 dirtied=1
I/O Timings: read=1.533
-> Hash (cost=141.30..141.30 rows=1 width=106) (actual time=2.239..2.239 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared read=95 dirtied=1
I/O Timings: read=0.902
-> Seq Scan on audit_order odr (cost=0.00..141.30 rows=1 width=106) (actual time=2.176..2.231 rows=1 loops=1)
Filter: ((order_title)::text = $1)
Rows Removed by Filter: 3701
Buffers: shared read=95 dirtied=1
I/O Timings: read=0.902
Planning Time: 0.027 ms
Execution Time: 7.251 ms有同学会问还是需要查询、评估、甚至询问开发绑定变量的值才能获取SQL的执行计划。
有没有直接获取执行计划的方案？–答案有的，使用null 代替绑定变量但是要设置
set plan_cache_mode = force_generic_plan; 强制使用通用执行计划
pms_test=# set plan_cache_mode = force_generic_plan;
SET
pms_test=# explain (analyse, buffers, costs ) execute sql1(null);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=141.31..356.46 rows=1 width=288) (actual time=2.104..2.109 rows=0 loops=1)
Hash Cond: ((svn.dir_name)::text = (odr.order_no)::text)
Buffers: shared hit=4 read=95
I/O Timings: read=0.841
-> Seq Scan on test_script_svn svn (cost=0.00..202.84 rows=4684 width=189) (actual time=0.046..0.046 rows=1 loops=1)
Buffers: shared read=1
I/O Timings: read=0.020
-> Hash (cost=141.30..141.30 rows=1 width=106) (actual time=1.952..1.953 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=1 read=94
I/O Timings: read=0.820
-> Seq Scan on audit_order odr (cost=0.00..141.30 rows=1 width=106) (actual time=1.951..1.951 rows=0 loops=1)
Filter: ((order_title)::text = $1)
Rows Removed by Filter: 3702
Buffers: shared hit=1 read=94
I/O Timings: read=0.820
Planning Time: 0.741 ms
Execution Time: 2.156 ms
(18 rows)
