索引最大行大小錯誤

列是否有上限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