PostgreSQL EXPLAIN

本文学习如何使用PostgreSQL EXPLAIN语句分析SQL执行计划。

1. PostgreSQL EXPLAIN语句

EXPLAIN语句返回给定SQL语句的PostgreSQL分析引擎生成的执行计划。展示语句中涉及的表如何被索引扫描或顺序扫描等，如果使用多个表，应用哪种join算法。
EXPLAIN 语句返回最重要的、有用的信息是返回第一行之前的起始成本和返回完整结果集的总成本。
下面是EXPLAIN 语法:

EXPLAIN [ ( option [, ...] ) ] sql_statement;

其中option 可以是下面内容:

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

boolean 表示选项开关。可以使用 TRUE, ON, 或1 启用选项, FALSE, OFF, 或0 禁用选项. 如果忽略boolean选项,缺省为启用。

• ANALYZE

ANALYZE 选项首先执行sql语句，然后返回实际运行时间统计信息。其中包括每个集合节点总耗时及实际返回行数。
ANALYZE 语句实际执行SQL语句并丢弃输出信息，因此如果想分析INSERT, UPDATE, 或 DELETE且不影响数据，应该在事务中包裹EXPLAIN ANALYZE:

BEGIN;
    EXPLAIN ANALYZE sql_statement;
ROLLBACK;

• VERBOSE

VERBOSE 参数可以显示执行集合更多信息，默认为false。

• COSTS

COSTS 选项包括每个计划节点的估计启动成本和总成本，以及查询计划中的估计行数和估计行的宽度。默认为TRUE。

• BUFFERS

该参数增加信息至buffer。BUFFERS 参数仅当ANALYZE 启用时才能使用。缺省为FALSE。

• TIMING

该参数包括实际启动时间及每个节点花费时间。TIMING 缺省为TRUE，仅当ANALYZE 启用时才能使用。

• SUMMARY

SUMMARY 参数在查询计划之后摘要信息（如总耗时）。当ANALYZE 启用时，缺省包括摘要信息。

• FORMAT

指定查询计划输出格式，如TEXT, XML, JSON, YAML。缺省为TEXT。

2. PostgreSQL EXPLAIN 示例

下面语句展示简单查询film表的执行计划:

EXPLAIN SELECT * FROM film;

输出如下:

下面示例展示查询特定film_id的执行计划：

EXPLAIN SELECT * FROM film WHERE film_id = 100;

输出如下:

因为film_id是索引，语句返回执行计划不一样。输出中计划使用索引扫描代替对film表的顺序扫描。
如果不需要显示成本，可以使用COSTS 选项:

EXPLAIN (COSTS FALSE) SELECT
    *
FROM
    film
WHERE
    film_id = 100;

下面示例显示使用聚集函数查询的执行计划：

EXPLAIN SELECT COUNT(*) FROM film;

输出如下:

下面示例返回多个表关联查询的执行计划：

EXPLAIN
SELECT
    f.film_id,
    title,
    name category_name
FROM
    film f
    INNER JOIN film_category fc 
        ON fc.film_id = f.film_id
    INNER JOIN category c 
        ON c.category_id = fc.category_id
ORDER BY
    title;

输出如下：

需要在输出中增加运行时间统计，需要使用ANALYZE选项:

EXPLAIN ANALYZE
    SELECT
        f.film_id,
        title,
        name category_name
    FROM
        film f
        INNER JOIN film_category fc 
            ON fc.film_id = f.film_id
        INNER JOIN category c 
            ON c.category_id = fc.category_id
    ORDER BY
        title;

输出如下：

3. 总结

本文学习了如何使用PostgreSQL EXPLAIN展示sql语句的执行计划。