SQL只有在比较JOIN(Presto/Aamazon Athena)中的值时才会变慢

Question

我有两个表，一个包含数据，另一个是元数据。

主数据表包含地理空间坐标网格(最多数十亿行(。坐标被投影到特定的坐标系中。该模式的相关部分是：

------------------
| x     | smallint |
|------------------|
| y     | smallint |
|------------------|
| value | string   |
------------------

元数据表包含x，y坐标的相应纬度和经度值。该模式的相关部分是：

----------------------------
| x         | smallint       |
|----------------------------|
| y         | smallint       |
|----------------------------|
| latitude  | decimal(18,15) |
|----------------------------|
| longitude | decimal(18,15) |
----------------------------

通过这两个表上的JOIN，可以知道特定X/Y坐标的实际纬度/经度。这将使表上的查询变得更容易，因为您不需要了解任何关于地图投影的信息。

从表中检索行的示例查询：

SELECT 
main.x,
main.y,
latitude,
longitude,
value
FROM database.main JOIN database.meta
ON main.x=meta.x AND main.y=meta.y
WHERE
main.x=1 AND main.y<=2

结果集看起来像：

--------------------------------------
| x | y | latitude | longitude | value |
|--------------------------------------|
| 1 | 1 | 12.345   | 54.321    | row1  |
|--------------------------------------|
| 1 | 2 | 12.345   | 98.765    | row2  |
--------------------------------------

这个查询只需要1-2秒，太棒了！

我的问题是，当我运行一个添加了比较纬度和经度的WHERE子句的查询时，该查询有效，但运行需要60秒以上。。。

例如：

SELECT 
main.x,
main.y,
latitude,
longitude,
value
FROM database.main JOIN database.meta
ON main.x=meta.x AND main.y=meta.y
WHERE
latitude=DECIMAL '12.345' AND longitude=DECIMAL '98.765'

我知道我可能遗漏了SQL和JOIN的一些基本内容，这导致了这个查询的速度变慢。对任一表的独立查询都非常快，所以我知道我在这里做错了一些与JOIN有关的事情。

所以问题是，我该如何让这个(看似(简单的比较在几秒钟内完成，而不是超过60秒？

Answer 1

由于您使用的是地理空间数据，因此需要利用sql的地理功能来加快查询速度。因此，首先创建一个点列：

create table test_geospatial(
...,
...,
long decimal,
lat decimal,
point as geography::Point(long, lat, SRID) persisted
)

然后在此列上创建一个索引：

create spatial index SPIX_Geocodes_Point on test_geospatial(point) using geography_auto_grid

Answer 2

添加JOIN时性能不佳的最可能解释是没有可用于优化联接的索引。

在您的情况下，在main(x,y)上指定索引最有可能加快JOIN的速度。