PostgreSQL为何不使用覆盖索引？多属性索引问题咨询

先梳理下你的测试场景：
你创建了GTable(id int, groupby int, orderby int, padding varchar(1000))，并在SQL Server 2016、PostgreSQL 9.6.1、Oracle 12c中导入了近似数据。PostgreSQL的建表和插入脚本如下：

CREATE TABLE GTable ( id INT NOT NULL, groupby INT NOT NULL, orderby INT NOT NULL, padding VARCHAR(1000) NOT NULL );
INSERT INTO GTable SELECT s, s % 100, s % 10000, RPAD('Value ' || s || ' ' , 500, '*') FROM generate_series(1, 100000) s;

创建复合索引后：

CREATE INDEX ix_gtable_groupby_orderby ON gtable(groupby, orderby);

执行查询：

SELECT groupby, max(orderby) gmax FROM gtable GROUP BY groupby;

Oracle和SQL Server会利用这个索引，但PostgreSQL默认走全表扫描，执行计划如下：

"HashAggregate (cost=8643.00..8643.10 rows=10 width=8) (actual time=54.906..54.907 rows=10 loops=1)"
" Group Key: groupby"
" Buffers: shared hit=4410 read=2733"
" -> Seq Scan on gtable (cost=0.00..8143.00 rows=100000 width=8) (actual time=0.077..32.065 rows=100000 loops=1)"
" Buffers: shared hit=4410 read=2733"
"Planning time: 0.096 ms"
"Execution time: 54.955 ms"

一、PostgreSQL为何默认不使用该索引？ #

核心原因是PostgreSQL 9.6的查询优化器成本估算逻辑：
对于你的查询，虽然(groupby, orderby)索引包含了计算所需的所有字段，但优化器认为全表扫描+哈希聚合的成本更低。

• 你的数据分布是groupby有100个不同值，每个group对应1000条记录（100000/100）。这种中等基数的group，哈希聚合的内存占用和CPU开销在优化器的估算中，比走索引后做排序聚合要划算。
• 另外，PostgreSQL的优化器在处理MAX()聚合时，不会像Oracle或SQL Server那样优先利用有序索引直接定位每个group的最大值，而是默认走全表聚合路径，除非索引的成本估算远低于全表扫描。

二、不同数据分布下会使用索引吗？ #

会的，优化器的选择完全基于成本估算，当数据分布变化导致索引路径成本更低时，就会选择索引：

• 高基数groupby：比如每个groupby值只对应1条记录（100000个不同group），此时走索引做排序聚合的成本会远低于哈希聚合，优化器会选择索引扫描。
• 极低基数groupby：比如只有2个不同的groupby值，每个group对应50000条记录，此时优化器可能会认为走索引定位每个group的最大值更高效。
• 数据有序性匹配：如果你的查询加上ORDER BY groupby，优化器可能更倾向于利用索引的有序性，避免额外排序。

三、强制避免堆扫描后为何仍访问表？ #

按建议强制避免堆扫描后，执行计划显示用了Bitmap Index Scan但还是做了Bitmap Heap Scan：

"HashAggregate (cost=17415.29..17415.39 rows=10 width=8) (actual time=59.014..59.016 rows=10 loops=1)"
" Group Key: groupby"
" Buffers: shared hit=14561"
" -> Bitmap Heap Scan on gtable (cost=1629.29..16915.29 rows=100000 width=8) (actual time=10.396..36.956 rows=100000 loops=1)"
" Heap Blocks: exact=14286"
" Buffers: shared hit=14561"
" -> Bitmap Index Scan on ix_gtable_orderby_groupby (cost=0.00..1604.29 rows=100000 width=0) (actual time=7.975..7.975 rows=100000 loops=1)"
" Buffers: shared hit=275"
"Planning time: 0.127 ms"
"Execution time: 59.260 ms"

这不是PostgreSQL多属性索引的问题，而是因为你没触发Index Only Scan（仅索引扫描）。要触发它需要两个条件：

1. 表的**可见性映射（VM）**是最新的：也就是说没有未提交的事务修改过这些数据，或者你执行过VACUUM ANALYZE gtable;来更新VM。
2. 索引包含了查询所需的所有字段：你的(groupby, orderby)索引已经满足这一点。

你可以尝试执行VACUUM ANALYZE gtable;之后再跑查询，优化器应该会选择Index Only Scan，此时就不会访问堆表了。

另外，PostgreSQL 10及以后的版本对聚合查询的索引利用逻辑做了优化，类似场景下更倾向于选择索引路径，如果你能升级版本，也能看到更好的表现。

内容的提问来源于stack exchange，提问作者Radim Bača