查询计划中的嵌套循环联接-检查连接谓词以避免笛卡尔产品

Question

我正在尝试用redshift写一个查询，这个查询在内部联接中有一个“中间”。在Redshift中，我知道这可能相当昂贵，但我想不出另一种写它的方法。似乎我有与粘贴的链接完全相同的查询，但在这个问题中没有显示出答案。有人知道如何重写这个查询以避免笛卡尔产品吗？

Redshift - Simplify Query Plan

select a.ip, a.userid, b.city, b.country, b.ip_start, b.ip_end
from usertable a 
left join ip_table on a.ip between b.ip_start and b.ip_end

在应用数据类型和排序/分发键的情况下，我尝试了许多可能性，但似乎没有什么能帮助我的查询。

• ip and ip_start/ip_end = bigint
• sort密钥应用于ip_start/ip_end，ip
• distribution密钥应用于ip和ip_start

Answer 1

对于union all和窗口函数，您可以使用一个技巧来获得ip范围。其思想是将两个表组合在一起，然后使用union all将最近的ip_start值组合在一起。

然后，通过连接到原始表，您可以引入其他列：

with ui as (
      select u.user_id, u.ip, null as ip_start
      from usertable u
      union all
      select null, i.ip_start, i.ip_start
      from ip_table i
     )
select u.*, ip.*
from (select u.user_id,
             max(ip_start) over (order by ip) as ip_start
      from ui
     ) ui join
     usertable u
     on u.user_id = ui.user_id join
     ip_table ip
     on ip.ip_start = ui.ip_start;

请注意，这假定范围不重叠，这通常是ip范围的。

这也不检查ip_end。如果您关心的ip地址不在查找表中，则可以这样做。您只需添加where u.ip <= ip.ip_end就可以过滤掉它们。