PostgreSQL GIN 索引原理、应用场景与最佳实践

值 1 → 行1, 行5, 行10
值 3 → 行1, 行2, 行7
值 5 → 行1, 行3, 行8

gin index structure:
┌─────────────┬──────────────────────┐
│    key      │    posting list      │
├─────────────┼──────────────────────┤
│   "apple"   │  (1,2), (5,3), ...   │
│   "banana"  │  (2,1), (7,4), ...   │
│     42      │  (3,2), (9,1), ...   │
└─────────────┴──────────────────────┘

-- 数组操作
select * from products where tags @> array['electronics'];  -- 包含
select * from products where tags && array['sale', 'discount']; -- 任一匹配
-- jsonb 操作
select * from users where profile @> '{"age": 25}';  -- 包含键值对
select * from users where profile ? 'email';         -- 包含键
select * from users where profile ?| array['phone', 'email']; -- 任一键存在
-- 全文检索
select * from articles where content_tsvector @@ to_tsquery('english', 'database & performance');

-- 数组操作
select * from products where tags @> array['electronics'];  -- 包含
select * from products where tags && array['sale', 'discount']; -- 任一匹配
-- jsonb 操作
select * from users where profile @> '{"age": 25}';  -- 包含键值对
select * from users where profile ? 'email';         -- 包含键
select * from users where profile ?| array['phone', 'email']; -- 任一键存在
-- 全文检索
select * from articles where content_tsvector @@ to_tsquery('english', 'database & performance');

insert process with fastupdate=on:
┌─────────────┐    ┌──────────────────┐    ┌─────────────┐
│   new data  │───▶│  pending list    │───▶│  main index │
└─────────────┘    └──────────────────┘    └─────────────┘
                          ▲
                          │
                   gin_clean_pending_list()

-- 查询: select * from table where col @> array[1,2];
-- gin 查询步骤：
-- 1. 查找值 1 的 posting list: [row1, row3, row5]
-- 2. 查找值 2 的 posting list: [row1, row2, row5]
-- 3. 取交集: [row1, row5]
-- 4. 返回结果

-- 表结构
create table products (
    id serial primary key,
    name text,
    tags text[],  -- 标签数组
    price numeric
);
-- 插入测试数据
insert into products (name, tags, price) values
('iphone 15', array['electronics', 'phone', 'apple'], 999),
('macbook pro', array['electronics', 'laptop', 'apple'], 2499),
('nike air max', array['clothing', 'shoes', 'sport'], 120);
-- 创建 gin 索引
create index idx_products_tags on products using gin (tags);
-- 查询包含特定标签的商品
explain analyze 
select * from products where tags @> array['electronics'];
-- index scan using idx_products_tags on products
-- 查询包含任一标签的商品
explain analyze 
select * from products where tags && array['phone', 'laptop'];
-- bitmap heap scan with bitmap index scan

-- 表结构
create table users (
    id serial primary key,
    name text,
    profile jsonb  -- 用户配置
);
-- 插入数据
insert into users (name, profile) values
('alice', '{"age": 25, "city": "beijing", "skills": ["java", "python"]}'),
('bob', '{"age": 30, "city": "shanghai", "skills": ["javascript", "react"]}');
-- 创建 gin 索引
create index idx_users_profile on users using gin (profile);
-- 查询年龄为25的用户
explain analyze 
select * from users where profile @> '{"age": 25}';
-- 查询具有特定技能的用户
explain analyze 
select * from users where profile->'skills' ? 'java';
-- 创建特定路径的 gin 索引（更高效）
create index idx_users_skills on users using gin ((profile->'skills'));

-- 1. 针对特定 json 路径创建索引（比全 jsonb 索引更高效）
create index idx_users_email on users using gin ((profile->>'email'));  -- 错误！
create index idx_users_email on users using gin ((profile->'email'));   -- 正确
-- 2. 使用表达式索引
create index idx_users_age on users using gin ((profile->'age'));
-- 3. 复合条件考虑部分索引
create index idx_active_users on users using gin (profile) 
where status = 'active';

-- 查看当前 gin 参数
show gin_fuzzy_search_limit;
show gin_pending_list_limit;
-- 会话级调整（临时）
set gin_pending_list_limit = '16mb';
set gin_fuzzy_search_limit = 10000;
-- 系统级调整（postgresql.conf）
# gin_pending_list_limit = 16mb
# gin_fuzzy_search_limit = 10000

-- 手动清理待处理列表（当 fastupdate=on 时）
select gin_clean_pending_list('idx_products_tags');
-- 监控 gin 索引状态
select * from pg_stat_user_indexes 
where indexname = 'idx_products_tags';
-- 定期 reindex（特别是在大量更新后）
reindex index idx_products_tags;

-- 对于写入密集型应用，考虑关闭 fastupdate
create index idx_write_heavy on table using gin (col) with (fastupdate = off);
-- 对于读取密集型应用，保持 fastupdate=on（默认）
-- 但监控 pending list 大小，避免查询性能下降

-- 确保使用正确的操作符
-- ✅ 正确：profile ? 'email'
-- ❌ 错误：profile->>'email' is not null
-- 检查索引是否创建正确
\d+ users  -- 查看索引信息
-- 强制使用索引（调试用）
set enable_seqscan = off;

-- 方案1：批量操作后手动清理
begin;
insert into table ...;  -- 批量插入
select gin_clean_pending_list('index_name');
commit;
-- 方案2：临时关闭 fastupdate
drop index idx_name;
create index idx_name on table using gin (col) with (fastupdate = off);

-- 方案1：只索引必要字段
-- 而不是 create index on table using gin (jsonb_col);
-- 使用 create index on table using gin ((jsonb_col->'needed_field'));
-- 方案2：定期 reindex
reindex index concurrently idx_name;
-- 方案3：考虑分区表