Postgres 可以對這個帶有連接表的查詢使用僅索引掃描嗎？

這是對以下內容的後續：PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？

考慮在連接表中過濾的另一個問題中模式的逆：

CREATE TABLE thing_types(
  id              INTEGER PRIMARY KEY
, first_lvl_type  TEXT
, second_lvl_type TEXT
);

CREATE TABLE things(
  id         INTEGER PRIMARY KEY
, thing_type INTEGER REFERENCES thing_types(id)
, t1c1       INTEGER
);

像這樣的查詢：

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

有一個像這樣的索引是不是很瘋狂：

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

哪個涵蓋了用於該連接的主鍵？該索引會被用作覆蓋索引來幫助JOIN上述查詢嗎？當我知道要JOIN像這樣編輯表時，是否應該更改索引策略以更頻繁地覆蓋主鍵？

如果滿足僅索引掃描的其他先決條件，則將列id作為尾隨列附加到索引（而不是前導列）是非常有意義的：

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

Postgres 11使用關鍵字引入了實際的覆蓋索引INCLUDE。

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type) INCLUDE (id);

對您的情況只有一點好處，但將列添加到 UNIQUE 或 PK 索引或約束是一個很好的選擇。

關於僅索引掃描：

• Postgres 維基
• 手冊

最重要的前提條件：表的可見性映射thing_types必須將大部分或所有頁面顯示為對所有事務“可見”。即表要麼是只讀的，要麼你的自動清理設置足夠激進，可以在寫入表後持續清理。

每增加一個索引都會增加成本。主要是寫性能。但也有副作用，例如耗盡的記憶體容量。（使用相同索引的多個查詢有更好的機會將它們駐留在記憶體中。）所以這也是一個size的問題。id通常是一個非常小的列integer或bigint. 使其成為案例的良好候選者。

特別是，將列添加到索引會禁用涉及該列的 HOT 更新選項。但是由於id無論如何都已編入索引並且通常不會更新（作為 PK），因此在這種情況下這不是問題。有關的：

• 更新語句中的冗餘數據

如果您實際上大部分時間都從這些索引中獲得僅索引掃描，那麼使用它們通常是有意義的。用 測試EXPLAIN。

舊版本中的部分索引存在限制。引用 Postgres 9.6的發行說明：

• 當索引的子句引用未編入索引的列時，允許對部分索引使用僅索引掃描（Tomas Vondra，Kyotaro Horiguchi）WHERE

例如，由 定義的索引CREATE INDEX tidx_partial ON t(b) WHERE a > 0現在可以用於指定WHERE a > 0且不使用的查詢的僅索引掃描a。以前這是不允許的，因為 a 未列為索引列。

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