Błąd maksymalnego rozmiaru indeksu

Czy istnieje górna granica dla arraykolumny?

Otrzymuję ten błąd podczas wstawiania do pola tablicy -

PG::Error: ERROR:  index row size 3480 exceeds maximum 2712 for index "ix_data"

Oto definicja mojej tabeli -

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);

Potrzebuję indeksu na polu tablicy, ponieważ przeprowadzam na nim przegląd.

Problem

Oto bardzo podobny przypadek omówiony na stronie pgsql.general . Chodzi o ograniczenie w indeksie b-drzewa, ale jest tak samo, ponieważ indeks GIN używa indeksu b-drzewa dla kluczy wewnętrznie, a zatem działa na to samo ograniczenie dla wielkości klucza (zamiast wielkości elementu w zwykłym b-drzewie indeks).

Cytuję instrukcję dotyczącą implementacji indeksu GIN :

Wewnętrznie indeks GIN zawiera indeks drzewa B zbudowany na kluczach, gdzie każdy klucz jest elementem jednego lub więcej indeksowanych elementów

Tak czy inaczej, co najmniej jeden element tablicy w kolumnie datajest zbyt duży, aby można go było zindeksować. Jeśli jest to pojedyncza dziwaczna wartość lub jakiś wypadek, możesz być w stanie obciąć tę wartość i zrobić to z nią.

Na potrzeby poniższej wersji demo zakładam inaczej: wiele długich wartości tekstowych w tablicy.

Proste rozwiązanie

Można zastąpić elementy w tablicy dataodpowiednimi wartościami skrótu . I wysyłaj wartości wyszukiwania za pomocą tej samej funkcji skrótu. Oczywiście zapewne chcesz gdzieś przechowywać swoje oryginały. Dzięki temu prawie doszliśmy do mojego drugiego wariantu ...

Zaawansowane rozwiązanie

Możesz utworzyć tabelę przeglądową dla elementów tablicy z serialkolumną jako zastępczym kluczem podstawowym (w rzeczywistości radykalnym rodzajem wartości skrótu) - co jest tym bardziej interesujące, jeśli zaangażowane wartości elementów nie są unikalne:

CREATE TABLE elem (
  elem_id serial NOT NULL PRIMARY KEY
, elem    text UNIQUE NOT NULL
);

Ponieważ chcemy, aby spojrzeć w górę elem, dodajemy indeksu - ale o indeks na wyrażenie tego czasu, tylko z pierwszych 10 znaków z długiego tekstu. W większości przypadków powinno to wystarczyć, aby zawęzić wyszukiwanie do jednego lub kilku trafień. Dostosuj rozmiar do dystrybucji danych. Lub użyj bardziej wyrafinowanej funkcji skrótu.

CREATE INDEX elem_elem_left10_idx ON elem(left(elem,10));

Twoja kolumna databyłaby wtedy typu int[]. Zmieniłem nazwę stołu datai pozbyłem się złowrogiego varchar(50), który miałeś w swoim przykładzie:

CREATE TEMP TABLE data(
  data_id serial PRIMARY KEY
, data int[]
);

Każdy element tablicy dataodnosi się do elem.elem_id. W tym momencie możesz rozważyć zamianę kolumny tablicy tabelą n: m, normalizując w ten sposób swój schemat i umożliwiając Postgresowi wymuszanie integralności referencyjnej. Indeksowanie i ogólna obsługa stają się łatwiejsze ...

Jednak ze względu na wydajność int[]kolumna w połączeniu z indeksem GIN może być lepsza. Rozmiar przechowywania jest znacznie mniejszy. W takim przypadku potrzebujemy indeksu GIN:

CREATE INDEX data_data_gin_idx ON data USING GIN (data);

Teraz każdy klucz indeksu GIN (= element tablicy) jest integerzamiast długiego text. Indeks będzie mniejszy o kilka rzędów wielkości, w związku z tym wyszukiwania będą znacznie szybsze.

Wada: zanim faktycznie możesz przeprowadzić wyszukiwanie, musisz spojrzeć elem_idna tabelę elem. Używając mojego nowo wprowadzonego indeksu funkcjonalnego elem_elem_left10_idx, to również będzie znacznie szybsze.

Możesz to wszystko zrobić za pomocą jednego prostego zapytania :

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

Możesz być zainteresowany rozszerzeniem intarray, które zapewnia dodatkowe operatory i klasy operatorów.

W pełni funkcjonalne demo na żywo na sqlfiddle.

Błąd dotyczy indeksu ix_data, a nie text[]pola. Maksymalny rozmiar wiersza w tym konkretnym typie indeksu jest ograniczony do 2712bajtów. Jeśli upuścisz indeks i spróbujesz ponownie wstawić, powinien on działać dla Ciebie. Jeśli potrzebujesz zaindeksować większe pole, możesz zajrzeć do funkcji pełnotekstowego indeksowania postgres.

Otrzymywałem to w kolumnie Geografia PostGIS. To dlatego, że przypadkowo utworzyłem indeks niepoprawnie. Podczas tworzenia takich indeksów należy dołączyć parametr USING GIST.