站在开发者角度聊聊索引日常

xiongcc2023年02月20日约 9 分钟

1前言

周六末一位读者在微信上问了我许多关于索引的事情，主要是站在开发者的视角，日常中想必各位也容易碰到类似的问题，昨晚赶着写了出来，分享一下

2正文

第一个问题是

我新创建一个表，先创建好索引再插入大批数据，和先插入大批数据再创建索引，这两中操作在后期使用中索引效率有啥区别吗？

乍一看这两个方式好像没有区别，数据都是同一批，实则不然，稍不注意就会掉坑里，产生巨大的性能差异。

简单试验一下，构造部分数据，为了演示效果我特意构造的是无序数据

postgres=# create table test(id int,info text);
CREATE TABLE                
postgres=# insert into test select i,md5(random()::text) from generate_series(1,10000000) as i;
INSERT 0 10000000
Time: 40430.341 ms (00:40.430)
postgres=# select info from test limit 10;  ---杂乱无章的数据
               info               
----------------------------------
 fb1f0721e1f6bf2c19ff859dfc8808c3
 fb9116a7a7fe5ea03900bf9ed8307847
 8626a2f5fb83037b49546a44c88d1eaf
 7a2c8eddea76d7fd09752aea5ff9a4e2
 65c04e47b95ea98e9d36ac5eabb7269d
 5872cac540f08b9933eb695dde0f6b08
 b9ebdf477ceaa29acaceed35b49eaaa7
 c09f7af078d9a9aba5b63f66f5480f14
 8dacb1d540b1a7f98e95b5fe0fc41164
 58da508cfea1fe3cde5a41e5f2664c1e
(10 rows)postgres=# \copy test to '/home/postgres/export.sql'
COPY 10000000

然后创建两个同样结构的表，只不过一个索引在导数前就建好，另外一个导数完成后再创建

postgres=# create table test1(id int,info text);  ---有索引
CREATE TABLE
postgres=# create index on test1(info);
CREATE INDEX
postgres=# create table test2(id int,info text);  ---无索引
CREATE TABLE
postgres=# \timing on
Timing is on.
postgres=# \copy test1 from '/home/postgres/export.sql';  ---接近8分钟
COPY 10000000
Time: 460090.898 ms (07:40.091)

可以看到导入 1000 万数据花费了接近 8 分钟之久，而且还是使用高效的 copy，同样 1000 万数据普通插入也仅需 40 秒而已。假如有持续关注的读者应该猜到了，这个问题我已经说过很多次，还是由于 FPI 导致的，数据库的时间全都浪费在了写 FPI 上。此处就不再过多分析了，👉🏻 从一个案例聊聊 FPI 的危害

1 群 2 群也有不少同志遇到过类似的问题

现在让我们换一下方式：导入之后再建索引

postgres=# \d test2
               Table "public.test2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 info   | text    |           |          | postgres=# \timing on
Timing is on.
postgres=# \copy test2 from '/home/postgres/export.sql';
COPY 10000000
Time: 17283.052 ms (00:17.283)
postgres=# create index on test2(info);
CREATE INDEX
Time: 99963.593 ms (01:39.964)

加起来接近 2 分钟，性能差距 4 倍，不难想象，随着数据量变大，性能差距也会越大。这种方式可以看到 FPI 的占比也基本没有了 👇🏻

除此之外，还有一点容易被人忽略的地方，没错，索引膨胀，也可以叫做索引的碎片率，准确数据需要使用 pgstattuple 插件，leaf_fragmentation 可以作为索引膨胀的依据，第一个碎片率为 0，第二个接近 50%，二者的大小也有差异

postgres=# select * from pgstatindex('test2_info_idx'::regclass);
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 3
index_size         | 590536704
root_block_no      | 12439
internal_pages     | 657
leaf_pages         | 71429
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 89.99
leaf_fragmentation | 0postgres=# select * from pgstatindex('test1_info_idx'::regclass);
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 3
index_size         | 764157952
root_block_no      | 15153
internal_pages     | 769
leaf_pages         | 92511
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 69.64
leaf_fragmentation | 49.92   ---碎片率接近50%
Time: 535.941 ms
postgres=# select pg_size_pretty(pg_relation_size('test1_info_idx'));
 pg_size_pretty 
----------------
 729 MB
(1 row)
Time: 0.390 ms
postgres=# select pg_size_pretty(pg_relation_size('test2_info_idx'));
 pg_size_pretty 
----------------
 563 MB
(1 row)
Time: 0.374 ms

