PostgreSQL相同低效条件下SELECT与DELETE性能差异根因问询

table_a表包含约600万条记录，以下是观察到的性能现象：

性能极差的DELETE（耗时10+分钟） #

DELETE FROM table_a 
WHERE key = '123' 
  AND month = ANY(SELECT TO_CHAR(generate_series, 'YYYY-MM') 
                  FROM GENERATE_SERIES(NOW() - interval '2 month', NOW(), interval '1'));

性能较好的DELETE（耗时1.7秒） #

DELETE FROM table_a 
WHERE key = '123' 
  AND month IN (SELECT month FROM time_bucket);

性能较差的SELECT（耗时6秒） #

SELECT * 
FROM table_a 
WHERE key = '123' 
  AND month = ANY(SELECT TO_CHAR(generate_series, 'YYYY-MM') 
                  FROM GENERATE_SERIES(NOW() - interval '2 month', NOW(), interval '1'));

性能较好的SELECT（耗时1.3秒） #

SELECT * 
FROM table_a 
WHERE key = '123' 
  AND month IN (SELECT month FROM time_bucket);

核心疑问 #

我理解DELETE通常比SELECT慢，但核心疑问是：在使用相同低效查询条件时，为何DELETE会异常缓慢，而对SELECT的影响却小很多？

低效DELETE的执行计划 #

QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
Delete on table_a  (cost=12.51..982166.00 rows=534 width=38) (actual time=72475.407..72475.410 rows=0 loops=1)
   ->  Nested Loop  (cost=12.51..982166.00 rows=534 width=38) (actual time=9704.336..9719.715 rows=120 loops=1)
         Join Filter: (table_a.month = to_char(generate_series.generate_series, 'YYYY-MM'::text))
         Rows Removed by Join Filter: 600
         ->  HashAggregate  (cost=12.51..14.51 rows=200 width=40) (actual time=4810.752..4810.764 rows=3 loops=1)
               Group Key: to_char(generate_series.generate_series, 'YYYY-MM'::text)
               ->  Function Scan on generate_series  (cost=0.01..10.01 rows=1000 width=40) (actual time=761.268..3863.142 rows=5270401 loops=1)
         ->  Materialize  (cost=0.00..978419.66 rows=1067 width=14) (actual time=94.432..1636.150 rows=240 loops=3)
               ->  Seq Scan on table_a  (cost=0.00..978414.32 rows=1067 width=14) (actual time=283.269..4908.276 rows=240 loops=1)
                     Filter: (key = '123'::text)
                     Rows Removed by Filter: 6129200
Planning Time: 0.175 ms
JIT:
   Functions: 18
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.372 ms, Inlining 9.279 ms, Optimization 83.968 ms, Emission 50.601 ms, Total 146.220 ms
Execution Time: 72504.330 ms

根本原因分析 #

1. 低效子查询的开销放大
   低效查询中，GENERATE_SERIES生成了527万多行数据，再通过HashAggregate聚合得到仅3个目标月份值。这一步的冗余计算在SELECT中只是额外的CPU和IO开销，但在DELETE中，子查询结果会驱动嵌套循环，导致后续行处理逻辑被重复触发。

2. 嵌套循环的执行逻辑差异
   执行计划显示数据库采用嵌套循环连接：先拿到3个月份值，再和table_a中key='123'的240行做笛卡尔积（共720行），最后通过Join Filter筛选出120行待删除记录。

   • SELECT阶段，即使有600行被过滤，也只是读取数据后丢弃，资源消耗有限；
   • DELETE阶段，数据库会对每一行候选记录尝试获取行级锁、预写WAL日志，哪怕最终被过滤掉的600行，这些锁操作和日志写入的开销也会累积，大幅增加总耗时。

3. DELETE的事务性额外开销
   DELETE属于写操作，必须保证事务原子性：每删除一行都要写入WAL日志以确保崩溃恢复，同时维护表上的所有索引。这些操作的开销远大于SELECT的只读操作，当执行计划存在冗余步骤时，写操作的开销会被成倍放大。

4. 高效查询的优化点
   高效版本使用IN (SELECT month FROM time_bucket)，直接从预定义的小数据集获取目标月份，避免了生成百万级序列再聚合的冗余计算。同时，这类查询更易触发优化器使用key+month复合索引快速定位目标行，减少全表扫描和无效行的处理，因此无论SELECT还是DELETE性能都更优。

内容的提问来源于stack exchange，提问作者Willy