PostgreSQL 15.5文本搜索查询过慢问题排查求助

环境与表结构 #

使用PostgreSQL 15.5，表结构如下：

CREATE TABLE IF NOT EXISTS directory_network_entity (
  id varchar(70) NOT NULL,
  country_code_2 varchar(2) NOT NULL,
  country_name varchar(50) NOT NULL,
  entity_name varchar(200) not NULL,
  source_name varchar(32) NOt NULL,
  source_category varchar(32) DEFAULT NULL,
  raw jsonb,
  identifiers_search_vectors tsvector default null,
  general_search_vectors tsvector default null,
  create_time_utc bigint not null default (extract(epoch from now()) * 1000),
  PRIMARY KEY (id)
);
CREATE INDEX gin_identifiers_search_vectors ON directory_network_entity USING GIN (identifiers_search_vectors);
CREATE INDEX gin_general_search_vectors ON directory_network_entity USING GIN (general_search_vectors);

问题描述 #

表内约有180K条数据，执行以下查询时耗时长达5秒：

SELECT (ts_rank_cd(general_search_vectors, query,  32) + ( 2 * ts_rank_cd(identifiers_search_vectors , query,  32))) as rank, 
id, entity_name FROM directory_network_entity , to_tsquery('0106/30253335`') query
WHERE query @@ identifiers_search_vectors order by rank desc limit 10;

执行计划 #

Limit  (cost=1499607.64..1499608.80 rows=10 width=94)
  ->  Gather Merge  (cost=1499607.64..1499695.14 rows=750 width=94)
        Workers Planned: 2
        ->  Sort  (cost=1498607.61..1498608.55 rows=375 width=94)
              Sort Key: ((ts_rank_cd(directory_network_entity.general_search_vectors, query.query, 32) + ('2'::double precision * ts_rank_cd(directory_network_entity.identifiers_search_vectors, query.query, 32)))) DESC
              ->  Nested Loop  (cost=0.25..1498599.51 rows=375 width=94)
                    Join Filter: (query.query @@ directory_network_entity.identifiers_search_vectors)
                    ->  Parallel Seq Scan on directory_network_entity  (cost=0.00..1496910.08 rows=74908 width=179)
                    ->  Function Scan on to_tsquery query  (cost=0.25..0.26 rows=1 width=32)

疑问 #

业务场景需要存储约10M条此类数据，担心PostgreSQL是否适配该场景，或是查询语句、索引设置存在疏漏。

优化方案与结论 #

1. 核心问题：未使用GIN索引 #

从执行计划可见，查询走了并行全表扫描而非已创建的GIN索引，这是耗时过长的根本原因，对应解决方法：

• 修正查询参数：to_tsquery('0106/30253335')末尾的反引号是无效字符，会导致tsquery解析异常，优化器判定无法使用索引。去掉反引号，改为to_tsquery('0106/30253335')`。
• 更新统计信息：执行计划中匹配行数的估计值可能与实际偏差过大，导致优化器选错路径。执行ANALYZE directory_network_entity;更新表统计信息。
• 优化查询写法：将to_tsquery调用独立出来，避免优化器误判，示例：

WITH query AS (SELECT to_tsquery('0106/30253335') AS q)
SELECT 
  (ts_rank_cd(general_search_vectors, q, 32) + 2 * ts_rank_cd(identifiers_search_vectors, q, 32)) AS rank,
  id, entity_name 
FROM directory_network_entity, query
WHERE q @@ identifiers_search_vectors 
ORDER BY rank DESC 
LIMIT 10;

2. 排序环节优化 #

若需频繁执行此类按rank排序的查询，可先通过GIN索引过滤匹配行，再计算rank排序，减少排序数据量：

WITH matched_rows AS (
  SELECT id, identifiers_search_vectors, general_search_vectors
  FROM directory_network_entity
  WHERE to_tsquery('0106/30253335') @@ identifiers_search_vectors
)
SELECT 
  (ts_rank_cd(mr.general_search_vectors, q, 32) + 2 * ts_rank_cd(mr.identifiers_search_vectors, q, 32)) AS rank,
  mr.id, d.entity_name 
FROM matched_rows mr
JOIN directory_network_entity d ON mr.id = d.id
CROSS JOIN (SELECT to_tsquery('0106/30253335') AS q) query
ORDER BY rank DESC 
LIMIT 10;

3. 10M数据场景适配性 #

PostgreSQL完全适配10M级别的全文检索场景，GIN索引在大体积文本数据处理上性能稳定，只要确保索引被正确使用，检索响应时间可控制在毫秒级。额外建议：

• 为检索向量创建自动更新触发器，避免数据更新后向量失效：

-- 示例：为identifiers_search_vectors创建自动更新触发器
CREATE TRIGGER identifiers_tsvector_update
BEFORE INSERT OR UPDATE ON directory_network_entity
FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(
  identifiers_search_vectors, 'pg_catalog.english', 字段1, 字段2 -- 替换为生成检索向量的原始字段
);

• 定期在业务低峰期维护索引，避免碎片化：执行REINDEX INDEX gin_identifiers_search_vectors;

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