LightDB EXPLAIN 浅析

EXPLAIN 语法

EXPLAIN 命令可以输出 SQL 语句的查询计划，具体语法如下：

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

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

其中：

• ANALYZE 选项为 TRUE 会实际执行SQL，并获得相应的查询计划，默认为FALSE。如果优化一些修改数据的 SQL 需要真实的执行但是不能影响现有的数据，可以放在一个事务中，分析完成后可以直接回滚。
• VERBOSE 选项为 TRUE 会显示查询计划的附加信息，默认为FALSE。附加信息包括查询计划中每个节点（后面具体解释节点的含义）输出的列（Output），表的SCHEMA 信息，函数的SCHEMA 信息，表达式中列所属表的别名，被触发的触发器名称等。
• COSTS 选项为 TRUE 会显示每个计划节点的预估启动代价（找到第一个符合条件的结果的代价）和总代价，以及预估行数和每行宽度，默认为TRUE。
• BUFFERS 选项为 TRUE 会显示关于缓存的使用信息，默认为 FALSE。该参数只能与 ANALYZE 参数一起使用。缓冲区信息包括共享块（常规表或者索引块）、本地块（临时表或者索引块）和临时块（排序或者哈希等涉及到的短期存在的数据块）的命中块数，更新块数，挤出块数。
• TIMING 选项为 TRUE 会显示每个计划节点的实际启动时间和总的执行时间，默认为 TRUE。该参数只能与 ANALYZE 参数一起使用。因为对于一些系统来说，获取系统时间需要比较大的代价，如果只需要准确的返回行数，而不需要准确的时间，可以把该参数关闭。
• SUMMARY 选项为 TRUE 会在查询计划后面输出总结信息，例如查询计划生成的时间和查询计划执行的时间。当ANALYZE 选项打开时，它默认为 TRUE。
• FORMAT 指定输出格式，默认为 TEXT。各个格式输出的内容都是相同的，其中XML | JSON | YAML 更有利于我们通过程序解析 SQL 语句的查询计划，为了更有利于阅读，我们下文的例子都是使用 TEXT 格式的输出结果。

例子表tmp_idx_lt01：

lightdb@lt_test=# \d+ tmp_idx_lt01;
                                Table "public.tmp_idx_lt01"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              |
 b      | text    |           |          |         | extended |              |
 c      | integer |           |          |         | plain    |              |
Indexes:
    "idx_lt01_a" btree (a)
    "idx_lt01_c" btree (c)
Access method: heap

代价估计信息

代价估计信息，简单例子：

EXPLAIN SELECT * FROM tmp_idx_lt01;

EXPLAIN 命令会在每个节点后面显示代价估计信息，包括cost、rows、width，这里将一一介绍。

在 LightDB 中，执行优化器会基于代价估计自动选择代价最小的查询计划树。而在 EXPLAIN 命令的输出结果中每个 cost 就是该执行节点的代价估计。它的格式是xxx…xxx，在… 之前的是预估的启动代价，即找到符合该节点条件的第一个结果预估所需要的代价，在…之后的是预估的总代价。而父节点的启动代价包含子节点的总代价。

那 cost 是怎么计算而来的呢？简单来说，是根据周期性收集到的统计信息按照一个代价估计模型计算而来的。其中会根据以下几个参数来作为代价估计的单位：

• seq_page_cost
• random_page_cost
• cpu_tuple_cost
• cpu_index_tuple_cost
• cpu_operator_cost
• parallel_setup_cost
• parallel_tuple_cost

其中，seq_page_cost 和 random_page_cost 可以使用 ALTER TABLESPACE 对每个 TABLESPACE 进行修改。

本例 tmp_idx_lt01 有63964个磁盘页面和10000000行。 开销被计算为 （页面读取数seq_page_cost）+（扫描的行数cpu_tuple_cost）。默认情况下，seq_page_cost是1.0，cpu_tuple_cost是0.01， 因此估计的开销是 (63964 * 1.0) + (10000000 * 0.01) = 163964。

代价估计信息中的其他两个，rows 代表预估的行数，width 代表预估的结果宽度，单位为字节。两者都是根据表的统计信息预估而来的。

真实执行信息（EXPLAIN ANALYZE）

真实执行信息，简单例子：

EXPLAIN ANALYZE SELECT * FROM tmp_idx_lt01;

当 EXPLAIN 命令中 ANALYZE 选项为on时，会在代价估计信息之后输出真实执行信息，包括：

• actual time 执行时间，格式为xxx…xxx，在… 之前的是该节点实际的启动时间，即找到符合该节点条件的第一个结果实际需要的时间，在…之后的是该节点实际的执行时间
• rows 指的是该节点实际的返回行数
• loops 指的是该节点实际的重启次数。如果一个计划节点在运行过程中，它的相关参数值（如绑定变量）发生了变化，就需要重新运行这个计划节点。

EXPLAIN 的输出结构

EXPLAIN 命令的输出可以看做是一个树形结构，我们称之为查询计划树，树的每个节点包括对应的节点类型，作用对象以及其他属性例如cost、rows、width 等。如果只显示节点类型，可以简化为如下结构：

Sort
└── Hash Join
    ├── Seq Scan
    └── Hash
        └── Bitmap Heap Scan
            └── Bitmap Index Scan

SQL 执行的一些特点：

• 按照查询计划树从底往上执行
• 每个节点执行返回一行记录给父节点（Bitmap Index Scan 除外）

节点类型有以下4种：

• 控制节点（Control Node)
• 扫描节点（ScanNode)
• 物化节点（Materialization Node)
• 连接节点（Join Node)

各种示例如下：











下例中规划器选择了使用一个哈希连接，在其中一个表的行被放入一个内存哈希表，在这之后其他表被扫描并且为每一行查找哈希表来寻找匹配。