PostgreSQL带过滤条件的COUNT查询性能优化求助

问题背景 #

为表A创建索引加速元素计数查询，无过滤条件时可快速执行Index Only Scan，但添加notes <> ''过滤后，不到100万条数据的查询耗时超过2分钟，需优化。

表结构与现有索引 #

CREATE TABLE A (
  id                          uuid,
  deployment_id               uuid,
  started_at                  timestamp,
  ended_at                    timestamp,
  notes                       text
);

CREATE INDEX IF NOT EXISTS A_by_deployment_id ON A(deployment_id, started_at desc);

慢查询语句 #

EXPLAIN ANALYZE SELECT COUNT(*)
FROM A
WHERE deployment_id = 'XXXX'
AND notes <> ''
AND date_trunc('day', '2023-01-01T05:00:00.000Z') <= started_at 
AND started_at <= date_trunc('day', '2023-09-10T03:59:59.999Z') + interval '1 day' 

执行计划（EXPLAIN ANALYZE） #

Aggregate  (cost=1947306.09..1947306.10 rows=1 width=8) (actual time=139313.811..139337.476 rows=1 loops=1)
  ->  Gather  (cost=32473.34..1947306.09 rows=1 width=0) (actual time=139299.726..139323.384 rows=0 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Bitmap Heap Scan on A  (cost=31473.34..1946305.99 rows=1 width=0) (actual time=139236.212..139236.216 rows=0 loops=3)
              Recheck Cond: ((deployment_id = 'XXXX'::uuid) AND (started_at <= ('2023-09-09 00:00:00+00'::timestamp with time zone + '1 day'::interval)))
              Rows Removed by Index Recheck: 3021055
              Filter: ((notes <> ''::text) AND ('2023-01-01 00:00:00+00'::timestamp with time zone <= started_at))
              Rows Removed by Filter: 220895
              Heap Blocks: exact=19014 lossy=130950
              ->  Bitmap Index Scan on A_by_deployment_id  (cost=0.00..31473.34 rows=636542 width=0) (actual time=317.141..317.142 rows=620293 loops=1)
                    Index Cond: ((deployment_id = 'XXXX'::uuid) AND (started_at >= '2023-01-01 00:00:00+00'::timestamp with time zone) AND (started_at <= ('2023-09-09 00:00:00+00'::timestamp with time zone + '1 day'::interval)))
Planning Time: 0.251 ms
Execution Time: 139386.633 ms

补充执行计划（EXPLAIN ANALYZE VERBOSE BUFFERS） #

Aggregate  (cost=1947318.09..1947318.10 rows=1 width=8) (actual time=241608.194..241613.857 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=7 read=465248
  I/O Timings: read=692920.945
  ->  Gather  (cost=32485.34..1947318.09 rows=1 width=0) (actual time=241608.176..241613.837 rows=0 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=7 read=465248
        I/O Timings: read=692920.945
        ->  Parallel Bitmap Heap Scan on A  (cost=31485.34..1946317.99 rows=1 width=0) (actual time=241581.932..241581.933 rows=0 loops=3)
              Recheck Cond: ((A.deployment_id = 'XXXX'::uuid) AND (A.started_at <= ('2023-09-09 00:00:00+00'::timestamp with time zone + '1 day'::interval)))
              Rows Removed by Index Recheck: 3020692
              Filter: ((A.notes <> ''::text) AND ('2023-01-01 00:00:00+00'::timestamp with time zone <= A.started_at))
              Rows Removed by Filter: 220880
              Heap Blocks: exact=19672 lossy=133295
              Buffers: shared hit=7 read=465248
              I/O Timings: read=692920.945
              Worker 0:  actual time=241577.148..241577.150 rows=0 loops=1
                Buffers: shared hit=1 read=152982
                I/O Timings: read=229432.745
              Worker 1:  actual time=241578.565..241578.567 rows=0 loops=1
                Buffers: shared hit=2 read=153127
                I/O Timings: read=229433.323
              ->  Bitmap Index Scan on A_by_deployment_id  (cost=0.00..31485.34 rows=636542 width=0) (actual time=5072.596..5072.597 rows=620293 loops=1)
                    Index Cond: ((A.deployment_id = 'XXXX'::uuid) AND (A.started_at >= '2023-01-01 00:00:00+00'::timestamp with time zone) AND (A.started_at <= ('2023-09-09 00:00:00+00'::timestamp with time zone + '1 day'::interval)))
                    Buffers: shared hit=2 read=6174
                    I/O Timings: read=4641.102
Query Identifier: 2755043791582677827
Planning Time: 16.373 ms
Execution Time: 241617.394 ms

优化方案 #

1. 创建覆盖索引 #

现有索引无法覆盖notes过滤条件，导致必须回表扫描。创建包含notes的覆盖索引，让查询直接从索引获取数据：

CREATE INDEX IF NOT EXISTS A_deployment_started_notes ON A(deployment_id, started_at desc) INCLUDE (notes);

2. 部分索引（空文本占比高时适用） #

如果大部分数据的notes为空或空文本，针对符合条件的行创建部分索引，缩小扫描范围：

CREATE INDEX IF NOT EXISTS A_deployment_started_notes_nonempty ON A(deployment_id, started_at desc) WHERE notes <> '';

3. 简化查询条件 #

原查询中date_trunc作用于常量，可提前计算结果，避免重复计算：

EXPLAIN ANALYZE SELECT COUNT(*)
FROM A
WHERE deployment_id = 'XXXX'
AND notes <> ''
AND started_at >= '2023-01-01 00:00:00+00'::timestamp with time zone
AND started_at <= '2023-09-10 00:00:00+00'::timestamp with time zone;

4. 调整work_mem解决lossy bitmap #

执行计划中出现lossy块，说明work_mem不足导致位图压缩，临时调高会话级参数：

SET work_mem = '64MB'; -- 根据实际情况调整至128MB或更高

5. 物化视图（周期性查询适用） #

若该查询是固定周期的统计，创建物化视图预计算结果：

CREATE MATERIALIZED VIEW mv_A_count_notes AS
SELECT deployment_id, date_trunc('day', started_at) AS day, COUNT(*) AS count
FROM A
WHERE notes <> ''
GROUP BY deployment_id, date_trunc('day', started_at);

定期刷新物化视图，查询时直接读取视图数据。

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