PostgreSQL中索引的工作

我有幾個關於 PostgreSQL 中索引工作的問題。我有一個Friends帶有以下索引的表：

  Friends ( user_id1 ,user_id2) 

user_id1並且是表user_id2的外鍵user

1. 這些是等價的嗎？如果不是那為什麼？

Index(user_id1,user_id2) and Index(user_id2,user_id1)

2. 如果我創建主鍵（user_id1，user_id2），它會自動為它創建索引嗎？

如果第一個問題中的索引不相等，那麼在上面的主鍵命令上創建了哪個索引？

這個答案是關於（預設）B-tree索引。稍後見，有關 GiST、GIN 等的相關答案：

• 為什麼 GiST 索引用於過濾非前導列？

---

以下是在多列索引的第二列上查詢表的結果。

任何人都可以輕鬆複製這些效果。在家裡試試。

我在 Debian 上使用 23322 行的中型數據庫表在 Debian 上測試了 PostgreSQL 9.0.5 。adr它實現了表（地址）和（屬性）之間的 n:m 關係att，但這與這裡無關。簡化模式：

CREATE TABLE adratt (
 adratt_id serial PRIMARY KEY
, adr_id    integer NOT NULL
, att_id    integer NOT NULL
, log_up    timestamp NOT NULL DEFAULT (now()::timestamp)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);

該UNIQUE約束有效地實現了唯一索引。我用一個簡單的索引重複了測試，以確保得到與預期相同的結果。

CREATE INDEX adratt_idx ON adratt(adr_id, att_id);

該表聚集在adratt_uni索引上，在我執行測試之前：

CLUSTER adratt;
ANALYZE adratt;

對查詢的順序掃描(adr_id, att_id)盡可能快。多列索引仍然可以單獨用於第二個索引列的查詢條件。

我執行了幾次查詢以填充記憶體，並選擇了十次執行中最好的一次以獲得可比較的結果。

1.使用兩列查詢

SELECT *
FROM   adratt
WHERE  att_id = 90
AND    adr_id = 10;

adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
      123 |     10 |     90 | 2008-07-29 09:35:54
(1 row)

輸出EXPLAIN ANALYZE：

Index Scan using adratt_uni on adratt  (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1)
 Index Cond: ((adr_id = 10) AND (att_id = 90))
Total runtime: 0.067 ms

2.使用第一列查詢

SELECT * FROM adratt WHERE adr_id = 10;

adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
      126 |     10 |     10 | 2008-07-29 09:35:54
      125 |     10 |     13 | 2008-07-29 09:35:54
     4711 |     10 |     21 | 2008-07-29 09:35:54
    29322 |     10 |     22 | 2011-06-06 15:50:38
    29321 |     10 |     30 | 2011-06-06 15:47:17
      124 |     10 |     62 | 2008-07-29 09:35:54
    21913 |     10 |     78 | 2008-07-29 09:35:54
      123 |     10 |     90 | 2008-07-29 09:35:54
    28352 |     10 |    106 | 2010-11-22 12:37:50
(9 rows)

輸出EXPLAIN ANALYZE：

Index Scan using adratt_uni on adratt  (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1)
 Index Cond: (adr_id = 10)
Total runtime: 0.058 ms

3.使用第二列查詢

SELECT * FROM adratt WHERE att_id = 90;

adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
      123 |     10 |     90 | 2008-07-29 09:35:54
      180 |     39 |     90 | 2008-08-29 15:46:07
...
(83 rows)

輸出EXPLAIN ANALYZE：

Index Scan using adratt_uni on adratt  (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1)
 Index Cond: (att_id = 90)
Total runtime: 0.849 ms

4.禁用indexscan & bitmapscan

SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90;

EXPLAIN ANALYZE 的輸出：

Bitmap Heap Scan on adratt  (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1)
 Recheck Cond: (att_id = 90)
 ->  Bitmap Index Scan on adratt_uni  (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1)
       Index Cond: (att_id = 90)
Total runtime: 0.894 ms

SET enable_bitmapscan = off;
SELECT * FROM adratt WHERE att_id = 90;

輸出EXPLAIN ANALYZE：

Seq Scan on adratt  (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1)
 Filter: (att_id = 90)
Total runtime: 2.680 ms

結論

正如預期的那樣，多列索引僅用於第二列的查詢。

正如預期的那樣，它的效率較低，但查詢仍然比沒有索引快 3 倍。

禁用索引掃描後，查詢計劃器選擇點陣圖堆掃描，其執行速度幾乎一樣快。只有在禁用它之後，它才會回退到順序掃描。

請參閱手冊中原始報價的其他答案。

自 Postgres 9.0 以來的更新

在 Postgres 13 中，一切仍然基本正確。最顯著的變化：

• Postgres 9.2 中的僅索引掃描
• 在 Postgres 11 中使用關鍵字的真正覆蓋索引INCLUDE
• Postgres 12 中的多項性能和空間改進（尤其是多列索引）
• Postgres 13 中的重複數據刪除。

都支持指數表現。（不過，順序掃描也變得更快了。）

引用自：https://dba.stackexchange.com/questions/6115