前言
接着灿灿老师分享的那本电子书：《Troubleshooting PostgreSQL》。本文就涉及书中的如下内容：
处理索引 （对应第三章）
3、处理索引
在本章中，将讨论数据库工作领域中最重要的主题之一——索引。在许多情况下，缺失或错误的索引是问题的主要来源，从而导致不良的性能、意外的行为和大量的失败。为了避免这些问题，本章将提供所有你需要的信息，使你的生活尽可能简单。
3.1 理解PG中的索引
如前所述，指标非常重要，其重要性怎么估计都不为过。因此，了解哪些索引是有用的，以及如何在不损害性能的情况下有效地使用它们，这一点非常重要。执行计划是理解PostgreSQL整体性能的一个重要组成部分。它们是了解数据库系统内部工作原理的窗口。理解执行计划和索引是很重要的。
3.1.1 使用简单的索引
test=# CREATE TABLE t_test (id int4);
CREATE TABLE
插入1000万条记录以后，
test=# INSERT INTO t_test
SELECT * FROM generate_series(1, 10000000);
INSERT 0 10000000
执行一个简单查询看看查询 计划：
test=# explain SELECT * FROM t_test WHERE id = 423425;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t_test (cost=0.00..169247.71
rows=1 width=4)
Filter: (id = 423425)
Planning time: 0.143 ms
(3 rows)
PG在执行计划里经历了四步：
解析：在此阶段，将检查查询的语法。解析器会抱怨语法错误。
重写：重写系统将重写查询、处理规则等等
优化：此时，PostgreSQL将决定策略并提出所谓的执行计划，该计划表示通过查询获得结果集的最快方式。
执行：由优化器生成的计划将由执行者执行，并将结果返回给最终用户。
要得到更详细的执行计划信息，可以使用explain analyze:
test=# explain analyze SELECT *
FROM t_test
WHERE id = 423425;
QUERY PLAN
------------------------------------------------------
Seq Scan on t_test (cost=0.00..169247.71
rows=1 width=4)
(actual time=60.842..1168.681 rows=1 loops=1)
Filter: (id = 423425)
Rows Removed by Filter: 9999999
Planning time: 0.050 ms
Execution time: 1168.706 ms
(5 rows)
这里总时间花了1.1秒。扫描用的是全表顺序扫描。因为没有创建索引。创建索引 之后：
test=# CREATE INDEX idx_id ON t_test (id);
CREATE INDEX
test=# explain analyze SELECT *
FROM t_test
WHERE id = 423425;
-------------------------------------------------------
Index Only Scan using idx_id on t_test
(cost=0.43..8.45 rows=1 width=4)
(actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (id = 423425)
Heap Fetches: 1
Planning time: 0.059 ms
Execution time: 0.034 ms
(5 rows)
执行时间降为0.034毫秒。这就是使用了索引之后的巨大优势。
3.1.2 索引如何工作
PG中使用的B树的实现是：Lehman-Yao树（估计是以人名命名）。首先，b树具有对数运行时行为。这意味着如果树不断生长，查询树所需的时间不会成比例地增加。100万倍的数据可能导致索引查找时间变慢20倍甚至更少(这取决于值的分布、可用的RAM数量、时钟速度等因素)。
有关B树更多信息，可以查阅： http://en.wikipedia.org/wiki/Binary_tree
3.2 避免使用索引时的麻烦
索引并不总是能解决问题，有时候，它自身也会成为问题。
test=# CREATE TABLE t_test (id int, x text);
CREATE TABLE
test=# INSERT INTO t_test SELECT x, 'house'
FROM generate_series(1, 10000000) AS x;
INSERT 0 10000000
test=# CREATE INDEX idx_x ON t_test (x);
CREATE INDEX
建完索引以后，我们看看表和索引分别占用的空间大小：
test=# SELECT
pg_size_pretty(pg_relation_size('t_test')),
pg_size_pretty(pg_relation_size('idx_x'));
pg_size_pretty | pg_size_pretty
----------------+----------------
422 MB | 214 MB
(1 row)
这样的话，总的空间大小差不多636MB。可问题是，对于下边的查询，索引也无能为力：
test=# explain SELECT * FROM t_test WHERE x = 'house';
QUERY PLAN
-------------------------------------------------------
Seq Scan on t_test (cost=0.00..179054.03 rows=10000000
width=10)
Filter: (x = 'house'::text)
(2 rows)
尽管有了索引，全是PG仍然走了全表扫描。原因非常简单：表中该列所有的值都是相同的，值的可选择性非常差。因此索引也就基本上派不上用场了。为什么用不上？我们可以这样想，索引的目的，是为了减少I/O。如果我们要用到表中的所有行，那么，我们不得不读取整个表。但是如果我们使用了索引，有必要会在读取表数据之前去读取所有的索引项。读取索引项再叠加读取整个表，开销肯定要大于只读取整个表。PG基于成本，会选择成本更低的一种读取方式。
作为例子，这里的索引也并不是一直就用不上，例如：
test=# explain SELECT * FROM t_test WHERE x = 'y';
QUERY PLAN
-------------------------------------------------------
Index Scan using idx_x on t_test (cost=0.43..4.45
rows=1 width=10)
Index Cond: (x = 'y'::text)
Planning time: 0.096 ms
(3 rows)
这里因为查询的值'y'并不在表里头出现（实际当中，也有可能是出现极为稀少的值），因此先读取索引项，直接定位。使用了索引扫描。
3.2.1 检查遗漏的索引
如何判断索引是否漏掉了？可以利用系统视图：pg_stat_user_tables。
SELECT schemaname, relname, seq_scan, seq_tup_read,
idx_scan, seq_tup_read / seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;
它实际上就是把那些执行了顺序扫描的表给统计出来，并且按照扫描的元组个数按大小排序，加上了限制条件seq_scan > 0。因为实际过程中有可能一个seq_scan都没有。
通过这个查询可以把那些“疑似”要添加索引的表给找出来。因为它们存在着大量的顺序扫描，并且会不断的发生。
显然，遍历最热门的候选对象并检查每个表是有意义的。请记住，PostgreSQL可以告诉你哪些表可能有问题，但它不会告诉你哪些列必须被索引。需要对应用程序有一些基本的了解。否则，你最终会做猜测工作。弄清楚应用程序对哪些列进行过滤是非常必要的。在这一点上，没有自动算法来检查。(补充：但是可以结合慢查询来一点点探测)
3.2.2 去掉无用索引
有些人可能想知道为什么索引太多是不好的。在读取数据时，在99%的情况下，索引根本没有问题(除非规划器做出了错误的决定，或者有些缓存太小)。然而，当涉及到插入时，索引是一个主要的性能瓶颈。
我们可以用简单的\timing元命令来度量时间。
test=# \timing
Timing is on.
test=# INSERT INTO t_test SELECT *
FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 6346.756 ms
这里看到，插入花了6.3秒。这是在有索引的情况下。如果是一张空表，不带索引：
test=# CREATE TABLE t_fast (id int, x text);
CREATE TABLE
Time: 92.034 ms
test=# INSERT INTO t_fast SELECT *
FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 2078.413 ms
时间开销仅为2秒。
您必须记住，每个不能产生任何好处的索引都是破坏性索引，因为它需要磁盘上的空间，更重要的是，它会大大降低写入速度。如果您的应用程序是写约束的，那么额外的索引可能是致命的。然而，在我的职业生涯中，我看到写约束的应用程序确实是少数。因此，过度索引可能和索引不足一样危险。然而，索引不足是更明显的问题，因为您会立即看到某些查询很慢。如果索引太多，性能问题通常会更微妙一些。
检测无用索引，可以利用系统视图：pg_stat_user_indexes
test=# \d pg_stat_user_indexes
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
相关的字段为idx_scan。它告诉我们某个索引被使用的频率。如果这个索引很少使用，那么删除它可能是有意义的。
请记住，在一个非常小的表(可能是查找表)或一个无论如何都应该增长的表上删除索引可能不是一个好主意。关注大型表及其索引是有意义的。
3.3 解决一般问题
3.3.1 管理外键
经常碰到的问题是与外键相关的索引 问题。请看下例：
test=# CREATE TABLE t_person (id int PRIMARY KEY, name text);
CREATE TABLE
test=# CREATE TABLE t_car (car_id int, person_id int REFERENCES t_person (id), info text);
CREATE TABLE
这里头， t_person表中确实是有id列的唯一索引（主键），但是我们往往忘了针对t_car表的外键person_id进行索引。试想下，如果要查询某个person_id下的车，就没有索引可以用上了。PG不会自动为您创建这个索引 (注意)。
补充：
postgres=# insert into t_person select n, 'test' || n from generate_series(1, 200000) as n;
INSERT 0 200000
postgres=# insert into t_car select n, n % 2000 + 1, 'tttt' || n from generate_series(1, 500000) as n;
INSERT 0 500000
postgres=# explain analyze select * from t_car where person_id = 1999;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..9997.07 rows=249 width=18) (actual time=1.267..34.448 rows=250 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on t_car (cost=0.00..8972.17 rows=104 width=18) (actual time=0.350..13.221 rows=83 loops=3)
Filter: (person_id = 1999)
Rows Removed by Filter: 166583
Planning Time: 0.103 ms
Execution Time: 34.475 ms
(8 rows)
建完索引以后：
postgres=# create index idx_person_id_car on t_car(person_id);
CREATE INDEX
postgres=# explain analyze select * from t_car where person_id = 1999;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t_car (cost=6.35..845.30 rows=249 width=18) (actual time=0.062..0.311 rows=250 loops=1)
Recheck Cond: (person_id = 1999)
Heap Blocks: exact=250
-> Bitmap Index Scan on idx_person_id_car (cost=0.00..6.29 rows=249 width=0) (actual time=0.039..0.039 rows=250 loops=1)
Index Cond: (person_id = 1999)
Planning Time: 0.123 ms
Execution Time: 0.334 ms
(7 rows)
推荐：高度推荐，对于外键的两边都创建合适的索引。
3.3.2 为几何数据类型创建GiST索引
但是现在怎样才能把事情做好呢?PostGIS项目(http://postgis)。Net/)拥有正确索引几何数据所需的一切。PostGIS是建立在所谓的GiST索引上的，它是PostgreSQL的一部分。什么是GiST?GiST的思想是提供一种索引结构，该结构提供普通b树无法提供的替代算法(例如，包含之类的操作)。
概述GiST如何在内部工作的技术细节肯定超出了本书的范围。因此，我建议您查看http://postgis。关于GiST和索引几何数据的更多信息。net/docs/manual-2.1/using_postgis_dbmanagement.html#idp7246368。
3.4 处理Like查询
LIKE是SQL语言中广泛使用的组件，它允许用户执行模糊搜索。有时，你对你要找的东西有一个想法，但你不确定它是什么。在这些情况下，通配符搜索可以派上用场，并显著改善用户体验。想想下面的例子: 你正在阅读一份刚刚收到的传真。在一份质量很差的传真上，你真的能一直区分B和8吗?我想没有。LIKE将通过允许您在查询中放置占位符来解决这个问题。
test=# CREATE TABLE t_location (name text);
CREATE TABLE
test=# COPY t_location FROM PROGRAM
'curl www.cybertec.at/secret/orte.txt';
COPY 2354
上边的例子，试图从中找出所有的城市和村庄。
3.4.1 简单like查询
test=# SELECT * FROM t_location WHERE name LIKE 'Wiener%';
name
-----------------
Wiener Neustadt
Wiener Neudorf
Wienerwald
(3 rows)
我们看下它的查询 计划：
test=# explain SELECT * FROM t_location
WHERE name LIKE 'Wiener%';
QUERY PLAN
---------------------------------------------------
Seq Scan on t_location (cost=0.00..43.42
rows=1 width=13)
Filter: (name ~~ 'Wiener%'::text)
Planning time: 0.078 ms
(3 rows)
走的是全表扫描。于是我们创建一个索引 ：
test=# CREATE INDEX idx_name
ON t_location (name text_pattern_ops);
CREATE INDEX
不能只在列上部署普通索引。为了使LIKE工作，在大多数情况下需要一个特殊的操作符类。对于文本，这个操作符类称为text_pattern_ops(对于varchar，它将是varchar_pattern_ops)。
什么是操作符类?这基本上是指数使用的一种策略。想象一个简单的b树。较小的值将移到树的左边缘，较大的值将移到树的右边缘。简单地说，操作符类将知道什么值被认为是小值，什么值被认为是大值。操作符类是教导索引如何行为的一种方式。
在本例中，为了首先启用索引扫描，索引的特殊操作符类是必要的。
test=# explain SELECT * FROM t_location
WHERE name LIKE 'Wiener%';
QUERY PLAN
-------------------------------------------------------
Index Only Scan using idx_name on t_location
(cost=0.28..8.30 rows=1 width=13)
Index Cond: ((name ~>=~ 'Wiener'::text)
AND (name ~<~ 'Wienes'::text))
Filter: (name ~~ 'Wiener%'::text)
Planning time: 0.197 ms
(4 rows)
当然，这里要注意, %只能出现在末尾，才能保证索引被用上。
3.4.2 复杂点的Like查询
test=# explain SELECT * FROM t_location
WHERE name LIKE '%Wiener%';
QUERY PLAN
-------------------------------------------------------
Seq Scan on t_location (cost=0.00..43.42 rows=1 width=13)
Filter: (name ~~ '%Wiener%'::text)
Planning time: 0.120 ms
(3 rows)
这不，当开头也出现%时，使用的又是全表扫描。解决这个问题：
为了解决这个问题，你可以使用PostgreSQL扩展。扩展是PostgreSQL的一个很好的特性，它允许用户轻松地将附加功能集成到服务器中。为了解决索引LIKE的问题，需要一个名为pg_trgm的扩展。这个特殊的扩展提供了对所谓的三字词(Trigrams)的访问，当涉及到模糊搜索时，这个概念特别有用:
test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
test=# CREATE INDEX idx_special ON t_location USING gist(name gist_trgm_ops);
CREATE INDEX
这次，GiST索引创建出来了，gist_trgm_ops操作符也可以用来处理索引中的问题。看看创建索引后的查询计划：
test=# explain SELECT * FROM t_location WHERE name LIKE '%Wiener%';
QUERY PLAN
-------------------------------------------------------
Index Scan using idx_special on t_location
(cost=0.14..8.16 rows=1 width=13)
Index Cond: (name ~~ '%Wiener%'::text)
Planning time: 0.232 ms
(3 rows)
这里我来加点，补充说明：这里总结的是一些通用的一般的处理方法。在实际使用过程中，还有其它的二字词的索引：pg_bigm。可以支持任意字数模糊查询，覆盖的范围更广。可以两者结合起来用。全文检索也不失为另一种解决方案。总之，能把索引用上，牺牲点存储空间，完全是值得的，取决于实际的业务需求。
3.5 寻找最佳匹配
到目前为止显示的查询并不是唯一可能导致问题的查询。想象一下搜索一个名字。也许，你不知道如何准确拼写它。您发出一个查询，结果只是一个空列表。对于最终用户来说，空列表可能非常令人沮丧。必须不惜任何代价避免挫败感，因此需要一个解决方案。
解决方案以所谓的距离算子(<->)的形式出现。它是这样工作的:
test=# SELECT 'PostgreSQL' <-> 'PostgresSQL';
?column?
----------
0.230769
(1 row)
附注：在你的PG环境下，针对字符串类型能使用到这个操作符'<->'。你可能碰到了。那是因为你没有创建扩展：pg_trgm。这里针对文本字符串的操作符：<->来源于扩展pg_trgm。
我们可以看到，这两个词间的距离为0.23。距离为0，表示完全相同，距离为1，表示完全不同。
然而，这个问题并不是关于两个单词之间的实际距离，而是关于哪个单词最接近。让我们假设我们正在寻找一个叫做Gramatneusiedl的村庄。现在，应用程序设计人员不能指望有人能够拼写这个棘手的名称。最终用户可能会去问Kramatneusiedel。精确匹配甚至不会返回任何一行，这对最终用户来说有点令人沮丧。
KNN（Nearest neighbor search）: K近邻查询 ，可以用来查询 距离 查询 目标最近的四个村庄：
test=# SELECT *, name <-> 'Kramatneusiedel'
FROM t_location
ORDER BY name <-> 'Kramatneusiedel'
LIMIT 4;
name | ?column?
----------------+----------
Gramatneusiedl | 0.52381
Klein-Neusiedl | 0.76
Potzneusiedl | 0.791667
Kramsach | 0.809524
(4 rows)
这样的查询 结果看起来也很符合预期。
3.6 解决全文检索问题
普通的索引问题解决之后，我们可以看看全文检索相关问题。
3.6.1 完全不用全文检索
一种情况是，很多人倾向于使用LIKE，用以替代全文检索。这样做，就像潘多拉的盒子，可能导致各种各样的性能问题。
为说明这一点，在这种情况下，莎士比亚的哈姆雷特被选中，可以从www.gutenberg.org(免费书籍档案)免费下载。要加载数据，终端用户可以使用curl，就像前面的示例所示。PostgreSQL将从管道中读取数据:
test=# CREATE TABLE t_text (payload text);
CREATE TABLE
test=# COPY t_text FROM PROGRAM 'curl http://www.gutenberg.org/cache/
epub/2265/pg2265.txt';
COPY 5302
这里，加载了5302行数据。现在目标是要查询所有包含company和find的行。你可以用：
test=# SELECT * FROM t_text
WHERE payload ILIKE '%company%'
AND payload ILIKE '%find%';
payload
--------------------------------------------
What company, at what expense: and finding
(1 row)
结果返回了一行。
这看起来不像是个问题，但它确实是个问题。让我们假设你不确定你是在找公司还是公司。如果你用谷歌搜索汽车，你也会对汽车感到满意，不是吗? 这同样适用于这里。因此，与其向PostgreSQL请求固定字符串，不如在索引表之前处理表中的数据。这里的专业术语是词干。以下是如何做到这一点:
test=# SELECT to_tsvector('english',
'What company, at what expense: and finding');
to_tsvector
---------------------------------
'compani':2 'expens':5 'find':7
(1 row)
使用英语语言规则来处理刚刚返回的文本。最后得到的是一组三个令牌。注意，有些词被遗漏了，因为它们是简单的停顿词。在what、at或and中没有语义负载，因此可以安全地跳过这些单词。
下一个重要的观察是单词是有词源的。词干提取是一种处理单词的过程，它将单词简化为某种词根(不是单词的真正词根，但通常与单词非常相似)。下面的清单显示了词干提取的一个示例:
test=# SELECT to_tsvector('english',
'company, companies, find, finding');
to_tsvector
--------------------------
'compani':1,2 'find':3,4
(1 row)
正如你所看到的，company和companies都被简化为compani，这不是一个真实的词，但很好地代表了实际的意思。这同样适用于查找和查找。现在重要的部分是这个词干向量被索引了;文本中出现的单词不是。这里的美妙之处在于，如果你正在搜索汽车，你最终也会找到汽车——这是一个不错的小改进!还有一个观察;注意词根后面的数字。这些数字表示单词在有词根的字符串中的位置。
为了确保良好的性能，有必要创建索引。对于全文搜索，GIN索引已被证明是最有益的索引。下面是我们的《哈姆雷特》的工作原理:
test=# CREATE INDEX idx_gin ON t_text
USING gin (to_tsvector('english', payload));
CREATE INDEX
test=# SELECT *
FROM t_text
WHERE to_tsvector('english', payload)
@@ to_tsquery('english', 'find & company');
payload
--------------------------------------------
What company, at what expense: and finding
(1 row)
一旦创建了索引，就可以使用它了。这里的重要部分是将词干提取过程创建的tsvector函数与我们的搜索字符串进行比较。目标是找到包含find和company的所有行。这里只返回一行。注意company在句子中出现在finding之前。搜索字符串也被截断了，所以这个结果是可能的。为了证明前面的观点，下面的列表包含了显示索引扫描的执行计划:
test=# explain SELECT *
FROM t_text
WHERE to_tsvector('english', payload)
@@ to_tsquery('english', 'find & company');
QUERY PLAN
-------------------------------------------------------
Bitmap Heap Scan on t_text
(cost=20.00..24.02 rows=1 width=33)
Recheck Cond: (to_tsvector('english'::regconfig,
payload) @@ '''find'' & ''compani'''::tsquery)
-> Bitmap Index Scan on idx_gin
(cost=0.00..20.00 rows=1 width=0)
Index Cond: (to_tsvector('english'::regconfig,
payload) @@ '''find'' &
''compani'''::tsquery)
Planning time: 0.096 ms
(5 rows)
PostgreSQL的全文索引提供了比这里实际描述的更多的功能。然而，考虑到本书的范围，不可能涵盖所有这些奇妙的功能。
3.6.2 全文检索与排序
全文搜索功能强大。然而，也有一些特殊情况需要注意。考虑以下类型的查询:
SELECT * FROM product WHERE "fti_query" ORDER BY price;
这种查询的用例可以很简单:查找标题中包含特定单词的所有图书，并按价格对它们进行排序;或者找到所有在其描述中包含披萨的餐馆，并按邮政编码(在大多数国家，是一个数字)进行排序。
GIN索引的组织方式非常适合全文搜索。问题在于，在GIN中，指向表中行的项目指针是按位置排序的，而不是按价格等标准排序的。那么让我们假设下面的查询:
SELECT *
FROM products
WHERE field @@ to_tsquery('english', 'book')
ORDER BY price;
在我作为PostgreSQL顾问的职业生涯中，我遇到了一个包含400万个产品描述的数据库，其中包含“书”这个词。这里将要发生的是，PostgreSQL必须找到所有这些书，并按价格排序。从索引中获取400万行，并且必须对400万行进行排序，这实际上是对查询性能的死刑判决。
现在人们可能会说，“为什么不缩小价格窗口呢?”在这种情况下，它没有帮助，因为20至50欧元的价格范围仍然包括300万欧元的产品。从PostgreSQL 9.4开始，很难修复这种类型的查询。因此，开发人员必须非常小心，并意识到GIN索引不一定提供排序的输出。
因此，从这种类型的查询返回的数据量与SQL请求的运行时成正比。PostgreSQL的未来版本可能会一劳永逸地解决这个问题。然而，与此同时，人们不得不接受这样一个事实，即您不能期望在短时间内对无限数量的行进行排序。
参考：
《Troubleshooting PostgreSQL》第三章
