PostgreSQL多表多列ILIKE查询优化方案咨询

针对1300万+数据量的sms和user_apps表多字段ILIKE慢查询问题，以下是具体优化步骤：

一、修正WHERE子句的逻辑优先级错误 #

原查询的WHERE条件存在逻辑歧义："sms"."status" ilike '%search_text%' and "sms.type" = 'sms' 会被PostgreSQL解析为独立的OR分支（等价于(A OR B ... OR G) OR (H AND I)），这会导致不符合sms.type='sms'的行也可能被纳入结果，同时增加无效过滤的开销。

如果你的业务逻辑是所有ILIKE条件满足其一，且sms.type='sms'，请修正为带括号的明确逻辑：

WHERE (
  "user_apps"."unique_id" ilike '%search_text%'
  OR "sms"."sender" ilike '%search_text%'
  OR "sms"."message" ilike '%search_text%'
  OR "sms"."msisdn_receiver" ilike '%search_text%'
  OR "sms"."country_code" ilike '%search_text%'
  OR CAST(sms.id AS VARCHAR(255)) ilike '%search_text%'
  OR "sms"."sim" ilike '%search_text%'
  OR "sms"."status" ilike '%search_text%'
) AND "sms"."type" = 'sms'

二、使用pg_trgm扩展优化模糊匹配 #

PostgreSQL的pg_trgm扩展专门用于处理任意子串的模糊匹配（包括%xxx%形式），比普通索引、全文索引更适合你的场景。

1. 安装pg_trgm扩展 #

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建针对性的GIN索引 #

GIN索引比GIST索引查询速度更快，适合只读或写操作较少的场景：

• 针对sms表的字段创建结合sms.type='sms'的部分索引，进一步缩小索引范围：

-- sender字段
CREATE INDEX idx_sms_sender_trgm_type_sms ON sms USING GIN (sender gin_trgm_ops) WHERE type = 'sms';
-- message字段
CREATE INDEX idx_sms_message_trgm_type_sms ON sms USING GIN (message gin_trgm_ops) WHERE type = 'sms';
-- msisdn_receiver字段
CREATE INDEX idx_sms_msisdn_receiver_trgm_type_sms ON sms USING GIN (msisdn_receiver gin_trgm_ops) WHERE type = 'sms';
-- country_code字段
CREATE INDEX idx_sms_country_code_trgm_type_sms ON sms USING GIN (country_code gin_trgm_ops) WHERE type = 'sms';
-- sim字段
CREATE INDEX idx_sms_sim_trgm_type_sms ON sms USING GIN (sim gin_trgm_ops) WHERE type = 'sms';
-- status字段
CREATE INDEX idx_sms_status_trgm_type_sms ON sms USING GIN (status gin_trgm_ops) WHERE type = 'sms';
-- sms.id的文本转换表达式索引
CREATE INDEX idx_sms_id_text_trgm_type_sms ON sms USING GIN ((id::TEXT) gin_trgm_ops) WHERE type = 'sms';

• 针对user_apps表的unique_id字段创建索引：

CREATE INDEX idx_user_apps_unique_id_trgm ON user_apps USING GIN (unique_id gin_trgm_ops);

三、重构查询，避免OR条件导致的索引失效 #

OR条件有时会让数据库无法同时使用多个索引，建议将查询拆分为多个UNION ALL子查询，每个子查询对应一个ILIKE条件，再合并结果排序：

SELECT * FROM (
  -- 匹配user_apps.unique_id
  SELECT 
    sms.*, user_apps.id AS uaId, sms.id AS smsId
  FROM sms
  JOIN user_apps ON user_apps.id = sms.user_app_id
  WHERE user_apps.unique_id ILIKE '%search_text%'
    AND sms.type = 'sms'
  
  UNION ALL
  
  -- 匹配sms.sender（排除已被上一个子查询匹配的行）
  SELECT 
    sms.*, user_apps.id AS uaId, sms.id AS smsId
  FROM sms
  LEFT JOIN user_apps ON user_apps.id = sms.user_app_id
  WHERE sms.sender ILIKE '%search_text%'
    AND sms.type = 'sms'
    AND NOT EXISTS (
      SELECT 1 FROM user_apps 
      WHERE user_apps.id = sms.user_app_id 
        AND user_apps.unique_id ILIKE '%search_text%'
    )
  
  UNION ALL
  
  -- 匹配sms.message（排除已被前两个子查询匹配的行）
  SELECT 
    sms.*, user_apps.id AS uaId, sms.id AS smsId
  FROM sms
  LEFT JOIN user_apps ON user_apps.id = sms.user_app_id
  WHERE sms.message ILIKE '%search_text%'
    AND sms.type = 'sms'
    AND NOT EXISTS (
      SELECT 1 FROM user_apps 
      WHERE user_apps.id = sms.user_app_id 
        AND user_apps.unique_id ILIKE '%search_text%'
    )
    AND sms.sender NOT ILIKE '%search_text%'
  
  -- 其他字段的匹配子查询以此类推，逐一排除已匹配的行避免重复
) AS combined_results
ORDER BY smsId DESC
LIMIT 51 OFFSET 0;

如果业务允许重复结果，也可以省略NOT EXISTS判断，用UNION替代UNION ALL去重，但UNION会增加排序开销，优先推荐UNION ALL+NOT EXISTS的方式。

四、配置调优提升性能 #

从执行计划的Buffers数据来看，磁盘读取量较高（read=647586），说明缓存命中率不足，可以调整以下配置：

• shared_buffers：设置为系统内存的25%-50%（PostgreSQL 10支持动态调整，重启生效），提升数据缓存能力。
• work_mem：增大该值（比如设置为64MB），让排序操作在内存中完成，避免磁盘排序。

关于是否需要创建FULL TEXT INDEX #

不需要。全文索引适合自然语言的分词搜索（比如匹配完整单词、短语），无法高效处理%xxx%这种任意子串的模糊匹配场景，pg_trgm扩展的索引是更合适的选择。

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