如何提高BigQuery中ST_INTERSECT的性能

Question

我有两个表，一个有166M个地址点，另一个有170000个分区多边形。我想通过多边形将点相交，以获得一个包含地址id和地址所在分区的分区id的表。为了避免分区与州边界重叠的问题(因为这是单个状态数据集的合并数据集(，我添加了一个条件，即每个文件中的状态相同。

为了提高性能，两个输入表都按各自的几何字段进行了聚类，使用了内部联接，并将输入表具体化为表(根据有关使用空间联接的文档(。但是，该过程在结束之前超时。

为了进行故障排除，我确保分区多边形具有预期的方向(这是本文中的问题(，并且我还查看了关于优化查询的BigQuery文档

我尝试的第一件事是标准的内部连接：

SELECT
addr.id_address,
prec.id_precinct,
prec.geom  
FROM precincts AS prec
INNER JOIN addresses AS addr  
ON prec.geo_state = addr.geo_state
AND ST_INTERSECTS(addr.geom, prec.geom)

我还尝试过使用窗口功能：

SELECT *
FROM (
SELECT
addr.id_address,
prec.id_precinct,
prec.geom,
ROW_NUMBER() OVER(PARTITION BY addr.geo_state) AS rn
FROM addresses AS addr
INNER JOIN precincts AS prec
ON prec.geo_state = addr.geo_state
AND ST_INTERSECTS(addr.geom, prec.geom)
) AS ranked
WHERE rn = 1

在这两种情况下，它都会在结束前超时。如有任何关于如何优化此查询的帮助，我们将不胜感激。请注意，我使用dbt来运行它，并且我的超时设置为1800秒(不过，在BigQuery UI中运行它时也会超时(。

Answer 1

TL；DR：如果删除状态相等条件prec.geo_state = addr.geo_state，查询是否运行得足够快？如果是，解决方案是将其删除并作为单独的查询进行单独筛选，或者将其转换为=两侧都依赖于左右连接子项的内容，例如

CONCAT(prec.geo_state, ".",  addr.geo_state) = CONCAT(addr.geo_state, ".", prec.geo_state)

说明：BigQuery还不支持高效混合不同类型的JOIN条件。如果它在单个联接中同时看到相等条件和地理空间条件；优选"；相等联接，将地理空间谓词作为常规过滤条件执行，而不是构建和使用地理空间索引。

这就是为什么这个技巧经常提高性能的原因：；隐藏"；相等条件，使BigQuery使用地理空间联接，然后才检查现在复杂的相等条件。当然，只有当没有状态相等谓词的查询运行得相当快时，它才有效。