PostgreSQL 12 count查询未命中status部分索引问题咨询

您好，提前感谢各位抽出时间解答问题，本次咨询旨在学习如何在count查询场景下高效使用索引。

环境版本 #

PostgreSQL 12.11

我有一张数据量约1500万行的大表lots，该表本应按整数类型字段status（实际取值范围为0到3）进行分区，但实际未做分区处理。

表中数据按status字段的分布情况如下：

-- lots by status
SELECT status, count(*), ROUND((count(*) * 100 / SUM(count(*)) OVER ()), 1) AS "%"
  FROM lots
GROUP BY status
ORDER BY count(*) DESC;

当前表上创建的索引信息如下：

其中pidx_active_lots_on_id为部分索引，定义语句如下：

CREATE UNIQUE INDEX CONCURRENTLY "pidx_active_lots_on_id" ON "lots" ("id" DESC) WHERE status = 0;

可以看到，针对status=0数据创建的部分索引大小仅38MB，远小于全量status索引的499MB。

我创建该部分索引是为了优化如下高频查询：

SELECT count(*) FROM lots WHERE status = 0;

统计status=0的记录数是该表最高频的count查询场景，但实际执行时该部分索引并未被查询优化器选用。我还尝试了另一种查询写法：

SELECT count(id) FROM lots WHERE status = 0;

第二个查询虽然使用了该部分索引，但执行性能反而更差。

说明：创建该部分索引后我已执行ANALYSE lots;更新表统计信息。

我的疑问如下：

1. 为什么第一个count(*)查询没有命中针对status=0创建的部分索引？
2. 为什么第二个查询使用部分索引后执行性能反而更差？

附执行计划详情 #

EXPLAIN(ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT COUNT(*) FROM lots WHERE lots.status = 0

Aggregate  (cost=539867.77..539867.77 rows=1 width=8) (actual time=16517.790..16517.791 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=79181 read=287729 dirtied=16606 written=7844
  I/O Timings: read=14040.416 write=58.453
  ->  Index Only Scan using index_lots_on_status on public.lots  (cost=0.11..539125.83 rows=1483881 width=0) (actual time=0.498..16238.580 rows=1501060 loops=1)
        Output: status
        Index Cond: (lots.status = 0)
        Heap Fetches: 1545139
        Buffers: shared hit=79181 read=287729 dirtied=16606 written=7844
        I/O Timings: read=14040.416 write=58.453
Planning Time: 1.856 ms
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.466 ms, Inlining 80.076 ms, Optimization 15.797 ms, Emission 12.393 ms, Total 108.733 ms
Execution Time: 16568.670 ms



EXPLAIN(ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT COUNT(id) FROM lots WHERE lots.status = 0

Aggregate  (cost=660337.71..660337.72 rows=1 width=8) (actual time=32127.686..32127.687 rows=1 loops=1)
  Output: count(id)
  Buffers: shared hit=80426 read=334949 dirtied=3 written=75
  I/O Timings: read=11365.273 write=22.365
  ->  Bitmap Heap Scan on public.lots  (cost=11304.17..659595.77 rows=1483887 width=4) (actual time=3783.122..30680.836 rows=1501176 loops=1)
        Output: id, url, title, ... *(list of all of the 32 columns)*
        Recheck Cond: (lots.status = 0)
        Heap Blocks: exact=402865
        Buffers: shared hit=80426 read=334949 dirtied=3 written=75
        I/O Timings: read=11365.273 write=22.365
        ->  Bitmap Index Scan on pidx_active_lots_on_id  (cost=0.00..11229.97 rows=1483887 width=0) (actual time=2534.845..2534.845 rows=1614888 loops=1)
              Buffers: shared hit=4866
Planning Time: 0.248 ms
JIT:
  Functions: 5
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.170 ms, Inlining 56.485 ms, Optimization 474.508 ms, Emission 205.882 ms, Total 738.045 ms
Execution Time: 32169.349 ms

问题解答 #

1. 为什么count(*)查询没有命中status=0的部分索引？ #

两个核心原因：

• 你建的pidx_active_lots_on_id索引键只有id列，而SELECT count(*) FROM lots WHERE status = 0既不需要返回id，也没在过滤、排序、分组逻辑里用到id。PostgreSQL 12的优化器筛选候选索引时，不会优先考虑索引键和查询逻辑完全无关的索引，哪怕这个索引的部分过滤规则和查询WHERE条件完全匹配。
• 优化器基于统计信息算成本时，判定走全量index_lots_on_status的Index Only Scan路径成本更低。但实际执行下来这个判断偏差很大：执行计划显示该路径的Heap Fetches达到154万，和返回的总行数基本持平，说明表的visibility map覆盖率极低，Index Only Scan基本退化成需要逐行回表的普通索引扫描，实际执行成本远高于估算值。

2. 为什么count(id)用了部分索引反而性能更差？ #

这个查询虽然用到了pidx_active_lots_on_id，但没走效率最高的Index Only Scan路径，而是选择了Bitmap Index Scan + Bitmap Heap Scan的执行逻辑：先扫描部分索引拿到所有符合条件行的物理位置，在内存中构建位置位图，再照着位图回表扫描堆块，逐行读取数据取id做计数。
从执行计划可以看到，这个过程需要访问402865个堆块，总共读取334949个内存页，比走全量status索引的读盘量还高；回表带来了大量IO，还触发了JIT编译增加额外开销，最终耗时比前者慢了近一倍。
实际上id本来就存储在pidx_active_lots_on_id索引里，索引里的所有条目天然满足status=0的条件，本来直接扫索引就能完成计数根本不需要回表，但优化器没选这个最优路径——本质是这个索引的设计目的是保证status=0的行id唯一，不是为count查询设计的，优化器没有识别到索引覆盖的可行性。

优化方案 #

如果要把这个高频count查询的性能拉满，专门创建一个适配该场景的小体积部分索引即可：

CREATE INDEX CONCURRENTLY pidx_lots_status0_cnt ON lots(status) WHERE status = 0;

建完后执行VACUUM ANALYZE lots;刷新统计信息和visibility map，之后查询会直接走这个小索引的Index Only Scan，不需要回表，执行时间可以从十几秒降到百毫秒级别。

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