PostGIS中BBOX空间查询与表结构的优化方案咨询

我有一个名为graphs的表，表结构如下：

Column             |            Type             | Collation | Nullable |              Default               | Storage  | Compression | Stats target | Description 
--------------------------------+-----------------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
 id                             | integer                     |           | not null | nextval('graphs_id_seq'::regclass) | plain    |             |              | 
 bbox_diagonale                 | double precision            |           |          |                                    | plain    |             |              | 
 bbox                           | geometry(Polygon,4326)      |           |          |                                    | external |             |              | 
Indexes:
    "graphs_pkey" PRIMARY KEY, btree (id)
    "bbox_diagonale_idx" btree (bbox_diagonale)
    "bbox_index" gist (bbox) CLUSTER
Access method: heap

需求：在graphs表中查找满足以下条件的记录：要么bbox与目标bbox完全相等，要么bbox的对角线长度不超过目标bbox的1.4倍且包含目标bbox。

当前使用的SQL语句：

SELECT id, bbox,BBOX_Diagonale FROM graphs 
WHERE (
ST_CONTAINS(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326)) 
OR 
ST_Equals(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326))) 
AND  BBOX_Diagonale <= 91   
order by BBOX_Diagonale ASC LIMIT 1;

已创建的索引：

CREATE INDEX bbox_index ON graphs USING gist(bbox);

CREATE INDEX BBOX_Diagonale_idx ON graphs (
  BBOX_Diagonale ASC
);

小范围目标bbox的执行计划 #

EXPLAIN (ANALYZE, BUFFERS) SELECT id, bbox,BBOX_Diagonale FROM graphs 
    WHERE (
    ST_CONTAINS(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326)) 
    OR 
    ST_Equals(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326))) 
    AND  BBOX_Diagonale <= 91   
    order by BBOX_Diagonale ASC LIMIT 1;

