postgres 对查询结果循环_Postgres索引那些事

本文转自Greenplum中文社区官方网站：greenplum.cn；

最实时最权威的Greenplum技术文档、博客，以及热点新闻，请关注greenplum.cn；

Postgres内部提供了很多种索引的方式，满足不同的索引需求。

传统索引方式(B-Tree)

说到传统，B-Tree索引当仁不让，它也是postgres里的默认索引方式。B-Tree的中文名字是二叉平衡树，每个叶子结点的深度都是一样的，特点是允许在O(logn)的时间内对所存储的内容进行搜索、顺序访问、插入及删除。

B-Tree索引对于唯一值的索引来说是相当之理想的(比如id序号这类数据)，它存储的是一对对的键值和指针，指针指向被索引数据所在的heap上的位置。

这里说下什么是heap。Heap(或者叫heap文件，用来区分同名不同义的数据结构heap)存储着postgres表里的所有数据(外部表除外)。heap里边的数据是无序的，这让数据库在向里面添加数据的时候不必考虑排序的问题。

目前postgres支持的索引中，只有B-Tree索引能够提供有序的查询结果(也就是支持Order by和Limit)，支持高并发场景(并行scan)，支持merge join和包含index scan的nested loop操作。

B-Tree是非常基本且古老的索引，但不代表它功能单一。

有时我们需要更多的功能，比如：表达式。

看下这个例子：

CREATE TABLE customer (name) AS SELECT 'cust' || i

FROM generate_series(1, 1000) AS g(i);

CREATE INDEX i_customer_name ON customer (name);

/*看一下直接对B-Tree索引列做fitler的plan*/

EXPLAIN SELECT * FROM customer WHERE name = 'cust999';

                            QUERY PLAN

------------------------------------------------

Index Only Scan using i_customer_name on customer ...

       Index Cond: (name = 'cust999'::text)

/* 没问题！使用了Index！另外，Index Only Scan的意思是，所有想要的column都在index里已经有了*/

/* 如果对B-Tree索引列在查询时增加一个表达式操作呢？*/

EXPLAIN SELECT * FROM customer WHERE lower(name) = 'cust999';

                            QUERY PLAN

-------------------------------------------------

Seq Scan on customer (cost=0.00..20.00 rows=5 width=7)

    Filter: (lower(name) = 'cust999'::text)

/* 又变回Seq Scan了！