左外连接关联场景下ILIKE查询的性能优化问题

1. 现有SQL的隐藏问题 #

你当前写的LEFT OUTER JOIN加WHERE companies.code ILIKE '%777%'的逻辑，实际已经等价于INNER JOIN：左连接生成的公司ID为NULL的交易记录会被WHERE条件直接过滤，优化时可以直接改成内连接，避免执行计划走不必要的逻辑。

2. 所需创建的索引 #

首先需要启用Postgres的trgm扩展支持模糊查询索引，再按场景建两类索引：

• 第一步：启用pg_trgm扩展（支持全模糊匹配的索引能力）

CREATE EXTENSION IF NOT EXISTS pg_trgm;

• 第二步：给公司表code字段建GIN索引，加速ILIKE全模糊匹配

CREATE INDEX idx_companies_code_trgm ON companies USING GIN (code gin_trgm_ops);

• 第三步：给交易表建复合索引，同时满足关联过滤和排序需求

CREATE INDEX idx_transactions_company_id_id_desc ON transactions (company_id, id DESC);

3. 更优的SQL写法 #

建议改成子查询写法，强制数据库先过滤仅200条的公司表，再查交易表，避免执行计划错误选择先扫2000万交易表的逻辑，无论是否有匹配记录都不会超时：

SELECT * FROM transactions
WHERE company_id IN (
    SELECT id FROM companies WHERE code ILIKE '%777%'
)
ORDER BY id DESC LIMIT 10;

优化逻辑说明 #

公司表仅有200条数据，先过滤得到符合条件的公司ID列表的成本极低：如果没有符合条件的公司，子查询直接返回空，整个查询立刻结束，不会出现无匹配时超时的问题。如果有符合条件的公司，通过交易表的复合索引可以直接定位到对应交易，且索引本身已经按ID倒序排序，不需要额外做排序操作，直接就能取到前10条结果，性能提升非常明显。

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