GIN 索引加速 JSONB 查询

没有索引的 JSONB 查询是全表扫描。数据量一大，@> 包含查询就会变成性能噩梦。GIN（Generalized Inverted Index）是专为 JSONB 设计的索引类型，能让包含查询提速 100 倍以上。

为什么 JSONB 需要 GIN 索引

-- 假设 products 表有 100 万行，attributes 列是 JSONB
-- 没有索引时，这个查询是全表扫描：
EXPLAIN ANALYZE
SELECT * FROM products WHERE attributes @> '{"color": "Silver"}';
-- → Seq Scan on products  (cost=0.00..28459.00 rows=1000 width=256)
--   Filter: (attributes @> '{"color": "Silver"}'::jsonb)
--   actual time=0.025..2891.234 rows=1200 loops=1
-- 加了 GIN 索引后：
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
EXPLAIN ANALYZE
SELECT * FROM products WHERE attributes @> '{"color": "Silver"}';
-- → Bitmap Heap Scan on products  (cost=28.00..312.50 rows=1000)
--   Recheck Cond: (attributes @> '{"color": "Silver"}'::jsonb)
--   → Bitmap Index Scan on idx_products_attributes
--     actual time=0.213..0.213 rows=1200 loops=1
-- Planning Time: 0.189 ms
-- Execution Time: 1.876 ms   ← 从 2891ms 降到 2ms

创建 GIN 索引

方法一：全字段 GIN 索引（最常用）

-- 对整个 JSONB 列建 GIN 索引
-- 支持：@>、<@、?、?|、?& 等所有 JSONB 操作符
CREATE INDEX idx_products_attributes
ON products USING GIN (attributes);
-- 并发创建（生产环境，不锁表）
CREATE INDEX CONCURRENTLY idx_products_attributes
ON products USING GIN (attributes);

方法二：jsonb_path_ops 操作符类（更小更快，但功能有限）

-- jsonb_path_ops：只支持 @> 操作符，但索引更小、查询更快
CREATE INDEX idx_products_attributes_path
ON products USING GIN (attributes jsonb_path_ops);
-- 对比两种操作符类：
-- jsonb_ops（默认）：支持 @>、<@、?、?|、?& 所有操作符
-- jsonb_path_ops：只支持 @>，但索引大小减少 30-50%，查询更快
-- 如果你只用 @> 查询（最常见），优先用 jsonb_path_ops

方法三：对 JSONB 内的特定键建 B-tree 索引

-- 如果某个 JSONB 键被频繁单独查询，建表达式索引更高效
-- 比如经常按 color 过滤：
CREATE INDEX idx_products_color
ON products ((attributes ->> 'color'));
-- 查询时必须用 ->> 操作符匹配
SELECT * FROM products WHERE attributes ->> 'color' = 'Silver';
-- → Index Scan using idx_products_color ✅
-- 注意：下面的查询不会用这个索引：
SELECT * FROM products WHERE attributes @> '{"color": "Silver"}';
-- 这需要 GIN 索引

GIN 索引支持的查询类型

-- 准备数据
CREATE TABLE events (
id      BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL
);
INSERT INTO events (payload) VALUES
('{"type": "purchase", "user_id": 101, "tags": ["vip", "mobile"], "amount": 299.9}'),
('{"type": "signup",   "user_id": 102, "tags": ["new"],           "country": "MY"}'),
('{"type": "purchase", "user_id": 103, "tags": ["mobile"],        "amount": 89.0}');
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- ✅ @> 包含查询（GIN 支持）
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';
SELECT * FROM events WHERE payload @> '{"tags": ["vip"]}';  -- 数组包含
-- ✅ ? 键存在（GIN 支持）
SELECT * FROM events WHERE payload ? 'country';
-- ✅ ?| 任意键存在（GIN 支持）
SELECT * FROM events WHERE payload ?| ARRAY['country', 'amount'];
-- ✅ ?& 所有键存在（GIN 支持）
SELECT * FROM events WHERE payload ?& ARRAY['type', 'user_id'];
-- ❌ ->> 比较不使用 GIN（需要表达式索引）
SELECT * FROM events WHERE payload ->> 'type' = 'purchase';
-- 这不使用 GIN 索引！需要 CREATE INDEX ON events ((payload ->> 'type'))

