Performance

索引最大行大小錯誤

  • September 4, 2021

列是否有上限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 索引(= 數組元素)的每個都是 aninteger而不是 longish text。索引會小幾個數量級,因此搜尋會更快

缺點:在您真正執行搜尋之前,您必須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

sqlfiddle

我在 PostGIS 地理專欄上得到了這個。這是因為我不小心錯誤地創建了索引。您應該在創建此類索引時包含 USING GIST 參數。

引用自:https://dba.stackexchange.com/questions/25138