數組欄位上的 btree 索引實際上有什麼作用嗎？

在 PG 中，您可以創建一個類似的表

CREATE TABLE foo (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid (),
 name text NOT NULL,
 things text[] NOT NULL DEFAULT ARRAY[] ::text[]
)

和相關索引通過

CREATE INDEX foo_text ON foo USING btree (name, things);

但很難找到有關此功能的資訊。我繼承了這樣的表（數億行，儘管這個數組中幾乎總是有 0 或 1 個條目），並且索引在 pg_stat_all_indexes 中偶爾會受到影響，所以至少在某些情況下可以發生了，但我也注意到這個索引相對於其他索引佔用了很多空間，並且真空度要慢得多。 PG 中數組欄位****的索引是否有意義？btree

如果目標是能夠找到things包含一個或多個值的行，是否有更好的方案？（假設我們此時無法將其正確規範化為它自己的表。）

我們期望會命中該索引的查詢看起來像

SELECT id FROM foo
WHERE name = $1
AND $2::text = ANY(things)

SELECT id FROM FOO
WHERE name = $1
AND things @> $2::text[]

數組上的 B 樹索引無法加快您問題中的查詢速度。一定是有人錯誤地創造了它們。

B-tree 索引應該只索引name. 如果該條件已經大大縮小了結果集的範圍，那就足夠了。如果您還需要一個索引來加速條件，請things在該列上創建一個單獨的 GIN 索引。

請注意，只有使用@>運算符的條件才能使用索引，things您的第一個查詢中的條件不能被索引。

該索引可以用於您顯示的兩個查詢，儘管它只是索引中的第一列（“名稱”）可以用於它們。但除非你也有一個單獨的“名稱”索引，否則我認為 foo_text 會得到很多使用，只是為了它的第一列。

它也可以用於“事物”列，但僅在測試該列是否相等或不相等時。不是在使用 ANY 或 @> 進行測試時。因此，例如，它在這裡使用效果很好：

where name='AA' and things='{6rL}';

（假設 name=‘AA’ 有很多需要打破的關係）

我還注意到，該索引相對於其他索引佔用了很多空間，並且清理速度要慢得多。

可能你的腫了。我沒有發現它的副本比 VACUUM 慢，也沒有發現它比我預期的要大。

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