Why?
该查询不能使用主体上的索引。您需要在表上建立索引locations
,但是你的那个在桌子上addresses
.
您可以通过设置来验证我的声明:
SET enable_seqscan = off;
(仅在您的会话中,并且仅用于调试。切勿在生产中使用它。)索引并不比顺序扫描更昂贵,Postgres 无法将它用于您的查询at all.
Aside: [INNER] JOIN ... ON true
只是一种尴尬的说法CROSS JOIN ...
为什么删除后还使用索引ORDER
and LIMIT
?
因为 Postgres 可以将这个简单的形式重写为:
SELECT *
FROM addresses a
JOIN locations l ON a.address ILIKE '%' || l.postalcode || '%';
您将看到完全相同的查询计划。 (至少我在 Postgres 9.5 上的测试中是这样做的。)
Solution
你需要一个索引locations.postalcode
。并且在使用时LIKE
or ILIKE
您还需要带上索引表达式(postalcode
)到left操作员一侧。ILIKE
与运营商一起实施~~*
并且这个操作符没有COMMUTATOR
(逻辑上的必然性),所以不可能翻转操作数。这些相关答案中的详细解释:
- PostgreSQL 可以索引数组列吗?
- PostgreSQL - 文本数组包含类似于的值
- 有没有办法有效地索引包含正则表达式模式的文本列?
一个解决方案是使用三元组相似算子%或其倒数,即距离算子<-> in a 最近的邻居改为查询(每个都是其自身的换向器,因此操作数可以自由切换位置):
SELECT *
FROM addresses a
JOIN LATERAL (
SELECT *
FROM locations
ORDER BY postalcode <-> a.address
LIMIT 1
) l ON address ILIKE '%' || postalcode || '%';
找到最相似的postalcode
对于每个address
,然后检查是否postalcode
实际上完全匹配。
这样,更长的时间postalcode
将自动成为首选,因为它比较短的更相似(距离更小)postalcode
这也匹配。
仍然存在一些不确定性。根据可能的邮政编码,由于字符串其他部分中的三元组匹配,可能会出现误报。问题中没有足够的信息可以说更多。
Here, [INNER] JOIN
代替CROSS JOIN
这是有道理的,因为我们添加了实际的连接条件。
手册:
这可以通过 GiST 索引非常有效地实现,但不能通过 GIN 索引。
So:
CREATE INDEX locations_postalcode_trgm_gist_idx ON locations
USING gist (postalcode gist_trgm_ops);