Postgresql

具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快?

  • August 3, 2020
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 或更早版本中,這會導致至少兩個索引(施加額外的寫入/維護/空間成本):

  1. 上的 PK 指數(primary_key),顯然。
  2. 上的普通(或冗餘UNIQUE)索引(primary_key, something)

Postgres 11使用該子句添加了真正的覆蓋索引,這可以方便地搭載非鍵列:INCLUDEsomething``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您提到的更寬的列(bigintint範例中的類似不符合條件),您還可以捎帶something上的索引other_key

CREATE INDEX other_idx ON tbl(other_key) INCLUDE (something);

雖然任一解決方案都可以優化給定查詢的讀取性能,但其他未檢索的查詢something必須使用更大的索引。所以權衡收益和成本(就像在創建索引時一樣)。

來自 Michael Paquier 詳細資訊的相關部落格條目:

引用自:https://dba.stackexchange.com/questions/207927