4

PostgreSQL数据库执行计划中常见操作符解读表

原创二两烧麦 2025-10-24

136

以下是PostgreSQL数据库执行计划中常见操作符的解读表，按功能分类整理，包含核心作用、典型场景、执行计划特征及优化建议，适合快速理解和分析执行计划：

类别	操作符名称	核心作用	典型场景	执行计划特征示例	优化建议
表扫描	Seq Scan（顺序扫描）	全表扫描：逐行遍历表中所有数据，无索引可用时触发，效率较低	1. 表数据量小（扫描成本低）； 2. 无合适索引； 3. 查询返回表中大部分数据（如`SELECT * FROM t`）	`Seq Scan on t (cost=0.00..100.00 rows=10000 width=100)` `Filter: (id > 100)`（含过滤条件）	1. 为过滤条件列创建索引； 2. 大表可启用并行扫描（`max_parallel_workers_per_gather`配置）； 3. 避免全表扫描高频执行
	Parallel Seq Scan（并行顺序扫描）	多进程并行全表扫描，提升大表扫描效率（PostgreSQL 9.6+支持）	大表全表扫描（如1000万行以上的表，`SELECT count(*) FROM big_t`）	`Parallel Seq Scan on big_t (cost=0.00..500.00 rows=1000000 width=8)` `Workers Planned: 4`（计划4个并行进程）	1. 调大`parallel_setup_cost`和`parallel_tuple_cost`鼓励并行； 2. 确保服务器CPU核心数足够
索引扫描	Index Scan（索引扫描）	基于索引定位数据行，先扫描索引获取行位置（CTID），再回表读取数据，适合少量行匹配	索引列的等值/范围查询（如`SELECT * FROM t WHERE name = '张三'`，`name`有B树索引）	`Index Scan using idx_t_name on t (cost=0.29..8.30 rows=1 width=100)` `Index Cond: (name = '张三'::text)`	1. 若回表成本高，创建包含查询列的复合索引（覆盖索引）； 2. 避免索引列使用函数（如`lower(name) = 'zhangsan'`会导致索引失效）
	Index Only Scan（仅索引扫描）	索引包含所有查询列，无需回表，直接从索引返回结果，效率极高	查询列均为索引列（如`SELECT id, name FROM t WHERE id < 100`，`id,name`为复合索引）	`Index Only Scan using idx_t_id_name on t (cost=0.29..20.30 rows=100 width=50)` `Index Cond: (id < 100)`	1. 确保索引覆盖所有查询列（新增查询列时扩展索引）； 2. 定期`VACUUM`维护可见性映射（VM），避免索引扫描退化为普通索引扫描
	Bitmap Index Scan（位图索引扫描）	先扫描索引生成位图（记录匹配行的位置），再通过位图批量定位数据行，适合多条件组合查询	多索引列的`AND`/`OR`查询（如`SELECT * FROM t WHERE age > 30 AND dept = 'IT'`，`age`和`dept`均有索引）	`Bitmap Index Scan on idx_t_age (cost=0.00..5.00 rows=100 width=0)` `Index Cond: (age > 30)`	搭配`Bitmap Heap Scan`使用，适合中等数据量匹配（行数过多可能退化为全表扫描）
	Bitmap Heap Scan（位图堆扫描）	根据`Bitmap Index Scan`生成的位图，批量读取表中数据行，减少随机IO	与`Bitmap Index Scan`配合，处理多条件组合查询（如上述`age`和`dept`组合查询）	`Bitmap Heap Scan on t (cost=10.00..50.00 rows=100 width=100)` `Recheck Cond: ((age > 30) AND (dept = 'IT'::text))`	1. 调大`work_mem`减少磁盘临时文件； 2. 避免多索引条件匹配行数过多（否则位图过大）
连接操作	Nested Loop（嵌套循环连接）	以小表为驱动表，逐行与被驱动表匹配，适合驱动表结果集小的场景	1. 驱动表过滤后行数少（如`WHERE id < 10`）； 2. 被驱动表连接列有索引（如`t1 JOIN t2 ON t1.id = t2.t1_id`，`t2.t1_id`有索引）	`Nested Loop (cost=0.58..100.58 rows=100 width=200)` `-> Index Scan using idx_t1_id on t1 ...` `-> Index Scan using idx_t2_t1_id on t2 ...`	1. 选择小表作为驱动表（减少外层循环次数）； 2. 被驱动表连接列创建索引（避免全表扫描）
	Hash Join（哈希连接）	小表构建哈希表，大表逐行匹配哈希表，适合无索引/大表连接，内存足够时效率高	1. 连接列无索引； 2. 大表连接（如`t1 JOIN t2 ON t1.code = t2.code`，两表均无索引）	`Hash Join (cost=100.00..500.00 rows=10000 width=200)` `Hash Cond: (t1.code = t2.code)` `-> Seq Scan on t1 ...` `-> Hash ...` `-> Seq Scan on t2 ...`	1. 调大`work_mem`确保哈希表在内存中（避免写入磁盘）； 2. 过滤小表减少哈希表大小（如`WHERE t2.status = 1`）
	Merge Join（归并连接）	两表按连接列排序后，按顺序归并匹配，适合连接列已排序（有索引）的场景	连接列均有索引且需有序输出（如`SELECT ... ORDER BY t1.code`，`t1.code`和`t2.code`有索引）	`Merge Join (cost=200.00..600.00 rows=10000 width=200)` `Merge Cond: (t1.code = t2.code)` `-> Index Scan using idx_t1_code on t1 ...` `-> Index Scan using idx_t2_code on t2 ...`	1. 确保连接列索引有效（维持有序性）； 2. 若表未排序，提前`ORDER BY`可能降低效率（归并连接需排序）
聚合与排序	Aggregate（聚合）	计算无`GROUP BY`的聚合函数（`COUNT`/`SUM`/`AVG`等），自动优化为快速聚合	无分组的聚合查询（如`SELECT COUNT(*), MAX(age) FROM t WHERE dept = 'IT'`）	`Aggregate (cost=50.00..50.01 rows=1 width=16)` `-> Seq Scan on t ...` `Filter: (dept = 'IT'::text)`	1. 过滤条件有效减少聚合前的行数； 2. 大表可结合索引（如`COUNT(*)`用`Index Only Scan`）
	GroupAggregate（分组聚合）	对排序后的数据分组聚合，适合分组列有索引（数据已有序）	分组列有索引的聚合查询（如`SELECT dept, COUNT(*) FROM t GROUP BY dept`，`dept`有索引）	`GroupAggregate (cost=100.00..200.00 rows=10 width=50)` `Group Key: dept` `-> Index Scan using idx_t_dept on t ...`	确保分组列索引顺序与`GROUP BY`一致，避免额外排序（`Extra`无`Sort`）
	HashAggregate（哈希聚合）	通过哈希表分组聚合，适合分组列无索引、数据无序的场景	分组列无索引的聚合查询（如`SELECT dept, COUNT(*) FROM t GROUP BY dept`，`dept`无索引）	`HashAggregate (cost=150.00..250.00 rows=10 width=50)` `Group Key: dept` `-> Seq Scan on t ...`	1. 调大`work_mem`避免哈希表写入磁盘； 2. 高频分组查询建议创建分组列索引（转为`GroupAggregate`）
	Sort（排序）	对结果集按指定列排序，内存不足时使用临时文件（效率低）	包含`ORDER BY`且无法利用索引排序（如`SELECT * FROM t ORDER BY age`，`age`无索引）	`Sort (cost=200.00..250.00 rows=10000 width=100)` `Sort Key: age` `-> Seq Scan on t ...`	1. 在排序列创建索引（利用索引有序性避免排序）； 2. 大结果集排序调大`work_mem`
子查询与其他	Subquery Scan（子查询扫描）	执行子查询并将结果作为临时数据集处理，本质是子查询逻辑的封装	非关联子查询（如`SELECT * FROM (SELECT id FROM t WHERE age > 30) sub`）	`Subquery Scan on sub (cost=50.00..150.00 rows=1000 width=4)` `-> Seq Scan on t ...` `Filter: (age > 30)`	1. 子查询尽量简化（如添加过滤条件）； 2. 关联子查询可改为`JOIN`提升效率
	CTE Scan（CTE扫描）	执行CTE（公用表表达式）并扫描结果，PostgreSQL默认物化CTE（生成临时表）	带`WITH`子句的查询（如`WITH cte AS (SELECT id FROM t) SELECT * FROM cte`）	`CTE Scan on cte (cost=50.00..150.00 rows=1000 width=4)` `CTE cte` `-> Seq Scan on t ...`	1. 小CTE可启用`WITH ORDINALITY`优化； 2. 大CTE可添加索引（`CREATE TEMPORARY INDEX`）
	BitmapAnd/BitmapOr（位图组合）	组合多个`Bitmap Index Scan`的结果（`AND`/`OR`逻辑），优化多条件查询	多索引列的`AND`/`OR`查询（如`WHERE (age > 30 OR dept = 'IT')`，两列均有索引）	`Bitmap Heap Scan on t ...` `-> BitmapOr` `-> Bitmap Index Scan on idx_t_age ...` `-> Bitmap Index Scan on idx_t_dept ...`	适合条件组合过滤性好的场景，避免过多条件导致位图过大

说明：

PostgreSQL执行计划的cost列表示估算成本（前值为启动成本，后值为总执行成本），rows为估算返回行数，需与实际行数对比（差异大时执行ANALYZE 表名;更新统计信息）。
优化核心方向：优先消除全表扫描（Seq Scan）、减少排序（Sort）和磁盘哈希/临时表（通过调大work_mem）、利用索引覆盖（Index Only Scan）和并行扫描提升大表性能。
操作符效率参考：Index Only Scan > Index Scan > Bitmap Index Scan + Bitmap Heap Scan > Seq Scan（针对小表）；连接效率：Nested Loop（小结果集） > Hash Join（大表无索引） > Merge Join（有序数据）。

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论