临时表中的 PostgreSQL 索引

Question

我有以下 PostGIS/greSQL 查询

SELECT luc.*
FROM spatial_derived.lucas12 luc,
  (SELECT geom
   FROM spatial_derived.germany_bld
   WHERE state = 'SN') sn
WHERE ST_Contains(sn.geom, luc.geom)

查询计划:

Nested Loop  (cost=2.45..53.34 rows=8 width=236) (actual time=1.030..26.751 rows=1282 loops=1)
  ->  Seq Scan on germany_bld  (cost=0.00..2.20 rows=1 width=18399) (actual time=0.023..0.029 rows=1 loops=1)
        Filter: ((state)::text = 'SN'::text)
        Rows Removed by Filter: 15
  ->  Bitmap Heap Scan on lucas12 luc  (cost=2.45..51.06 rows=8 width=236) (actual time=1.002..26.031 rows=1282 loops=1)
        Recheck Cond: (germany_bld.geom ~ geom)
        Filter: _st_contains(germany_bld.geom, geom)
        Rows Removed by Filter: 499
        Heap Blocks: exact=174
        ->  Bitmap Index Scan on lucas12_geom_idx  (cost=0.00..2.45 rows=23 width=0) (actual time=0.419..0.419 rows=1781 loops=1)
              Index Cond: (germany_bld.geom ~ geom)
Planning time: 0.536 ms
Execution time: 27.023 ms

这是由于几何列上的索引非常快。但是，当我想向 sn 多边形添加缓冲区时(1 个大多边形代表边界线，因此是一个非常简单的功能):

SELECT luc.*
FROM spatial_derived.lucas12 luc,
  (SELECT ST_Buffer(geom, 30000) geom
   FROM spatial_derived.germany_bld
   WHERE state = 'SN') sn
WHERE ST_Contains(sn.geom, luc.geom)

查询计划:

Nested Loop  (cost=0.00..13234.80 rows=7818 width=236) (actual time=6221.391..1338380.257 rows=2298 loops=1)
  Join Filter: st_contains(st_buffer(germany_bld.geom, 30000::double precision), luc.geom)
  Rows Removed by Join Filter: 22637
  ->  Seq Scan on germany_bld  (cost=0.00..2.20 rows=1 width=18399) (actual time=0.018..0.036 rows=1 loops=1)
        Filter: ((state)::text = 'SN'::text)
        Rows Removed by Filter: 15
  ->  Seq Scan on lucas12 luc  (cost=0.00..1270.55 rows=23455 width=236) (actual time=0.005..25.623 rows=24935 loops=1)
Planning time: 0.271 ms
Execution time: 1338381.079 ms

查询需要很长时间!我将其归咎于临时表 sn 中不存在的索引。速度的大幅下降不是由 ST_Buffer() 引起的，因为它本身非常快并且缓冲功能很简单。

两个问题:

1)我说得对吗？

2) 我该怎么做才能达到与第一个查询相似的速度？

Answer 1

我遇到了一个陷阱。 ST_Buffer() 在这里不是正确的选择，而 ST_DWithin() 在实际执行边界框比较时保留每个几何列的索引。 help page对于 ST_Buffer() 明确指出不要在使用 ST_Buffer() 时出错，而是使用 ST_DWithin() 进行半径搜索。由于很多 GIS 软件都使用了 Buffer 这个词，所以我没有考虑寻找替代品。

SELECT luc.*
FROM spatial_derived.lucas12 luc
JOIN spatial_derived.germany_bld sn ON ST_DWithin(sn.geom, luc.geom, 30000)
WHERE bld.state = 'SN'

工作并且只需要一秒钟(在那个“缓冲区”内 2300 点)!