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

来源：这里教程网时间：2026-03-14 19:29:42 作者：

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

通过日志分析得到的执行计划数据结构如下图所示: