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

在 Postgres 世界中，索引對於數據庫存儲（稱為“堆”）的高效導航至關重要。 Postgres 不支持它的集群，並且 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)

它有所幫助，隨後的掃描變成了“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. 要搜索所有此類電影，我們需要選擇所有帶有“幕後花絮”的行任何數組值 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-Tree 索引是並發的。為了加快創建過程，可以使用多個並行工作器。但是，請確保正確設置了這些配置選項：

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 提供了很大的靈活性來創建索引和解決任何特殊情況的方法，以及在應用程序爆炸式增長的情況下管理數據庫的方法。我們希望這些技巧能幫助您快速查詢並讓您的數據庫做好擴展準備。

來源： www.habr.com

使用 PostgreSQL 中索引的所有功能

使用覆蓋索引

使用部分索引

使用多值索引

擺脫重複索引

超集索引

未使用的索引

用更少的鎖重建索引

啟用並行索引創建

後台索引創建

添加評論取消回复

使用 PostgreSQL 中索引的所有功能

使用覆蓋索引

使用部分索引

使用多值索引

擺脫重複索引

超集索引

未使用的索引

用更少的鎖重建索引

啟用並行索引創建

後台索引創建

添加評論 取消回复

添加評論取消回复