PGSQL GIN索引“失效”

Rocky大约 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也是走索引，同时执行也恢复到秒回状态

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

实验

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

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万行数据）

系统推荐

随机毒鸡汤：算完工资的涨幅后，再算算猪肉的，就会发现自己连猪都不如。

PGSQL GIN索引“失效”

# 问题背景

# 实验

# 系统推荐

问题背景

实验

系统推荐