利用 PostgreSQL 部分索引提高查詢陳述句的性能

大家好，我是只談技術不剪發的 Tony 老師，

在 PostgreSQL 資料庫中，部分索引（partial index）是指對表中滿足特定條件的資料行進行索引，由于它不需要對全部資料進行索引，因此索引會更小，在特定場景下通過部分索引查找資料時性能會更好，本文就給大家介紹一下 PostgreSQL 中的部分索引功能，

如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁

PostgreSQL 在創建索引時可以通過一個 WHERE 子句指定需要索引的資料行，從而創建一個部分索引，例如，對于以下訂單表 orders：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  status TEXT
);

INSERT INTO orders (id, customer_id, status)
SELECT
  i,
  (random()*10000)::INT,
  CASE (random() * 100)::int
    WHEN 0 THEN 'pending'
    WHEN 1 THEN 'shipped'
    ELSE 'completed'
  END
    FROM generate_series(1, 1000000) i;

該表中總共有 1000000 個訂單，通常絕大部的訂單都處于完成狀態，一般情況下，我們只需要針對某個用戶未完成的訂單進行查詢跟蹤，因此可以創建一個基于用戶編號和狀態的部分索引：

CREATE INDEX full_idx ON orders (customer_id, status);

然后使用 EXPLAIN ANALYZE 命令查看 SELECT 陳述句的執行計劃：

EXPLAIN ANALYZE
SELECT * 
FROM orders
WHERE customer_id = 5678
AND status != 'completed';

QUERY PLAN                                                                                                           |
---------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on orders  (cost=5.18..369.08 rows=1 width=17) (actual time=33.661..34.040 rows=1 loops=1)          |
  Recheck Cond: (customer_id = 5678)                                                                                 |
  Filter: (status <> 'completed'::text)                                                                              |
  Rows Removed by Filter: 109                                                                                        |
  Heap Blocks: exact=109                                                                                             |
  ->  Bitmap Index Scan on full_idx  (cost=0.00..5.17 rows=100 width=0) (actual time=33.526..33.526 rows=110 loops=1)|
        Index Cond: (customer_id = 5678)                                                                             |
Planning Time: 1.252 ms                                                                                              |
Execution Time: 34.180 ms                                                                                            |

輸出結果顯示利用索引 full_idx 掃描了 110 行，然后通過 status 過濾掉了 109 行，而不是直接通過索引掃描出所需的資料，

此時，我們可以查看一下索引 full_idx 占用的空間大小：

select pg_size_pretty(pg_table_size('full_idx'));

pg_size_pretty|
--------------|
30 MB         |

接下來我們再創建一個部分索引，只包含未完成的訂單資料，從而減少索引的資料量：

CREATE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';

索引 partial_idx 中只有 customer_id 欄位，不需要 status 欄位，然后再次執行相同的 EXPLAIN ANALYZE 陳述句，查看執行計劃：

EXPLAIN ANALYZE
SELECT * 
FROM orders
WHERE customer_id = 5678
AND status != 'completed';

QUERY PLAN                                                                                                         |
-------------------------------------------------------------------------------------------------------------------|
Index Scan using partial_idx on orders  (cost=0.29..8.30 rows=1 width=17) (actual time=0.246..0.249 rows=1 loops=1)|
  Index Cond: (customer_id = 5678)                                                                                 |
Planning Time: 0.397 ms                                                                                            |
Execution Time: 0.295 ms                                                                                           |

輸出結果顯示 PostgreSQL 執行計劃選擇了索引 partial_idx，而不是 full_idx；因為這樣性能更好，只需要掃描 1 行記錄就可以得到結果，

同樣可以查看一下索引 partial_idx 占用的空間大小：

select pg_size_pretty(pg_table_size('partial_idx'));

pg_size_pretty|
--------------|
352 kB        |

索引只有 352 KB，而不是 30 MB，因為絕大多數訂單都處于完成狀態，

另外，部分索引還可以用于實作其他的功能，例如，我們可以將索引 partial_idx 定義為唯一索引，從而實作每個用戶只能存在一個未完成訂單的約束，

DROP INDEX partial_idx;
TRUNCATE TABLE orders;

CREATE UNIQUE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';

INSERT INTO orders(id, customer_id, status) VALUES (1, 1, 'pending');

INSERT INTO orders(id, customer_id, status) VALUES (2, 1, 'pending');
SQL 錯誤 [23505]: 錯誤: 重復鍵違反唯一約束"partial_idx"
  詳細：鍵值"(customer_id)=(1)" 已經存在

用戶必須完成一個訂單之后才能繼續生成新的訂單，

📝更多關于 PostgreSQL 索引和優化的內容，可以參考這篇文章，