Postgresql Spatial Query太慢了

发布时间：2020-12-13 16:07:48 所属栏目：百科来源：网络整理

导读：我有两张桌子,我想交叉.第一个表代表大约5000万个点,第二个表是世界上所有国家的多边形层.我想获得与该多边形相交的所有点. SELECT d.id,d.geom FROM export d,world_boundaries b WHERE (b.cntry_name = 'UK') AND d.date_inserted = '2012-06-01' AND d.ge

我有两张桌子,我想交叉.第一个表代表大约5000万个点,第二个表是世界上所有国家的多边形层.我想获得与该多边形相交的所有点.

SELECT d.id,d.geom 
FROM export d,world_boundaries b 
WHERE (b.cntry_name = 'UK') 
  AND d.date_inserted >= '2012-06-01' 
  AND d.geom && b.wkb_geometry 
  AND intersects(d.geom,b.wkb_geometry);

此查询非常简单,但运行时间超过4小时.我在每个表的几何列上构建了GIST索引,并对它们进行了VACUUM ANALYZE.仍然没有性能提升.我使用Postgres 8.4和PostGIS 1.5运行CENTOS 6.有人能说明如何加快速度吗？将查询限制为1000到10000条记录时,我会很快得到结果.当我尝试抓取完整的结果集时,它会拖动.思考？

更新：我现在看到我必须优化我的查询作为此过程的第一步.我得到这样的信封

select astext(st_envelope(wkb_geometry)) as e 
from world_borders 
where cntry_name = 'UK'

现在,作为整个查询的一部分包含/执行此操作的最有效方法是什么？

解决方法

尝试使用EXPLAIN(和LIMIT)运行它以查看是否正在使用索引.

由于真正的交集检查是那里最慢的操作,因此可能针对子查询的ST_Collect(除了ST_Intersects检查之外的所有内容)运行它会有所帮助.那样只有一个调用,如果多几何结构足够快,最终结果可能会更好.

EDIT1：
好吧,事实证明它不是那么理想,因为除非你强制坐标到3d(也保持id),需要额外的查找来获得几何id：

SELECT d.id,d.geom
FROM
(
    SELECT *
    FROM
    ( 
        SELECT ST_Collect(d.geom)
        FROM export d,world_boundaries b 
        WHERE (b.cntry_name = 'UK') 
        AND d.date_inserted >= '2012-06-01' 
        AND d.geom && b.wkb_geometry
    ) as c,world_boundaries b 
    WHERE (b.cntry_name = 'UK')
    AND ST_Intersection(c.geom,b.wkb_geometry);
) as e,export d
WHERE (ST_Dump(e.geom)).geom = d.geom

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!