如何为Django 3.2+PostgreSQL 12.8的加权全文搜索创建有效索引

首先，你的问题核心在于现有索引的表达式和Django生成的查询里的tsvector表达式完全不匹配，所以PostgreSQL根本没法用这个索引来加速查询。咱们一步步来修复：

1. 为什么现有索引没用？ #

你创建的索引是把三个字段的to_tsvector直接拼接：

create index search_view_idx on core_post using gin(
to_tsvector('english', COALESCE("core_post"."post_title", '') || to_tsvector('english', COALESCE("core_post"."post_subtitle", '') || to_tsvector('english', COALESCE("core_post"."post_text", '') ))));

但Django的查询里，是给每个字段的to_tsvector分别设置权重后再合并：

setweight(to_tsvector(COALESCE("core_post"."post_title", '')), 'A') || 
setweight(to_tsvector(COALESCE("core_post"."post_subtitle", '')), 'B') || 
setweight(to_tsvector(COALESCE("core_post"."post_text", '')), 'C')

这两个表达式完全不一样，数据库无法匹配索引，只能走全表扫描。

2. 创建匹配查询的正确索引 #

你需要创建一个和查询中tsvector表达式完全一致的GIN索引，包括权重设置、COALESCE处理，以及文本搜索配置（注意你的查询里没指定'english'，用的是数据库默认配置，所以索引里也保持一致，或者明确指定避免歧义）：

CREATE INDEX core_post_fulltext_idx ON core_post USING GIN (
    setweight(to_tsvector(COALESCE(post_title, '')), 'A') ||
    setweight(to_tsvector(COALESCE(post_subtitle, '')), 'B') ||
    setweight(to_tsvector(COALESCE(post_text, '')), 'C')
);

如果你的数据库默认文本配置不是english，或者你想明确指定，就给每个to_tsvector加上配置参数：

CREATE INDEX core_post_fulltext_idx ON core_post USING GIN (
    setweight(to_tsvector('english', COALESCE(post_title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(post_subtitle, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(post_text, '')), 'C')
);

注意：必须保证索引里的表达式和查询里的完全一致，包括是否指定文本配置、函数调用顺序，否则索引还是不会被使用。

3. 优化查询性能的额外建议 #

• 避免重复计算rank：你的查询在SELECT和WHERE里都计算了一次ts_rank，可以用子查询先计算rank，减少重复计算：

  SELECT id, blog_name, post_url, post_title, post_subtitle, post_text, rank
  FROM (
      SELECT 
          id, blog_name, post_url, post_title, post_subtitle, post_text,
          ts_rank(
              setweight(to_tsvector(COALESCE(post_title, '')), 'A') ||
              setweight(to_tsvector(COALESCE(post_subtitle, '')), 'B') ||
              setweight(to_tsvector(COALESCE(post_text, '')), 'C'),
              plainto_tsquery('Angel')
          ) AS rank
      FROM core_post
  ) AS ranked_posts
  WHERE rank >= 0.3
  ORDER BY rank DESC
  LIMIT 15;
  

• 验证索引是否生效：创建索引后，再次执行EXPLAIN ANALYZE，如果看到Bitmap Index Scan on core_post_fulltext_idx或者使用了该索引的步骤，说明索引生效了。

4. Django层面的优化（可选） #

如果你想让Django直接使用这个索引，可以自定义查询表达式，或者使用SearchVector结合Weight来构建查询，确保生成的SQL和索引表达式匹配：

from django.contrib.postgres.search import SearchVector, Weight

vector = (
    Weight(SearchVector('post_title'), 'A') +
    Weight(SearchVector('post_subtitle'), 'B') +
    Weight(SearchVector('post_text'), 'C')
)
posts = Post.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.3).order_by('-rank')[:15]

这样Django生成的SQL会和你创建的索引表达式完全匹配，确保索引被正确使用。

内容的提问来源于stack exchange，提问作者André Carvalho