索引最大行大小錯誤
列是否有上限
array
?插入數組欄位時出現此錯誤 -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
這是我的表定義 -
create table test_array(id varchar(50), data text[]); ALTER TABLE test_array ADD PRIMARY KEY (id); CREATE INDEX ix_data ON test_array USING GIN (data);
我需要數組欄位的索引,因為我正在對其進行一些查找。
問題
這是pgsql.general 上討論的一個非常相似的案例。這是關於 b-tree 索引的限制,但都是一樣的,因為 GIN 索引在內部使用 b-tree 索引作為鍵,因此遇到相同的鍵大小限制(而不是普通 b-tree 中的項目大小指數)。
我引用了有關 GIN 索引實現的手冊:
在內部,一個 GIN 索引包含一個基於鍵建構的 B-tree 索引,其中每個鍵是一個或多個索引項的一個元素
無論哪種方式,列中至少有一個數組元素
data
太大而無法索引。如果這只是一個奇異的異常值或某種意外,您可以截斷該值並完成它。出於以下展示的目的,我將假設:數組中有很多長文本值。
簡單的解決方案
您可以
data
用相應的雜湊值替換數組中的元素。並通過相同的散列函式發送查找值。當然,您可能還想將原件儲存在某個地方。有了這個,我們幾乎到達了我的第二個變體……先進的解決方案
您可以為數組元素創建一個查找表,其中一
serial
列作為代理主鍵(實際上是一種激進的雜湊值) - 如果涉及的元素值不是唯一的,這將更加有趣:CREATE TABLE elem ( elem_id serial NOT NULL PRIMARY KEY , elem text UNIQUE NOT NULL );
因為我們要查找
elem
,所以我們添加了一個索引——但這次在表達式上添加了一個索引,只有長文本的前 10 個字元。在大多數情況下,這應該足以將搜尋範圍縮小到一個或幾個命中。根據您的數據分佈調整大小。或者使用更複雜的雜湊函式。CREATE INDEX elem_elem_left10_idx ON elem(left(elem,10));
您的列
data
將是類型int[]
。我將表格重命名為data
並擺脫了varchar(50)
您範例中的不祥之兆:CREATE TABLE data( data_id serial PRIMARY KEY , data int[] );
中的每個數組元素都
data
引用一個elem.elem_id
. 此時,您可以考慮將數組列替換為 n:m 表,從而規範您的模式並允許 Postgres 強制引用完整性。索引和一般處理變得更容易……但是,出於性能原因,
int[]
與 GIN 索引結合使用的列可能會更好。儲存大小要小得多。在這種情況下,我們需要 GIN 索引:CREATE INDEX data_data_gin_idx ON data USING GIN (data);
現在,GIN 索引(= 數組元素)的每個鍵都是 an
integer
而不是 longishtext
。索引會小幾個數量級,因此搜尋會更快。缺點:在您真正執行搜尋之前,您必須
elem_id
從表中查找elem
。使用我新引入的功能索引elem_elem_left10_idx
,這也會快得多。您可以通過一個簡單的查詢完成所有操作:
SELECT d.*, e.* FROM elem e JOIN data d ON ARRAY[e.elem_id] <@ d.data WHERE left(e.elem, 10) = left('word1234word', 10) -- match index condition AND e.elem = 'word1234word'; -- need to recheck, functional index is lossy
您可能對
intarray
提供附加運算符和運算符類的擴展感興趣。db<>fiddle here
我在 PostGIS 地理專欄上得到了這個。這是因為我不小心錯誤地創建了索引。您應該在創建此類索引時包含 USING GIST 參數。