PostGIS 性能优化系列文章: bounding boxes

PostGIS 性能优化系列文章: bounding boxes

告别地图查询慢如蜗牛的时代！PostGIS 性能优化的终极秘籍！

在处理大规模地理空间数据时，您是否曾被一个核心痛点所困扰？您的数据库中存储着数以万计甚至百万计的 Geometry data (几何数据) ，但当您尝试执行一次简单的 spatial join (空间连接) 或查询时，系统却陷入了漫长的等待，仿佛被困在了 大海捞针 的窘境中？

尤其当您的 polygon (多边形) 形状复杂、横跨广阔区域（例如，跨洲的国家边界），PostGIS 赖以加速查询的 spatial index (空间索引) 效果就会大打折扣。这是因为那些巨大的、包裹着大量空白海洋区域的 Bounding Boxes (边界框) 正在悄悄地“毒害”您的 R-tree indexes (R树索引) ，让本该精确的查找变成了低效的 大范围扫描 。

好消息是： 您不必再忍受这种低效。

本文将揭示两种革命性的 PostGIS 函数 —— Decompose (分解) 和 Subdivide (细分) —— 如何彻底重塑您的几何图形，打造出更紧凑、更精准的 Bounding Boxes (边界框)，从而将原本耗时数秒的查询，提速至 4 倍以上！

如果您渴望让您的空间查询像闪电一样快，如果您想知道如何用少量的预处理成本换取巨大的长期性能收益，那么请 务必继续阅读全文 。终极性能秘籍，即刻为您揭晓！

翻了一下历史文档, 发现2017年我写过两篇类似的文章, 参考如下：

• 《PostgreSQL 空间切割(st_split, ST_Subdivide)功能扩展 - 空间对象网格化 (多边形GiST优化)》
• 《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

不过这篇又增加了Decompose( ST_Dump 可用于展开几何图形。它与 ST_Collect / GROUP BY 的操作相反，因为它会创建新行。例如，它可以用于将 MULTIPOLYGONS 展开为 POLYGONS ), 一起来学习一下吧, 以下内容来自:

• https://www.crunchydata.com/blog/postgis-performance-improve-bounding-boxes-with-decompose-and-subdivide

PostGIS 性能 (Performance)：使用 Decompose (分解) 和 Subdivide (细分) 改进 Bounding Boxes (边界框)

在 PostGIS 性能 (Performance) 系列的第三部分中，我想讨论关于 bounding boxes (边界框/包围盒) 的性能。

Geometry data (几何数据) 与你在 relational database (关系型数据库) 中发现的大多数列类型不同。geometry (几何) 列中的对象在它们覆盖的数据域范围以及它们在磁盘上占用的物理大小方面可能存在巨大差异。

“admin0” Natural Earth 数据集中的数据范围从 1.2 公顷的梵蒂冈城，到 16 亿公顷的俄罗斯；从定义 Serranilla Bank 的 4 个点的 **polygon (多边形)**，到定义加拿大的 6.8 万个点的 **polygon (多边形)**。

SELECT ST_NPoints(geom) AS npoints, name
FROM admin0  
ORDERBY1DESCLIMIT5;  

SELECT ST_Area(geom::geography) AS area, name
FROM admin0  
ORDERBY1DESCLIMIT5;  

可以想象，如此不同的 polygon (多边形) 会有不同的性能特征：

• 物理上较大的对象需要更长的时间来处理。包括从磁盘中提取、扫描和计算。
• 地理范围大的对象会覆盖更多的其他对象，并降低你的 indexes (索引) 的有效性。

你的 spatial indexes (空间索引) 是 r-tree indexes (R树索引) ，其中每个对象都由一个 bounding box (边界框) 表示。

bounding boxes (边界框) 可能会重叠，并且有些框可能会覆盖数据集的很大一部分。

例如，这是法国的 **bounding box (边界框)**。

什么？！这怎么是法国？原来，法国不仅仅包括欧洲部分，它还包括印度洋南部的留尼汪岛 (Reunion)，以及加勒比海的瓜德罗普岛 (Guadaloupe)。它们合在一起形成了这个非常大的 bounding box (边界框) 。

