PostgreSQL explain使用

1. 概述

PostgreSQL 为每个收到的查询产生一个执行计划，这个执行计划是一个非完全的二叉树。通过这个执行计划，DBA或者应用人员可以清晰的了解到某个SQL在数据库中的预估的执行情况以及实际的执行情况，也能根据执行计划中资源的消耗判断性能的瓶颈点，从而对该SQL进行有针对性的优化。下面通过 explain 语法来获取数据库中解析之后的执行计划，方便使用人员去阅读。

2. 语法

shxdb=# \h explain 
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

URL: https://www.postgresql.org/docs/12/sql-explain.html

option解释：

• ANALYZE：默认关闭(false)，如果以默认关闭的情况下使用 explain语法，那么获取到的结果便是数据库通过统计信息生成预计执行的执行计划，SQL并不会实际执行。
• VERBOSE：默认关闭。如果打开，会显示一些执行计划的附加信息，比如：Output(输出的列)，表的schema信息，函数的schema信息等。
• BUFFERS：默认关闭。打开会显示关于缓存的使用信息。缓冲区信息包括共享块（常规表或者索引块）、本地块（临时表或者索引块）和临时块（排序或者哈希等涉及到的短期存在的数据块）的命中块数，更新块数，挤出块数。
• COSTS：默认打开，显示每个计划节点的预估启动代价（找到第一个符合条件的结果的代价）和总代价，以及预估行数和每行宽度。
• SUMMARY：在查询计划后面输出总结信息，例如查询计划生成的时间和查询计划执行的时间
• FORMAT：指定输出格式，比如：TEXT | XML | JSON | YAML

3. 示例

通过对下面两张表进行关联查询，获取SQL的执行计划

shx=# \d student 
                       Table "shx.student"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 sno    | integer               |           |          | 
 sname  | character varying(30) |           |          | 
 ssex   | character varying(2)  |           |          | 

shx=# \d score 
                 Table "shx.score"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 sno    | integer |           |          | 
 cno    | integer |           |          | 
 degree | integer |           |          |

查询SQL

explain (analyze, buffers, verbose)
select * from student a, score b 
where a.sno = b.sno;

执行计划结果

QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=24.85..295.97 rows=6732 width=106) (actual time=0.029..0.033 rows=7 loops=1)
   Output: a.sno, a.sname, a.ssex, b.sno, b.cno, b.degree
   Hash Cond: (b.sno = a.sno)
   Buffers: shared hit=2
   ->  Seq Scan on shx.score b  (cost=0.00..30.40 rows=2040 width=12) (actual time=0.007..0.008 rows=7 loops=1)
         Output: b.sno, b.cno, b.degree
         Buffers: shared hit=1
   ->  Hash  (cost=16.60..16.60 rows=660 width=94) (actual time=0.009..0.010 rows=5 loops=1)
         Output: a.sno, a.sname, a.ssex
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=1
         ->  Seq Scan on shx.student a  (cost=0.00..16.60 rows=660 width=94) (actual time=0.003..0.004 rows=5 loops=1)
               Output: a.sno, a.sname, a.ssex
               Buffers: shared hit=1
 Planning Time: 0.118 ms
 Execution Time: 0.091 ms
(16 rows)