Typowe problemy z indeksami

Nawet najnowsza fura nie pojedzie, jeśli zamiast benzyny wlejesz lemoniadę. Tak samo jest z indeksami w PostgreSQL. To naprawdę potężne narzędzie, ale trzeba go używać z głową. Zobaczmy kilka typowych problemów związanych z indeksami.

Problem 1: nadmiarowe indeksowanie

Na początek przypomnijmy sobie temat z przed-przedniej lekcji. Gdy masz za dużo indeksów na jednej tabeli, PostgreSQL musi ogarniać każdy z nich, żeby były aktualne. To bezpośrednio wpływa na operacje INSERT, UPDATE i DELETE. Każdy indeks trzeba nie tylko zaktualizować, ale też zsynchronizować!

Załóżmy, że mamy tabelę students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

I postanawiasz zrobić indeksy na każdej kolumnie „na wszelki wypadek”:

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Wyobraź sobie teraz, że wrzucasz 10 tysięcy nowych rekordów. PostgreSQL musi nie tylko zapisać dane do tabeli, ale też zaktualizować wszystkie trzy indeksy. Jeśli danych jest dużo, prędkość zapisu spada, a wydajność całego systemu cierpi.

Jak tego uniknąć? Przed stworzeniem indeksu zadaj sobie dwa pytania:

1. Jak często ta kolumna bierze udział w filtrowaniu (WHERE), sortowaniu (ORDER BY) albo grupowaniu (GROUP BY)?
2. Czy zapytanie faktycznie użyje tego indeksu, czy i tak zrobi pełny skan tabeli?

Jeśli odpowiedź na oba pytania to „rzadko” albo „nigdy”, indeks jest zbędny.

Problem 2: zły wybór kolumn do indeksu

Tworzenie indeksu na danych o małej różnorodności to jak próba nalania herbaty do kubka z zaklejoną pokrywką: efekt praktycznie żaden. Jeśli w kolumnie są tylko 2-3 unikalne wartości, PostgreSQL najpewniej zrobi pełny skan tabeli zamiast użyć indeksu.

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

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Może być tylko 'Beginner', 'Intermediate' albo 'Advanced'
);

I postanawiasz zrobić indeks na kolumnie level:

CREATE INDEX idx_courses_level ON courses(level);

Ale zapytanie:

SELECT * FROM courses WHERE level = 'Beginner';

może nie użyć indeksu, bo PostgreSQL po prostu policzy, że łatwiej przeskanować całą tabelę niż zaglądać do indeksu. To szczególnie dotyczy małych tabel i danych o małej różnorodności.

Dlatego indeksy mają sens na kolumnach o wysokiej kardynalności (czyli z dużą liczbą unikalnych wartości). Dla danych o małej różnorodności lepiej użyć innych sposobów optymalizacji, np. partycjonowania tabel.

Problem 3: przestarzałe indeksy

Czasem indeksy są tworzone, a potem o nich się zapomina, nawet jeśli już nie są potrzebne. To jak pliki na pulpicie: na początku jest ich mało — dwa, trzy, pięć. A potem nagle łapiesz się na tym, że tracisz czas, szukając właściwej ikony... Znasz to?

Załóżmy, że stworzyliśmy indeks do starej funkcjonalności, potem zmieniliśmy logikę zapytań i dodaliśmy nowy indeks. Stary indeks już nikomu niepotrzebny, ale dalej zajmuje miejsce i spowalnia operacje zapisu.

Żeby tego uniknąć, regularnie sprawdzaj i analizuj indeksy. PostgreSQL daje fajną metrykę:

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Tutaj idx_scan pokazuje, ile zapytań użyło danego indeksu. Jeśli wartość to 0, indeks nie jest używany i można go wywalić:

DROP INDEX idx_courses_level;

Problem 4: indeksy na kolumnach często aktualizowanych

Jeśli na kolumnie, którą często aktualizujesz, jest indeks, PostgreSQL musi go przebudowywać przy każdej zmianie. To może mocno spowolnić wydajność.

Wyobraź sobie tabelę z danymi o zamówieniach:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Może się zmieniać kilka razy (np. "nowy", "w trakcie", "zakończony")
    total NUMERIC(10, 2)
);

Tworzysz indeks na kolumnie status, żeby przyspieszyć filtrowanie po statusach:

CREATE INDEX idx_orders_status ON orders(status);

Ale jeśli status jest aktualizowany dziesiątki razy dla każdego rekordu, indeks zaczyna szkodzić wydajności.

Żeby tego uniknąć, nie twórz indeksów na kolumnach z częstymi zmianami. Jeśli jednak indeks jest konieczny, rozważ użycie indeksów częściowych:

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'w trakcie';

Dzięki temu indeks będzie aktualizowany tylko dla rekordów z podaną wartością.

Problem 5: Ograniczenia UNIQUE na niepotrzebnych kolumnach

Unikalne indeksy (UNIQUE) są automatycznie tworzone, żeby pilnować unikalności danych. Ale jeśli unikalność nie jest naprawdę wymagana, te indeksy tylko obciążają bazę.

Załóżmy, że stworzyliśmy tabelę z logami:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

Jeśli co sekundę do tabeli wpadają tysiące rekordów, pilnowanie unikalności po created_at robi niezły zamęt.

Żeby było git, zostawiaj ograniczenia UNIQUE tylko tam, gdzie to naprawdę potrzebne. W naszym przykładzie, jeśli unikalność po created_at nie jest wymagana, zamień indeks na zwykły:

CREATE INDEX idx_logs_created_at ON logs(created_at);

Problem 6: złe użycie indeksów złożonych

Indeksy złożone (multi-column indexes) są spoko, jeśli zapytania filtrują albo sortują po kilku kolumnach naraz. Ale trzeba je tworzyć z głową, bo inaczej będą bezużyteczne.

Załóżmy, że mamy taki indeks:

CREATE INDEX idx_students_name_grade ON students(name, grade);

Ten indeks jest używany, jeśli zapytanie filtruje albo sortuje po obu kolumnach:

SELECT * FROM students WHERE name = 'Alice' AND grade = 90;

Ale zapytanie:

SELECT * FROM students WHERE grade = 90;

nie użyje tego indeksu, bo pole name jest pierwsze.

Żeby uniknąć problemu, twórz indeksy złożone tylko w takiej kolejności, w jakiej najczęściej są używane w zapytaniach. Jeśli trzeba filtrować tylko po jednej kolumnie, zrób osobny indeks.

Przydatne tipy

Monitoruj użycie indeksów. W PostgreSQL jest systemowy widok pg_stat_user_indexes, gdzie możesz sprawdzić, które indeksy są używane, a które nie.

Optymalizuj zapytania razem z indeksami. Słabe zapytania pozostaną słabe nawet z indeksami.

Nie zapominaj o usuwaniu. Przestarzałe indeksy tylko zajmują miejsce i spowalniają operacje zapisu.

No i to tyle, ekipo! Indeksy to potężne narzędzie, ale pamiętajcie, że z wielką mocą idzie wielka odpowiedzialność. Używajcie indeksów świadomie, a wasza baza będzie śmigać jak rakieta od SpaceX!