JSONB 数组的 GIN 索引

GIN 索引对 JSONB 中的数组特别有效：

-- 产品标签场景
CREATE TABLE articles (
id   BIGSERIAL PRIMARY KEY,
tags JSONB  -- 存储为 ["postgresql", "database", "performance"]
);
INSERT INTO articles (tags) VALUES
('["postgresql", "database", "performance"]'),
('["mysql", "database"]'),
('["postgresql", "jsonb", "gin"]');
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
-- 找出包含 "postgresql" 标签的文章
SELECT * FROM articles WHERE tags @> '"postgresql"';
-- 找出同时包含 "postgresql" 和 "gin" 的文章
SELECT * FROM articles WHERE tags @> '["postgresql", "gin"]';
-- 找出至少包含一个指定标签的文章
-- 注意：?| 对于 JSONB 数组要转换格式
SELECT * FROM articles
WHERE tags ?| ARRAY['postgresql', 'mysql'];

GIN 索引维护与性能监控

-- 查看 GIN 索引大小
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexname LIKE '%gin%' OR indexname LIKE '%jsonb%'
ORDER BY pg_relation_size(indexrelid) DESC;
-- GIN 索引有一个待处理列表（pending list）机制：
-- 新插入的数据先进入 pending list，不立即插入 GIN 树
-- 这让写入更快，但查询时需要同时扫描 pending list
-- 当 pending list 过大时，PostgreSQL 会自动 VACUUM 清理
-- 手动清理 GIN pending list（如果需要即时性能）
-- gin_pending_list_limit 控制触发清理的阈值（默认 4MB）
SELECT gin_clean_pending_list('idx_events_payload');
-- 查看 pending list 大小
SELECT pg_relation_size(oid), relname
FROM pg_class
WHERE relname = 'idx_events_payload';

实战：多条件 JSONB 查询的索引策略

-- 电商产品搜索场景
-- 用户可以按多个属性筛选：颜色、内存、存储、价格区间
CREATE TABLE products (
id         BIGSERIAL PRIMARY KEY,
name       TEXT NOT NULL,
price      NUMERIC(10,2) NOT NULL,
category   TEXT NOT NULL,
attributes JSONB
);
-- 策略：组合使用 GIN + 普通索引
-- 1. GIN 索引处理属性过滤
CREATE INDEX idx_products_attrs ON products USING GIN (attributes jsonb_path_ops);
-- 2. B-tree 索引处理 category 和 price 过滤
CREATE INDEX idx_products_category_price ON products (category, price);
-- 3. 高频单键过滤，建表达式索引
CREATE INDEX idx_products_color ON products ((attributes ->> 'color'));
CREATE INDEX idx_products_storage ON products ((attributes ->> 'storage'));
-- 查询示例：多条件搜索
EXPLAIN ANALYZE
SELECT id, name, price
FROM products
WHERE category = 'laptop'
AND price BETWEEN 3000 AND 8000
AND attributes @> '{"color": "Silver"}'
AND attributes @> '{"memory": "16GB"}'
ORDER BY price
LIMIT 20;
-- PostgreSQL 会根据代价选择最优的索引组合

GIN vs 其他索引类型对比

常见误区

-- ❌ 误区：GIN 索引可以加速所有 JSONB 查询
-- 下面的查询不使用 GIN：
SELECT * FROM products WHERE attributes ->> 'color' = 'Silver';
-- ✅ 正确：用表达式索引
CREATE INDEX ON products ((attributes ->> 'color'));
-- ❌ 误区：对小表也需要 GIN 索引
-- 表小于 1 万行时，全表扫描可能比索引扫描更快
-- ❌ 误区：jsonb_path_ops 和 jsonb_ops 可以互换
-- jsonb_path_ops 不支持 ? 键存在查询
SELECT * FROM products WHERE attributes ? 'discount';
-- 如果索引是 jsonb_path_ops，这个查询不会走索引！

下一节：JSONB 的增删改操作符——掌握 jsonb_set、||、-、#- 等操作符，在不替换整个对象的情况下精确修改 JSONB 中的嵌套字段。