查询执行计划(Query Plan)对于DBA都已经很熟悉了，这一小节结合先前一些章节的内容，通过日志分析获得执行计划对应的数据结构(PlannedStmt)，通过分析可以知道执行计划是怎么来的，达到"知其然而知其所以然"的目的。
一、开启日志
编辑postgresql.conf配置文件,设置debug_print_plan/debug_pretty_print为on:
log_destination = 'csvlog'
log_directory = 'pg_log' #与postgresql.conf文件在同一级目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 2d
log_rotation_size = 100MB
#
debug_print_parse = off #是否打印parse树
debug_print_rewritten = off #是否打印parse rewrite树
debug_print_plan = on #是否打印plan树
debug_pretty_print = on #是否以优雅的方式显示
二、日志分析
开启日志,重启数据库后,在$PGDATA/pg_log目录下会生成相应的日志文件,执行SQL语句,可以找到对应的日志输出.
测试SQL语句:
select * from (
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
where t_dwxx.dwbh IN ('1001')
union all
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
where t_dwxx.dwbh IN ('1002')
) as ret
order by ret.grbh
limit 4;
使用Sublime Text工具打开日志,如下图所示（注意:planTree、rtable节点已折叠）:
计划树结构
commandType值为1,对应的是SELECT,SQL语句长度为455,relationOids的值为(o 16391 16394 16397 16391 16394 16397)，分别对应t_dwxx/t_grxx/t_jfxx三张表.
testdb=# select relname from pg_class where oid in (16391,16394,16397);
relname
---------
t_dwxx
t_grxx
t_jfxx
(3 rows)
rtable和planTree中的详细结构已在先前章节做过详细解释(相关链接详见参考资料),这里不再累述.
三、执行计划数据结构
SQL语句的执行计划:
testdb=# explain
testdb-# select * from (
testdb(# select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
testdb(# from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
testdb(# inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
testdb(# where t_dwxx.dwbh IN ('1001')
testdb(# union all
testdb(# select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
testdb(# from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
testdb(# inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
testdb(# where t_dwxx.dwbh IN ('1002')
testdb(# ) as ret
testdb-# order by ret.grbh
testdb-# limit 4;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=96.80..96.81 rows=4 width=360)
-> Sort (cost=96.80..96.83 rows=14 width=360)
Sort Key: t_grxx.grbh
-> Append (cost=16.15..96.59 rows=14 width=360)
-> Nested Loop (cost=16.15..48.19 rows=7 width=360)
-> Seq Scan on t_dwxx (cost=0.00..12.00 rows=1 width=256)
Filter: ((dwbh)::text = '1001'::text)
-> Hash Join (cost=16.15..36.12 rows=7 width=180)
Hash Cond: ((t_jfxx.grbh)::text = (t_grxx.grbh)::text)
-> Seq Scan on t_jfxx (cost=0.00..17.20 rows=720 width=84)
-> Hash (cost=16.12..16.12 rows=2 width=134)
-> Seq Scan on t_grxx (cost=0.00..16.12 rows=2 width=134)
Filter: ((dwbh)::text = '1001'::text)
-> Nested Loop (cost=16.15..48.19 rows=7 width=360)
-> Seq Scan on t_dwxx t_dwxx_1 (cost=0.00..12.00 rows=1 width=256)
Filter: ((dwbh)::text = '1002'::text)
-> Hash Join (cost=16.15..36.12 rows=7 width=180)
Hash Cond: ((t_jfxx_1.grbh)::text = (t_grxx_1.grbh)::text)
-> Seq Scan on t_jfxx t_jfxx_1 (cost=0.00..17.20 rows=720 width=84)
-> Hash (cost=16.12..16.12 rows=2 width=134)
-> Seq Scan on t_grxx t_grxx_1 (cost=0.00..16.12 rows=2 width=134)
Filter: ((dwbh)::text = '1002'::text)
(22 rows)
通过日志分析得到的执行计划数据结构如下图所示:
执行计划数据结构
四、参考资料
PostgreSQL 源码解读（22）- 查询语句#7（PlannedStmt结构详解-日志分析）
PostgreSQL 源码解读（23）- 查询语句#8（PlannedStmt与QUERY PLAN）
网友评论