ST_Intersects()查询耗时太长

Question

我正在使用实现'空间连接'工作的PostGIS扩展进行查询。运行查询花了很长时间，最后失败了。查询方式如下:

CREATE INDEX buffer_table_geom_idx ON buffer_table USING GIST (geom);
CREATE INDEX point_table_geom_idx ON point_table USING GIST (geom);
SELECT
point_table.*,
buffer_table.something
FROM
point_table
LEFT JOIN buffer_table ON ST_Intersects (buffer_table.geom, point_table.geom);

其中point_table表示包含超过1000万行点记录的表;buffer_table表示只包含一个多多边形几何的表。

我想知道我的代码是否有什么问题和调整的方法。提前谢谢。

Answer 1

对于LEFT JOIN，您将遍历point_table的每个记录，因此忽略索引。试试这个，看看有什么不同:

SELECT point_table.*
FROM point_table
JOIN buffer_table ON ST_Contains(buffer_table.geom, point_table.geom);

与ST_SubDivide分治

考虑到你的多多边形的大小(见注释)，把它分成更小的部分可能会很有趣，这样每个包含/交叉计算的顶点数量也会减少，从而使查询更便宜。

首先将大的几何图形分成更小的部分并存储在另一个表中(您也可以使用CTE/Subquery)

CREATE TABLE buffer_table_divided AS
SELECT ST_SubDivide(geom) AS geom FROM buffer_table
CREATE INDEX buffer_table_geom_divided_idx ON buffer_table_divided USING GIST (geom);

. .然后对这个新表再次执行查询:

SELECT point_table.*
FROM point_table
JOIN buffer_table_divided d ON ST_Contains (d.geom, point_table.geom);

Demo:db<>fiddle