这样一个巨大的框对于“admin0”中所有对象的 spatial index (空间索引) 来说是一个糟糕的补充。我可能正在大西洋中部用一个 query key (查询键) 进行搜索，但索引仍然会告诉我“可能在法国？”。

测试设置 (Test Setup)

为了这次测试，我创建了一个覆盖全球的包含一百万个随机点的合成数据集。

CREATE TABLE random_normal AS
SELECTid,  
    ST_Point(  
      random_normal(0, 180),  
      random_normal(0, 80),  
      4326) AS geom  
FROM generate_series(0, 1000000) ASid;  


CREATEINDEX random_normal_geom_x ON random_normal USING GIST (geom);  

未经修改的“admin0” bounds (边界) ，即用于运行 spatial join (空间连接) 的 bounds (边界) ，看起来是这样的。大量重叠，并且在许多地方这些 bounds (边界) 覆盖了 polygon (多边形) 本身未覆盖的区域。

基准性能 (Baseline Performance)

使用未经修改的“admin0”数据执行 spatial join (空间连接) 的 baseline time (基准时间) 是 9 seconds (9 秒) 。

SELECT Count(*), admin0.name  
  FROM admin0 JOIN random_normal  
    ON ST_Intersects(random_normal.geom, admin0.geom)  
  GROUP BY admin0.name;  

改进一：使用 ST_Dump 进行 Decompose (分解)

如果我们不直接针对原始的“admin0”（其中包含像法国和拥有数百个岛屿的加拿大这样的奇怪情况）执行 **join (连接)**，而是先使用 ST_Dump ( https://postgis.net/docs/ST_Dump.html ) 将每个对象 decompose (分解/拆解) 成构成它的单个 **polygon (多边形)**，会怎样？

decomposed objects (分解后的对象) 覆盖的海洋区域要少得多，并且更准确地代表了它们所代表的 **polygon (多边形)**。

full join (完整连接) 100 万个点所需的时间（**including the cost of decomposing the objects (包括分解对象的成本)**）降至 **3.8 seconds (3.8 秒)**。

WITH polys AS  (  
  SELECT (ST_Dump(geom)).geom AS geom, name  
  FROM admin0  
)  
SELECT Count(*), polys.name  
FROM polys JOIN random_normal  
ON ST_Intersects(random_normal.geom, polys.geom)  
GROUP BY polys.name;  

改进二：使用 ST_Subdivide 进行 Subdivide (细分)

这里仍然查询了大量的海洋区域，而且一些 polygon (多边形) 不仅空间上非常大，还包含大量的 **vertices (顶点)**。如果我们使用 ST_Subdivide ( https://postgis.net/docs/ST_Subdivide.html ) 将这些 polygon (多边形)chop up (切分) 得更小，会怎样？

这些 bounds (边界) 几乎是完美的，它们覆盖的海洋区域非常少，并且它们还将任何 polygon (多边形) 的最大内存大小减少到不超过 **256 vertices (256 个顶点)。而且性能，even including the very expensive subdivision step (即使包含了非常昂贵的细分步骤)**，变得更快了。

WITH polys AS (  
  SELECT ST_Subdivide(geom,128) AS geom, name FROM admin0  
)  
SELECT Count(*), polys.name  
FROM polys JOIN random_normal  
ON ST_Intersects(random_normal.geom, polys.geom)  
GROUP BY polys.name;  

最终的查询仅需 1.8 seconds (1.8 秒)，是简单 boxes (边界框) 速度的两倍，是 naive spatial join (朴素空间连接) 速度的四倍。对于较小的点集合，naive approach (朴素方法) 的速度可能与 subdivision (细分) 一样快，但对于这个 100 万点的测试集来说，执行 subdivision (细分) 的 overhead (开销) 仍然远小于使用更有效的 bounds (边界) 所带来的收益。

结论

投入计算资源来创建更好、更小、更简单的 **geometries (几何图形)**，通过使 spatial index (空间索引) 更加有效，可以为大型数据集带来巨大的回报。