PGSQL GIN索引“失效”
问题背景
现在有一张标签表,主要有两个字段,member_id和tags。如下:
create table user_tags
(
id serial8 primary key,
member_id int8 not null ,
tags jsonb
);
create index concurrently on user_tags using gin (tags);
大致有5500多万行数据,每个member_id一行数据。tags字段就存储了这个member的所有标签。
大致结构如下:
{
"19": [
"5006268"
],
"标签ID": [
"标签值1","标签值2"
],
....
}
现在有个需求需要查询哪些用户被打上了标签A且标签值为固定的某个值,大致的查询语句如下:
select * from user_tags where tags @> '{"50":["5006268"]}';
表示查询被打上了50这个标签,且标签值为5006268的那些用户
这个查询讲道理会走gin索引,同时实际情况也确实走了gin索引,查询基本是秒回。
然后系统有个定时任务,每天会更新这张表的数据,大致更新过程如下:
-- 删除临时表
drop table if exists "user_tags_tmp";
-- 复制现有的表结构
create unlogged table if not exists user_tags_tmp (like user_tags including constraints including indexes including defaults);
--- 往user_tags_tmp表写数据(本案例中采用的是copy方法写入,每次大致写入5500多万行数据)
-- copy user_tags_tmp FROM 'data.csv' WITH (FORMAT csv, ESCAPE '\"', QUOTE '\"', NULL 'N/A')
set statement_timeout=15000;
drop table if exists user_tags_bak;
begin;
-- 重命名现有的表
alter table user_tags rename to user_tags_bak; ----A
-- 把新的表重命名为正式表
alter table user_tags_tmp rename to user_tags; ----B
end;
-- 注意:上面两个rename要放在一个事务里,否则并发情况下,A执行完后B执行完前,其他事务对user_tags表就会操作失败。
终于有一天,上面的查询语句突然变得很慢,导致应用端超时看不到数据
通过执行explain analyse发现居然没有走索引,这个就有点懵了
后面同事执行了一下 analyse verbose user_tags;
然后重新执行查询语句的explain analyse,发现开始走索引了,但还是没有恢复到之前的秒回状态。
后面查资料,当对gin索引进行大量的插入、更新操作时,系统会将这些操作的变化暂时存储在一个叫做 "pending list" 的结构中。这个结构旨在将多次小的索引更新合并成较大的批量更新,以减少磁盘 I/O 的开销。
为了保证索引能有效,在把数据写入_tmp表后,执行了一下
select gin_clean_pending_list('gin索引名');
这个执行后,explain analyse也是走索引,同时执行也恢复到秒回状态
在这个这个语句的过程中,表的增、删、改、查均不受影响
实验
先随机生成2000万行数据
INSERT INTO user_tags (member_id,tags) SELECT gen_random_uuid(),jsonb_build_object( 'name', md5(random()::text), 'age', (random() * 10000)::int, 'registered', NOW() - INTERVAL '1 day' * (random() * 800)::int ) FROM generate_series(1, 20000000); -- 这个tags字段没有按照上面的格式生成,说明问题就行 -- 这个sql大致用时:26 m 10 s
执行查询
select * from user_tags where tags @> '{"name":"0f665df9b5403be11ac9d765eb0d2a89"}'; -- 用时:65 ms (execution: 50 ms, fetching: 15 ms)
执行explain analyse
explain analyse select * from user_tags where tags @> '{"name":"0f665df9b5403be11ac9d765eb0d2a89"}'; Bitmap Heap Scan on user_tags (cost=67.50..7693.46 rows=2000 width=165) (actual time=0.079..0.080 rows=1 loops=1) " Recheck Cond: (tags @> '{""name"": ""0f665df9b5403be11ac9d765eb0d2a89""}'::jsonb)" Heap Blocks: exact=1 -> Bitmap Index Scan on user_tags_tags_idx1 (cost=0.00..67.00 rows=2000 width=0) (actual time=0.073..0.073 rows=1 loops=1) " Index Cond: (tags @> '{""name"": ""0f665df9b5403be11ac9d765eb0d2a89""}'::jsonb)" Planning Time: 0.803 ms Execution Time: 0.098 ms
执行 gin_clean_pending_list
select gin_clean_pending_list('user_tags_tags_idx1'); -- 用时:70 ms (execution: 50 ms, fetching: 20 ms) -- 输出:0
再次插入数据(但稍许不一样)
begin; SET gin_pending_list_limit TO '10000MB'; INSERT INTO user_tags (member_id,tags) SELECT gen_random_uuid(),jsonb_build_object( 'name', md5(random()::text), 'age', (random() * 10000)::int, 'registered', NOW() - INTERVAL '1 day' * (random() * 800)::int ) FROM generate_series(1, 20000000); commit ; -- 相比第一次insert,多了一个 SET gin_pending_list_limit TO '10000MB'; -- 用时:4 m 51 s 659 ms
gin_pending_list_limit 参数解释
- 当对gin索引进行更新的时候,会将数据写暂时存储在pending list中,以减少索引频繁更新。当pending list中的数据达到一定数量后,系统会将其内容合并到实际的gin索引中。
- gin_pending_list_limit 就是控制pending list的大小。默认情况下是4MB,把这个参数调大,可以让更多数据写入到pending list中,延迟合并操作,从而提供了数据插入性能。
- 如果需要尽快插入大批量数据,可以调高这个参数值。(从上面的用时情况就能看出)
5执行完后尽快执行查询
select * from user_tags where tags @> '{"name":"0f665df9b5403be11ac9d765eb0d2a89"}'; -- 用时:29 s 173 ms (execution: 29 s 156 ms, fetching: 17 ms) -- 用时:28 s 960 ms (execution: 28 s 939 ms, fetching: 21 ms)
执行explain analyse
explain analyse select * from user_tags where tags @> '{"name":"0f665df9b5403be11ac9d765eb0d2a89"}'; Gather (cost=1000.00..1185343.38 rows=4000 width=165) (actual time=44228.215..44229.401 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on user_tags (cost=0.00..1183943.38 rows=1667 width=165) (actual time=44152.806..44161.681 rows=0 loops=3) " Filter: (tags @> '{""name"": ""0f665df9b5403be11ac9d765eb0d2a89""}'::jsonb)" Rows Removed by Filter: 13333333 Planning Time: 0.137 ms Execution Time: 44229.432 ms
可以发现同一个sql 用时相差巨大,一个走了索引一个没有走索引.
等了一段时间后,估计2-3小时(这期间啥也没有动),再执行查询语句就恢复到秒回了,同时explain analyse 也显示走索引了
此时执行
select gin_clean_pending_list('user_tags_tags_idx1'); -- 这个就是把pending list里的数据写入到实际的gin索引中 -- 此时执行时,这个sql秒回,返回值0
再次执行5的sql
begin; SET gin_pending_list_limit TO '10000MB'; INSERT INTO user_tags (member_id,tags) SELECT gen_random_uuid(),jsonb_build_object( 'name', md5(random()::text), 'age', (random() * 10000)::int, 'registered', NOW() - INTERVAL '1 day' * (random() * 800)::int ) FROM generate_series(1, 20000000); commit ; -- 用时:4 m 45 s 478 ms
再次执行上面的查询和explain,查询又变慢了,又没有走索引了。这里就不贴执行结果了。
在系统自动恢复gin索引前,手动触发一下
select gin_clean_pending_list('user_tags_tags_idx1'); -- 这次就不是秒回了,用时:反正很久,几十分钟肯定有的
在14执行期间,可以对表进行增、删、改、查操作
等待14执行完成后,在执行查询又会回到秒回(到这一步的时候,表数据有6000万行数据)
系统推荐
- JVM垃圾收集器
- MySQL常用命令
- ES6.2.3(3节点)数据迁移到ES7.4.1(5节点)
- JVM参数设置
- Markdown Mermaid画图实例
- IO相关
- Thread & ExecutorService & ThreadPoolExecutor 总览
- 不重启 JVM,如何替换掉已经加载的类?
- MyBatis xml特殊字符处理
- Lombok的Accessors导致EasyExcel读取失败
- vuepress-theme-hope使用心得
- vuepress-theme-hope 添加谷歌广告
- 随机毒鸡汤:你记得关于TA所有的事,但就是记不住,TA不喜欢你。