PostgreSQL大表SQL查询优化求助：耗时超15分钟

原查询与数据背景 #

原查询逻辑简单，但在超大型表上耗时超15分钟：

select pi2.*  
from preval_item pi2
join preval_shipment ps on ps.pvs_shipping_number = pi2.pvs_shipping_number
where ps.pvs_shipping_date < to_date('09/01/2022', 'DD/MM/YYYY')
  and ps.shs_id = '30';

• preval_item表：4600万行，39G数据
• preval_shipment表：34万行，100M数据
• 两张表均已创建对应索引

尝试的改写查询 #

尝试用物化CTE改写，但未达到优化效果，其中物化CTE部分仅需几秒完成，耗时点在后续的表关联操作：

with pvs as materialized (
select pi2.*
from preval_item pi2
join preval_shipment ps on ps.pvs_shipping_number = pi2.pvs_shipping_number
where
    ps.pvs_shipping_date < to_date('09/01/2022','DD/MM/YYYY')
    and ps.shs_id = '30'
)
select pi.pvs_shipping_number
from preval_item pi
join pvs on pvs.pvs_shipping_number = pi.pvs_shipping_number;

执行计划 #

执行EXPLAIN得到的执行计划如下：

Merge Join  (cost=13845931.39..950104909.06 rows=62349421560 width=11)                                                                       
  Merge Cond: ((pvs.pvs_shipping_number)::text = (pi.pvs_shipping_number)::text)                                                             
  CTE pvs                                                                                                                                   
    ->  Gather  (cost=16337.02..7189241.05 rows=29442834 width=821)                                                                         
          Workers Planned: 2                                                                                                                
          ->  Parallel Hash Join  (cost=15337.02..4243957.65 rows=12267848 width=821)                                                        
                Hash Cond: ((pi2.pvs_shipping_number)::text = (ps.pvs_shipping_number)::text)                                                
                ->  Parallel Seq Scan on preval_item pi2  (cost=0.00..4177367.72 rows=19524672 width=821)                                    
                ->  Parallel Hash  (cost=14234.83..14234.83 rows=88175 width=11)                                                             
                      ->  Parallel Seq Scan on preval_shipment ps  (cost=0.00..14234.83 rows=88175 width=11)                                     
                            Filter: (((shs_id)::text = '30'::text) AND (pvs_shipping_date < to_date('09/01/2022'::text, 'DD/MM/YYYY'::text)))
  ->  Sort  (cost=6656689.78..6730296.87 rows=29442834 width=38)                                                                             
        Sort Key: pvs.pvs_shipping_number                                                                                                    
        ->  CTE Scan on pvs  (cost=0.00..588856.68 rows=29442834 width=38)                                                                   
  ->  Materialize  (cost=0.56..987588.70 rows=46859212 width=11)                                                                             
        ->  Index Only Scan using idx_shipping_number on preval_item pi  (cost=0.56..870440.67 rows=46859212 width=11) 

疑问与补充说明 #

疑问：是否因为数据量过大，只能被动等待查询完成？

补充：pvs_shipping_number是两张表的主键（表约束截图显示）

核心问题拆解 #

从执行计划能定位到关键问题：

1. 原查询索引未被有效利用：preval_item采用了Parallel Seq Scan全表扫描，说明要么索引类型不匹配，要么表统计信息过时，导致优化器选错执行路径。
2. 改写查询存在冗余逻辑：物化CTE已经拿到了符合条件的preval_item数据，后续又关联全量preval_item表，相当于把2900多万条数据和4600万条数据做Merge Join，完全是多余操作，必然耗时极长。

针对性优化步骤 #

1. 修复原查询的索引利用问题 #

• 先更新表统计信息，确保优化器能拿到准确数据分布：

ANALYZE preval_item;
ANALYZE preval_shipment;

• 给preval_shipment建复合索引，快速过滤符合条件的配送记录：

CREATE INDEX idx_ps_shs_date_number ON preval_shipment (shs_id, pvs_shipping_date, pvs_shipping_number);

• 检查字段类型一致性：执行计划里出现::text类型转换，若pvs_shipping_number字段类型不一致，会导致索引失效，需确保两张表该字段类型完全相同。

2. 删除改写查询的冗余关联 #

如果你的目标是获取符合条件的pvs_shipping_number，直接从物化CTE提取即可，无需再次关联全表：

with pvs as materialized (
select pi2.pvs_shipping_number
from preval_item pi2
join preval_shipment ps on ps.pvs_shipping_number = pi2.pvs_shipping_number
where
    ps.pvs_shipping_date < to_date('09/01/2022','DD/MM/YYYY')
    and ps.shs_id = '30'
)
select pvs_shipping_number from pvs;

或者直接简化原查询（若存在重复值可加distinct）：

select distinct pi2.pvs_shipping_number  
from preval_item pi2
join preval_shipment ps on ps.pvs_shipping_number = pi2.pvs_shipping_number
where ps.pvs_shipping_date < to_date('09/01/2022', 'DD/MM/YYYY')
  and ps.shs_id = '30';

3. 临时调整执行计划参数（应急用） #

若索引优化后仍未生效，可临时禁用全表扫描强制优化器使用索引：

set enable_seqscan = off;
-- 执行目标查询
set enable_seqscan = on;

注意：此为临时方案，不要全局开启，避免影响其他查询的执行计划选择。

结论 #

不是单纯数据量的问题，是查询逻辑冗余和索引/执行计划选择不当导致的性能瓶颈。按上述步骤优化后，查询耗时可大幅降低，无需硬等。

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