别再乱建联合索引了
·
简介
在 PostgreSQL 的世界里，索引从来不是“有没有”的问题，而是“值不值”的问题。随着 PostgreSQL 近几个大版本（12 → 17）的持续演进，索引相关的优化逐渐从“单点能力增强”，转向“在有限资源约束下的整体效率提升”，如PG14的索引去重、PG16的多列综合评估等。虽然内核上对于多列索引的算法做了很多优化，但是其索引顺序依然会对SQL的检索性能产生较大影响。
在最新的版本算法中，这些变化，使得我们重新审视一个“老话题”——联合索引（Composite Index）到底该怎么建，字段顺序该如何选。以postgresql-16为例
示例
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id int,
status int,
create_time timestamp
);
--字段反序创建两个索引
-- 索引 A
CREATE INDEX idx_a ON orders (user_id, status);
-- 索引 B
CREATE INDEX idx_b ON orders (status, user_id);
-- 随机插入100万的数据
INSERT INTO orders (user_id, status, create_time)
SELECT
(random() * 10000)::int, -- 用户很多
(random() * 5)::int -- 状态很少（0~4）
now() - (random() * interval '30 days')
FROM generate_series(1, 1000000);
查看页面数据排序规则
这里我们拿数据0页数据进行举例
select ctid,* from orders where ctid::varchar like '(0,%';
-- 或者
CREATE EXTENSION pageinspect;
SELECT * FROM heap_page_items(get_raw_page('orders', 0));
PG采用的堆表规则，ID为单调递增的，所以表中的数据会看似以ID升序。
看一下索引a的排序规则
SELECT * FROM bt_page_items('idx_a', 1); --CREATE INDEX idx_a ON orders (user_id, status);
itemoffset 该 tuple 在索引页内的 slot 编号
ctid 索引 tuple 自己的 TID（在索引里的物理位置）
itemlen 索引 tuple 占用的字节数
nulls 索引键中是否包含 NULL
vars 是否有变长字段
data 索引键值的二进制表示（核心）
dead 是否是 dead index tuple
htid 指向堆表的 ctid（Heap TID）
tids 同一个索引键下的多个 heap TID（重复键）
--转换对应索引键上的索引键值
WITH idx_data AS (
SELECT
htid,
tids,
decode(data, 'hex') AS data_bytes
FROM bt_page_items('idx_a', 1)
)
SELECT
htid,
-- user_id：小端序前 4 字节
get_byte(data_bytes,0)
+ get_byte(data_bytes,1)*256
+ get_byte(data_bytes,2)*65536
+ get_byte(data_bytes,3)*16777216 AS user_id,
-- status：小端序后 4 字节
get_byte(data_bytes,4)
+ get_byte(data_bytes,5)*256
+ get_byte(data_bytes,6)*65536
+ get_byte(data_bytes,7)*16777216 AS status
FROM idx_data;
看一下索引b的排序规则
SELECT * FROM bt_page_items('idx_b', 1); --CREATE INDEX idx_a ON orders (user_id, status);
itemoffset 该 tuple 在索引页内的 slot 编号
ctid 索引 tuple 自己的 TID（在索引里的物理位置）
itemlen 索引 tuple 占用的字节数
nulls 索引键中是否包含 NULL
vars 是否有变长字段
data 索引键值的二进制表示（核心）
dead 是否是 dead index tuple
htid 指向堆表的 ctid（Heap TID）
tids 同一个索引键下的多个 heap TID（重复键）
--转换对应索引键上的索引键值
WITH idx_data AS (
SELECT
htid,
tids,
decode(data, 'hex') AS data_bytes
FROM bt_page_items('idx_b', 1)
)
SELECT
htid,
-- user_id：小端序前 4 字节
get_byte(data_bytes,0)
+ get_byte(data_bytes,1)*256
+ get_byte(data_bytes,2)*65536
+ get_byte(data_bytes,3)*16777216 AS user_id,
-- status：小端序后 4 字节
get_byte(data_bytes,4)
+ get_byte(data_bytes,5)*256
+ get_byte(data_bytes,6)*65536
+ get_byte(data_bytes,7)*16777216 AS status
FROM idx_data;
索引a 和索引b 其索引字段的创建字段先后顺序不同，其索引在叶上的排序规则也会有所区别。这在索引扫描上。如果顺序不同，其扫描的选择性会有较大的折损。在联合索引中，字段顺序应优先选择选择性高的字段放在最左边，以提高索引扫描效率。低选择性字段即使放在索引最左列，也可能无法显著减少访问的堆页数。
基于优化器的计算规则下，也会自动选择最优的索引进行scan。
postgres=# explain analyze select * from orders where user_id =0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on orders (cost=5.19..365.40 rows=99 width=24) (actual time=0.105..0.311 rows=56 loops=1)
Recheck Cond: (user_id = 0)
Heap Blocks: exact=56
-> Bitmap Index Scan on idx_a (cost=0.00..5.17 rows=99 width=0) (actual time=0.072..0.072 rows=56 loops=1)
Index Cond: (user_id = 0)
Planning Time: 0.407 ms
Execution Time: 0.447 ms
(7 rows)
postgres=# explain analyze select * from orders where status =0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on orders (cost=1277.62..8894.71 rows=99767 width=24) (actual time=31.698..88.916 rows=99534 loops=1)
Recheck Cond: (status = 0)
Heap Blocks: exact=6370
-> Bitmap Index Scan on idx_b (cost=0.00..1252.68 rows=99767 width=0) (actual time=29.004..29.005 rows=99534 loops=1)
Index Cond: (status = 0)
Planning Time: 0.211 ms
Execution Time: 93.421 ms
(7 rows)
postgres=# drop index idx_b ;
DROP INDEX
postgres=# explain analyze select * from orders where status =0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on orders (cost=0.00..18870.00 rows=99733 width=24) (actual time=0.043..331.153 rows=99534 loops=1)
Filter: (status = 0)
Rows Removed by Filter: 900466
Planning Time: 0.226 ms
Execution Time: 337.093 ms
(5 rows)
PostgreSQL 16 没有官方支持 index skip scan，严格遵循最左前缀。PG17 才开始实验支持类似 skip scan。在此之前PostgreSQL 联合索引遵循最左前缀原则，因此索引 (a,b) 可用于查询 a=? 或 a=? AND b=?，但不能直接用于 b=? 查询。12 版本之前，联合索引主要依赖最左列的统计信息，优化器可能无法准确估算多列选择性，但索引本身仍然有效。
在postgresql-16，创建联合索引会不会有用呢？我们来看一下索引成本
relpages * seq_page_cost + reltuples * (cpu_tuple_cost + cpu_operator_cost)
WITH stats AS (
SELECT
c.relpages,
c.reltuples,
current_setting('seq_page_cost')::numeric AS seq_page_cost,
current_setting('cpu_tuple_cost')::numeric AS cpu_tuple_cost,
current_setting('cpu_operator_cost')::numeric AS cpu_operator_cost
FROM pg_class c
WHERE c.relname = 'orders'
)
SELECT
relpages * seq_page_cost AS disk_run_cost,
reltuples * (cpu_tuple_cost + cpu_operator_cost) AS cpu_run_cost,
relpages * seq_page_cost
+ reltuples * (cpu_tuple_cost + cpu_operator_cost) AS total_cost_estimate
FROM stats;
disk_run_cost | cpu_run_cost | total_cost_estimate
---------------+--------------+---------------------
6370 | 12500 | 18870
(1 row)
postgres=# explain analyze select * from orders where status =0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on orders (cost=0.00..18870.00 rows=99733 width=24) (actual time=1.293..674.209 rows=99534 loops=1)
Filter: (status = 0)
Rows Removed by Filter: 900466
Planning Time: 8.855 ms
Execution Time: 681.312 ms
(5 rows)
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
3
0- 0
已为社区贡献3条内容
所有评论(0)