PostgreSQL 教程: EXPLAIN 物化节点

八月 27, 2025

摘要:在本教程中,你将了解在执行计划中可能会看到的各种物化 “节点” 或操作。

目录

Sort

排序的含义似乎很容易理解:排序操作接收给定的记录,并按某种方式对这些记录排序后返回。

示例:

explain analyze select * from pg_class order by relname;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Sort  (cost=22.88..23.61 rows=292 width=203) (actual time=0.230..0.253 rows=295 loops=1)
   Sort Key: relname
   Sort Method: quicksort  Memory: 103kB
   ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=203) (actual time=0.007..0.048 rows=295 loops=1)
 Total runtime: 0.326 ms
(5 rows)

尽管排序操作看似简单,但其内部包含着精细的逻辑。首先,若排序所用内存超过work_mem的限制,排序方式会切换为基于磁盘的排序:

explain analyze select random() as x from generate_series(1,14000) i order by x;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=62.33..64.83 rows=1000 width=0) (actual time=16.713..18.090 rows=14000 loops=1)
   Sort Key: (random())
   Sort Method: quicksort  Memory: 998kB
   ->  Function Scan on generate_series i  (cost=0.00..12.50 rows=1000 width=0) (actual time=2.036..4.533 rows=14000 loops=1)
 Total runtime: 18.942 ms
(5 rows)
 
explain analyze select random() as x from generate_series(1,15000) i order by x;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=62.33..64.83 rows=1000 width=0) (actual time=27.052..28.780 rows=15000 loops=1)
   Sort Key: (random())
   Sort Method: external merge  Disk: 264kB
   ->  Function Scan on generate_series i  (cost=0.00..12.50 rows=1000 width=0) (actual time=2.171..4.894 rows=15000 loops=1)
 Total runtime: 29.767 ms
(5 rows)

请注意上述示例中排序方式(Sort Method)的变化。

为应对这类内存不足的情况,PostgreSQL 会使用存储在$PGDATA/base/pgsql_tmp/目录下的临时文件。当然,这些临时文件在不再需要时会被立即删除。

排序操作还有一个额外特性:若它被 LIMIT 操作调用,排序方式会发生改变,如下例所示:

explain analyze select * from pg_class order by relfilenode limit 5;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=15.77..15.78 rows=5 width=203) (actual time=0.119..0.120 rows=5 loops=1)
   ->  Sort  (cost=15.77..16.50 rows=292 width=203) (actual time=0.118..0.118 rows=5 loops=1)
         Sort Key: relfilenode
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=203) (actual time=0.005..0.047 rows=295 loops=1)
 Total runtime: 0.161 ms
(6 rows)

通常情况下,要对给定数据集排序,需要完整处理整个数据集。但 PostgreSQL 知道,若只需返回少量行,无需对整个数据集排序,只需获取前 N 个目标值即可满足需求。

用计算复杂度 O 表示法来看,常规排序的时间复杂度为O (m * log (m)),而 Top-N 排序(即上述针对 LIMIT 优化的排序)的时间复杂度为O (m * log (n)),其中 m 是表中的总行数,n 是需返回的行数。最重要的是,这种排序方式占用的内存也少得多,毕竟无需构建完整的排序数据集,只需处理少量行,因此使用速度较慢的磁盘存储临时文件的可能性也更低。

HashAggregate

基本上,当查询中使用GROUP BY子句以及sum()avg()min()max()等聚合函数时,就会用到哈希聚合操作。

示例:

explain analyze select relkind, count(*) from pg_class
  group by relkind;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=12.38..12.42 rows=4 width=1) (actual time=0.223..0.224 rows=5 loops=1)
   ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=1) (actual time=0.008..0.053 rows=295 loops=1)
 Total runtime: 0.273 ms
(3 rows)

哈希聚合的工作原理大致如下:对于接收到的每一行数据,先确定GROUP BY子句指定的键(本例中为relkind),然后在哈希表(关联数组、字典)中,将该行数据放入由该键指定的桶中。

当所有行都处理完成后,扫描哈希表,为每个键值返回一行结果;若有需要,还会执行相应的计算(如求和、求最小值、求平均值等)。

需要明确的是,哈希聚合必须扫描完所有行后,才能返回第一行结果。

理解这一点后,你可能会想到一个潜在问题:若数据量达到数百万行,该如何处理?此时哈希表会因过大而无法完全放入内存。这就又要用到work_mem了,若生成的哈希表超出work_mem限制,部分数据会溢出到磁盘(同样存储在$PGDATA/base/pgsql_tmp目录下)。

这意味着,若执行计划中同时包含哈希聚合(HashAggregate)和排序(Sort)操作,内存占用最多可能达到2 * work_mem。要得到这样的执行计划并不复杂,示例如下:

explain analyze select relkind, count(*) from pg_class
  group by relkind order by relkind;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Sort  (cost=12.46..12.47 rows=4 width=1) (actual time=0.260..0.261 rows=5 loops=1)
   Sort Key: relkind
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=12.38..12.42 rows=4 width=1) (actual time=0.221..0.222 rows=5 loops=1)
         ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=1) (actual time=0.006..0.044 rows=295 loops=1)
 Total runtime: 0.312 ms
