PostgreSQL多条件ILIKE查询超时，求性能优化方案

1. 先修正逻辑运算符优先级问题 #

原查询的OR和AND优先级会导致逻辑不符合预期：PostgreSQL中AND优先级高于OR，所以原条件实际等价于：

(lower(name) ILIKE 'BL%') 
OR 
(lower(name) ILIKE 'Bule%' AND p.site_id = 123 AND p.product_type = 0)

这会导致部分不符合site_id=123和product_type=0的记录也被返回，必须给OR条件加上括号，确保逻辑正确：

SELECT * FROM products p 
WHERE (lower(name) ILIKE 'BL%' OR lower(name) ILIKE 'Bule%')
  AND p.site_id = 123 
  AND p.product_type = 0 
ORDER BY external_id ASC LIMIT 25;

2. 针对性创建索引（核心优化） #

方案一：函数复合索引（不修改字段类型） #

因为查询依赖lower(name)的前缀匹配，加上site_id、product_type的等值过滤，再结合排序字段external_id，创建覆盖索引可以避免回表和排序开销：

CREATE INDEX idx_products_lower_name_site_type_extid 
ON products (lower(name), site_id, product_type, external_id);

这个索引可以直接满足：

• 前缀匹配lower(name)的条件
• 快速过滤site_id和product_type的等值条件
• 直接用索引中的external_id完成排序，无需额外排序操作

方案二：改用citext类型（更高效的大小写不敏感处理） #

如果允许修改name字段类型，将其改为citext（需要先安装citext扩展：CREATE EXTENSION IF NOT EXISTS citext;），这样可以直接使用大小写不敏感的匹配，无需调用lower()函数，索引效率更高：

ALTER TABLE products ALTER COLUMN name TYPE citext;
-- 创建覆盖索引
CREATE INDEX idx_products_name_site_type_extid 
ON products (name, site_id, product_type, external_id);

对应的查询可以简化为：

SELECT * FROM products p 
WHERE (name ILIKE 'BL%' OR name ILIKE 'Bule%')
  AND p.site_id = 123 
  AND p.product_type = 0 
ORDER BY external_id ASC LIMIT 25;

3. 查询改写优化 #

可以将多个ILIKE条件合并为更简洁的写法，逻辑与原条件完全一致：

-- 用LIKE ANY替代多个OR
SELECT * FROM products p 
WHERE lower(name) LIKE ANY (ARRAY['BL%', 'Bule%'])
  AND p.site_id = 123 
  AND p.product_type = 0 
ORDER BY external_id ASC LIMIT 25;

-- 或者用SIMILAR TO（注意语法匹配）
SELECT * FROM products p 
WHERE lower(name) SIMILAR TO '(BL|Bule)%'
  AND p.site_id = 123 
  AND p.product_type = 0 
ORDER BY external_id ASC LIMIT 25;

4. 其他优化点 #

• 避免SELECT *：如果只需要部分字段，明确指定字段名，配合覆盖索引可以大幅减少数据读取量
• 检查执行计划：用EXPLAIN ANALYZE执行查询，确认索引是否被正确使用，是否存在全表扫描或排序开销

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