PGSQL GIN索引“失效”

RockyPGSQLGIN索引PGSQLgin_clean_pending_list大约 6 分钟

问题背景

现在有一张标签表,主要有两个字段,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也是走索引,同时执行也恢复到秒回状态

在这个这个语句的过程中,表的增、删、改、查均不受影响

实验

  1. 先随机生成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
    
  2. 执行查询

    select * from user_tags where tags @> '{"name":"0f665df9b5403be11ac9d765eb0d2a89"}';
    
    -- 用时:65 ms (execution: 50 ms, fetching: 15 ms)
    
  3. 执行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
    
  4. 执行 gin_clean_pending_list

    select gin_clean_pending_list('user_tags_tags_idx1');
    
    -- 用时:70 ms (execution: 50 ms, fetching: 20 ms)
    -- 输出:0
    
  5. 再次插入数据(但稍许不一样)

    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
    
  6. gin_pending_list_limit 参数解释

    1. 当对gin索引进行更新的时候,会将数据写暂时存储在pending list中,以减少索引频繁更新。当pending list中的数据达到一定数量后,系统会将其内容合并到实际的gin索引中。
    2. gin_pending_list_limit 就是控制pending list的大小。默认情况下是4MB,把这个参数调大,可以让更多数据写入到pending list中,延迟合并操作,从而提供了数据插入性能。
    3. 如果需要尽快插入大批量数据,可以调高这个参数值。(从上面的用时情况就能看出)
  7. 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)
    
  8. 执行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
    
    
  9. 可以发现同一个sql 用时相差巨大,一个走了索引一个没有走索引.

  10. 等了一段时间后,估计2-3小时(这期间啥也没有动),再执行查询语句就恢复到秒回了,同时explain analyse 也显示走索引了

  11. 此时执行

    select gin_clean_pending_list('user_tags_tags_idx1');
    
    -- 这个就是把pending list里的数据写入到实际的gin索引中
    
    -- 此时执行时,这个sql秒回,返回值0
    
  12. 再次执行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
    
  13. 再次执行上面的查询和explain,查询又变慢了,又没有走索引了。这里就不贴执行结果了。

  14. 在系统自动恢复gin索引前,手动触发一下

    select gin_clean_pending_list('user_tags_tags_idx1');
    
    -- 这次就不是秒回了,用时:反正很久,几十分钟肯定有的
    
  15. 在14执行期间,可以对表进行增、删、改、查操作

  16. 等待14执行完成后,在执行查询又会回到秒回(到这一步的时候,表数据有6000万行数据)


系统推荐









  • 随机毒鸡汤:你记得关于TA所有的事,但就是记不住,TA不喜欢你​​​​。