PostgreSQL JSONB 与全文检索组合优化

--- title: PostgreSQL JSONB 与全文检索组合优化 keywords: - PostgreSQL - JSONB - GIN 索引 - pg_trgm - 全文检索 description: 在 JSONB 场景中组合使用 GIN、pg_trgm 与全文检索，提升查询性能并保证可维护性。 tags: - GIN 索引 - JSONB - PostgreSQL - pg_trgm - 全文检索 - 性能优化 - 数据库 categories: - 文章资讯 - 技术教程 --- # PostgreSQL JSONB 与全文检索组合优化 ## 基础索引 ```sql -- 为 JSONB 字段创建 GIN 索引 CREATE INDEX idx_data_gin ON items USING gin (data jsonb_path_ops); -- 启用三元组扩展以优化模糊匹配 CREATE EXTENSION IF NOT EXISTS pg_trgm; ``` ## 全文检索 ```sql -- 建立向量与索引 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` 验证查询计划与索引命中 ## 总结 针对不同查询特性选择合适索引与检索方式，可在保持灵活性的同时获得稳定的性能收益。