PostgreSQL同表子查询SQL优化及ORDER BY性能、work_mem问题
问题背景
我有如下PostgreSQL查询语句：
EXPLAIN (ANALYZE, BUFFERS) select h.pk::varchar(255), ( h.fecha at TIME zone 'Mexico/General' at TIME zone 'UTC' )::date as fecha from historial h where ( h.fecha at TIME zone 'Mexico/General' at TIME zone 'UTC' )::date < '2022-07-30' and h.del = 0 and ( select COUNT(h.pk) from historial h where h.tipo in (3, 6, 9, 10) and h.del = 0 ) > 10 order by h.fkcr, h.fecha asc
但historial表有数百万行数据，使用ORDER BY子句后，查询时间从约300ms骤升至约6000ms！请问是否可以对此查询进行优化？
以下是该语句的EXPLAIN (ANALYZE, BUFFERS)执行计划：
QUERY PLAN | ------------------------------------------------------------------------------------------------------------------------------------------------+ Sort (cost=215616.65..217123.46 rows=602724 width=40) (actual time=11770.953..13789.063 rows=1810355 loops=1) | Sort Key: h.fkcr, h.fecha | Sort Method: external merge Disk: 145136kB | Buffers: shared hit=16461 read=49282, temp read=32832 written=32832 | InitPlan 1 (returns $0) | -> Aggregate (cost=48588.68..48588.69 rows=1 width=16) (actual time=479.485..479.486 rows=1 loops=1) | Buffers: shared hit=441 read=24000 | -> Bitmap Heap Scan on historial h_1 (cost=3146.86..48088.10 rows=200230 width=16) (actual time=26.643..357.554 rows=186103 loops=1)| Recheck Cond: (tipo = ANY ('{3,6,9,10}'::integer[])) | Filter: (del = 0) | Rows Removed by Filter: 21311 | Heap Blocks: exact=23863 | Buffers: shared hit=441 read=24000 | -> Bitmap Index Scan on idx_tipo (cost=0.00..3096.80 rows=208414 width=0) (actual time=22.107..22.107 rows=207414 loops=1) | Index Cond: (tipo = ANY ('{3,6,9,10}'::integer[])) | Buffers: shared hit=9 read=569 | -> Result (cost=0.00..92681.53 rows=602724 width=40) (actual time=479.802..7149.979 rows=1810355 loops=1) | One-Time Filter: ($0 > 10) | Buffers: shared hit=16453 read=49282 | -> Seq Scan on historial h (cost=0.00..83640.67 rows=602724 width=40) (actual time=0.293..2781.099 rows=1810355 loops=1) | Filter: ((del = 0) AND ((timezone('UTC'::text, timezone('Mexico/General'::text, fecha)))::date < '2022-07-30'::date)) | Rows Removed by Filter: 71719 | Buffers: shared hit=16012 read=25282 | Planning time: 1.105 ms | Execution time: 14991.655 ms |
我注意到Sort节点使用磁盘进行排序，于是尝试将work_mem修改为1GB，但仍使用磁盘排序。请问work_mem本应生效吗？还是我误解了它的作用？
优化方案与问题解答
一、查询优化建议
简化子查询逻辑
当前子查询(select COUNT(h.pk) from historial h where h.tipo in (3,6,9,10) and h.del=0) >10是独立聚合查询，主查询执行时会单独跑一遍。若该条件结果不会频繁变化，可：- 预先计算值并存储到变量或临时表，避免重复执行
- 若业务逻辑允许，可将其改写为
EXISTS子句（注：此子句为全局计数，EXISTS仅适合存在性判断，优先考虑提前计算）
优化时间条件，避免索引失效
主查询中(h.fecha at TIME zone 'Mexico/General' at TIME zone 'UTC')::date < '2022-07-30'对fecha做了时区转换和类型转换，会导致无法使用fecha上的普通索引。可：- 反向计算条件值：将目标日期
'2022-07-30'转换为Mexico/General时区的时间范围，直接匹配h.fecha字段，示例：
此方式计算的是常量值，h.fecha < ('2022-07-30'::date AT TIME ZONE 'UTC' AT TIME ZONE 'Mexico/General')h.fecha可直接使用索引。 - 创建函数索引：若无法修改条件逻辑，可创建基于时区转换后的表达式索引：
结合CREATE INDEX idx_historial_fecha_utc_date ON historial (((fecha AT TIME ZONE 'Mexico/General' AT TIME ZONE 'UTC')::date)) WHERE del = 0;del=0的条件，进一步缩小索引范围。
- 反向计算条件值：将目标日期
创建覆盖索引消除排序
当前排序需扫描181万行数据后在内存/磁盘排序，开销极大。可创建包含排序键、过滤条件和查询字段的覆盖索引，让数据库直接通过索引获取有序数据，跳过Sort步骤：CREATE INDEX idx_historial_del_fecha_fkcr_pk ON historial (del, fecha, fkcr) INCLUDE (pk) WHERE del = 0;或结合优化后的时间条件，创建更精准的覆盖索引：
CREATE INDEX idx_historial_del_fecha_range_fkcr_pk ON historial (del, fecha, fkcr) INCLUDE (pk) WHERE del = 0 AND fecha < ('2022-07-30'::date AT TIME ZONE 'UTC' AT TIME ZONE 'Mexico/General');优化主查询扫描方式
执行计划显示主查询用了全表扫描（Seq Scan），创建合适索引后，可切换为索引扫描，减少数据读取量。
二、关于work_mem的疑问
work_mem是PostgreSQL用于排序、哈希连接等操作的单操作内存限额，每个Sort节点可使用的最大内存。当前Sort用了约145MB磁盘空间，理论上调高到200MB以上即可使用内存排序，修改后未生效可能有以下原因：
- 修改方式错误：临时修改需确保当前会话生效；修改配置文件后需重启PostgreSQL或执行
SELECT pg_reload_conf();让配置生效。 - 实际内存需求超设置值：虽显示磁盘使用145MB，但PostgreSQL估算的内存需求可能更高，或并发操作占用内存，导致单个Sort无法分配足够
work_mem。 - 配置被覆盖：数据库级、用户级或会话级的
work_mem设置会覆盖全局配置，可执行SHOW work_mem;查看当前会话实际生效值。
即使调高work_mem让排序在内存进行，也只是缓解排序性能问题，无法解决全表扫描和重复子查询的根本问题，优先建议做查询逻辑和索引优化。
内容的提问来源于stack exchange，提问作者H3lltronik