(6 rows)

实际上,单个查询可能会多次使用work_mem,因为work_mem是单个操作的内存限制。因此,若查询中使用了 1000 个哈希聚合、排序(及其他需使用work_mem的操作),总内存占用可能会非常高。

Materialize

PostgreSQL 的命令行工具 psql 有许多内部命令,\dTS就是其中之一,用于列出所有系统数据类型。该命令在内部会执行如下查询:

SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;

该查询的执行计划如下:

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2783.00..2783.16 rows=65 width=68) (actual time=3.883..3.888 rows=87 loops=1)
   Sort Key: n.nspname, (format_type(t.oid, NULL::integer))
   Sort Method: quicksort  Memory: 39kB
   ->  Nested Loop Left Join  (cost=16.32..2781.04 rows=65 width=68) (actual time=0.601..3.657 rows=87 loops=1)
         Join Filter: (n.oid = t.typnamespace)
         Rows Removed by Join Filter: 435
         ->  Hash Anti Join  (cost=16.32..2757.70 rows=65 width=8) (actual time=0.264..0.981 rows=87 loops=1)
               Hash Cond: ((t.typelem = el.oid) AND (t.oid = el.typarray))
               ->  Seq Scan on pg_type t  (cost=0.00..2740.26 rows=81 width=12) (actual time=0.012..0.662 rows=157 loops=1)
                     Filter: (pg_type_is_visible(oid) AND ((typrelid = 0::oid) OR (SubPlan 1)))
                     Rows Removed by Filter: 185
                     SubPlan 1
                       ->  Index Scan using pg_class_oid_index on pg_class c  (cost=0.15..8.17 rows=1 width=1) (actual time=0.002..0.002 rows=1 loops=98)
                             Index Cond: (oid = t.typrelid)
               ->  Hash  (cost=11.33..11.33 rows=333 width=8) (actual time=0.241..0.241 rows=342 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 14kB
                     ->  Seq Scan on pg_type el  (cost=0.00..11.33 rows=333 width=8) (actual time=0.002..0.130 rows=342 loops=1)
         ->  Materialize  (cost=0.00..1.09 rows=6 width=68) (actual time=0.000..0.001 rows=6 loops=87)
               ->  Seq Scan on pg_namespace n  (cost=0.00..1.06 rows=6 width=68) (actual time=0.002..0.003 rows=6 loops=1)
 Total runtime: 3.959 ms

其中,Materialize 操作由嵌套循环左连接(Nested Loop Left Join)调用。我们知道,嵌套循环会导致指定操作被多次执行,本例中执行了 87 次。

连接的右侧是对pg_namespace表的顺序扫描(Seq Scan)。理论上,PostgreSQL 应该要对pg_namespace表执行 87 次顺序扫描。若单次顺序扫描该表耗时 0.003 毫秒,总耗时预计约为 0.25 毫秒。

但 PostgreSQL 的优化器意识到,只需扫描一次表,并在内存中构建所有行的结果,后续就无需再次扫描表、检查可见性信息或解析数据页,直接从内存中获取数据即可,这样成本更低。

得益于物化操作,“扫描表一次 + 内存中构建数据行 + 扫描内存数据 87 次” 的总耗时仅为 0.087 毫秒。

在下面的示例中,合并连接操作只执行了一次扫描,为何也会使用物化操作呢?

explain analyze select * from
  (select oid, * from pg_class order by oid) as c
  join
  (select * from pg_attribute a order by attrelid) as a
  on c.oid = a.attrelid;
                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=23.16..268.01 rows=2273 width=410) (actual time=0.658..3.779 rows=2274 loops=1)
   Merge Cond: (pg_class.oid = a.attrelid)
   ->  Sort  (cost=22.88..23.61 rows=292 width=207) (actual time=0.624..0.655 rows=293 loops=1)
         Sort Key: pg_class.oid
         Sort Method: quicksort  Memory: 102kB
         ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=207) (actual time=0.011..0.211 rows=293 loops=1)
   ->  Materialize  (cost=0.28..212.34 rows=2273 width=203) (actual time=0.028..1.264 rows=2274 loops=1)
         ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.28..183.92 rows=2273 width=203) (actual time=0.015..0.752 rows=2274 loops=1)
 Total runtime: 4.009 ms
(9 rows)

确实,合并连接只执行了一次扫描。但问题在于,合并连接的数据源需满足多个条件:有些条件很明显(数据必须已排序),而有些条件则更偏向技术层面,不太容易察觉(数据必须支持前后滚动访问)。

正因为这些不易察觉的条件,PostgreSQL 有时需要将数据源(本例中为索引扫描)的数据进行物化,以确保在使用这些数据时,能具备所有必需的特性。

简而言之,物化操作从底层操作获取数据,并将其存储在内存中(或部分存储在内存中),这样既能更快地使用这些数据,也能为其赋予底层操作所不具备的额外特性。