为何二者有这样的差异呢？不难理解，索引 BTREE 本身是有序的，要维护索引的有序性，那么在创建的过程中，新插入的无序数据会导致叶子节点（leaf）不断分裂、合并，导致碎片，而且索引页也是会导致 FPI 的

参照之前的生产案例 👉🏻 膨胀真的不简单，膨胀的索引也会导致性能下降，因此第一个问题的答案也就出来了：基本上，先插入大批数据再创建索引的效率更高，因为数据十分有序的场景还是比较少

接着第二个问题：

但是我测试了一个表，先建好索引在同步数据整个耗时30分钟，先同步数据再创建索引整个耗时72分钟，前者明显快于后者，所以我想试试前者，但又怕前者索引效率不高，因为先插入数据再创建索引耗时太长了，甲方不同意，相反，先创建索引在插入数据耗时能快点

这里我构造了 5000 万的有序数据

postgres=# create table t1(id int);
CREATE TABLE
Time: 4.804 ms
postgres=# insert into t1 values(generate_series(1,50000000));
INSERT 0 50000000
Time: 100397.190 ms (01:40.397)
postgres=# \copy t1 to '/home/postgres/t1.sql';
COPY 50000000
Time: 35190.148 ms (00:35.190)
postgres=# create table t2(id int);
creaCREATE TABLE
postgres=# create index on t2(id);
CREATE INDEX
postgres=# create table t3(id int);
CREATE TABLE
postgres=# \timing on
Timing is on.
postgres=# \copy t2 from '/home/postgres/t1.sql' ;
COPY 50000000
Time: 201768.979 ms (03:21.769)
postgres=# \copy t3 from '/home/postgres/t1.sql' ;
COPY 50000000
Time: 47285.410 ms (00:47.285)
postgres=# create index on t3(id);
CREATE INDEX
Time: 70798.446 ms (01:10.798)
postgres=# select pg_size_pretty(pg_relation_size('t2_id_idx'));
 pg_size_pretty 
----------------
 1071 MB
(1 row)Time: 0.468 ms
postgres=# select pg_size_pretty(pg_relation_size('t3_id_idx'));
 pg_size_pretty 
----------------
 1071 MB
(1 row)
postgres=# select * from pgstatindex('t2_id_idx'::regclass);
-[ RECORD 1 ]------+-----------
version            | 4
tree_level         | 3
index_size         | 1123082240
root_block_no      | 116816
internal_pages     | 482
leaf_pages         | 136612
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 90.09
leaf_fragmentation | 0
Time: 5243.043 ms (00:05.243)
postgres=# select * from pgstatindex('t3_id_idx'::regclass);
-[ RECORD 1 ]------+-----------
version            | 4
tree_level         | 3
index_size         | 1123115008
root_block_no      | 81517
internal_pages     | 485
leaf_pages         | 136613
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 90.09
leaf_fragmentation | 0
Time: 6749.540 ms (00:06.750)

可以看到，先建索引再导数其实性能比先导数再建索引的效率低，并且二者大小相同，碎片率都为 0，其实论原理二者基本是类似的，所以二者的性能不会相差太大，至于这位读者说的相差了一倍至多，我猜可能是锁导致的吧，普通 create index 是5级锁，和写入冲突，因此要等待写入完成才得以创建，假如是 concurrently 的方式，还要等待多个阶段的事务提交（参照以前的文章 👉🏻 创建索引的各个阶段，你真的搞懂了吗），另外还取决于具体的数据类型，比如 numeric 创建索引的效率就要比 int4/int8 慢得多

CIC 总结来说流程就是

在系统表中插入索引的元数据，包括pg_class、pg_index，然后开启两个事务，进行两次扫描
开启事务1，拿到当前snapshot1。
扫描test表前，等待所有修改过test表（写入、删除、更新）的事务结束。
扫描test表，并建立索引。
结束事务1。
开启事务2，拿到当前snapshot2。
再次扫描test表前，等待所有修改过test表（写入、删除、更新）的事务结束。
在snapshot2之后启动的事务对test表执行的DML，会修改这个myidx的索引。
再次扫描test表，更新索引。（从TUPLE中可以拿到版本号，在snapshot1到snapshot2之间变更的记录，将其合并到索引）
上一步更新索引结束后，等待事务2之前开启的持有snapshot的事务结束。
结束索引创建。索引可见。

所以为什么能慢这么多还是要仔细分析一下才行。

那么再看看第三个问题：

2亿的表数据量，创建单个索引都是15分钟，max_parallel_maintenance_workers=8 maintenance_work_mem=8g ，还需要改啥参数能提高创建速度？

