Indeksowanie tablic i operatory (`@>`, `<@`, `&&`) dla szybkiego wyszukiwania

Tablice w PostgreSQL pozwalają trzymać wiele wartości w jednej komórce tabeli. To mega wygodne, kiedy chcesz pogrupować powiązane dane, np. listę tagów dla artykułu albo kategorie produktu. Ale jak tylko zaczynasz szukać, filtrować albo sprawdzać przecięcia tablic, wydajność może mocno spaść. Właśnie dlatego indeksowanie tablic to ratunek. Indeksy pozwalają przyspieszyć takie operacje jak:

• sprawdzenie, czy tablica zawiera konkretny element,
• szukanie tablic, które zawierają podane elementy,
• sprawdzenie przecięcia tablic.

Operatory do pracy z tablicami

Zanim przejdziemy do tworzenia indeksów, ogarnijmy podstawowe operatory do pracy z tablicami:

@> (contains) — sprawdza, czy tablica zawiera wszystkie elementy z innej tablicy.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Tutaj szukamy kursów, które mają tag "SQL".

<@ (is contained by) — sprawdza, czy jedna tablica zawiera się w drugiej.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Tutaj szukamy kursów, których tagi zawierają wszystkie elementy tablicy ARRAY['PostgreSQL', 'SQL'].

&& (overlap) — sprawdza, czy jest przecięcie między tablicami.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

To zapytanie znajdzie kursy, które mają przynajmniej jeden z tagów "NoSQL" albo "Big Data".

Jak pomaga indeksowanie?

Wyobraź sobie, że masz tabelę courses z milionami rekordów i robisz zapytanie z jednym z powyższych operatorów. Bez indeksu PostgreSQL musi sprawdzić każdą linię po kolei — operacja, która może trwać wieczność (szczególnie jeśli masz cierpliwość jak programista czekający na kompilację).

Z indeksami tego typu koszty można ominąć. PostgreSQL daje dwa typy indeksów, które pasują do pracy z tablicami:

1. GIN (Generalized Inverted Index) — najlepszy wybór dla tablic.
2. BTREE — używany do porównywania całych tablic.

Przykład: Tworzymy indeks dla tablic

Stwórzmy małą tabelę z tablicami, żeby przetestować wszystko w praktyce.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Dodajmy kilka rekordów:

INSERT INTO courses (name, tags)
VALUES
    ('Podstawy SQL', ARRAY['SQL', 'PostgreSQL', 'Bazy danych']),
    ('Praca z Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Programowanie w Python', ARRAY['Python', 'Web', 'Dane']),
    ('Kurs PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Tak wygląda taka tabelka:

Bez indeksu: wolne wyszukiwanie

Wyobraź sobie, że chcesz znaleźć wszystkie kursy, które mają tag SQL.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

To zapytanie się wykona, ale jeśli danych będzie dużo, będzie mega wolne. PostgreSQL zrobi tzw. sekwencyjne przeszukiwanie (Sequential Scan), czyli sprawdzi każdą linię tabeli.

Przykładowy wynik zapytania:

Tworzenie indeksu GIN

Żeby przyspieszyć wyszukiwanie, tworzymy indeks typu GIN:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Spróbujmy wykonać to samo zapytanie:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Teraz PostgreSQL użyje stworzonego indeksu GIN, co mocno skróci czas wykonania zapytania.

Jeśli wcześniej był sekwencyjny przegląd (Seq Scan), to teraz w planie wykonania zobaczysz Bitmap Index Scan:

Jak operatory działają z indeksami?

Przykład 1: Operator @>

Zapytanie:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Indeks GIN idealnie pasuje do tego operatora. Postgres szybko sprawdza, które wiersze mają podany element i zwraca wynik.

Wynik zapytania:

Przykład 2: Operator &&

Zapytanie:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Ten operator sprawdza przecięcia tablic: zwróci wiersze, gdzie tablica tags przecina się z przynajmniej jednym elementem z podanej tablicy.

Indeks GIN znowu robi robotę — wyszukiwanie jest szybkie nawet przy dużej ilości danych.

Wynik zapytania:

Indeksowanie i optymalizacja

Pracując z tablicami, trzymaj się tych zasad:

1. Używaj indeksów GIN do wyszukiwania w tablicach. Są szybsze niż przegląd sekwencyjny.
2. Dodawaj indeksy tylko na kolumny, które naprawdę często są w zapytaniach. Indeksowanie zajmuje miejsce i spowalnia wstawianie danych, więc nie indeksuj wszystkiego jak leci.
3. Profiluj zapytania za pomocą EXPLAIN i EXPLAIN ANALYZE, żeby sprawdzić, czy twój indeks jest faktycznie używany.

Przykłady: tworzenie indeksów dla tablic

Zobaczmy, jak tworzyć indeksy pod konkretne typy operacji na tablicach i po co to w praktyce.

Indeks dla operatora @>

Załóżmy, że mamy już taką tabelę courses:

Żeby przyspieszyć zapytania z operatorem @> (tablica zawiera element), tworzymy indeks GIN:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Teraz wykonajmy zapytanie:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Wynik:

Indeks dla operatorów @>, <@, i &&

Tabela jest taka sama jak w poprzednim przykładzie.

Ponieważ operatory @>, <@ i && wszystkie dobrze działają z indeksami typu GIN, możesz zrobić jeden uniwersalny indeks, który przyspieszy zapytania z każdym z tych operatorów:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Przykłady zapytań i ich wyniki:

• @> — sprawdzenie, czy tablica zawiera podane elementy:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — sprawdzenie, czy tablica zawiera się w innej tablicy:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — sprawdzenie przecięcia tablic:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Spróbujmy czegoś trudniejszego

Zróbmy zapytanie, które zwróci kursy, gdzie wśród tagów jest przynajmniej jedno przecięcie z listą ['Python', 'SQL', 'NoSQL']:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Wynik:

Z GIN indeksem takie zapytanie wykonuje się błyskawicznie, nawet jeśli w tabeli są miliony rekordów.

Typowe błędy przy pracy z tablicami

Indeks nie jest używany: jeśli w wyniku EXPLAIN widzisz Seq Scan, sprawdź, czy indeks jest stworzony i czy operator, którego używasz, faktycznie wspiera indeksowanie.

Rzadkie użycie tablicy: jeśli kolumna z tablicami rzadko jest w zapytaniach albo często się aktualizuje, indeks może zajmować niepotrzebnie miejsce i nie dawać realnej korzyści.

Zbędne indeksy: indeksy zajmują miejsce na dysku i spowalniają operacje zapisu, więc rób tylko te, które naprawdę są potrzebne i będą używane w zapytaniach.

Teraz masz wszystkie narzędzia, żeby efektywnie pracować z tablicami w PostgreSQL — przyspieszaj zapytania za pomocą operatorów @>, <@, && i indeksów GIN. Nie bój się przetestować tego samemu na swoich danych!