Postgresql
具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快?
SELECT something FROM table WHERE primary_key = ?
對比
SELECT something FROM table WHERE primary_key = ? AND other_key = ?
假設這是一個包含
other_key
不改變結果集的場景。第二個查詢在實踐中更快嗎?或者如果提供了幾個,數據庫是否只使用一個最佳密鑰?
詢問
SELECT something FROM table WHERE primary_key = ?
這是可能的最快形式。添加任何其他謂詞只會使其變慢。理論上。
特殊例外情況適用,例如當 PK 索引因某種原因膨脹時,或 PK 列相對較大,或多列 PK,導致索引更大,而添加的謂詞的索引
other_key
更小。然後 Postgres 可能決定使用添加謂詞的索引,訪問堆並過濾.primary_key = ?
不太可能,但可能。如果添加的謂詞評估為除 之外的任何內容
TRUE
,則不會得到任何行-結果不同,因此不是公平的比較-但這不是您斷言的情況。約束對
FOREIGN KEY
讀取性能沒有直接影響。引用列甚至不必被索引(與引用列相反)。最高讀取性能的覆蓋索引
對於大小不一且寫入活動不多的表,請考慮添加多列索引以
(primary_key, something)
允許僅索引掃描。在Postgres 10 或更早版本中,這會導致至少兩個索引(施加額外的寫入/維護/空間成本):
- 上的 PK 指數
(primary_key)
,顯然。- 上的普通(或冗餘
UNIQUE
)索引(primary_key, something)
。Postgres 11使用該子句添加了真正的覆蓋索引,這可以方便地搭載非鍵列:
INCLUDE
something``PRIMARY KEY
CREATE TABLE tbl ( primary_key bigint GENERATED ALWAYS AS IDENTITY , other_key integer NOT NULL REFERENCES other_tbl , something text , PRIMARY KEY (primary_key) INCLUDE (something) -- here's the magic );
如果
primary_key
恰好是比other_key
您提到的更寬的列(bigint
與int
範例中的類似不符合條件),您還可以捎帶something
上的索引other_key
:CREATE INDEX other_idx ON tbl(other_key) INCLUDE (something);
雖然任一解決方案都可以優化給定查詢的讀取性能,但其他未檢索的查詢
something
必須使用更大的索引。所以權衡收益和成本(就像在創建索引時一樣)。來自 Michael Paquier 詳細資訊的相關部落格條目: