將 GIN PostgreSQL 索引用於整數外鍵有什麼缺點嗎？

我有一個帶有other_id類型外鍵的大表（6 億行） integer。外鍵的單個值平均重複大約 100 次。我需要在該 FK 列上有一個索引，因為數據經常被other_id.

我的測試表明，該gin索引類型大約比預設索引小 10 倍，性能大約是預設btree索引的 3 倍（使用SELECT查詢測試了性能）。

問題是使用gin索引而不是btree索引是否有任何現實世界的缺點？看起來這種索引類型並沒有太多用於像我這樣的非常常見的情況，即integer外鍵。但是我的測試顯示了巨大的性能提升。那麼為什麼gin不建議在這種情況下使用呢？

我必須執行CREATE EXTENSION btree_gin才能使用列的gin索引integer。

我知道UPDATE由於FASTUPDATE預設啟用而可能會很慢：Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

我只關心相等=運算符能夠使用索引（也IN (...)可能有大量值，但我認為這也是相等）。

這似乎是一件相當深奧的事情，這可能就是為什麼它沒有得到那麼多推薦的原因。當問題出現時，我確實推薦它，但這並不常見。在整個數據庫的上下文中，一個索引的大小不太可能有那麼大的意義，因此通常不必擔心太多。

我認為作為外鍵沒有任何缺點。用於維護約束的多值側的自動生成的查詢可以使用 GIN 索引和 Btree 索引一樣。

我會將“快速更新”關閉，除非您進行相關的基準測試以顯示您想要它。由於索引是使用“btree_gin”的標量，因此不會像使用普通 GIN 索引那樣對每個插入的行進行索引頁寫入的爆炸式增長，因此對 fastupdate 的需求較少。

由於這種索引類型比正常 btree 索引使用得少得多，因此更可能隱藏著未被發現的錯誤（尤其是如果 fastpdate = on）。如果它真的為我提供了一些有價值的東西，我不會（也不會）讓它讓我停止使用它，但它讓我不會盲目地在我可以想像的任何地方使用“btree_gin”。

我注意到的一件事是對 GIN 索引的更新/插入的重放似乎很慢（與我天真的預期相比），這可能與 PITR、恢復或流複製有關。如果這些事情中的任何一個對您很重要，請確保將它們包括在您的測試中。

gin 索引類型大約比預設 btree 索引小 10 倍，性能大約是預設 btree 索引的 3 倍（使用 SELECT 查詢測試了性能）。

和：

btree 30GB，杜松子酒 3GB

是的，這非常有利於 GIN 指數。

哦，這個：

平均重複約 100 次。

還有這個：

我想我永遠不會更新 other_id 列，但我可以插入很多行。此外，我可能會向將設置相同 other_id 值的行發出 UPDATE 語句。

似乎表明您的大部分錶是根據物理排序的other_id，或者至少使用相同的集群other_id。如果是這樣，和/或您不會無序更新太多行，並且可以插入您提到的按排序的“多行”other_id和/或偶爾可以負擔CLUSTER您的表（或執行相同操作pg_repack以允許並發寫訪問），然後考慮一個塊範圍索引（BRIN），它應該小得多，但是：

CREATE INDEX tbl_other_id_brin_idx ON tbl USING brin (other_id)

手冊：

pages_per_range

為 BRIN 索引的每個條目定義組成一個塊範圍的表塊的數量（有關更多詳細資訊，請參見第 67.1 節）。預設值為**128**.

在您的情況下嘗試較小的pages_per_range設置可能會有所幫助，例如：

CREATE INDEX tbl_other_id_brin_idx ON tbl USING brin (other_id)
WITH (pages_per_range = 8);

該設置以及索引大小和性能在很大程度上取決於物理集群、行大小、寫入模式和典型查詢。雖然通常比 B 樹索引慢，但它可能適用於您的情況。

有關的：

• 加快 Postgres 部分索引的創建

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