Postgres大表快速搜索优化方案咨询——Express+Sequelize场景

我们基于Express + Sequelize + Postgres搭建了一套API，其中销售列表接口支持分页、排序、筛选及搜索功能。当前接口会执行两次逻辑完全重复的查询：一次获取单页数据，一次统计符合条件的总记录数，两次查询的FROM和WHERE子句完全一致。

当传入搜索词时，WHERE子句会追加10个ILIKE条件：

• 8个为普通字段的模糊匹配（如name ILIKE '%test name%'）
• 2个为计算字段的匹配：
  • 拼接客户全名：concat(Client.first_name, ' ', Client.last_name) ILIKE '%Test Name%'
  • 地点名称缩写匹配：regexp_replace(Location.name, '([a-z ])+', '', 'g') ILIKE '%ABCD%'

性能现状 #

用包含10万条数据的客户库测试：

• 无搜索词（仅精确匹配）：接口响应1-2秒（可接受），原生SQL耗时约600ms
• 带搜索词：接口响应增至6秒（两次查询放大了耗时），原生SQL耗时约3秒
• 通过explain analyze分析，ILIKE条件（尤其是客户全名拼接的逻辑）是主要性能瓶颈

已尝试/考虑的基础优化 #

• 减少搜索字段数量
• 移除地点缩写搜索功能
• 用||替代concat拼接客户全名（正在Sequelize中验证）
• 添加索引

1. 除上述基础优化外，还有哪些可行的性能优化方案？
2. 正在评估Elasticsearch，但担心运维成本过高，Postgres全文搜索是否值得推荐？是否需要搭配GIST/GIN索引？
3. 针对这类模糊搜索场景，还有哪些Postgres专属的优化建议？

一、Postgres全文搜索（优先推荐） #

完全可以替代ILIKE实现高效模糊搜索，无需额外运维Elasticsearch的成本，推荐搭配GIN索引（比GIST更适合全文搜索，查询速度更快，索引占用空间稍大）。

具体实现步骤 #

1. 创建全文索引：
   • 普通字段直接创建tsvector索引：

     CREATE INDEX idx_sales_name ON sales USING GIN (to_tsvector('english', name));
     

   • 客户全名拼接场景，创建生成列+索引：

     ALTER TABLE clients ADD COLUMN full_name_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', first_name || ' ' || last_name)) STORED;
     CREATE INDEX idx_clients_full_name ON clients USING GIN (full_name_tsv);
     

   • 地点缩写场景，预计算缩写并存储为字段后创建索引：

     ALTER TABLE locations ADD COLUMN name_abbreviation text GENERATED ALWAYS AS (regexp_replace(name, '([a-z ])+', '', 'g')) STORED;
     CREATE INDEX idx_locations_abbrev ON locations USING GIN (to_tsvector('english', name_abbreviation));
     

2. 修改查询逻辑：
   将ILIKE '%xxx%'替换为全文搜索的@@ to_tsquery('english', 'xxx')，Sequelize中可通过sequelize.literal实现：

   const searchQuery = sequelize.literal(`to_tsquery('english', '${searchTerm}:*')`);
   // :* 用于前缀匹配，模拟 ILIKE '%xxx' 效果，全词匹配可去掉
   

二、合并两次查询的重复逻辑 #

当前接口执行两次相同WHERE子句的查询，可合并为一次查询同时获取数据和总条数，减少数据库解析与执行成本：

SELECT 
  *,
  COUNT(*) OVER() AS total_count
FROM sales
WHERE [你的筛选条件]
LIMIT [pageSize] OFFSET [offset];

Sequelize中可通过findAll的attributes添加sequelize.literal('COUNT(*) OVER() AS total_count')，之后从结果中取第一条的total_count即可。

三、针对ILIKE的索引优化（暂不切换全文搜索时） #

若继续使用ILIKE，可创建表达式索引（需先安装pg_trgm扩展：CREATE EXTENSION IF NOT EXISTS pg_trgm;）：

• 客户全名拼接场景的表达式索引：

  CREATE INDEX idx_clients_full_name_ilike ON clients USING GIN (lower(first_name || ' ' || last_name) gin_trgm_ops);
  

• 普通字段的ILIKE可创建lower(column)的GIN/GIST索引，搭配lower(column) ILIKE lower('%xxx%')使用。

四、其他小优化 #

• 限制搜索词最小长度（如至少3个字符），避免过短搜索词触发全表扫描
• 缓存高频搜索结果（如热门客户、热门地点的搜索结果），减少数据库查询
• 用keyset pagination（基于唯一有序字段的分页）替代OFFSET，避免大偏移量时的性能问题：

  SELECT * FROM sales WHERE id > last_id ORDER BY id LIMIT 10;
  

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