索引的种类
PostgreSQL默认支持3种索引:B-Tree indexes, R-Tree indexes和 GiST indexes。
B-Tree用于可以在一个方向上排序的数据,如数字(numbers),字母(letters),日期(dates)。地理数据不能再一个方向上排序,所以B-Tree不能用于地理数据。
R-Trees是将数据分解成矩形,子矩形,子子矩形等。R-Trees被一些数据库用于地理数据的索引。但是PostgreSQL的R-Tree实现没有GiST实现那么健壮。
GiST(Generalized Search Trees)将数据分解成“东西在哪一边”,“东西覆盖什么”,“东西在什么里”,它可以用于广泛的数据结构,包括地理数据。PostGIS在GiST的基础上实现R-Tree去索引地理数据。
GiST的全称是“通用搜索树”,是索引的一般形式。
GiST用于加快各种不规则数据结构(整形数组,光谱数据等)的查询速度,这些数据不服从普通的B-Tree索引。
一旦地理数据表超过几千行,你就需要建立一个索引来加快数据的空间搜索(除非你的所有搜索都基于非地理属性)。
建立GiST索引的语法:
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
上面的语法是将建立2D索引。要建立PostGIS2.0+支持的n维索引,你可以用下面的语法:
CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);
建立空间索引是一个计算密集的工作:在一个1百万数据的表里,300MHZ的Solaris机器上,建立GiST索引大约需要1个小时。
建立索引之后,非常重要的是要强制PostgreSQL做优化查询的数据表分析:
VACUUM ANALYZE [table_name] [(column_name)];
-- 下面只在PostgreSQL 7.4以下(含)版本需要
SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);
-- 下面只在PostgreSQL 7.4以下(含)版本需要
SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);
GiST索引比R-Tree索引有两个优势。
第一、GiST索引是"null值安全"的,索引的字段可以包括空值(null)。
第二、GiST索引支持"lossiness"的概念,这个概念对于大地理数据分厂重要(大于PostgreSQL的8K页面大小)。Lossiness允许PostgreSQL只存储地理信息中“重要”的一部分数据到索引中,仅计算边框。地理数据大于8K会导致R-Tree索引创建失败。
通常情况下,索引加快数据访问。一旦索引建立,查询规划器决定何时使用索引信息来加快查询,这个过程是透明的。
不幸的是,PostgreSQL查询规划器对GiST索引的优化不是很好,所以有些查询需要使用空间索引来替代默认的遍历全表。
如果你发现你的空间索引没有被使用,你可以做以下几件事情:
1、首先,确保分析收集了表的记录数量和分布,保证查询规划器使用更好的索引进行优化查询。从PostgreSQL8.0版本以后,运行VACUUM ANALYZE操作。你应该定期运行vaccuum。
2、如果vacuum不起作用,你可以强制规划器使用索引信息,通过使用SET ENABLE_SEQSCAN=OFF命令。你应该谨慎使用这个命令,并只在空间索引的查询中使用。一般来说,使用B-Tree索引时,查询规划器会更好的知道如何查询,一旦你运行了你的查询,应该考虑将ENABLE_SEQSCAN设置回来,这样其他查询可以正常利用规划器。
3、如果你发现查询规划器在全表遍历和索引使用上有错误,试着减少postgresql.conf中random_page_cost的值,或者使用SET random_page_cost=#命令。默认值是4,设置成1或2。递减该值使规划器更倾向于使用索引扫描。
检查索引的使用
尽管在PostgreSQL中的索引不需要维护或调整,但是检查索引在真实查询中的作用还是非常重要的。
检查独立查询中的索引使用情况可以使用EXPLAIN命令。
很难用跟一个标准化公式来决定需要创建哪些索引。
这里有一些典型事例:
1、总是先运行ANALYZE。这个命令收集统计数据在表中的分布值。这个值是估计查询结果条数所必须的,查询规划器根据它来实际分配查询消耗。在缺乏任何真正的统计数据时,会使用一些假设的默认值,这是几乎可以肯定是不准确的。在不运行ANALYZE时就检查索引的使用是错误的。
2、使用真实数据进行实验。
3、当索引未被使用时,可以强制使用。有些运行参数可以关掉各种规划类型。
例如关闭顺序扫描(ENABLE_SEQUSCAN)和嵌套循环连接(ENABLE_NESTLOOP),关掉这些最基本的规划,可以破事系统使用不同的规划。如果系统仍然使用循序扫描或前台循环连接则可能是不适用索引的根本原因。比如查询条件不匹配索引。
4、如果强制使用索引时,索引被使用了,那么有两种可能:使用的索引不恰当或者查询规划器的消耗估计不反应真实情况。
可以用EXPLAIN ANALYZE命令找原因。
5、如果证明是查询规划器的消耗估计错误,有两种可能:
1)总消耗是从每行节点的时间倍数计算得来。估计该规划节点的消耗可以通过运行参数进行调整。
2)不准确的评估是由于统计数据不足造成的。有可能可以通过调整statistics-gathering参数来改善。
相关推荐
PostGIS支持多种空间索引类型,如GiST(Generalized Search Tree)、SP-GiST(Spatial GiST)和BRIN(Bitmap R-Tree Indexes),每种都有其适用场景和性能特点。 总的来说,PostGIS的索引集群是优化数据库性能的一...
在PostgreSQL中创建空间索引,首先需要安装并启用POSTGIS扩展。一旦完成,你可以创建一个名为`GIST`(Generalized Search Tree)的空间索引。例如,如果你有一个包含地理点数据的表`locations`,并且该表有一个名为`...
PostGIS弥补了PostgreSQL在GIS领域的不足,它遵循OpenGIS规范,提供空间对象、空间索引、空间操作函数和操作符。PostGIS扩展了PostgreSQL的空间数据类型,包括三维和四维数据以及EWKT和EWKB数据类型,后者包含了SRID...
PostGIS 是 PostgreSQL 的空间数据库扩展,提供空间信息服务功能,包括空间对象、空间索引、空间操作函数和空间操作符。PostGIS 完全遵循 OpenGIS 规范,提供了强大的空间数据处理能力。 PostGIS 安装依赖项 ------...
4. **空间索引**:介绍如何建立和使用空间索引来提高空间查询性能,包括 GIST(GiST - Generalized Search Tree)和 SP-GiST(Spatio-temporal GiST)索引。 5. **地理编码与反地理编码**:讲解如何将地址转换为...
4. **空间索引**:PostGIS 支持使用 R-树等高效的空间索引,加速空间查询。理解如何创建和管理这些索引对于性能优化至关重要。 5. **投影和坐标系统**:了解如何在不同的坐标系之间转换,如 WGS84 和 UTM,这对于...
PostGIS是一个空间扩展插件,支持空间对象存储、空间索引、空间操作函数和空间操作符等一系列功能,能满足大规模空间数据的索引、存取和计算等要求。 使用PostgreSQL数据库、Greenplum分布式集群数据库,再结合...
PostGIS是对象关系型数据库系统PostgreSQL的一个扩展,PostGIS提供如下空间信息服务功能:空间对象、空间索引、空间操作函数和空间操作符。同时,PostGIS遵循OpenGIS的规范。 PostGIS的版权被纳入到GNU的GPL中,也...
例如,查询语句执行缓慢可能是因为SQL没有正确利用空间索引,或者空间索引本身的效率低下。为了解决这些问题,我们需要确保查询语句能够直接引用几何字段,避免在WHERE子句中使用导致索引失效的表达式函数,如ST_...
3. **空间索引**:支持使用R-Tree等高效的空间索引,加速空间查询性能。 4. **遵守OGC标准**:PostGIS遵循开放地理空间联盟(OGC)的标准,如简单特征访问规范(Simple Feature Access),确保与其他GIS软件的兼容性...
此压缩包包括PostGIS 2.2.0dev 开发手册(伏念译) 、PostGIS 3.1.4dev Manual(双语-彩云小译网页翻译) 、PostGIS 3.1.4dev ...PostGIS 包括对基于 GIS 的 R-Tree 空间索引的支持,以及对 GIS 对象进行分析和处理的功能。
该扩展提供了许多功能,包括地理空间数据类型、空间索引、空间查询和分析等。此外,PostGIS还提供了许多工具和函数,方便用户进行地理空间数据的处理和分析。 postgis-bundle-pg15x64-setup-3.3.2-2.exe是一个针对...
6. 空间索引:利用R-树等高效的数据结构,加速空间查询和分析。 使用PostGIS JDBC驱动的步骤大致如下: 1. 添加JDBC驱动到项目类路径:将"postgis-1.5.2.jar"添加到Java项目的类路径中,确保运行时能正确加载驱动。...
6. **空间索引**:通过使用R-Tree索引,提高空间查询的性能。 7. **GIS分析**:提供空间分析工具,如缓冲区分析、最短路径计算、地形分析等。 **PostgreSQL与PostGIS的集成:** PostGIS与PostgreSQL紧密集成,允许...
5. **空间索引**:为了高效查询大型地理数据集,PostGIS提供了空间索引,如R-树,能加速空间关系查询。 6. **地理编码(Geocoding)**:PostGIS可以将地址转换为经纬度坐标,这是一个常见的地理信息处理任务,对于...
10. **空间索引**:为了加速空间查询,PostGIS 提供了基于 R-Tree 的空间索引。正确创建和使用空间索引可以显著提高查询性能。 在学习和使用 PostGIS 1.2.1 时,可以参考官方文档、教程和社区资源,逐步掌握其核心...
2. **空间索引**:为了提高空间查询性能,PostGIS支持创建空间索引,如GIST(Generalized Inverted Indexes for Trees)和SP-GiST(Space Partitioning Generalized Search Tree),它们能加速对空间数据的检索。...
PostGIS是开源地理数据库扩展,它为开源数据库系统PostgreSQL提供了强大的空间和地理数据支持。这个"postgis-2.5.2.zip"文件包含了PostGIS的2.5.2版本,这是一个重要的更新,因为每个版本都可能包含性能优化、新功能...
PostGIS还支持空间索引,显著提升查询性能。 6. **GIS应用程序集成**: PostGIS可以与各种GIS软件集成,如QGIS、ArcGIS、MapServer等,提供数据存储和后端服务。开发人员也可以通过ODBC/JDBC接口或API(如Python的...