理解PostgreSQL查询执行计划（一）-CSDN博客

本文链接：https://blog.csdn.net/weixin_37578680/article/details/148027524

初识EXPLAIN的困惑

每个新晋DBA接触PostgreSQL时学到的第一个命令就是"EXPLAIN"。但第一次尝试理解它的输出时，往往会遇到令人困惑的情况：

Sort  (cost=238.32..240.39 rows=826 width=961)
   Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
   ->  Hash Join  (cost=1.25..198.30 rows=826 width=961)
         Hash Cond: (p.pronamespace = n.oid)
         ->  Seq Scan on pg_proc p  (cost=0.00..182.94 rows=1009 width=692)
               Filter: pg_function_is_visible(oid)
         ->  Hash  (cost=1.14..1.14 rows=9 width=117)
               ->  Seq Scan on pg_namespace n  (cost=0.00..1.14 rows=9 width=117)
                     Filter: (nspname <> ALL ('{pg_catalog,information_schema}'::name[]))
(9 rows)

这个复杂的执行计划输出对初学者来说简直像天书一般。让我们从基础开始，逐步揭开EXPLAIN的神秘面纱。

PostgreSQL的智能之处

首先需要理解一个关键概念：PostgreSQL"知道"你的数据。它会维护详尽的元信息：

表行数统计
不同值的分布
最常见值
数据分布直方图

对于大型表，这些统计基于随机抽样，但总体而言，PostgreSQL对数据特性的把握相当准确。正是基于这些统计信息，查询规划器才能做出明智的执行计划决策。

从简单案例开始

让我们从一个最简单的查询开始：

EXPLAIN SELECT * FROM test WHERE i = 1;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan ON test  (cost=0.00..40.00 ROWS=12 width=4)
   FILTER: (i = 1)
(2 ROWS)

这个执行计划包含几个关键元素：

操作类型：这里是顺序扫描(Seq Scan)
成本估算：0.00…40.00
行数估算：12行
行宽估算：4字节

理解执行计划的结构

执行计划是一个树形结构，每个节点代表一个操作：

上层节点依赖下层节点的数据
每个节点显示其特有的信息
缩进表示操作间的层级关系

在我们的简单例子中，只有一个操作节点（顺序扫描）及其过滤条件。

成本估算的奥秘

成本估算值(cost)可能是最令人困惑的部分。需要明确几点：

成本不是时间单位，而是一个抽象的相对值

基于postgresql.conf中的成本参数计算：

seq_page_cost = 1.0      # 顺序页读取成本
random_page_cost = 4.0   # 随机页读取成本 
cpu_tuple_cost = 0.01    # 处理每行的CPU成本
cpu_index_tuple_cost = 0.005  # 索引扫描的CPU成本
cpu_operator_cost = 0.0025   # 操作符执行的CPU成本

成本值以启动成本..总成本的形式呈现。启动成本是该操作返回第一行前需要的工作量，总成本是返回所有行的总工作量。

索引使用的决策逻辑

考虑这个表和查询：

CREATE TABLE t (
    id serial PRIMARY KEY,
    some_column integer,
    something text
);
CREATE INDEX q ON t(some_column);

EXPLAIN SELECT * FROM t WHERE some_column = 123;

PostgreSQL会根据统计信息决定是否使用索引：

对于小表（如只有几行），顺序扫描通常更快
对于大表且匹配行很少的情况，索引扫描更优
对于大表且匹配行很多的情况，索引扫描反而更慢

执行计划的三种形态

让我们观察同一个查询在不同设置下的执行计划变化：

1.默认情况（使用索引扫描）：

EXPLAIN SELECT * FROM test WHERE id = 50;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 INDEX Scan USING test_pkey ON test  (cost=0.28..8.29 ROWS=1 width=36)

2.禁用索引扫描（使用位图扫描）：

SET enable_indexscan = false;
EXPLAIN SELECT * FROM test WHERE id = 50;

                               QUERY PLAN                               
------------------------------------------------------------------------
 Bitmap Heap Scan ON test  (cost=4.28..8.30 ROWS=1 width=13)
   ->  Bitmap INDEX Scan ON test_pkey  (cost=0.00..4.28 ROWS=1 width=0)

3. 禁用所有索引相关扫描（退化为顺序扫描）：

SET enable_bitmapscan = false;
EXPLAIN SELECT * FROM test WHERE id = 50;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan ON test  (cost=0.00..18.50 ROWS=1 width=13)

比较这三种情况的成本估算，可以清楚地看到为什么PostgreSQL默认选择索引扫描——它的总成本最低(8.29)。

实际执行分析

EXPLAIN ANALYZE会实际执行查询并提供真实数据：

EXPLAIN ANALYZE SELECT * FROM t LIMIT 100;

                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 LIMIT  (cost=0.00..9.33 ROWS=100 width=608) (actual TIME=0.008..0.152 ROWS=100 loops=1)
   ->  Seq Scan ON t  (cost=0.00..93333.86 ROWS=999986 width=608) (actual TIME=0.007..0.133 ROWS=100 loops=1)
 Total runtime: 0.181 ms