Unique

从名称就能看出该操作的作用:移除重复数据。

例如,执行下面查询时可能会用到去重操作:

select distinct field from table

但在较新版本的 PostgreSQL 中,这类查询通常会通过哈希聚合(HashAggregate)实现。

去重操作有一个特点:它要求数据必须已排序。这并非因为它需要数据按特定顺序排列,而是为了让所有具有相同值的行 “聚集在一起”,便于去重。

这一特性使去重操作在适用场景下极具优势,因为它几乎不占用内存。其原理非常简单:只需检查前一行的值与当前行是否相同,若相同则丢弃当前行,仅此而已。

因此,我们可以通过预先排序数据,强制查询使用去重操作:

explain select distinct relkind from
  (select relkind from pg_class order by relkind) as x;
                              QUERY PLAN
-----------------------------------------------------------------------
 Unique  (cost=22.88..27.26 rows=4 width=1)
   ->  Sort  (cost=22.88..23.61 rows=292 width=1)
         Sort Key: pg_class.relkind
         ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=1)
(4 rows)

Result

Result 操作主要出现在非常简单的测试查询中。当查询返回某些常量值时,就会使用该操作:

explain select 1, 2;
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
(1 row)

除测试查询外,在一些插入数据但避免重复的查询中,有时也会看到 Result 操作:

explain insert into t (i)
  select 1
  where not exists (select * from t where i = 1);
                             QUERY PLAN
---------------------------------------------------------------------
 Insert on t  (cost=3.33..3.35 rows=1 width=4)
   ->  Result  (cost=3.33..3.34 rows=1 width=0)
         One-Time Filter: (NOT $0)
         InitPlan 1 (returns $0)
           ->  Seq Scan on t t_1  (cost=0.00..40.00 rows=12 width=0)
                 Filter: (i = 1)
(6 rows)

GroupAggregate

分组聚合与前文中介绍的哈希聚合操作类似。

两者的区别在于:要使用分组聚合,必须先按照GROUP BY子句中指定的所有列对数据进行排序。

和去重(Unique)操作一样,分组聚合占用的内存极少,但会强制要求数据先进行排序。

示例:

explain select relkind, count(*) from
  (select relkind from pg_class order by relkind) x
  group by relkind;
                              QUERY PLAN
-----------------------------------------------------------------------
 GroupAggregate  (cost=22.88..28.03 rows=4 width=1)
   ->  Sort  (cost=22.88..23.61 rows=292 width=1)
         Sort Key: pg_class.relkind
         ->  Seq Scan on pg_class  (cost=0.00..10.92 rows=292 width=1)
(4 rows)

HashSetOp

该操作用于处理INTERSECT(交集)和EXCEPT(差集)操作(可搭配可选的ALL修饰符)。

其工作流程为:先对一组子查询执行附加(Append)操作,然后根据操作结果以及可选的ALL修饰符,确定应返回哪些行。

从以下示例可以看出,与UNION(并集)不同,INTERSECTEXCEPT操作会处理两个数据源:

explain select * from (select oid from pg_class order by oid) x
  intersect all
  select * from (select oid from pg_proc order by oid) y;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 HashSetOp Intersect All  (cost=0.15..170.72 rows=292 width=4)
   ->  Append  (cost=0.15..163.36 rows=2941 width=4)
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.15..18.37 rows=292 width=4)
               ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.15..12.53 rows=292 width=4)
         ->  Subquery Scan on "*SELECT* 2"  (cost=0.28..145.00 rows=2649 width=4)
               ->  Index Only Scan using pg_proc_oid_index on pg_proc  (cost=0.28..92.02 rows=2649 width=4)
(6 rows)

但当数据源增加到三个时,执行计划会形成更复杂的层级结构:

explain select * from (select oid from pg_class order by oid) x
  intersect all
  select * from (select oid from pg_proc order by oid) y
  intersect all
  select * from (select oid from pg_database order by oid) as w;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 HashSetOp Intersect All  (cost=1.03..172.53 rows=2 width=4)
   ->  Append  (cost=1.03..171.79 rows=294 width=4)
         ->  Subquery Scan on "*SELECT* 3"  (cost=1.03..1.07 rows=2 width=4)
               ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                     Sort Key: pg_database.oid
                     ->  Seq Scan on pg_database  (cost=0.00..1.02 rows=2 width=4)
         ->  Result  (cost=0.15..170.72 rows=292 width=4)
               ->  HashSetOp Intersect All  (cost=0.15..170.72 rows=292 width=4)
                     ->  Append  (cost=0.15..163.36 rows=2941 width=4)
                           ->  Subquery Scan on "*SELECT* 1"  (cost=0.15..18.37 rows=292 width=4)
                                 ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.15..12.53 rows=292 width=4)
                           ->  Subquery Scan on "*SELECT* 2"  (cost=0.28..145.00 rows=2649 width=4)
                                 ->  Index Only Scan using pg_proc_oid_index on pg_proc  (cost=0.28..92.02 rows=2649 width=4)
(13 rows)

了解更多

PostgreSQL 优化