postgresql – LATERAL JOIN不使用trigram索引
我想使用Postgres对地址进行一些基本的地理编码.我有一个地址表,有大约100万个原始地址字符串:
=> d addresses Table "public.addresses" Column | Type | Modifiers ---------+------+----------- address | text | 我还有一张位置数据表: => d locations Table "public.locations" Column | Type | Modifiers ------------+------+----------- id | text | country | text | postalcode | text | latitude | text | longitude | text | 大多数地址字符串都包含邮政编码,所以我的第一次尝试是做类似和横向连接: EXPLAIN SELECT * FROM addresses a JOIN LATERAL ( SELECT * FROM locations WHERE address ilike '%' || postalcode || '%' ORDER BY LENGTH(postalcode) DESC LIMIT 1 ) AS l ON true; 这给出了预期的结果,但结果很慢.这是查询计划: QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=18383.07..18540688323.77 rows=1008572 width=91) -> Seq Scan on addresses a (cost=0.00..20997.72 rows=1008572 width=56) -> Limit (cost=18383.07..18383.07 rows=1 width=35) -> Sort (cost=18383.07..18391.93 rows=3547 width=35) Sort Key: (length(locations.postalcode)) -> Seq Scan on locations (cost=0.00..18365.33 rows=3547 width=35) Filter: (a.address ~~* (('%'::text || postalcode) || '%'::text)) 我尝试将gist trigram索引添加到地址列,如https://stackoverflow.com/a/13452528/36191所述,但上述查询的查询计划没有使用它,并且查询计划保持不变. CREATE INDEX idx_address ON addresses USING gin (address gin_trgm_ops); 我必须删除顺序和限制横向连接查询以使用索引,这不会给我我想要的结果.这是没有ORDER或LIMIT的查询的查询计划: QUERY PLAN ----------------------------------------------------------------------------------------------- Nested Loop (cost=39.35..129156073.06 rows=3577682241 width=86) -> Seq Scan on locations (cost=0.00..12498.55 rows=709455 width=28) -> Bitmap Heap Scan on addresses a (cost=39.35..131.60 rows=5043 width=58) Recheck Cond: (address ~~* (('%'::text || locations.postalcode) || '%'::text)) -> Bitmap Index Scan on idx_address (cost=0.00..38.09 rows=5043 width=0) Index Cond: (address ~~* (('%'::text || locations.postalcode) || '%'::text)) 我可以做些什么来让查询使用索引,还是有更好的方法来重写这个查询?
为什么?
查询不能使用主体索引.您需要一个关于表位置的索引,但是您拥有的索引位于表地址上. 您可以通过设置来验证我的声明: SET enable_seqscan = off; (仅在您的会话中,仅用于调试.永远不要在生产中使用它.)这不像索引比顺序扫描更昂贵,Postgres根本无法将它用于您的查询. 除此之外:[INNER] JOIN … ON true只是说CROSS JOIN的一种尴尬方式…… 删除ORDER和LIMIT后为什么使用索引? 因为Postgres可以将这个简单的表单重写为: SELECT * FROM addresses a JOIN locations l ON a.address ILIKE '%' || l.postalcode || '%'; 您将看到完全相同的查询计划. (至少我在Postgres 9.5的测试中做了.) 解 您需要location.postalcode的索引.在使用LIKE或ILIKE时,您还需要将索引表达式(邮政编码)带到运算符的左侧. ILIKE是用运算符~~ *实现的,这个运算符没有COMMUTATOR(逻辑必然),因此无法翻转操作数.这些相关答案中的详细说明: > Can PostgreSQL index array columns? 一个解决方案是在最近的邻居查询中使用trigram similarity operator SELECT * FROM addresses a JOIN LATERAL ( SELECT * FROM locations ORDER BY postalcode <-> a.address LIMIT 1 ) l ON address ILIKE '%' || postalcode || '%'; 找到每个地址最相似的邮政编码,然后检查该邮政编码是否真正完全匹配. 这样,较长的邮政编码将自动首选,因为它比同样匹配的较短邮政编码更相似(更小的距离). 还有一点不确定性.根据可能的邮政编码,由于字符串其他部分中的三元组匹配,可能会出现误报.问题中没有足够的信息可以说更多. 这里,[INNER] JOIN而不是CROSS JOIN是有道理的,因为我们添加了一个实际的连接条件. The manual:
所以: CREATE INDEX locations_postalcode_trgm_gist_idx ON locations USING gist (postalcode gist_trgm_ops); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |