postgresql索引的使用

## 索引的类型

*PostgreSQL提供了几种索引类型：B-tree，Hash，GiST，SP-GiST，GIN和BRIN。每个索引类型使用不同的算法，适合不同种类的查询。默认情况下，CREATE INDEX命令创建B-tree索引，这符合最常见的情况。*

+ 目前，只有B-tree，GiST，GIN和BRIN索引类型支持多列索引。最多可以指定32列。

+ 多列B树索引可以用于涉及索引列的任何子集的查询条件，但是当前导（最左侧）列存在约束时，索引效率最高。确切的规则是，前导列上的等式约束以及不具有相等约束的第一列上的任何不等式约束将用于限制扫描的索引部分。在索引中检查这些列右侧的列的约束，因此它们保存对表的访问，但它们不会减少必须扫描的索引部分。例如，给定（a，b，c）上的索引和查询条件WHERE a = 5 AND b> = 42 AND c <77，必须从a = 5和b = 42通过最后一个条目，a = 5，c> = 77的索引条目将被跳过，但仍需扫描。这个索引原则上可以用于对b和/或c有约束的查询，而对b没有约束，但是整个索引必须被扫描，所以在大多数情况下，计划员会喜欢使用索引进行顺序表扫描。

## B-Tree索引页分为几种类别

>meta page ~~存放的是索引的元数据信息（描述索引本身的信息），每个索引文件的第0页都是meta page~~

>root page ~~meta page的下一页（第一页）叫做root page，对于索引量小的情况（一页root page即可存放所有索引数据），只有一个root page即可~~

>branch page ~~用于连接root page和leaf page（不存放实际的数据，存放索引数据）~~

>leaf page ~~存放指向tuple物理位置的索引~~

```

select * from bt_metap('tab1_pkey');

select * from bt_page_stats('tab1_pkey',1);

select * from bt_page_items('tab1_pkey',1);

```

# 实际应用

**唯一索引**

``` sql

索引也可用于强制列的值的唯一性，或多个列的组合值的唯一性。

CREATE UNIQUE INDEX name ON table (column [, ...]);

 

```

**表达式上的索引**

``` sql

索引列不必仅仅是基础表的列，也可以是从表的一个或多个列计算的函数或标量表达式。此功能对于根据计算结果快速访问表是非常有用的。

SELECT * FROM test1 WHERE lower(col1) = 'value';

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

```

**部分索引**

``` sql

设置部分索引以排除常见值

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)

WHERE NOT (client_ip > inet '192.168.100.0' AND

client_ip < inet '192.168.100.255');

设置部分索引以排除不感兴趣的值

CREATE INDEX orders_unbilled_index ON orders (order_nr)

WHERE billed is not true;

设置一个部分唯一索引

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)

WHERE success;

 

```

索引扫描的堆访问部分涉及到堆中的大量随机访问，这可能很慢，特别是在传统的旋转介质上。 基本思想是直接从每个索引条目返回值，而不是查询关联的堆条目。这种方法可以使用两个基本限制：

- 索引类型必须支持仅索引扫描。 B-tree索引总是支持的。 GiST和SP-GiST索引支持一些操作符类的仅索引扫描，而不支持其他操作符。其他索引类型不支持。基本要求是索引必须物理存储或者能够重建每个索引条目的原始数据值。作为反例，GIN索引不能支持仅索引扫描，因为每个索引条目通常只保留原始数据值的一部分。

- 查询必须仅引用索引中存储的列。例如，给定一个也有列z的表的x和y列的索引，这些查询可以使用仅索引扫描：

SELECT x, y FROM tab WHERE x = 'key';

SELECT x FROM tab WHERE x = 'key' AND y < 42;

但是另外的这些不能使用：

SELECT x, z FROM tab WHERE x = 'key';

SELECT x FROM tab WHERE x = 'key' AND z < 42;

 

**检查索引的使用**

- 始终先运行ANALYZE。此命令收集表中值的分布统计信息。需要此信息来估计查询返回的行数，Planner需要为每个可能的查询计划分配实际成本。在没有任何真实的统计数据的情况下，假定一些默认值，这几乎是不准确的。因此，检查应用程序的索引使用而不运行ANALYZE是不可行的。

- 使用实际数据进行实验。使用测试数据设置索引会告诉您测试数据需要哪些索引，但仅仅是对测试数据有效。

- 使用非常小的测试数据集尤其致命。当选择100000行中的1000个可能是索引的候选者时，选择100行中的1个几乎不会是这样的，因为100行可能适合单个磁盘页面，并且没有计划可以顺序地获取1个磁盘页面。

- 在编写测试数据时也要小心，当应用程序尚未投入生产时，这通常是不可避免的。非常相似，完全随机或以排序顺序插入的值将使统计数据偏离实际数据所具有的分布。

- 当不使用索引时，测试可能有助于强制使用它们。运行时参数可以关闭各种计划类型。例如，关闭最基本的计划的顺序扫描（enable_seqscan）和嵌套循环连接（enable_nestloop）将强制系统使用不同的计划。如果系统仍然选择顺序扫描或嵌套循环连接，那么可能还有一个更根本的原因是为什么索引没有被使用;例如，查询条件与索引不匹配。 （什么样的查询可以使用上一节中介绍的类型的索引。）

- 如果强制索引使用确实使用索引，那么有两种可能性：系统是正确的，使用索引确实不合适，或者查询计划的成本估计并不反映现实情况。所以你应该检查使用和不使用索引进行查询时的时间消耗。 EXPLAIN ANALYZE命令在这里非常有用。

- 如果事实证明成本估计是错误的，那么再有两种可能性。总成本由每个计划节点的每行成本乘以计划节点的选择性估计值计算。可以通过运行时参数来调整计划节点的成本。选择性估算不准确是由于统计数据不足。通过调整统计信息收集参数可以改善这一点。