postgresql 索引类型[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

postgresql提供了B-tree,R-tree,GiST和hash索引类型。不同的索引类型适合特定的查询类型。
绝大多数数据库都支持B-tree索引类型，postgresql默认的create index语句也是创建B-tree索引。

R-tree:
R树是一种用于处理多维数据的数据结构，用来访问二维或者更高维区域对象组成的空间数据.R树是一棵平衡树。树上有两类结点：叶子结点和非叶子结点。每一个结点由若干个索引项构成。对于叶子结点，索引项形如(Index，Obj_ID)。其中，Index表示包围空间数据对象的最小外接矩形MBR，Obj_ID标识一个空间数据对象。对于一个非叶子结点，它的索引项形如(Index，Child_Pointer)。 Child_Pointer 指向该结点的子结点。Index仍指一个矩形区域，该矩形区域包围了子结点上所有索引项MBR的最小矩形区域。
postgresql提供了支持空间存储的数据类型：

   
   
   

    
    
    Name Storage Size Representation Description point 
    
    
    16
    
    
     bytes Point on the plane (x,y) line 
    
    
    32
    
    
     bytes Infinite line (not fully implemented) ((x1,y1),(x2,y2)) lseg 
    
    
    32
    
    
     bytes Finite line segment ((x1,y1),(x2,y2)) box 
    
    
    32
    
    
     bytes Rectangular box ((x1,y1),(x2,y2)) path 
    
    
    16
    
    
    +
    
    
    16n bytes Closed path (similar to polygon) ((x1,y1),...) path 
    
    
    16
    
    
    +
    
    
    16n bytes Open path [(x1,y1),...] polygon 
    
    
    40
    
    
    +
    
    
    16n bytes Polygon (similar to closed path) ((x1,y1),...) circle 
    
    
    24
    
    
     bytes Circle 
    
    
    <
    
    
    (x,y),r
    
    
    >
    
    
     (center and radius)
   
   
   

参考文档：http://www.postgresql.org/docs/8.3/static/datatype-geometric.html
在这些数据类型的列上可以创建R-tree类型的索引。举例说明：

  
  
  

   
   
   wyz
   
   
   =
   
   
   # CREATE TABLE abc (shape polygon); CREATE wyz
   
   
   =
   
   
   # CREATE INDEX spacial_idx ON abc USING RTREE (shape); CREATE
  
  
  

相对R-tree做深一步了解可以看以下Guttman的《R-Trees – A Dynamic Index Structure for Spatial Searching》。

Hash
查看文档说hash类型的索引常用于进行”=”操作的数据列。其中也著名hash类型索引相对B-tree类型的索引不管是存储还是性能都比较差，不建议使用hash类型索引。
创建hash类型索引的过程实质是对数据进行hash函数操作，然后存储hash值。从hash函数原理的角度去考虑就能很快发现hash类型索引的劣势在哪了。对在一个OLTP的数据库中采用hash类型索引的表进行DML操作时，不仅增大CPU的开销，也没有节省存储的消耗。

GiST
通用搜索树（Generalized Search Tree），与其说是一种索引类型，不如说是建立索引的平台或者模板。利用Gist可以建立B-tree,R-tree或者其他的类型索引。
对GiST感兴趣可以参考GiST的项目网站http://gist.cs.berkeley.edu/

postgresql的索引创建语法： 

  
  
  

   
   
   CREATE [ UNIQUE ] INDEX indexname ON table [ USING indextype ] ( functionname ( column [, ...] ) [ opclass ] )
  
  
  

索引的类型

PostgreSQL 提供了好几种索引类型∶ B-tree，R-tree，Hash 和 GiST。每种索引类型都比较适合某些特定的查询类型，因为它们用了不同的算法。 缺省时，CREATE INDEX 命令将创建一个B-tree索引， 它适合大多数情况。

B-tree 可以处理那些可以按照某种顺序存储的数据的等于和范围查询。特别是在一个建立了索引的列涉及到使用下列操作符之一进行比较的时候， PostgreSQL 的查询规划器都会考虑使用 B-tree 索引∶

构造等效于这些操作符的组合，比如 BETWEEN 和 IN，也可以用 B-tree 索引搜索视线。（但是要注意，IS NULL 不等于=，并且是不能建立索引的。）

优化器也会把 B-tree 所以用于涉及模式匹配操作符 LIKE， ILIKE，~，和 ~* 的查询，条件是模式锚接在字串的开头，比如，col LIKE ‘foo%’ 或者 col ~ ‘^foo’，而不是 col LIKE ‘%bar’。 但是，如果你的服务器不适用 C 区域，那么你需要用一个特殊的操作符表创建索引来支持模式匹配查询上的索引。R-tree 索引特别适合于空间数据。要创建一个 R-tree 索引， 使用下面形式的命令

CREATE INDEX name ON table USING RTREE (column);

当一个索引了的列涉及到使用下列操作符之一进行比较的时候，PostgreSQL 的查询规划器都会考虑使用 R-tree 索引∶

散列（hash）索引只能处理简单的等于比较。 当一个索引了的列涉及到使用= 操作符进行比较的时候， 查询规划器会考虑使用散列索引。 下面的命令用于创建散列索引∶

CREATE INDEX name ON table USING HASH (column);

注意: 测试表明 PostgreSQL 的散列索引和 B-tree 索引类似或者慢一些， 而且散列索引的尺寸和制作时间要差很多。而且在高度并发的条件下，散列索引的性能也很差。因此，目前咱们不建议使用散列索引。

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

B-tree 索引是一个 Lehman-Yao 高并发 B-tree 的实 现。R-tree 索引用 Guttman 的二次分割算法实现了标准的 R-tree。 hash（散列）索引是 Litwin 的线性散列的一个实现。 咱们单独的列出这些所用的算法是要表明所有这些索引方法都是完全动态的并且不必进行周期性的优化（例如，象静态散列算法常见的那样）。

GIST使用举例：

CREATE INDEX ttf_nav_link_geom on ttf.nav_link using gist(geom);

To create a GiST index on a point attribute so that we can efficiently use box operators on the result of the conversion function:
CREATE INDEX pointloc
ON points USING gist (box(location,location));
SELECT
*
FROM points
WHERE box(location,location) && ’(0,0),(1,1)’::box;

To create an index without locking out writes to the table:
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

PostgreSQL 8.1 中文文档

http://www.php100.com/manual/PostgreSQL8/sql-createindex.html