索引的种类
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]);
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参数来改善。
网友评论