索引的种类
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是对象关系型数据库系统PostgreSQL的一个扩展,PostGIS提供如下空间信息服务功能:空间对象、空间索引、空间操作函数和空间操作符。同时,PostGIS遵循OpenGIS的规范。PostGIS的版权被纳入到GNU的GPL中,也...
NULL 博文链接:https://guoranaccp.iteye.com/blog/2093673
PostGIS是一个空间扩展插件,支持空间对象存储、空间索引、空间操作函数和空间操作符等一系列功能,能满足大规模空间数据的索引、存取和计算等要求。 使用PostgreSQL数据库、Greenplum分布式集群数据库,再结合...
PostGIS是对象关系型数据库系统PostgreSQL的一个扩展,PostGIS提供如下空间信息服务功能:空间对象、空间索引、空间操作函数和空间操作符。同时,PostGIS遵循OpenGIS的规范。 PostGIS的版权被纳入到GNU的GPL中,也...
此压缩包包括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是一个针对...
PostGIS简介、安装、新建空间数据库、简单SQL语句查询练习、几何数据操作和练习、空间关系操作与练习、空间链接与练习、投影数据与练习、空间索引与练习…… 还包括PostgreSQL for spatial的配置,验证,postgis函数...
PostGIS是一个对象关系型数据库系统PostgreSQL的扩展,它能吧...PostGIS有基于GiST的-R树空间索引的支持以及丰富的分析和GIS对象的处理函数支持本文档为postgis官方2.2版本中文版,包含各函数的使用方法与数据库理论.
下面简短介绍了空间数据库的发展,然后回顾了将空间数据与数据库关联起来的三个方面:数据类型、索引和函数空间数据类型用于指定图形为点(point)、线(line)和面(polygon)多维度空间索引被用于进行
knex-postgis 用于空间扩展。 去做 使用自定义方法扩展knex.client.QueryBuilder.prototype 。
PostGIS模式有用的PostGIS问题,模式和解决方案的摘要,其来源包括:参见分类目录多边形查询点查询行查询多边形查询相交查询空间关系查询使用索引查询空间统计查询距离查询最近邻居查询几何形状查询无效的几何查询...
它扩展了ActiveRecord迁移语法,以支持创建空间类型的列和空间索引。 您可以控制PostGIS提供的各种属性,例如纬度,尺寸和地理数学与几何数学。 其次,它可以识别空间类型并将其正确地转换为RGeo几何对象。 适配器...
h3-pg:H3的PostgreSQL绑定,H3是分层的六角形地理空间索引系统
应用到《空间数据库技术及应用》课程中关于空间数据库几个重要扩展如空间数据扩展postgis、栅格数据数据扩展postgis_raster等技术,并结合课程内容空间数据库设计与实现、空间索引、缓冲区分析、空间关系分析等操作...
pgh3:H3分层地理空间索引系统的PostgreSQL扩展
Neo4j空间 Neo4j Spatial是一个促进中空间数据的导入,存储和查询的。 该项目手册作为部署在本地版本中。 历史 该库开始于2010年初,是Neo-Technology与之间的协作愿景作为其2010年Google Summer of Code(GSoC)...
常用地理数据库的对比测试,主要对比测试了MySQL 5.7.4的Spatial Extension、PostgreSQL9.2.2的PostGIS、Oracle Spatial10.2.0.1.0、ArcSDE10.0(ST_Geometry)及MongoDB2.6.5五种数据库在建立空间索引的情况下的...
matlab小动物图形代码很棒的地理空间 一长串的地理空间分析工具。 地理空间分析,或仅仅是空间分析,是一种将统计...-还支持GeoJSON和空间索引。 -Spatial Extender使您可以在DB2中存储,管理和分析空间数据。 -Informi
matlab小动物图形代码 Geospatial / Ag领域中有关机器学习/深度学习的资源,帖子,项目,数据集 内容 ...-还支持GeoJSON和空间索引。 -Spatial Extender使您可以在DB2中存储,管理和分析空间数据。 -
Tarantool /地理信息系统Tarantool / GIS是的功能齐全的地理空间扩展。... 使用Tarantool的RTREE快速实现内存空间索引。 Tarantool / GIS处于Alpha阶段。 所有功能均已记录在案,并由单元测试和功能测试全面涵盖