充分利用PostgreSQL索引

在Postgresql中，索引对于有效地导航表数据存储（也称为“heap”）至关重要。Postgresql没有为heap维护集群，而MVCC架构导致了同一元组的多个版本。创建和维护有效且高效的索引以支持应用程序是一项基本技能。

请继续阅读，了解有关优化和改进部署中索引使用的一些提示。

注意：下面显示的查询在pagila示例数据库上运行。

使用覆盖索引

用一个查询来获取所有非活动客户的电子邮件。customer表有一个active列，查询很简单：

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;

QUERY PLAN

Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
该查询调用customer表的完整顺序扫描。让我们在 active 列上创建一个索引：

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;

QUERY PLAN

Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
这有作用，顺序扫描已成为“索引扫描”。这意味着Postgresql将扫描索引“idx_cust1”，然后进一步查找表的堆以读取查询所需的其他列值（在本例中为email列）。

PostgreSQL 11引入了覆盖索引。此功能允许您在索引本身中包含一个或多个其他列 - 也就是说，这些额外列的值存储在索引数据存储中。

如果我们要使用此功能并在索引中包含email的值，Postgresql将不需要查看表的heap以获取email的值。让我们看看这是否有效：

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;

QUERY PLAN

Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
“Index Only Scan”告诉我们索引本身现在完全满足查询，因此可能会避免读取表heap的所有磁盘I / O.

覆盖索引仅适用于B-Tree索引。而且，维护覆盖索引的成本自然高于常规成本。

使用部分索引

部分索引仅索引表中行的子集。 这使索引的尺寸更小，扫描速度更快。

假设我们需要获取位于加利福尼亚州的客户的email列表。 查询是：

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
它有一个查询计划，涉及扫描所有连接的表：

pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';

QUERY PLAN

Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)

看看普通索引的效果：

pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';

QUERY PLAN

Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
Seq Scan on address已被idx_address1上的Bitmap Index Scan 和Bitmap Heap Scan所取代。

假设这是一个频繁的查询并且需要进行优化，我们可以使用部分索引，该索引仅索引区域为“加利福尼亚”的address行：

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';

QUERY PLAN

Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
查询现在只读取索引idx_address2并且不触及表地址 。

使用多值索引

某些需要建立索引的列可能没有标量数据类型。 像jsonb ， 数组和tsvector这样的列类型具有复合或多个值。 如果您需要索引此类列，通常情况下您需要搜索这些列中的各个值。

让我们试着找到所有包含幕后花絮的电影。 电影表有一个名为special_features的文本数组列，如果电影具有该功能，它包括文本数组元素幕后花絮 。 要查找所有这些影片，我们需要在数组special_features的 任何值中选择所有具有“幕后花絮”的行：

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
它要求对堆进行全面扫描，成本为67。

让我们看看常规的B-Tree索引是否有帮助：

pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';

QUERY PLAN

Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)
甚至没有考虑该指数。B-Tree索引不知道它所索引的值中存在单个元素。

我们需要的是GIN指数。

pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';

QUERY PLAN

Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)
GIN索引能够支持单个值与索引复合值的匹配，从而导致查询计划的成本低于原始成本的一半。

消除重复索引

随着时间的推移，索引会累积，有时会添加一个与另一个完全相同的定义。您可以使用目录视图pg_indexes来获取索引的人类可读SQL定义。您还可以轻松检测相同的定义：

SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
这是在pagila数据库上运行时的结果：

pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;

(6 rows)
超集索引

您也可能最终得到多个索引，其中一个索引指向另一个索引的列的超集。这可能是也可能不是所希望的 - 超集可以导致仅索引扫描这是一件好事，但可能占用太多空间，或者可能不再使用最初打算优化的查询。

如果您希望自动检测此类索引，pg_catalog表pg_index是一个很好的起点。

未使用的索引

随着使用数据库的应用程序的发展，他们使用的查询也在不断发展。任何查询都可能不再使用先前添加的索引。 每次扫描索引时，统计信息管理器都会记录该索引，并且系统目录视图pg_stat_user_indexes中的累计计数可用作值idx_scan 。 在一段时间（例如，一个月）内监视此值可以很好地了解哪些索引未使用且可以删除。

以下是获取“公共”模式中所有索引的当前扫描计数的查询：

SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
像这样的输出：

pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;

(10 rows)
以较少的锁定重建索引

索引需要重新创建并不罕见。索引也可能变得臃肿，重新创建索引可以解决这个问题，从而使索引变得更快。索引也可能会损坏。 改变指数参数也可能需要重新创建指数。

启用Paralell索引创建

在PostgreSQL 11中，B-Tree索引创建是并发的。它可以利用多个并行工作程序来加速索引的创建。但是，您需要确保适当地设置这些配置条目：

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;
默认值不合理地小。 理想情况下，这些数字应该随着CPU核心数量的增加而增加。有关更多信息，请参阅 docs。

在后台创建索引

您还可以使用CREATE INDEX命令的CONCURRENTLY参数在后台创建索引 ：

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX
这与执行常规创建索引不同，因为它不需要锁定表，因此不会锁定写入。在缺点方面，需要更多时间和资源才能完成。