两张表之间(真的真的)在两个表之间进行了postgis st_intersect

Question

我正在尝试在两个表之间进行空间连接：

1. 表1：397265功能(在Geom Field上具有GIST索引(
2. 表2：73功能(在GEOM字段上具有GIST索引(

表1和2具有相同的SRID。

我做了很多测试，这些桌子之间的空间连接需要几天(5天后，我杀死了查询(。我试图理解为什么解释分析如此慢：

EXPLAIN ANALYZE 
SELECT 
    table1.id,  table2.id
FROM table1
INNER JOIN table2 ON ST_Intersects(
        ST_Buffer(table1.geom,0),
        ST_Buffer(table2.geom,0)
)

但是，现在我仍在等待结果，因为解释分析确实执行查询。

如果我只做解释，则结果是：

"Gather  (cost=1000.00..3820127.08 rows=9667 width=40)"
"  Workers Planned: 3"
"  ->  Nested Loop  (cost=0.00..3818111.26 rows=3118 width=40)"
"        Join Filter: ((st_buffer(table2.geom, '0'::double precision) && st_buffer(table1.geom, '0'::double precision)) AND _st_intersects(st_buffer(table2.geom, '0'::double precision), st_buffer(table1.geom, '0'::double precision)))"
"        ->  Parallel Seq Scan on table1  (cost=0.00..21964.50 rows=128150 width=344)"
"        ->  Seq Scan on table2  (cost=0.00..9.73 rows=73 width=714516)"

我还针对计算机的Configurarion更新了我的PostgreSQL配置文件：

shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 7
max_parallel_workers_per_gather = 4
max_parallel_workers = 7

你能告诉我这么长的时间吗？

Answer 1

未使用空间索引。

您可以丢弃buffer的东西(也许首先修复您的几何形状..(

INNER JOIN table2 ON ST_Intersects(table1.geom,table2.geom)

或者您可以在缓冲几何形状上创建索引

CREATE INDEX geom_idx ON table1 USING gist (ST_Buffer(table1.geom,0));

Answer 2

正如其他评论所表明的那样，最好修复几何形状。我想您使用零距离的缓冲区来重建无效的几何形状。因此，最好做的是

UPDATE table1 SET geom = st_buffer(geom,0);
UPDATE table2 SET geom = st_buffer(geom,0);

然后重新索引表(通过几何来重建其原始的要点索引(

，根据几何性质，指数效率低下。索引本身基本上是每个几何形状(围绕多边形或线性形状的矩形(的一组边界框，并且要首先确定哪个矩形相交，然后从配对中定义精确的几何形状相交的速度要快得多。但是，如果您的多边形太大，大多数矩形正在相交和/或太复杂的形状，则可能会减慢连接的速度。

此外，您可能的数据可能在点密度方面很重，这也会减慢算法，并且可以在不伤害结果的情况下删除，尤其是如果此数据在空间上不是很准确的情况下。您可以尝试简化数据，例如

ALTER table1 ADD COLUMN geom_simplified geom(<your geom type and srid>);
UPDATE table1 SET geom_simplified = st_snaptogrid(geom,<relevant rounding number depending on your srid and desired accuracy>);

检查一下

Answer 3

谢谢@jgh的答案，您发现了问题。未使用该索引。我永远不会找到它，因为对我来说很明显，即使使用索引的缓冲区。

我试图清洁数据，但是有很多错误。因此，我决定使用您的解决方案(ST_BUFFER(((

索引

我的查询现在需要14个小时。关于表2中的功能数量，仍然有很多时间，但至少查询结束了...

"Gather  (cost=1000.14..116847.28 rows=397265 width=8) (actual time=70.548..51214359.966 rows=415151 loops=1)"
"  Workers Planned: 3"
"  Workers Launched: 3"
"  ->  Nested Loop Left Join  (cost=0.14..76120.78 rows=128150 width=8) (actual time=1312.989..51210248.284 rows=103788 loops=4)"
"        ->  Parallel Seq Scan on table1  (cost=0.00..21964.50 rows=128150 width=338) (actual time=0.009..3485.290 rows=99316 loops=4)"
"        ->  Index Scan using table2_idx_buffer on table2  (cost=0.14..0.41 rows=1 width=714516) (actual time=460.535..515.510 rows=0 loops=397265)"
"              Index Cond: (st_buffer(table1.geom, '0'::double precision) && st_buffer(geom, '0'::double precision))"
"              Filter: _st_intersects(st_buffer(table1.geom, '0'::double precision), st_buffer(geom, '0'::double precision))"
"              Rows Removed by Filter: 3"
"Planning time: 0.227 ms"
"Execution time: 51214434.490 ms"

Answer 4

带有postgresql 14和ubuntu 22上的postgis 3.3，我还面临着非常缓慢的查询混合 st_intersects and 内在。就我而言， JOIN 甚至不是基于空间标准。例如。(这不是实际查询，而是一个示例(：

SELECT table1.*, table2.placename FROM table1 
INNER JOIN table2
ON table1.fk=table2.pk
WHERE 
 ST_INTERSECTS(table2.geom, 
      ST_GEOMFROMTEXT(
              'POLYGON((0 1,1 1,1 0,0 0,0 1))'
            ,4326)
            );

此查询花费了大约80秒的 table1 具有CA。70 000记录和 table2 30 000记录。Table2具有空间索引，几何列中的数据非常简单(点或非常简单的多边形(。搜索语句中的实际WKT几何形状实际上比示例中的区域大得多，但也使用了4分。在我看来，同一查询在早期版本上的速度更快(最初来自使用PostgreSQL 9.6和PostGIS2.2或PostGIS2.5的项目。我可以通过在哪个部分中使用子查询来克服困难：

SELECT table1.*, table2.placename FROM table1 
INNER JOIN table2
ON table1.fk=table2.pk
WHERE table2.pk IN 
  (
    SELECT pk FROM table2 WHERE ST_INTERSECTS(table2.geom, 
    ST_GEOMFROMTEXT('POLYGON((0 1,1 1,1 0,0 0,0 1))'
            ,4326))
  );

虽然不是最佳的，但由于表2上的递归，该解决方案比原始查询要快得多(CA 0.8秒，而第一个则花费了超过一分钟(。在此期间，调用空间指数的内部策略可能发生了变化。CTE也可以用于避免递归：

WITH geo_filter AS (
SELECT pk, placename FROM table2 WHERE ST_INTERSECTS(table2.geom, 
   ST_GEOMFROMTEXT('POLYGON((0 1,1 1,1 0,0 0,0 1))'
        ,4326))
)
SELECT table1.*, geo_filter.placename 
  FROM table1
    INNER JOIN geo_filter ON table1.fk= geo_filter.pk;