PostgreSQL正向指定type字段的慢查询性能优化求助

问题场景 #

在一张大表上执行以下正向匹配type的查询时，耗时约5秒，速度极慢，但表上已存在type字段的单独索引：

SELECT * FROM datTable WHERE type='bla' 
AND timestamp > (CURRENT_DATE - INTERVAL '1 day')

尝试的临时方案 #

改用排除法查询后，性能提升至1秒左右，但硬编码排除类型列表的方式无法长期使用：

SELECT * FROM datTable WHERE type NOT IN ('blu','bli','blo') 
AND timestamp > (CURRENT_DATE - INTERVAL '1 day')

尝试用CTE包装上述排除查询后再过滤目标type，性能又回到5秒的糟糕状态：

with res as (
    SELECT * FROM datTable WHERE type NOT IN ('blu','bli','blo') 
    AND timestamp > (CURRENT_DATE - INTERVAL '1 day')
)
select * from res where type='bla'

核心需求 #

如何在**正向指定type='bla'**的前提下，让查询性能达到1秒以内？

执行计划分析（CTE查询的EXPLAIN ANALYZE结果） #

GroupAggregate  (cost=677400.59..677493.09 rows=3595 width=59) (actual time=4789.667..4803.183 rows=3527 loops=1)
  Group Key: event_historic.sender
  ->  Sort  (cost=677400.59..677412.48 rows=4756 width=23) (actual time=4789.646..4792.808 rows=68045 loops=1)
        Sort Key: event_historic.sender
        Sort Method: quicksort  Memory: 9469kB
        ->  Bitmap Heap Scan on event_historic  (cost=505379.21..677110.11 rows=4756 width=23) (actual time=4709.494..4769.437 rows=68045 loops=1)
              Recheck Cond: (("timestamp" > (CURRENT_DATE - '1 day'::interval)) AND ((type)::text = 'NEAR_TRANSFER'::text))
              Heap Blocks: exact=26404
              ->  BitmapAnd  (cost=505379.21..505379.21 rows=44676 width=0) (actual time=4706.080..4706.082 rows=0 loops=1)
                    ->  Bitmap Index Scan on event_historic_timestamp_idx  (cost=0.00..3393.89 rows=263109 width=0) (actual time=167.838..167.838 rows=584877 loops=1)
                          Index Cond: (("timestamp" > (CURRENT_DATE - '1 day'::interval)))
                    ->  Bitmap Index Scan on event_historic_type_idx  (cost=0.00..501982.69 rows=45316549 width=0) (actual time=4453.071..4453.071 rows=44279973 loops=1)
                          Index Cond: ((type)::text = 'NEAR_TRANSFER'::text)
Planning Time: 0.385 ms
JIT:
  Functions: 10
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.505 ms, Inlining 18.102 ms, Optimization 87.745 ms, Emission 44.270 ms, Total 152.622 ms
Execution Time: 4809.099 ms

从计划可以看出，PostgreSQL对两个单独索引做了BitmapAnd操作：

• type索引扫描耗时极长（4453ms），因为type='NEAR_TRANSFER'的记录量多达4400万条，生成的bitmap体积过大
• 后续的bitmap交集、堆扫描和排序操作因此变得效率极低

最终解决方案：创建联合索引 #

创建(type, timestamp)的联合索引后，查询耗时直接降至约100ms，完全满足性能要求：

-- 创建联合索引
CREATE INDEX typetimestamp ON event_historic (type, timestamp);

优化后的执行计划：

HashAggregate  (cost=156685.88..156786.59 rows=8057 width=59) (actual time=95.201..96.511 rows=3786 loops=1)
  Group Key: sender
  Batches: 1  Memory Usage: 2449kB
  Buffers: shared hit=31041
  ->  Index Scan using typetimestamp on event_historic eh  (cost=0.57..156087.67 rows=47857 width=44) (actual time=12.244..55.921 rows=76220 loops=1)
        Index Cond: (((type)::text = 'NEAR_TRANSFER'::text) AND (("timestamp" > (CURRENT_DATE - '1 day'::interval))))
        Buffers: shared hit=31041
Planning:
  Buffers: shared hit=5
Planning Time: 0.567 ms
JIT:
  Functions: 10
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 2.543 ms, Inlining 0.000 ms, Optimization 1.221 ms, Emission 10.819 ms, Total 14.584 ms
Execution Time: 99.496 ms

优化原理 #

联合索引(type, timestamp)可以直接定位到同时满足两个条件的记录：

1. 先通过type='bla'过滤出该类型的所有记录
2. 再在这个子集里快速筛选出符合时间条件的数据
3. 索引扫描直接返回目标结果，避免了单独索引的bitmap交集、大量堆扫描和排序操作

内容的提问来源于stack exchange，提问作者François Richard