老生常谈的问题，如何提高索引创建的速度？对于大表，想必各位应该都被折磨过，吭哧吭哧不知道创建到哪一步了，在 pg_stat_progress_create_index 视图出来之前更是完全懵逼的状态，后面的版本就方便了，可以使用如下 SQL 查询 👇🏻

SELECT
    now(),
    query_start AS started_at,
    now() - query_start AS query_duration,
    format('[%s] %s', a.pid, a.query) AS pid_and_query,
    index_relid::regclass AS index_name,
    relid::regclass AS table_name,
    (pg_size_pretty(pg_relation_size(relid))) AS table_size,
    phase,
    nullif (wait_event_type, '') || ': ' || wait_event AS wait_type_and_event,
    current_locker_pid,
    (
        SELECT
            nullif (
            LEFT (query, 150), '') || '...'
        FROM
            pg_stat_activity a
        WHERE
            a.pid = current_locker_pid) AS current_locker_query,
    format('%s (%s of %s)', coalesce((round(100 * lockers_done::numeric / nullif (lockers_total, 0), 2))::text || '%', 'N/A'), coalesce(lockers_done::text, '?'), coalesce(lockers_total::text, '?')) AS lockers_progress,
    format('%s (%s of %s)', coalesce((round(100 * blocks_done::numeric / nullif (blocks_total, 0), 2))::text || '%', 'N/A'), coalesce(blocks_done::text, '?'), coalesce(blocks_total::text, '?')) AS blocks_progress,
    format('%s (%s of %s)', coalesce((round(100 * tuples_done::numeric / nullif (tuples_total, 0), 2))::text || '%', 'N/A'), coalesce(tuples_done::text, '?'), coalesce(tuples_total::text, '?')) AS tuples_progress,
    format('%s (%s of %s)', coalesce((round(100 * partitions_done::numeric / nullif (partitions_total, 0), 2))::text || '%', 'N/A'), coalesce(partitions_done::text, '?'), coalesce(partitions_total::text, '?')) AS partitions_progress
FROM
    pg_stat_progress_create_index p
    LEFT JOIN pg_stat_activity a ON a.pid = p.pid;

那么有哪些提升索引创建速度的手段呢？

并行，最容易想到，用资源换时间，调整 max_parallel_maintenance_workers，指定在 CREATE INDEX、CREATE TABLE AS、SELECT INTO 的并行数量，同时还要注意表级的并行度（parallel_workers，这个参数很容易被忽略）、全局的并行度（max_parallel_workers）、整个实例的后端进程数（max_worker_processes）都要大于等于该参数，至于实际创建过程中使用的并行进程可以查看 pg_stat_activity
内存，maintenance_work_mem，对于 BTREE 索引，必须对输入的数据进行排序，如果要排序的数据在 maintenance_work_mem 指定的内存中放置不下，就会溢出到磁盘中，这个值可以合理调大，该参数可以会话级调整，因此创建索引前手动调大一下即可

还有一个方式是分散 IO，简而言之，创建索引会产生大量的 WAL，导致大量 IO，假如把 WAL 单独放到其他存储上，再把索引和排序产生的 IO 进行分散，充分利用存储

test=# CREATE TABLESPACE indexspace LOCATION '/ssd1/tabspace1';
CREATE TABLESPACE
test=# CREATE TABLESPACE sortspace LOCATION '/ssd2/tabspace2';
CREATE TABLESPACE
test=# SET temp_tablespaces TO sortspace;   ---指定排序的临时数据涉及的IO
SET
test=# CREATE INDEX idx6 ON t_demo (data) TABLESPACE indexspace;  ---指定索引创建过程中涉及的IO
CREATE INDEX
Time: 408508.976 ms (06:48.509)

另外一个不太实用的技巧便是参照上方的实验，在迁移/导数前将数据先调整为有序，这样就可以降低排序的数据量，降低碎片率

3小结

总而言之，索引创建对于大表来说创建还是比较麻烦的，提速的手段实用的也就 并行 + 内存。

另外，定期重建释放碎片率以紧实是十分有必要的，膨胀导致问题的生产案例我已经遇到不下三次了。

4参考

https://www.cybertec-postgresql.com/en/postgresql-parallel-create-index-for-better-performance/

1前言

2正文

3小结

4参考

来自同一个声音

PGSimCity - PostgreSQL 工作原理可视化解析

从 Lakebase 到 LTAP，是旧饭新炒，还是新的技术范式？

AI 时代首选数据库：PostgreSQL 入门到进阶实战

pg-slide-harvester：自动收集 PG 大会演讲资料