如何优化含Left Join的psql查询以提升百万级数据处理速度？
2026-8-14
百万级数据层级关联查询优化方案
问题背景
我有两张表：migrate_data存储产品详情，ws_data存储d_id（流程ID）及其对应的parent（d_id的父级）字段。数据量达百万级，需要通过Left Join关联这两张表，并基于d_id和parent生成child、parent、grandparent层级结构。
模拟数据生成脚本
CREATE TABLE ws_data ( id SERIAL UNIQUE NOT NULL, d_id integer, parent integer, CONSTRAINT ws_data_pk PRIMARY KEY (id) ); CREATE TABLE migrate_data ( id SERIAL UNIQUE NOT NULL, d_id_sell integer, d_id_curr integer, product_name VARCHAR(100) NOT NULL, -- not unique CONSTRAINT migrate_data_pk PRIMARY KEY (id) ); insert into migrate_data ( product_name, d_id_sell, d_id_curr ) select md5(random()::text), floor(random() * (1000 + 1)), floor(random() * (1000 + 1)) from generate_series(1, 10000); insert into ws_data ( parent, d_id ) select floor(random() * (1000 + 1)), floor(random() * (1000 + 1)) from generate_series(1, 10000); DELETE FROM ws_data T1 WHERE T1.d_id = T1.parent; DELETE FROM ws_data T1 USING ws_data T2 WHERE T1.id < T2.id -- delete the older versions AND T1.parent = T2.parent AND T1.d_id = T2.d_id; -- add more columns if needed
核心查询语句
explain (analyze, buffers, format text) select a.d_id_sell, a.d_id_curr, a.product_name from migrate_data a left join ws_data t1_parent on t1_parent.d_id = a.d_id_sell left join ws_data t1_grandparent on t1_grandparent.d_id = t1_parent.parent left join ws_data t2_parent on t2_parent.d_id = a.d_id_curr left join ws_data t2_grandparent on t2_grandparent.d_id = t2_parent.parent;
查询执行计划分析
"Hash Right Join (cost=36223.20..1292695.17 rows=97566770 width=41) (actual time=224.185..4777.545 rows=96825218 loops=1)" " Hash Cond: (t2_parent.d_id = a.d_id_current)" " Buffers: shared hit=314, temp read=7415 written=7415" " -> Hash Right Join (cost=280.00..1740.20 rows=99270 width=4) (actual time=1.110..6.031 rows=98682 loops=1)" " Hash Cond: (t2_grandparent.d_id = t2_parent.parent)" " Buffers: shared hit=110" " -> Seq Scan on ws_data t2_grandparent (cost=0.00..155.00 rows=10000 width=4) (actual time=0.002..0.360 rows=9944 loops=1)" " Buffers: shared hit=55" " -> Hash (cost=155.00..155.00 rows=10000 width=8) (actual time=1.059..1.059 rows=9944 loops=1)" " Buckets: 16384 Batches: 1 Memory Usage: 517kB" " Buffers: shared hit=55" " -> Seq Scan on ws_data t2_parent (cost=0.00..155.00 rows=10000 width=8) (actual time=0.008..0.470 rows=9944 loops=1)" " Buffers: shared hit=55" " -> Hash (cost=14914.56..14914.56 rows=987731 width=41) (actual time=221.897..221.898 rows=981173 loops=1)" " Buckets: 65536 Batches: 32 Memory Usage: 2891kB" " Buffers: shared hit=204, temp written=7086" " -> Hash Right Join (cost=599.00..14914.56 rows=987731 width=41) (actual time=9.566..75.486 rows=981173 loops=1)" " Hash Cond: (t1_parent.d_id = a.d_id_seller)" " Buffers: shared hit=204" " -> Hash Right Join (cost=280.00..1740.20 rows=99270 width=4) (actual time=3.302..10.555 rows=98682 loops=1)" " Hash Cond: (t1_grandparent.d_id = t1_parent.parent)" " Buffers: shared hit=110" " -> Seq Scan on ws_data t1_grandparent (cost=0.00..155.00 rows=10000 width=4) (actual time=0.007..0.508 rows=9944 loops=1)" " Buffers: shared hit=55" " -> Hash (cost=155.00..155.00 rows=10000 width=8) (actual time=3.256..3.257 rows=9944 loops=1)" " Buckets: 16384 Batches: 1 Memory Usage: 517kB" " Buffers: shared hit=55" " -> Seq Scan on ws_data t1_parent (cost=0.00..155.00 rows=10000 width=8) (actual time=0.018..1.623 rows=9944 loops=1)" " Buffers: shared hit=55" " -> Hash (cost=194.00..194.00 rows=10000 width=41) (actual time=6.223..6.224 rows=10000 loops=1)" " Buckets: 16384 Batches: 1 Memory Usage: 841kB" " Buffers: shared hit=94" " -> Seq Scan on migrate_data a (cost=0.00..194.00 rows=10000 width=41) (actual time=0.034..3.721 rows=10000 loops=1)" " Buffers: shared hit=94" "Planning Time: 0.701 ms"
优化需求
我正尝试降低该查询的执行时间，了解到Lateral关联但不知如何运用，也不清楚如何优化当前的Left Join逻辑，恳请提供优化思路。
层级结构示例
| grandparent | parent | child |
|---|---|---|
| 50 | 300 | 101 |
| 50 | 300 | 345 |
| 50 | 300 | 356 |
| 50 | 300 | 456 |
| 50 | 150 | 458 |
| 50 | 150 | 546 |
| 50 | 200 | 547 |
| 50 | 200 | 600 |
| 50 | 200 | 601 |
| 80 | ||
| 80 | ||
| 90 | 500 | |
| 90 | 501 |
优化思路
1. 优先添加索引，消除全表扫描
从执行计划能看到，所有ws_data的查询都是Seq Scan（全表扫描），这是性能瓶颈的核心。给ws_data创建针对性索引：
-- 给d_id建唯一索引，匹配关联条件 CREATE UNIQUE INDEX idx_ws_data_d_id ON ws_data(d_id); -- 可选：如果需要反向通过parent查找，添加该索引 CREATE INDEX idx_ws_data_parent ON ws_data(parent);
索引生效后，数据库会用Index Scan替代全表扫描，大幅降低数据读取量。
2. 用LATERAL关联简化层级查询
LATERAL允许子查询引用主表的字段，把d_id_sell和d_id_curr的层级查询分别封装，逻辑更清晰，且能利用索引快速定位数据：
SELECT a.d_id_sell, a.d_id_curr, a.product_name, -- d_id_sell的三级层级 t1.child AS sell_child, t1.parent AS sell_parent, t1.grandparent AS sell_grandparent, -- d_id_curr的三级层级 t2.child AS curr_child, t2.parent AS curr_parent, t2.grandparent AS curr_grandparent FROM migrate_data a LEFT JOIN LATERAL ( SELECT w1.d_id AS child, w1.parent AS parent, w2.d_id AS grandparent FROM ws_data w1 LEFT JOIN ws_data w2 ON w2.d_id = w1.parent WHERE w1.d_id = a.d_id_sell ) t1 ON true LEFT JOIN LATERAL ( SELECT w1.d_id AS child, w1.parent AS parent, w2.d_id AS grandparent FROM ws_data w1 LEFT JOIN ws_data w2 ON w2.d_id = w1.parent WHERE w1.d_id = a.d_id_curr ) t2 ON true;
这种方式避免了多表Hash Join带来的内存溢出问题，每个主表行仅触发两次小范围索引查询。
3. 预计算层级结构（物化视图）
如果层级结构不需要实时更新，提前用物化视图预计算ws_data的三级层级，查询时直接关联即可：
-- 创建物化视图存储层级数据 CREATE MATERIALIZED VIEW mv_ws_hierarchy AS SELECT w1.d_id AS child, w1.parent AS parent, w2.d_id AS grandparent FROM ws_data w1 LEFT JOIN ws_data w2 ON w2.d_id = w1.parent; -- 给物化视图加索引加速关联 CREATE UNIQUE INDEX idx_mv_ws_child ON mv_ws_hierarchy(child);
查询时直接关联物化视图：
SELECT a.d_id_sell, a.d_id_curr, a.product_name, t1.parent AS sell_parent, t1.grandparent AS sell_grandparent, t2.parent AS curr_parent, t2.grandparent AS curr_grandparent FROM migrate_data a LEFT JOIN mv_ws_hierarchy t1 ON t1.child = a.d_id_sell LEFT JOIN mv_ws_hierarchy t2 ON t2.child = a.d_id_curr;
数据更新后，执行REFRESH MATERIALIZED VIEW mv_ws_hierarchy;刷新即可。
4. 调整内存参数优化Hash Join（可选）
从执行计划看到Hash Join用到了临时磁盘（temp read=7415 written=7415），说明内存不足导致溢出。临时调整会话级参数测试：
SET work_mem = '64MB';
如果效果稳定，可在postgresql.conf中修改全局参数后重启数据库，注意不要设置过大导致内存耗尽。
内容的提问来源于stack exchange，提问作者Pygirl
相关产品推荐
相关产品推荐
