Wydajność Postgresa: indeksy, których ci brakuje

Gdy aplikacja Rails robi się wolna, przyczyną jest zwykle baza, a przyczyną w bazie jest zwykle brakujący indeks. Indeks zamienia zapytanie skanujące każdy wiersz milionowej tabeli w takie, które skacze wprost do garstki, której potrzebuje — różnica między sekundami a milisekundami. A jednak indeksowanie jest szeroko niezrozumiane: ludzie niedoindeksowują (i cierpią wolne zapytania), przeindeksowują (i spowalniają zapisy) albo indeksują w zły sposób (a planner ignoruje ich indeks całkowicie). Oto jak poprawnie indeksować Postgresa.

Czym jest indeks, krótko

Indeks B-tree — domyślny i ten, którego chcesz w 95% przypadków — to posortowana struktura danych pozwalająca Postgresowi znaleźć wiersze pasujące do wartości (albo zakresu) bez skanowania całej tabeli. Wymianą do zapamiętania jest: indeks czyni odczyty na indeksowanych kolumnach dramatycznie szybszymi, ale czyni zapisy nieco wolniejszymi (każdy insert/update musi utrzymać indeks) i zajmuje miejsce na dysku. Więc indeksowanie to nie „więcej znaczy lepiej” — to „indeksuj to, co odpytujesz, i nic, czego nie”.

Co indeksować

Cele o wysokiej wartości, po kolei:

Klucze obce. To najczęściej pomijany indeks i o najwyższym wpływie. Rails tworzy asocjację belongs_to, a ty odpytujesz post.comments, co uruchamia WHERE post_id = ? — a jeśli comments.post_id nie jest zindeksowane, to pełny skan tabeli za każdym razem. Postgres nie indeksuje kluczy obcych automatycznie, a Rails historycznie też nie, więc ogromna liczba aplikacji ma niezindeksowane FK powodujące wolne wyszukiwania asocjacji i wolne JOINy. Indeksuj każdy klucz obcy.
Kolumny w klauzulach WHERE. Wszystko, po czym regularnie filtrujesz — status, email, published_at — chce indeksu, jeśli tabela jest duża.
Kolumny w ORDER BY. Sortowanie dużego zbioru wyników bez indeksu oznacza, że Postgres sortuje w pamięci (albo na dysku); indeks pasujący do twojej kolejności sortowania pozwala mu czytać wiersze już posortowane.
Kolumny z wymaganiem unikalności. Unikalny indeks egzekwuje ograniczenie i przyspiesza wyszukiwania.

Pytanie przewodnie dla każdej kolumny: czy zapytanie filtruje, joinuje albo sortuje po tym, na tabeli dość dużej, by to miało znaczenie? Jeśli tak, indeksuj.

Indeksy złożone i reguła kolejności kolumn

Gdy zapytania filtrują po kilku kolumnach razem, indeks złożony (na wielu kolumnach) bije osobne indeksy jednokolumnowe — ale tylko, jeśli kolejność kolumn jest właściwa, i tu ludzie błądzą. Indeks złożony na (a, b, c) może obsłużyć zapytania filtrujące po a, po (a, b) albo (a, b, c) — prefiks od lewej do prawej — ale nie zapytanie filtrujące po samym b ani samym c. Najbardziej lewe kolumny muszą być użyte:

-- obsługuje WHERE status = ? , oraz WHERE status = ? AND created_at > ?
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- NIE obsługuje wydajnie WHERE created_at > ?  (created_at nie jest prefiksem)

Więc porządkuj kolumny indeksu złożonego według tego, jak odpytujesz: kolumny filtrowane równością pierwsze, potem kolumny zakresu lub sortowania. Zła kolejność daje ci indeks, którego planner nie może użyć dla twojego faktycznego zapytania — indeks, za który płacisz przy każdym zapisie i nic z niego nie zyskujesz na odczytach.

Indeksy częściowe i pokrywające

Dwa bardziej zaawansowane rodzaje zarabiają na swoje w konkretnych sytuacjach:

Indeksy częściowe indeksują tylko wiersze pasujące do warunku, co czyni je mniejszymi i szybszymi, gdy odpytujesz tylko podzbiór. Klasycznym przypadkiem jest flaga soft-delete albo status — jeśli niemal zawsze odpytujesz aktywne rekordy, indeksuj tylko je:
```
CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;
```
Indeks jest ułamkiem rozmiaru pełnego i obsługuje zapytanie, które faktycznie uruchamiasz.
Indeksy pokrywające włączają dodatkowe kolumny, by zapytanie mogło być odpowiedziane z samego indeksu, bez odwiedzania tabeli wcale („index-only scan”). Postgres 11 dodaje INCLUDE dokładnie do tego:
```
CREATE INDEX idx_orders_status ON orders (status) INCLUDE (total);
-- zapytanie wybierające `total` filtrowane po `status` nigdy nie dotyka sterty
```
Gdy gorące zapytanie czyta tylko parę kolumn, indeks pokrywający może uczynić je dramatycznie szybszym, unikając wyszukiwania w tabeli całkowicie.

Czytaj EXPLAIN — planner mówi ci prawdę

Nie powinieneś zgadywać, czy indeks pomaga; Postgres ci powie. EXPLAIN ANALYZE pokazuje plan zapytania i faktyczny czas, a najważniejszą rzeczą do wypatrzenia jest Seq Scan vs Index Scan:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';

Seq Scan na dużej tabeli oznacza, że Postgres czyta każdy wiersz — zwykle brakujący lub nieużywalny indeks. Index Scan (albo Index Only Scan) oznacza, że używa indeksu. Jeśli dodałeś indeks i wciąż widzisz Seq Scan, indeks jest zły dla zapytania (często błąd kolejności kolumn w indeksie złożonym) albo tabela jest dość mała, by skan był naprawdę tańszy (planner jest w tym mądry). Nauczenie się czytania EXPLAIN zamienia indeksowanie z zabobonu w mierzalne ćwiczenie — robisz zmianę i widzisz, jak plan się zmienia.

Znajdź brakujące indeksy — i usuń te, których nie używasz

Postgres śledzi statystyki wskazujące cię wprost na problemy:

Brakujące indeksy: pg_stat_user_tables zapisuje seq_scan (skany sekwencyjne) na tabelę. Duża tabela z wysoką i rosnącą liczbą seq_scan to tabela mocno skanowana — silny kandydat na brakujący indeks. Znajdź zapytania w nią uderzające (twój APM albo pg_stat_statements) i zindeksuj to, po czym filtrują.
Nieużywane indeksy: pg_stat_user_indexes zapisuje idx_scan (jak często każdy indeks jest używany). Indeks z idx_scan bliskim zeru na ruchliwej bazie to martwy ciężar — kosztuje cię wydajność zapisu i dysk bez korzyści odczytu. Usuń go. Przeindeksowanie jest tak samo realnym problemem jak niedoindeksowanie, a nieużywane indeksy kumulują się po cichu, gdy zapytania zmieniają się przez lata.

To empiryczna pętla: własne statystyki bazy mówią ci, którym tabelom brakuje indeksów, a które indeksy zarabiają na swoje, więc indeksujesz dowodami, a nie zgadywaniem.

Werdykt

Duża większość wolnych zapytań jest o brakujący lub zły indeks od szybkości, a dobre indeksowanie to mała, wyuczalna dyscyplina. Indeksuj swoje klucze obce (najczęściej pomijane, o najwyższym wpływie), kolumny WHERE i ORDER BY na dużych tabelach oraz ograniczenia unikalności. Porządkuj kolumny indeksu złożonego według kształtu zapytania (równość pierwsza, potem zakres/sortowanie), sięgaj po indeksy częściowe, gdy odpytujesz podzbiór, i pokrywające dla gorących zapytań tylko-do-odczytu, i weryfikuj przez EXPLAIN ANALYZE, zamiast zgadywać. Potem niech statystyki bazy cię prowadzą — liczby seq_scan, by znaleźć brakujące indeksy, liczby idx_scan, by usunąć nieużywane — by twoje indeksowanie pozostało dopasowane do tego, jak aplikacja faktycznie odpytuje. Zrobione tak, baza przestaje być wąskim gardłem, a większość twoich incydentów „aplikacja jest wolna” po prostu przestaje się zdarzać.