对LEFT外部联接关系的ILIKE查询进行优化

Question

postgres很新，这里似乎有问题，我不确定该朝哪个方向发展。

我有一个查询导致了一些性能问题，我无法找到如何优化它

查询实际上很简单：

SELECT transactions.* FROM transactions
LEFT OUTER JOIN companies ON "companies"."id" = "transactions"."company_id"
WHERE companies.code ILIKE '%777%'
ORDER BY transactions.id desc LIMIT 10

我想查找名称中有777的公司的所有交易。数据库中大约有2000万笔交易，大约有200家公司。

查询当前超时，尽管我们的LIMIT为10。我相信这是因为ILIKE没有使用索引，所以速度非常慢。

这会是一个您可能想要将where过滤器添加到JOIN的实例吗？我已经测试过了，如果发现一个记录，它的工作速度会闪电般快。如果没有找到记录，则再次超时。

我们是否应该研究一种可以提高速度的指数？

Answer 1

您可以尝试EXISTS条件，因为您不需要公司表中的任何列：

SELECT tr.* 
FROM transactions tr
WHERE exists (select *  
from companies c 
where c.id = tr.company_id 
and c.code ILIKE '%777%')
ORDER BY tr.id desc 
LIMIT 10

但最终，order by可能是这里的瓶颈。例如，如果返回了1000万个事务，那么对这1000万行进行排序将需要一些时间。

Answer 2

查询的EXPLAIN会有所帮助。也就是说，我不认为ILIKE在这里阻碍你，而是扫描交易表。随后是潜在的大型ORDER BY分拣操作

尽管(我认为(系统已经在自动完成这项工作，但让我们拆分功能，并通过使用(索引的(临时表手动强制执行操作顺序。

大致如下：

SELECT id
INTO TEMPORARY TABLE matching_companies
FROM companies 
WHERE companies.code ILIKE '%777%';

CREATE UNIQUE INDEX idx_matching_companies_id ON matching_companies (id);
SELECT t.* 
FROM transactions t
JOIN matching_companies c
ON c."id" = t."company_id"
ORDER BY t.id desc LIMIT 10;

假设很少有公司符合777的要求，这就说明了company_id上的事务表索引在这里应该很有用。

如果简单地添加索引可能已经加快了原始查询的速度，我不会感到太惊讶，因为IMHO系统会将查询拆分为几乎相同的操作(可能减去idx_matching_companies_id(