PG EXPLAIN (query planner)

Synopsis

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

Important

- 如果想看到实际的执行计划但又不想让change生效
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Notes

为了让PostgreSQL query planner正确的选择，需要确保所有表的统计信息都是最新的（pg_statistic），当然一般情况下autovacuum daemon会自动处理，但是有些时候 table因大批量的change而无法保持最新时，可能需要手工运行ANALYZE而不是等待autovacuum

In order to measure the run-time cost of each node in the execution plan, the current implementation of EXPLAIN ANALYZE adds profiling overhead to query execution.
 As a result, running EXPLAIN ANALYZE on a query can sometimes take significantly longer than executing the query normally. 
 The amount of overhead depends on the nature of the query, as well as the platform being used. 
 The worst case occurs for plan nodes that in themselves require very little time per execution, and on machines that have relatively slow operating system calls for obtaining the time of day.

Explain基础

查询计划的结构是一个计划结点的树。最底层的结点是扫描结点：它们从表中返回未经处理的行。 
不同的表访问模式有不同的扫描结点类型：顺序扫描、索引扫描、位图索引扫描。 也还有不是表的行来源，例如VALUES子句和FROM中返回集合的函数，它们有自己的结点类型。
如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作，那么就会在扫描结点之上有其它额外的结点来执行这些操作。 
并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的结点类型。
 EXPLAIN给计划树中每个结点都输出一行，显示基本的结点类型和计划器为该计划结点的执行所做的开销估计。
  第一行（最上层的结点）是对该计划的总执行开销的估计；计划器试图最小化的就是这个数字。