所以我在一个很大的表上有这个查询：

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

这个查询太慢了，大概5秒钟;并且type上有一个索引
所以我试着：

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

这个查询比1 second好得多，但问题是我不想硬编码这个not type列表。
所以我试着：

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'

型
我又回到了糟糕的表现，5秒和以前一样。
你知道我如何欺骗postgres获得1 sec perf，但明确指定我想要的type（“bla”）吗？

编辑：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

编辑2：在（类型，时间戳）上添加索引后，结果会更快：

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