增加索引真能提升查询性能吗？__众所周知，当创建一个新的索引之后，数据的修改操作会变慢并且该索引会占据一定的磁盘存储空间，这是我们创建一个新索引必须付出的代价。这也是为什么你需要尝试没有创建多余索引的原因。: 世界上功能最强大的开源数据库...

### 作者：Laurenz Albe  翻译：陈雁飞   校对：李冉
### 作者简介
#### Laurenz Albe : Cybertec公司的高级顾问和支持工程师。13年来，他一直参与并给PostgreSQL数据库做出贡献。
### 译者简介
#### 陈雁飞，开源PostgreSQL爱好者，一直从事PostgreSQL数据库运维工作
#### 李冉，瀚高基础软件工具开发工程师。
	
#### 众所周知，当创建一个新的索引之后，数据的修改操作会变慢并且该索引会占据一定的磁盘存储空间，这是我们创建一个新索引必须付出的代价。这也是为什么你需要尝试没有创建多余索引的原因（<https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/>）。

#### 但是很多人认为SELECT查询语句不会受到一个新索引的影响。当心索引没有使用到的时候，就会发生更糟糕的情况。

#### 当然，这个并不总是正确的。我在实际中看到不止一个这样的例子。下面我将给你介绍一个这样的案例，并告诉你能做些什么。
### 一个例子
#### 使用下面的表进行测试

```
CREATE TABLE skewed (
sort integer NOT NULL,
category integer NOT NULL,
interesting boolean NOT NULL
);
INSERT INTO skewed
SELECT i, i00, i>50000
FROM generate_series(1, 1000000) i;
CREATE INDEX skewed_category_idx ON skewed (category);
VACUUM (ANALYZE) skewed;
```
#### 我们希望找到前面20个且category值为42，insteresting值为真的元组。

```
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM skewed
WHERE interesting AND category = 42
ORDER BY sort
LIMIT 20;
```

#### 这个查询语句的性能很好

```
QUERY PLAN
--------------------------------------------------------------------
Limit (cost=2528.75..2528.80 rows=20 width=9)
(actual time=4.548..4.558 rows=20 loops=1)
Buffers: shared hit=1000 read=6
-> Sort (cost=2528.75..2531.05 rows=919 width=9)
(actual time=4.545..4.549 rows=20 loops=1)
Sort Key: sort
Sort Method: top-N heapsort  Memory: 25kB
Buffers: shared hit=1000 read=6
 -> Bitmap Heap Scan on skewed
(cost=19.91..2504.30 rows=919 width=9)
(actual time=0.685..4.108 rows=950 loops=1)
Recheck Cond: (category = 42)
Filter: interesting
Rows Removed by Filter: 50
Heap Blocks: exact=1000
Buffers: shared hit=1000 read=6
 -> Bitmap Index Scan on skewed_category_idx
(cost=0.00..19.68 rows=967 width=0)
(actual time=0.368..0.368 rows=1000 loops=1)
Index Cond: (category = 42)
Buffers: shared read=6
Planning time: 0.371 ms
Execution time: 4.625 ms
```

#### PostgreSQL使用索引扫描方法找到category值为42的1000个元组，筛选掉一些不感兴趣的，然后对其排序并返回前面20个元组。5毫秒的执行时间看起来非常不错。
#### 一个新索引让事情变得糟糕
#### 下面我们将增加一个索引，因为索引对排序有帮助。如果我们要找到前20个结果，那么绝对将是有意思的结果：
```
CREATE INDEX skewed_sort_idx ON skewed (sort);>
```
#### 突然间，情况变得更糟糕：

```
QUERY PLAN
-------------------------------------------------------------
Limit (cost=0.42..736.34 rows=20 width=9)
(actual time=21.658..28.568 rows=20 loops=1)
Buffers: shared hit=374 read=191
 -> Index Scan using skewed_sort_idx on skewed
(cost=0.42..33889.43 rows=921 width=9)
(actual time=21.655..28.555 rows=20 loops=1)
Filter: (interesting AND (category = 42))
Rows Removed by Filter: 69022
Buffers: shared hit=374 read=191
Planning time: 0.507 ms
Execution time: 28.632 ms
```

### 发生了什么？
#### PostgreSQL认为利用索引顺序查找可以更加快速的匹配到满足条件的20个元组。但是数据库并不知道满足条件的行是如何按照排序列分布的，也就是说数据库并不知道需要扫描69042行才能找到20个匹配元组（执行计划中显示过滤了69022行）。
### 我们该怎样做来获得更好的计划？
#### PostgreSQL 10版本中增加了扩展统计信息功能，（<https://www.postgresql.org/docs/current/planner-stats.html>）用于跟踪记录不同列之间的相关性，但是这不会记录值的分布情况，因此对我们没有帮助

#### 有两种解决方法：
#### 1.删除误导PostgreSQL的索引。如果可行，这是一个简单的解决方案。但是通常情况下不能这样做，因为索引要么用于保证唯一性约束，或者可以用于加速其他查询语句。
#### 2.重写查询语句，以便PostgreSQL不使用不当的索引。在许多可能的解决方案中，我列举下面两个：
#### ●带有offset 0的子查询
```
SELECT *
FROM (SELECT * FROM skewed
WHERE interesting AND category = 42
OFFSET 0) q
ORDER BY sort
LIMIT 20;
```
#### 这里利用OFFSET和LIMIT子句阻止进行上提子查询的优化，即使它们对查询结果没有任何影响。
#### ●使用表达式作为排序键

```
SELECT * FROM skewed
WHERE interesting AND category = 42
ORDER BY sort + 0
LIMIT 20;
```
#### 这里利用了PostgreSQL无法推断sort+0与sort相同的特性。请记住，PostgreSQL是支持扩展的，你可以定义自己的+运算符！

#### 原文地址：
#### <https://www.cybertec-postgresql.com/en/index-decreases-select-performance/>
### 译后感
#### 在上面的例子中，增加索引之后导致优化器选择非最优的执行计划本原因在于：目前优化器假设数据在页面中是均匀分布的，但是实际情况却不一定，因此导致代价估计产生“偏差”。详细分析可以参考德哥的博客 《PostgreSQL 优化器案例之 - order by limit 索引选择问题》，
#### 链接地址：https://github.com/digoal/blog/blob/master/201807/20180712_01.md   可以计算出一个“分水岭”值。

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)