Limit  (cost=4476.66..4476.66 rows=1 width=132) (actual time=9.484..9.488 rows=1 loops=1)
   Buffers: shared hit=506
   ->  Sort  (cost=4476.66..4477.21 rows=219 width=132) (actual time=9.483..9.486 rows=1 loops=1)
         Sort Key: bbox_diagonale
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=506
         ->  Bitmap Heap Scan on graphs  (cost=591.39..4475.56 rows=219 width=132) (actual time=9.454..9.458 rows=1 loops=1)
               Recheck Cond: ((st_contains(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry) OR st_equals(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry)) AND (bbox_diagonale <= '91'::double precision))
               Filter: (st_contains(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry) OR st_equals(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry))
               Heap Blocks: exact=1
               Buffers: shared hit=503
               ->  BitmapAnd  (cost=591.39..591.39 rows=76 width=0) (actual time=9.383..9.385 rows=0 loops=1)
                     Buffers: shared hit=502
                     ->  BitmapOr  (cost=6.69..6.69 rows=216 width=0) (actual time=3.165..3.166 rows=0 loops=1)
                           Buffers: shared hit=256
                           ->  Bitmap Index Scan on bbox_index  (cost=0.00..3.29 rows=108 width=0) (actual time=2.417..2.418 rows=2618 loops=1)
                                 Index Cond: (bbox ~ '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry)
                                 Buffers: shared hit=128
                           ->  Bitmap Index Scan on bbox_index  (cost=0.00..3.29 rows=108 width=0) (actual time=0.745..0.746 rows=0 loops=1)
                                 Index Cond: (bbox ~= '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry)
                                 Buffers: shared hit=128
                     ->  Bitmap Index Scan on bbox_diagonale_idx  (cost=0.00..584.39 rows=38117 width=0) (actual time=6.068..6.068 rows=37983 loops=1)
                           Index Cond: (bbox_diagonale <= '91'::double precision)
                           Buffers: shared hit=246
 Planning:
   Buffers: shared hit=247
 Planning Time: 28.373 ms
 Execution Time: 9.762 ms

大范围目标bbox的慢查询执行计划 #

EXPLAIN (ANALYZE, BUFFERS) SELECT id, bbox,BBOX_Diagonale FROM graphs 
WHERE (
ST_CONTAINS(bbox,ST_MakeEnvelope( 9.272461,48.019324,12.700195,51.034486 , 4326)) 
OR 
ST_Equals(bbox,ST_MakeEnvelope( 9.272461,48.019324,12.700195,51.034486, 4326))) 
AND  BBOX_Diagonale <= 410   
order by BBOX_Diagonale ASC LIMIT 1;

Limit  (cost=0.42..229.62 rows=1 width=132) (actual time=127.426..127.426 rows=0 loops=1)
   Buffers: shared hit=83455
   ->  Index Scan using bbox_diagonale_idx on graphs  (cost=0.42..4513494.64 rows=19692 width=132) (actual time=127.424..127.424 rows=0 loops=1)
         Index Cond: (bbox_diagonale <= '410'::double precision)
         Filter: (st_contains(bbox, '0103000020E61000000100000005000000F4DE1802808B22409203763579024840F4DE1802808B2240BE1589096A8449403CA583F57F662940BE1589096A8449403CA583F57F6629409203763579024840F4DE1802808B22409203763579024840'::geometry) OR st_equals(bbox, '0103000020E61000000100000005000000F4DE1802808B22409203763579024840F4DE1802808B2240BE1589096A8449403CA583F57F662940BE1589096A8449403CA583F57F6629409203763579024840F4DE1802808B22409203763579024840'::geometry))
         Rows Removed by Filter: 89567
         Buffers: shared hit=83455
 Planning Time: 0.299 ms
 Execution Time: 127.457 ms

请问是否可以对该查询语句或表结构进行优化，以提升查询性能？

1. 简化查询条件，移除冗余判断 #

当两个bbox完全相等时，ST_CONTAINS(bbox, 目标bbox)必然成立（多边形包含自身），因此可直接将OR条件合并为单一的ST_CONTAINS判断，减少索引扫描分支：

SELECT id, bbox, bbox_diagonale
FROM graphs
WHERE ST_CONTAINS(bbox, ST_MakeEnvelope(9.272461,48.019324,12.700195,51.034486, 4326))
  AND bbox_diagonale <= 410
ORDER BY bbox_diagonale ASC
LIMIT 1;

2. 创建覆盖式空间索引，避免回表 #

现有索引仅单独覆盖bbox或bbox_diagonale，查询时需回表取数。可创建包含查询所需字段的GIST索引，让数据库直接从索引中获取结果：

CREATE INDEX idx_graphs_bbox_include ON graphs USING GIST(bbox)
INCLUDE (id, bbox_diagonale);

此索引适用于PostgreSQL 12+版本，能大幅降低IO开销。

3. 调整查询逻辑，优先筛选空间条件 #

针对大范围bbox场景，当前执行计划会扫描大量符合对角线条件但不符合空间条件的行。可通过子查询先筛选空间符合条件的记录，再排序取最小对角线结果：

SELECT id, bbox, bbox_diagonale
FROM (
    SELECT id, bbox, bbox_diagonale
    FROM graphs
    WHERE ST_CONTAINS(bbox, ST_MakeEnvelope(9.272461,48.019324,12.700195,51.034486, 4326))
      AND bbox_diagonale <= 410
) sub_query
ORDER BY bbox_diagonale ASC
LIMIT 1;

这种方式强制数据库先使用空间索引筛选子集，再排序，避免扫描大量无关行。

4. 更新表统计信息，优化执行计划 #

数据库执行计划依赖准确的统计数据，运行以下命令更新graphs表统计信息，帮助PostgreSQL生成更优计划：

ANALYZE graphs;

5. 调整内存参数，提升Bitmap操作效率 #

大范围查询时，BitmapAnd操作可能因内存不足使用磁盘临时文件，导致性能下降。可临时调高当前会话的work_mem参数（需根据服务器内存情况调整）：

SET work_mem = '64MB';

内容的提问来源于stack exchange，提问作者Andreas