PostgreSQL 空间查询速度太慢问题

Question

我有两个表格需要求交集。第一个表格包含大约五千万个点，第二个表格是世界上所有国家的多边形图层。我想获取与该多边形相交的所有点。

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。仍然没有性能提升。我正在使用CENTOS 6与Postgres 8.4和PostGIS 1.5。有谁能够指导如何加快速度吗？当将查询限制为1000到10000条记录时，我可以很快地获得结果。当我尝试获取完整的结果集时，速度变慢。有什么想法吗？

更新：我现在意识到，我需要先优化我的查询作为这个过程的第一步。我像这样获取包络线

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

现在，最有效的将其包含/执行作为整个查询的一部分的方式是什么？

- aeupinhere

---

最近的每个版本都改进了GiST和GIN索引。您可能想考虑升级到新的主要版本。甚至可以尝试在9.2 beta版本上解决您的问题，因为它包括SP-GiST。http://www.postgresql.org/docs/9.2/static/spgist-intro.html - kgrittn

Answer 1

尝试使用EXPLAIN（和LIMIT）运行它，以查看索引是否被使用。

由于真正的交集检查是最慢的操作，因此可能针对子查询（除了ST_Intersects检查之外的所有内容）的ST_Collect运行它会有所帮助。这样只需要一个调用，如果多几何体构造足够快，则净结果可能更好。

编辑1： 好吧，事实证明它并不是那么理想，因为除非您强制将坐标设置为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

- lynxlynxlynx

---

不要期望使用LIMIT生成的计划与没有LIMIT的计划完全相同。它可能是相同的，但也可能完全不同，因为规划器将寻找返回所请求行数的最便宜的计划，而不是返回所有行的最便宜的计划。这两者通常是不同的。 - kgrittn

能否提供一个ST_Collect()子查询的示例，就像上面的SQL语句一样？ - aeupinhere

当然，但我非常怀疑不使用索引会更便宜，所以这应该可以作为我所说的诊断工具。 - lynxlynxlynx