PostgreSQL JSONB 与全文检索组合优化

PostgreSQL JSONB 与全文检索组合优化基础索引-- 为 JSONB 字段创建 GIN 索引

CREATE INDEX idx_data_gin ON items USING gin (data jsonb_path_ops);

-- 启用三元组扩展以优化模糊匹配

CREATE EXTENSION IF NOT EXISTS pg_trgm;

全文检索-- 建立向量与索引

ALTER TABLE articles ADD COLUMN tsv tsvector;

UPDATE articles SET tsv = to_tsvector('simple', coalesce(title,'') || ' ' || coalesce(content,''));

CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

-- 查询

SELECT id, title FROM articles WHERE tsv @@ to_tsquery('simple', 'web & vitals');

组合查询策略对结构化条件使用 JSONB + GIN对文本搜索使用 tsvector + GIN对模糊匹配使用 `LIKE` + `pg_trgm`维护与监控保持统计信息更新：`ANALYZE`，必要时 `VACUUM`通过 `EXPLAIN ANALYZE` 验证查询计划与索引命中总结针对不同查询特性选择合适索引与检索方式，可在保持灵活性的同时获得稳定的性能收益。