PostgreSQL13 大量多语言GIN索引导致视图查询规划耗时过高

PostgreSQL 13的查询规划器在处理视图展开后的查询树时，会遍历关联表的所有索引逐一校验适用性。你创建的多语言函数式GIN索引包含固定词典参数和JSON路径提取逻辑，单条索引的校验开销远高于普通字段索引，150+条索引的校验开销累加后就导致了180ms的规划耗时。直接执行底层SQL时规划器可以快速跳过无关的函数索引，因此不会触发该问题。

• 改造现有多语言索引为部分索引（改造成本最低）
  给每个多语言函数索引增加对应语言路径存在的过滤条件，示例如下：

  CREATE INDEX nodes_label_sv_idx
  ON nodes
  USING GIN (to_tsvector('swedish_text', data #>> '{label,sv}'))
  WHERE data #>> '{label,sv}' IS NOT NULL;
  

  新增WHERE条件后，规划器校验索引适用性时会先判断查询是否满足部分索引的谓词条件，你的业务查询完全不涉及data字段，会直接跳过所有多语言索引的校验，规划耗时可降低90%以上，不需要修改任何业务搜索逻辑。
• 合并多语言索引减少总索引数量
  废弃单语言单路径的索引设计，改用统一的生成列存储所有多语言搜索向量，仅需1个GIN索引即可支持所有语言的搜索需求，示例如下：

  -- 新增生成列存储所有语言的tsvector
  ALTER TABLE nodes ADD COLUMN search_tsv tsvector GENERATED ALWAYS AS (
    jsonb_path_query_first(data, '$.** ? (@.type() == "string")')::text::tsvector
  ) STORED;
  -- 建单个GIN索引
  CREATE INDEX nodes_search_idx ON nodes USING GIN(search_tsv);
  

  总索引数从150+降到个位数，从根源上消除索引校验的额外开销。
• 启用查询计划缓存
  对于这类高频执行的固定模式查询，开启服务器端计划缓存复用执行计划，完全跳过重复规划步骤：
  1. 全局调整postgresql.conf参数：plan_cache_mode = force_generic_plan
  2. 业务侧使用PREPARE语句预编译查询，后续调用直接复用计划
     改造后这类查询的规划耗时直接降为0。
• 升级PostgreSQL版本
  PostgreSQL 14及以上版本对大量函数索引存在时的规划逻辑做了定向优化，相同场景下规划速度是13版本的3~10倍，直接升级即可解决该问题，不需要修改现有业务和索引结构。
• 拆分搜索字段到独立关联表
  将data字段中的多语言搜索内容拆分到单独的nodes_search关联表，主表nodes仅保留基础业务字段，关联视图查询时只会扫描主表的少量基础索引，完全不涉及多语言搜索索引的校验逻辑，搜索时再关联nodes_search表即可。

内容的提问来源于stack exchange，提问作者Maarten Truyens