八月 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
(并集)不同,INTERSECT
和EXCEPT
操作会处理两个数据源:
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)