PostGIS教程十一：空间索引
目录
目录
回想一下，空间索引是空间数据库的三个关键特性之一。索引使得对大型数据集使用空间数据库存储成为可能。在没有索引的情况下，对要素的任何搜索都需要对数据库中的每条记录进行”顺序扫描”。索引通过将数据组织到搜索树中来加快搜索速度，搜索树可以快速遍历以查找特定记录。
空间索引是PostGIS的最大价值之一。在前面的示例中，构建空间连接需要对整个表进行相互比较。这样做的代价很高：连接两个包含10000条记录的表（每个表都没有索引）将需要进行100000000次比较；如果使用空间索引，则比较次数可能低至20000次比较。
加载nyc_census_blocks表时，pgShapeLoader会自动创建名为nyc_census_blocks_geom_idx的空间索引。
为了演示空间索引对性能有多重要，让我们在没有空间索引的情况下搜索nyc_census_blocks表。
我们的第一步是删除索引：
DROP INDEX nyc_census_blocks_geom_idx;
注意：DROP INDEX语句从数据库系统中删除现有索引。有关更多信息，请参见PostgreSQL文档。
现在，查看pgAdmin查询窗口右下角的”计时表“并运行以下命令。我们的查询将搜索每个单独的人口普查块（census block），以确定宽街（Broad Street）那个记录。
SELECT blocks.blkid
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.geom, subways.geom)
WHERE subways.name = 'Broad St';
nyc_census_blocks表非常小（只有几千条记录），因此即时没有索引，查询也非常快。
现在，重新添加空间索引并再次进行查询。
CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom);
注意：USING GIST子句告诉PostgreSQL在构建索引时使用generic index structure（GIST-通用索引结构）。创建索引时，如果收到类似错误：ERROR:index row requires 11340 bytes，maximum size is 8911，则可能是因为没有添加USING GIST子句。
在我的测试计算机上，时间下降到9毫秒。表越大，索引查询的相对速度提高就越大。
一、空间索引是怎样工作的？
标准数据库索引基于正在编制索引的列的值创建层次结构树。空间索引略有不同-它们不能索引几何要素本身，而是索引几何要素的边界框。
在上图中，与黄星相交的线数是一条，即红线。但是与黄色框相交的要素的边界框是两个，红框和蓝框。
数据库有效回答”哪些直线与黄星相交“这一问题的方法是，首先是使用索引（速度非常快）解决”哪些框与黄色框相交“的问题，然后仅对第一次返回的几何要素进行”哪些直线与黄星相交“的精确计算。
对于一个大的数据表来说，这种先评估近似索引，然后进行精确测试的”两遍”系统可以从根本上减少回答查询所需的计算量。
PostGIS和Oracle Spatial都具有相同的”R-Tree“空间索引结构。R-Tree将数据分解为矩形（rectangle）、子矩形（sub-rectangle）和子-子矩形（sub-sub rectangle）等。它是一种自调优（self-tuning）索引结构，可自动处理可变数据的密度和对象大小。
二、纯索引查询
PostGIS中最常用的函数（ST_Contains、ST_Intersects、ST_DWithin等）都包含自动索引过滤器。但有些函数（如ST_Relate）不包括索引过滤器。
要使用索引执行边界框搜索（即纯索引查询–Index only Query-没有过滤器），需要使用”&&“运算符。对于几何图形，&&运算符表示”边界框重叠或接触”（纯索引查询），就像对于数字，”=“运算符表示”值相同”。
让我们将对”West Village“人口的纯索引查询与更精确的查询进行比较。使用&&我们的纯索引查询如下所示：
SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON neighborhoods.geom && blocks.geom
WHERE neighborhoods.name = 'West Village';
现在，让我们使用更精确的ST_Intersects函数执行相同的查询：
SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON ST_Intersects(neighborhoods.geom, blocks.geom)
WHERE neighborhoods.name = 'West Village';
结果数量低得多！第一个查询汇总了与社区（neighborhood）的关于边界框相交的每个人口统计块（census block）；第二个查询仅汇总了与该社区空间数据本身相交的人口统计块。
三、分析
PostgreSQL查询规划器（query planner）智能地选择何时使用或不使用索引来计算查询。与直觉相反，执行索引搜索并不总是更快：如果搜索将返回表中的每条记录，则遍历索引树以获取每条记录实际上比从一开始线性读取整个表要慢。
为了弄清楚要处理的数据的大概内容（读取表的一小部分信息，而不是读取表的大部分信息），PostgreSQL保存关于每个索引表列中数据分布的统计信息。默认情况下，PostgreSQL定期收集统计信息。但是，如果你在短时间内更改了表的构成，则统计数据将不会是最新的。
为确保统计信息与表内容匹配，明智的做法是在表中加载和删除大容量数据后运行ANALYZE命令。这将强制统计系统收集所有索引列的数据。
ANALYZE命令要求PostgreSQL遍历该表并更新其用于查询计划而估算的内部统计信息（查询计划分析将在稍后讨论）。
ANALYZE nyc_census_blocks;
四、清理（VACUUM）
值得强调的是，仅仅创建索引不足以让PostgreSQL有效地使用它。每当创建新索引或对表发出大量更新、插入或删除后，都必须执行清理（VACUUMing）。VACUUM命令要求PostgreSQL回收表页面中因记录的更新或删除而留下的任何未使用的空间。
清理对于数据库的高效运行非常关键，因此，PostgreSQL提供了一个“自动清理（autovacuum）”选项。
默认情况下，自动清理机制会根据活动级别确定的合理间隔自动清理（恢复空间）和分析（更新统计信息）。虽然这对于高度事务性的数据库是必不可少的，但在添加索引或大容量数据之后等待自动清理运行是不明智的，如果执行大批量更新，则应该手动运行VACUUM命令。
根据需要，可以单独执行清理和分析。发出VACUUM命令不会更新数据库统计信息；同样，执行ANALYZE命令也不会清理未使用的表空间。这两个命令都可以针对整个数据库、单个表或单个列运行。
VACUUM ANALYZE nyc_census_blocks;
五、相关函数
附录： PostGIS官方教程汇总目录
转载自：https://blog.csdn.net/qq_35732147/article/details/86212840
