在有條件的表上加入時我應該索引什麼？

我有一個這樣的查詢：

SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "main_table"."user_id" = 1
 AND (other_table.date BETWEEN '2020-02-26 23:29:42.678693' AND '2020-02-27 01:29:42.678739')

您可能從查詢中了解有關架構所需的所有資訊，但請告訴我任何問題。

為此制定的最佳索引是什麼？

main_table 很簡單（對嗎？）

CREATE INDEX ON main_table (user_id);

對於 other_table，我很想“首先 postgres 將‘進行連接’，然後它將‘按日期過濾’”。這表明該指數：

CREATE INDEX ON other_table (id, date) where deleted_at IS NULL;

但是，在不創建索引和執行 EXPLAIN 的情況下，postgres 將如何進行查詢是不可知的，它可能認為最好先按日期過濾，然後“進行連接”？

• 此選擇通常會匹配 1-10 行
• 對於給定的日期範圍，other_table 中有數千行
• 對於給定的 user_id，main_table 中有數千行
• 此查詢的結果永遠不會有兩個 main_table 行與一個 other_table 行相關，反之亦然。總是1-1。
• other_table 有大約 2/3 的行，deleted_at 不為空

鑑於您有關數據分佈的資訊，以下執行計劃將是最好的：

• 使用索引掃描從other table.
• 使用 as 內表執行嵌套循環連接，main_table如果連接條件被索引，這將很快。

所以理想的索引other_table是

CREATE INDEX ON other_table (date) WHERE deleted_at IS NULL;

If deleted_atis 通常NULL，您可以省略該WHERE子句而不會損失太多。

如果您想獲得僅索引掃描other_table（這可能不是必需的，因為您只獲取很少的行），您可以改為

CREATE INDEX ON other_table (date) INCLUDING (id) WHERE deleted_at IS NULL;

---

理想的索引main_table是

CREATE INDEX ON main_table (user_id, appointment_id);

在這種情況下，列的順序無關緊要，因為您將使用=運算符掃描兩列。

---

在現實世界中，您將嘗試選擇對盡可能多的查詢有用的索引，因為索引過多會損害性能並浪費空間。

與 main_table 存在 1-1 關係。

你的意思是 main_table.appointment_id 和 other_table.id 都是各自表的主鍵嗎？這很少是一個好的設計，如果將表合併到一個具有更多列的表中，則更容易獲得有效的查詢。

但是，不知道 postgres 將如何進行查詢，並且它可能認為最好先按日期過濾，然後“進行連接”？

這不是不可知的，如果你使用EXPLAINor EXPLAIN (ANALYZE)。

如果這只是測試，您可以創建一堆索引並查看實際使用了哪些索引，以及是否可以很好地使用它們，並將好的索引保留在生產環境中。

對於從 main_table 到 other_table 的嵌套循環，您現有的索引看起來相當不錯。但它可能更願意提取帶有 user_id 的數千個，以及帶有正確日期的數千個，然後將它們散列連接在一起。在這種情況下，你會想要

CREATE INDEX ON other_table (date,id) where deleted_at IS NULL;

或者它可能想用 other_table 驅動做一個嵌套循環，在這種情況下你會想要上面的索引加上：

CREATE INDEX ON main_table (appointment_id, user_id);

（儘管列的其他順序也應該起作用）

如果deleted_at 通常為NULL，則指定“where deleted_at IS NULL;” 可能不值得，因為它使索引對其他查詢的靈活性降低，而速度卻沒有那麼快（儘管如果啟用 index-only-scan，它可能會快很多）。

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