连接时空数据

Question

我在psql中有两个表，我试图根据某些属性连接它们-它们非常大--分别是17 M行和2.7 M行

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='foo';
 estimate 
----------
17087196
(1 row)

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='bar';
 estimate 
----------
  2763829
(1 row)

在这两个方面，我都创建了空间索引

CREATE INDEX foo_gix ON foo USING GIST (the_geom);

我正在运行的查询是基于历史时间间隔收集的数据在多边形分析中的一个点--当两个表上的时间戳匹配时，在多边形中计数点。点是移动电话连接的位置(bar_history和bar)，多边形是某些区域的缓冲区(foo_history，foo)。

看起来是这样的：

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id 
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
group by s.id LIMIT 5

此查询在几秒钟内成功返回结果。但是，当我按时间戳添加联接时：

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id 
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
AND 
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) = 
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)
    group by s.id LIMIT 5 

尽管几乎所有的时间戳都将从两个表中相互匹配，但查询仍会无休止地运行。我不是一个数据库专家，我希望有任何建议，如何加快这个查询或添加某种类型的索引，可能加快它。

Answer 1

时间

这真的很糟糕：

AND 
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) = 
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)

相反，尝试用l.time来重写它，而忽略了时代。我不知道你想要多久。

AND l.time BETWEEN s.time AND (s.time + '10 minutes');

或者别的什么。如果您试图将任意时间戳分配到10分钟或其他时间间隔，则不需要将时间戳转换回时间戳。为了方便，你可以考虑编写自己的函数，

CREATE FUNCTION myIntervalExtract(ts timestamp with time zone)
RETURNS int
AS $
  SELECT floor( extract(epoch FROM l.time::timestamp) / 600 )::int * 600;
$ LANGUAGE sql
IMMUTABLE;

现在，您可以使用上面的内容在myIntervalExtract(l.time)和myIntervalExtract(s.time)上创建一个功能索引。只要您加入，就能满足连接的要求。

FROM l
JOIN s ON myIntervalExtract(l.time) = myIntervalExtract(s.time)

ST_Contains(ST_Buffer()..) 

这也是一个反模式。您会想要将其重写为ST_DWithin。您还应该力求在联接条件中不包含ST_Transform。相反，考虑在表上使用geography。

FROM t1 JOIN t2 ON ST_DWithin(t1.polygeog, t2.geogpoint, distance_in_meters)