使用 GIN 索引位串

我正在嘗試擴展 PostgreSQL 以索引最多 1000 位的位字元串。（這些位串是通過量化高維向量創建的，因此每個維度最多分配 4 個位）。插入很少見，而搜尋是最常用的操作。在搜尋中，我想獲取與位字元串完全匹配的所有行。

它看起來對 GIN 來說是一個完美的工作（結合我自己的數據類型），或者你怎麼看？

在搜尋中，我想獲取與位字元串完全匹配的所有行。

使用預設類型 B-Tree 索引。我在這裡沒有看到 GIN 索引的案例。

最多 1000 位會在磁碟上產生最多 133 個字節（或稍多）的儲存bit varying大小。

SELECT pg_column_size(repeat('1', 1000)::varbit)  -- 133

沒那麼多。一個普通的 B-Tree 索引應該可以。但也許該列足夠大，以下技巧可以提高性能。

如果位串列的一小部分足夠獨特，可以將搜尋範圍縮小到少數命中，則表達式上的索引 可能會為您提供更好的性能，因為較小的索引可以放入 RAM 並且可以更快地處理所有內容。不要為小桌子煩惱，成本會吃掉好處。但是對於大桌子來說可能會有很大的不同。

例子

給定表：

CREATE TABLE tbl(id serial PRIMARY KEY, b_col varbit);

如果前 10 位足以將搜尋範圍縮小到幾個命中，則可以在表達式上創建索引 b_col::bit(10)。強制轉換為bin(n)截斷bitstring到 n 位。

CREATE INDEX tbl_b_col10_idx ON tbl ((b_col::bit(10)))

索引定義中的強制轉換運算符需要額外的括號。看：

• 如何在 postgres 中的整數 json 屬性上創建索引

然後，而不是查詢

SELECT * FROM tbl WHERE b_col = '1111011110111101'::varbit; -- 16 bit

你會使用：

SELECT *
FROM   tbl
WHERE  b_col::bit(10) = '1111011110111101'::bit(10) -- utilize index
AND    b_col = '1111011110111101'::varbit;  -- filter to exact match

請注意，當轉換為 時，較短的值會用0’s 填充到右側（最低有效位）bit(n)。

在現實世界的應用程序中，這開始對幾百個比特有意義。測試轉折點。

進一步優化

由於大多數安裝使用MAXALIGN8 字節（64 位作業系統）（此處有更多詳細資訊）執行，因此對於任何不超過 8 字節的數據，您的索引大小都是相同的。實際上，每行：

4 字節項目標識符
索引元組標頭為 8 個字節（或堆元組為 23 + 1 個字節）
? 數據的實際空間
? 填充到最接近的 8 個字節的倍數

加上每頁和索引/表的一些小成本。手冊或有關 stackoverflow 的相關答案中的詳細資訊。

因此，您應該能夠進一步優化上述方法。取第一個64 位（或最後一個或任何最獨特且最適合您的），將其轉換為bigint並在此表達式上建立索引。

CREATE INDEX tbl_b_col64_idx ON tbl ((b_col::bit(64)::bigint))

我投了兩次 ( ) 因為在andb_col::bit(64)::bigint之間沒有定義投。此相關答案中的詳細資訊：varbit``bigint

• 將文本表示中的十六進制轉換為十進制數

實際上，這只是一個非常快速且簡單的散列函式，其中散列值還允許查找值的範圍。根據確切的要求，您可以更進一步並使用任何 IMMUTABLE散列函式，例如md5(). 上面連結的答案中的詳細資訊。

隨之而來的查詢：

SELECT *
FROM   tbl
WHERE  b_col::bit(64)::bigint = '1111011110111101'::bit(64)::bigint -- utilize index
AND    b_col = '1111011110111101'::varbit;  -- narrow down to exact match

結果索引應該與第一個範例中的索引一樣大，但查詢應該更快，原因有以下三個：

• 索引通常返回更少的命中（64 位資訊與 10 位）
• Postgres 可以使用整數運算，這應該更快，即使是簡單的=操作。（沒有測試來驗證這一點。）
• 該類型integer沒有像varbit- 5 或 8 字節這樣的成本。（在我的安裝中，最多960 位需要 5 個字節，更多需要 8 個字節）。

實際上，要使索引保持最小大小，您只能將24 位資訊打包到varbit索引中 - 與索引的64 位資訊相比bigint。

CLUSTER

在這種情況下CLUSTER應該提高性能：

CLUSTER TABLE tbl USING tbl_b_col10_idx;

這是一次性操作，必須在您的設計間隔內重複。如果您想使用它，請務必閱讀手冊。CLUSTER或者考慮像pg_repack或pg_suqeeze這樣的社區工具。細節：

• 為讀取性能配置 PostgreSQL

如果您的值的前 64 位在大多數情況下都是唯一的，CLUSTER則幾乎沒有幫助，因為在大多數情況下索引掃描將返回單行。如果沒有，CLUSTER將有很大幫助。因此，對於索引優化程度較低的第一個範例，效果會大得多。

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