PostgresSQL学习手册(索引)

一、索引的类型：

PostgreSQL提供了多种索引类型：B-Tree,Hash,GiST和GIN，由于他们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1.B-Tree：

CREATE TABLE test(

     id integer,

     content varchar

);

CREATE INDEX test_id_index ON test1(id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符"<、<=、-、>=和>“作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE, ILIKE, ~和~*，仅当模式存在一个变量，且该变量位于模式字符串的开头，如col LIKE 'foo%'或col ~'^foo',索引才会生效，否则将会执行全局扫描，如:col LIKE ‘%bar'。

2.Hash：

CREATE INDEX name ON table USING hash(column);

散列（Hash）索引只能处理简单的等于比较。当索引列等于操作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不REINDEX重建散列索引。

3.GiST

GiST索引不是一种单独的索引类型，而一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

4.GIN：

GIN索引是反转索引，它可以处理包含多个键的值（比如数组）。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如<@, @>、=、&&等。

二、复合索引：

PostgreSQL中的索引可以定义在数据表的多个字段上，如：

CREATE TABLE test2(

    major int,

    minor int,

    name varchar

) 

CREATE INDEX test2_mm_idx ON test2(major, minor);

在当前的版本中，只有B-Tree，GiST和GIN支持复合索引，其中最多可以声明32个字段。

1.B-Tree类型的复合索引：

在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段（最左边）被包含其中时，才可以获得最多效率。

2.GiST类型的复合索引：

在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只会被限制索引返回的条目。假设第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。

3.GIN类型的复合索引：

与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。

三。组合多个索引：

PostgreSQL可以在查询时组合多个索引（包含同一索引的多次使用），来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x=42 OR x=47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询字句，之后再讲这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x=5 AND y=6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后再内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或OR的操作并得出最终的BITMAP。最后，索引数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原有的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

四、唯一索引：

目前，只有B-Tree索引可能被声明为唯一索引。

CREATE UNIQUE INDEX name ON table （column [,....])；

如果索引声明为唯一索引，那么就不允许出现多个索引值相同的行。我们认为NULL值相互不相等。

http://www.cnblogs.com/stephen-liu74/archive/2012/05/09/2298182.html