🥇我们使用了PostgreSQL中索引的所有特性

在 Postgres 世界中，索引对于数据库存储（称为“堆”）的有效导航至关重要。 Postgres 不支持集群，并且 MVCC 架构会导致您最终得到同一元组的多个版本。因此，能够创建和维护高效的索引来支持应用程序非常重要。

以下是优化和改进索引使用的一些技巧。

注意：下面显示的查询适用于未修改的帕吉拉样本数据库.

使用覆盖索引

让我们看一下提取非活动用户电子邮件地址的请求。桌子 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)

这很有帮助，随后的扫描变成了“index scan”。这意味着 Postgres 将扫描索引”idx_cust1”，然后继续搜索表堆读取其他列的值（本例中为该列 email）查询需要的。

PostgreSQL 11 中引入了覆盖索引。它们允许您在索引本身中包含一个或多个附加列 - 它们的值存储在索引数据存储中。

如果我们利用此功能并将电子邮件值添加到索引中，那么 Postgres 就不需要在表堆中搜索该值。 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' 告诉我们查询现在只需要一个索引，这有助于避免所有磁盘 I/O 读取表堆。

覆盖索引目前仅适用于 B 树。但在这种情况下，维护工作量会更高。

使用部分索引

部分索引仅索引表中行的子集。这可以节省索引的大小并使扫描速度更快。

假设我们想要获取加利福尼亚州客户的电子邮件地址列表。请求将是这样的：

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
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)

扫描 address 已被索引扫描取代 idx_address1然后扫描堆 address.

由于这是一个频繁的查询并且需要优化，我们可以使用部分索引，它只索引那些地址所在的行 ‘California’:

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 并且不碰桌子 address.

使用多值索引

某些要索引的列可能不包含标量数据类型。列类型如 jsonb, arrays и tsvector 包含复合值或多个值。如果需要对此类列建立索引，通常必须搜索这些列中的所有单个值。

让我们尝试查找所有包含不成功镜头剪辑的电影的标题。桌子 film 有一个名为的文本列 special_features。如果电影具有此“特殊属性”，则该列包含作为文本数组的元素 Behind The Scenes。要搜索所有此类电影，我们需要在以下情况下选择所有包含“Behind The Scenes”的行：任何数组值 special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

嵌套运算符 @> 检查右侧是否是左侧的子集。

请求计划：

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)

其中请求完整堆扫描，成本为 67。

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

pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
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 树索引不知道索引值中单个元素的存在。

我们需要一个 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 索引支持将单个值与索引复合值进行映射，从而使查询计划成本减少一半以上。

摆脱重复索引

索引会随着时间的推移而累积，有时新索引可能包含与先前索引之一相同的定义。您可以使用目录视图来获取人类可读的索引 SQL 定义。 pg_indexes。您还可以轻松找到相同的定义：

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indexes                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id
(6 rows)

超集索引

您最终可能会得到许多索引，其中一个索引是索引其他索引的列的超集。这可能是理想的，也可能不是理想的——超集可能会导致仅索引扫描，这很好，但它可能会占用太多空间，或者不再使用超集要优化的查询。

如果您需要自动定义此类索引，您可以从 pg_索引从表中 pg_catalog.

未使用的索引

随着使用数据库的应用程序的发展，它们使用的查询也在不断发展。任何查询可能不再使用之前添加的索引。每次扫描索引时，都会由统计管理器进行标记，并在系统目录视图中 pg_stat_user_indexes 你可以看到价值 idx_scan，这是一个累积计数器。在一段时间内（比如一个月）跟踪这个值可以很好地了解哪些索引没有被使用并且可以被删除。

这是一个查询，用于获取模式中所有索引的当前扫描计数 ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM   pg_catalog.pg_stat_user_indexes
pagila-# WHERE  schemaname = 'public'
pagila-# LIMIT  10;
    relname    |    indexrelname    | idx_scan
---------------+--------------------+----------
 customer      | customer_pkey      |    32093
 actor         | actor_pkey         |     5462
 address       | address_pkey       |      660
 category      | category_pkey      |     1000
 city          | city_pkey          |      609
 country       | country_pkey       |      604
 film_actor    | film_actor_pkey    |        0
 film_category | film_category_pkey |        0
 film          | film_pkey          |    11043
 inventory     | inventory_pkey     |    16048
(10 rows)

用更少的锁重建索引

索引经常需要重建，例如当索引变得臃肿时，重建可以加快扫描速度。索引也可能被损坏。更改索引参数可能还需要重建它。

启用并行索引创建

在 PostgreSQL 11 中，创建 B 树索引是并发的。为了加快创建过程，可以使用多个并行工作线程。但是，请确保这些配置选项设置正确：

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

默认值太小。理想情况下，这些数字应该随着处理器核心数量的增加而增加。阅读更多内容文件资料.

后台索引创建

您可以使用以下选项在后台创建索引 CONCURRENTLY 命令 CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

此索引创建过程与通常的索引创建过程不同，它不需要表上的锁，因此不会阻止写入操作。另一方面，它需要更多的时间并消耗更多的资源。

Postgres 为创建索引提供了很大的灵活性，提供了解决任何特殊情况的方法，以及在应用程序爆炸式增长时管理数据库的方法。我们希望这些提示将帮助您快速查询并帮助您的数据库做好扩展的准备。

来源： habr.com

使用 PostgreSQL 中索引的所有功能

使用覆盖索引

使用部分索引

使用多值索引

摆脱重复索引

超集索引

未使用的索引

用更少的锁重建索引

启用并行索引创建

后台索引创建

添加评论取消回复

使用 PostgreSQL 中索引的所有功能

使用覆盖索引

使用部分索引

使用多值索引

摆脱重复索引

超集索引

未使用的索引

用更少的锁重建索引

启用并行索引创建

后台索引创建

添加评论 取消回复

添加评论取消回复