ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(9) - 执行计划数据结构

PostgreSQL DBA(9) - 执行计划数据结构

原创 PostgreSQL 作者:husthxd 时间:2018-09-29 11:07:21 0 删除 编辑

查询执行计划(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)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2374846/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1530
  • 访问量
    4044202