PostgreSQL 11.2如何优化4000万行表的慢查询与失效索引问题？

问题背景 #

运行环境为PostgreSQL 11.2，数据库核心配置如下：

shared_buffers = 1024MB
effective_cache_size = 2048MB
maintenance_work_mem = 320MB
checkpoint_completion_target = 0.5
wal_buffers = 3932kB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 64MB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

现有public.my_table表数据量约4000万行，执行的查询语句如下（仅WHERE子句为核心过滤逻辑）：

select id,name from my_table where
action_performed = true AND
should_still_perform_action = false AND
action_performed_at <= '2021-09-05 00:00:00.000'
LIMIT 100;

该查询的业务目标是拉取待处理条目，客户端拉取数据后需基于元数据上传文件到云服务商，耗时较长；时间戳条件用于过滤早于指定时间的条目，返回顺序无要求，加LIMIT是为了避免网络操作导致应用挂起。
表结构（脱敏后）及现有索引定义如下：

Table "public.my_table"
 action_performed_at              | timestamp without time zone |           |          | now()
 should_still_perform_action      | boolean                     |           | not null | true
 action_performed                 | boolean                     |           | not null | false

Indexes:
    "index001" btree (action_performed_at, should_still_perform_action, action_performed) WHERE should_still_perform_action = false AND action_performed = true
    "index002" btree (action_performed, should_still_perform_action, action_performed_at DESC) WHERE should_still_perform_action = false AND action_performed = true

问题现象 #

• 现有两个部分索引仅在重建后短期生效，REINDEX操作无效，后续不再被查询优化器选中
• 表中符合查询条件的行约10万，但查询走全表扫描，执行时间超过100秒，查询计划如下：

QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
------------------------------------------------------------------------------------
 Limit  (cost=0.00..707.80 rows=100 width=3595) (actual time=18520.627..100644.933 rows=100 loops=1)
   Buffers: shared hit=0 read=1392361 dirtied=26 written=26
   ->  Seq Scan on my_table  (cost=0.00..4164264.45 rows=5883377 width=3595) (actual time=18520.624..100644.073 rows=100 loops=1)
         Filter: (action_performed AND (NOT should_still_perform_action) AND (action_performed_at <= '2021-09-05 00:00:00'::timestamp without time zone))
         Rows Removed by Filter: 19846606
         Buffers: shared hit=0 read=1392361 dirtied=26 written=26
 Planning Time: 63.667ms
 Execution Time: 100645.548 ms
(10 rows)

• 索引膨胀检测显示两个索引膨胀率分别超过99%和67%，检测结果如下：

current_database | schemaname |  tblname  |       idxname      |  real_size  | extra_size  |    extra_pct     | fillfactor | bloat_size  |    bloat_pct     | is_na 
------------------+------------+------------------+---------------------------------------+-------------+-------------+------------------+------------+-------------+------------------+-------
 mine             | public     | my_table | index001           |   343244800 |   341598208 | 99.5202863961814 |         90 |   341426176 | 99.4701670644391 | f
 mine             | public     | my_table | index002           |  3290316800 |  2338521088 | 71.0728245985311 |         90 |  2231902208 |  67.832441180132 | f

• 执行ANALYZE更新统计信息后，查询仍然走全表扫描，查询计划如下：

QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
------------------------------------------------------------------------------------
 Limit  (cost=0.00..690.14 rows=1000 width=3591) (actual time=0.044..5840.228 rows=1000 loops=1)
    Buffers: shared hit=3 read=81426 dirtied=18 written=18
   ->  Seq Scan on my_table  (cost=0.00..4163978.60 rows=6033500 width=3591) (actual time=0.034..5839.599 rows=100 loops=1)
         Filter: (action_performed AND (NOT should_still_perform_action) AND (action_performed_at <= '2021-09-05 00:00:00'::timestamp without time zone))
         Rows Removed by Filter: 953640
         Buffers: shared hit=3 read=81426 dirtied=18 written=18
 Planning Time: 63.667ms
 Execution Time: 100645.548 ms
(10 rows)

业务要求不能修改现有查询的LIMIT语法，需要长期可落地的优化方案。

根因分析 #

• 核心问题是部分索引高膨胀：索引过滤的是待处理的业务数据，这类数据处理完成后会被删除或更新布尔字段值，产生大量死元组，默认autovacuum触发不及时，导致索引体积急速膨胀，优化器估算走索引的成本高于全表扫描，因此选择全表扫描。
• 现有索引设计冗余：部分索引的过滤条件已经固定了两个布尔字段的值，索引字段中重复存储这两个字段，没有实际作用，还增大了索引体积，加快膨胀速度。
• 原有索引不是覆盖索引：走索引后还需要回表查询堆表获取id和name字段，进一步抬高了索引扫描的估算成本。

优化方案 #

1. 从根源解决索引膨胀问题 #

• 针对该表单独调整自动清理参数，提高autovacuum触发频率，及时清理死元组：

ALTER TABLE public.my_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.05,
    toast.autovacuum_vacuum_scale_factor = 0.01
);

• 配置定时任务，每两周/每月对该表的部分索引执行REINDEX CONCURRENTLY操作，无需删除重建即可清理残留膨胀，且不会锁表阻塞业务查询。

2. 优化索引设计，降低索引成本 #

删除现有两个冗余索引，新建覆盖索引，查询可直接从索引获取全部所需字段，无需回表，大幅降低索引扫描成本：

-- 并发删除旧索引，不阻塞业务读写
DROP INDEX CONCURRENTLY IF EXISTS public.index001, public.index002;
-- 新建覆盖型部分索引
CREATE INDEX CONCURRENTLY idx_my_table_pending_process ON public.my_table (action_performed_at)
INCLUDE (id, name)
WHERE should_still_perform_action = false AND action_performed = true;

新索引仅保留必要的时间过滤字段，体积相比原有索引降低70%以上，膨胀速度也会明显变慢。

3. 优化统计信息精度，避免优化器错估 #

调高时间字段的统计信息采样精度，让优化器更准确估算符合条件的行数，避免因行数错估选择全表扫描：

ALTER TABLE public.my_table ALTER COLUMN action_performed_at SET STATISTICS 1000;
ANALYZE public.my_table (action_performed_at);

4. 可选业务优化 #

如果已处理完成的业务数据不需要继续保留在当前表，可定期归档或删除历史数据，降低表的总大小，即使偶发全表扫描也不会出现过高延迟。

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