Jak wybrać odpowiedni typ indeksu

Już zagłębiliśmy się w teorię indeksów, poznaliśmy ich rodzaje, nauczyliśmy się je tworzyć i usuwać, a także ogarnęliśmy, jak indeksować bardziej złożone typy danych, jak tablice i JSONB. Teraz czas pogadać o tym, jak wybrać właśnie ten indeks, który będzie działał efektywnie dla twoich zadań — bo zły wybór może skończyć się niezłą katastrofą.

Wyobraź sobie, że twoja baza danych to biblioteka, a zapytania to goście, którzy szukają książek. Jeśli książki są po prostu porozrzucane po podłodze, szukanie zamienia się w wieczne błądzenie. Indeksy to uporządkowane półki i katalogi, które pozwalają szybko znaleźć to, czego szukasz, bez tracenia czasu na przeglądanie wszystkiego po kolei.

Ale jeśli postawisz nieodpowiednią półkę albo katalog, na przykład użyjesz indeksu HASH tam, gdzie potrzebny jest indeks do wyszukiwania po zakresie, to tak, jakby bibliotekarz próbował szukać książek po tytule, mając tylko katalog według roku wydania — proces się przeciąga i wszyscy zaczynają narzekać. W bazie danych to oznacza wolne zapytania i większe obciążenie systemu.

Dzisiaj ogarniemy, jak dobrze dobrać indeks, żeby twoje zapytania śmigały, a baza nie była przemęczona. Bo jak zrobisz to źle, efekt będzie kiepski: zapytania zamulają, zasoby znikają, a bibliotekarz (PostgreSQL) wpada w depresję.

Kryteria wyboru indeksu: checklista

Kiedy wybierasz indeks, odpowiedz sobie na kilka pytań:

1. Jaki typ danych masz w tej kolumnie?
   • Na przykład liczby INTEGER, FLOAT często potrzebują indeksu B-TREE, tablice — GIN, pola tekstowe — to już zależy od zadania.

2. Jakie zapytania wykonujesz najczęściej?

   • WHERE field = value? Proste wyszukiwanie? Najpewniej sprawdzi się B-TREE albo HASH.
   • Wyszukiwanie po tablicach albo JSONB? Patrz w stronę GIN.
   • Dane geograficzne, zakresy? Pomyśl o GiST.

3. Co się dzieje z twoimi danymi?

   • Jeśli masz tabelę z częstymi insertami i update'ami, unikaj nadmiarowego indeksowania, bo to zwiększy narzut.

4. Czy musisz zapewnić unikalność?

   • Wtedy musisz użyć indeksu z atrybutem UNIQUE.

Case'y: prawdziwe przykłady wyboru indeksu

Zobaczmy kilka realnych scenariuszy.

1. Proste wyszukiwanie po równości

Pracujesz z bazą studentów i chcesz szybko znaleźć studenta po jego emailu:

SELECT * FROM students WHERE email = 'student@example.com';

Co tu ważne? Szukamy po równości. Najlepszym wyborem będzie indeks B-TREE, bo świetnie radzi sobie z wyszukiwaniem dokładnych dopasowań.

CREATE INDEX idx_students_email ON students (email);

Albo, jeśli email musi być unikalny:

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. Wyszukiwanie po zakresie

Załóżmy teraz, że chcesz znaleźć studentów starszych niż 18 lat:

SELECT * FROM students WHERE age > 18;

Dla wyszukiwania po zakresie B-TREE też się świetnie nadaje, bo jego struktura jest stworzona do szukania po kolejności.

CREATE INDEX idx_students_age ON students (age);

3. Filtrowanie po tablicach

Masz tabelę courses, gdzie w jednej z kolumn trzymasz tablicę z ID studentów zapisanych na kurs. Chcesz znaleźć wszystkie kursy, na które zapisany jest student o ID 123.

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

Do takich zapytań idealny jest indeks GIN, bo jest zoptymalizowany do pracy z tablicami.

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. Wyciąganie danych z JSONB

Załóżmy, że masz tabelę z danymi JSONB, gdzie trzymasz info o zamówieniach. Chcesz znaleźć wszystkie zamówienia, gdzie klient jest z miasta "Moscow":

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

Tutaj sprawdzi się indeks GIN, który pozwala efektywnie szukać po kluczach i wartościach JSONB.

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. Dane geograficzne

Jeśli pracujesz z danymi geograficznymi, np. chcesz znaleźć wszystkie punkty w zadanym promieniu, użyj indeksu GiST. Ten typ indeksu świetnie działa z geometrią i zakresami.

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

Porównanie wydajności różnych indeksów

Weźmy prawdziwy przykład z wyszukiwaniem studentów po emailu. W tabeli jest milion rekordów. Spróbujemy wykonać zapytanie z różnymi indeksami i bez indeksu:

Wniosek: w tym przypadku użycie indeksu B-TREE przyspiesza zapytanie ponad 500 razy.

Błędy przy wyborze indeksów

Najczęstszy błąd — tworzenie indeksów "na wszelki wypadek". Na przykład postanawiasz zindeksować każdą kolumnę w tabeli, a potem okazuje się, że wydajność insertów spadła. Zapamiętaj, indeks to nie magiczne narzędzie, które działa zawsze i wszędzie. To potężne narzędzie w dobrych rękach, ale złe użycie może zaszkodzić.

Inny typowy błąd — wybór złego typu indeksu. Załóżmy, że używasz indeksu HASH do wyszukiwania po zakresie i nagle twoje zapytania robią się strasznie wolne. Wszystko dlatego, że HASH jest tylko do dokładnych wyszukiwań.

Rekomendacje przy wyborze indeksu

• Jeśli często robisz wyszukiwanie po równości albo sortowanie, używaj B-TREE.
• Dla dokładnych dopasowań, ale z minimalnym zużyciem pamięci, możesz użyć HASH.
• Jeśli pracujesz z tablicami albo JSONB, twój wybór to GIN.
• Dla zakresów albo danych geograficznych używaj GiST.

I na koniec najważniejsza rada: zawsze analizuj swoje zapytania! Używaj komendy EXPLAIN i EXPLAIN ANALYZE, żeby zobaczyć, jak PostgreSQL używa indeksów i co można poprawić.

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

No i to wszystko na dziś! Teraz jesteś uzbrojony w wiedzę, żeby wybierać indeksy jak Jedi wybiera swój miecz świetlny. Bądź ostrożny, nie twórz indeksów tam, gdzie nie są potrzebne, i zawsze sprawdzaj, jak wpływają na wydajność.