postgresql - 找到 1000 个点中每个点周围 30 平方米范围内的所有街道段。尽可能快地做

Question

我有一个使用 PostgreSQL 的快速解决方案。我还有一个使用自定义数据库和自定义索引的解决方案，速度提高了 8.75 倍。

自定义解决方案正在发挥作用:下一步是在整个北美实现它。但是，我想确定我没有忽略同样快(或更快)的 PostgreSQL 解决方案。

解决方案必须返回每个点周围 30 平方米范围内的所有街段。但是，如果为了速度的缘故，它还返回一些在更大的正方形内的街道段，比如 37.5 平方米，那是可以接受的。 (我的自定义解决方案之所以这样做，是因为它采用了镶嵌瓷砖)。

我提出的最佳 Postgres 解决方案创建了一个返回所有 1000 个解决方案的查询。它看起来像下图，但包含了所有 1000 个点。 (每个 ST_MakeEnvelope 创建围绕每个点的 30 米正方形)。

SELECT 0 AS position, segment_id 
FROM roads  
WHERE ST_Intersects(ST_MakeEnvelope(-87.584051340952,50.9904613494613,-87.583193442952,50.9910013500013, 4326), geom_way)  

UNION ALL  SELECT 1 AS position, segment_id 
FROM roads  
WHERE ST_Intersects(ST_MakeEnvelope(-87.632028114596,50.9314626994627,-87.631171305396,50.9320027000027, 4326), geom_way)  

UNION ALL  SELECT 2 AS position, segment_id 
FROM roads  
WHERE ST_Intersects(ST_MakeEnvelope(-87.684002999977,50.8724640494641,-87.683147275777,50.873004050004, 4326), geom_way)  

UNION ALL  
SELECT 3 AS position, segment_id 
FROM roads  
WHERE ST_Intersects(ST_MakeEnvelope(-87.91989093966,50.8134653994654,-87.91903629686,50.8140054000054, 4326), geom_way)  

UNION ALL  
SELECT 4 AS position, segment_id 
FROM roads  
WHERE ST_Intersects(ST_MakeEnvelope(-87.616034053248,50.7544667494668,-87.615180488048,50.7550067500068, 4326), geom_way)

我以编程方式创建查询，然后使用 ODBC 将其传递给 Postgres。

在我的计算机上，此查询在一秒钟内返回 1,022 个职位的结果。在同一台计算机上，自定义数据库和自定义索引在一秒钟内返回 8,940 个位置的结果。

请告诉我如何编写(一个可能很复杂的)Postgres 查询，而且速度一样快或更快!

Answer 1

长话短说，您可以在 CTE 中分解所有包络，然后让引擎根据道路查询它们

WITH cte (pos, geom) AS (select * from (values (0, ST_MakeEnvelope(64666.5,64666.5,64667.5,64667.5, 4326))
,(1, ST_MakeEnvelope(87392.5,87392.5,87393.5,87393.5, 4326))
,(2, ST_MakeEnvelope(69426.5,69426.5,69427.5,69427.5, 4326))
,(3, ST_MakeEnvelope(71599.5,71599.5,71600.5,71600.5, 4326))
,(4, ST_MakeEnvelope(45573.5,45573.5,45574.5,45574.5, 4326))
  ) as foo) SELECT pos,roads.* FROM roads,cte WHERE ST_Intersects(loc, cte.geom);

在我的机器上 6000 个结果在 250 毫秒内

---

更多详情

在你的查询中，规划器会做类似的事情

SELECT 0 AS position, loc FROM roads WHERE ST_Intersects(ST_MakeEnvelope(-0.5,-0.5,0.5,0.5, 4326), loc)
UNION ALL 
SELECT 1 AS position, loc FROM roads WHERE ST_Intersects(ST_MakeEnvelope(0.5,0.5,1.5,1.5, 4326), loc);

 Append  (cost=6.07..167.19 rows=170 width=36)
   ->  Bitmap Heap Scan on roads  (cost=6.07..82.75 rows=85 width=36)
         Recheck Cond: ('...'::geometry && loc)
         Filter: _st_intersects('...'::geometry, loc)
         ->  Bitmap Index Scan on roads_loc_index  (cost=0.00..6.05 rows=254 width=0)
               Index Cond: ('...'::geometry && loc)
   ->  Bitmap Heap Scan on roads roads_1  (cost=6.07..82.75 rows=85 width=36)
         Recheck Cond: ('...'::geometry && loc)
         Filter: _st_intersects('...'::geometry, loc)
         ->  Bitmap Index Scan on roads_loc_index  (cost=0.00..6.05 rows=254 width=0)
               Index Cond: ('...'::geometry && loc)
(11 rows)

注意 -> 道路上的位图堆扫描。我强调的不是 Bitmap heap Scan，而是 -> 有多少位置就有多少。所以想法是让规划者按照他的意愿“迭代”道路，而不是告诉他 UNION ALL。

现在请注意，通过使用(对于 4000 个位置)

WITH cte (pos, geom) AS ( SELECT 0, ST_MakeEnvelope(5687.5,5687.5,5688.5,5688.5, 4326)
 UNION ALL 
SELECT 1, ST_MakeEnvelope(13717.5,13717.5,13718.5,13718.5, 4326)
 UNION ALL 
SELECT 2, ST_MakeEnvelope(53009.5,53009.5,53010.5,53010.5, 4326)
 UNION ALL 
SELECT 3, ST_MakeEnvelope(60566.5,60566.5,60567.5,60567.5, 4326)
 UNION ALL 
SELECT 4, ST_MakeEnvelope(17843.5,17843.5,17844.5,17844.5, 4326) ) SELECT pos FROM cte;

它也“分解”查询，响应时间为 538 毫秒。我们甚至不交叉道路，只是创建 cte 很慢。

结论 在使用自定义值填充 CTE 时，避免使用 UNION ALL 并仅使用 VALUES 声明所有行。