CREATE INDEX name ON table (column); buduje indeks, dzięki któremu PostgreSQL znajduje pasujące wiersze bez czytania całej tabeli. Domyślny typ to B-tree, który obsługuje =, <, >, BETWEEN, IN, IS NULL i ORDER BY. Inny typ wybierasz przez USING: gin, gist, spgist, brin albo hash.
Pierwsza linia planu to Index Scan using users_email_idx on users: PostgreSQL szuka wartości w indeksie i czyta tylko pasujący wiersz. Usuń linię z CREATE INDEX i uruchom zapytanie ponownie, a zobaczysz Seq Scan on users, który czyta wszystkie 10 000 wierszy.
Przy tabeli z kilkudziesięcioma wierszami planer często wybiera skan sekwencyjny, nawet gdy indeks istnieje: przeczytanie kilku stron jest tańsze. Jak czytać takie plany, wyjaśnia EXPLAIN ANALYZE.
Wybór typu indeksu
| Typ | Użyj do |
|---|---|
| B-tree (domyślny) | równości, zakresów, sortowania, LIKE 'prefix%' (z porządkiem sortowania C albo text_pattern_ops) |
| GIN | wartości złożonych z wielu części: jsonb (@>, ?), tablice (@>, &&), pełnotekstowe tsvector |
| GiST | geometrii, zakresów (&&, nakładanie się), pełnego tekstu, najbliższych sąsiadów przez ORDER BY ... <-> |
| SP-GiST | danych dzielących się na rozłączne obszary: punktów, adresów IP, prefiksów tekstu |
| BRIN | bardzo dużych tabel, których wartości idą zgodnie z fizyczną kolejnością, jak dopisywane tylko created_at |
| Hash | tylko równości; B-tree robi to samo i więcej, więc wybiera się go rzadko |
Indeks GIN na kolumnie jsonb sprawia, że zapytania o zawieranie używają indeksu:
Plan pokazuje Bitmap Index Scan na indeksie GIN, który zasila Bitmap Heap Scan czytający pasujące wiersze. Strona o JSONB omawia jego operatory.
Indeks na kilku kolumnach
Wielokolumnowy indeks B-tree sortuje po pierwszej kolumnie, a w obrębie każdej jej wartości po drugiej. Obsługuje zapytania filtrujące po kolumnie wiodącej albo po wiodącej i kolejnych. Filtr tylko po drugiej kolumnie niewiele na nim zyskuje, więc na początek wstaw kolumnę, po której zawsze filtrujesz przez =.
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
INCLUDE (PostgreSQL 11+) zapisuje w indeksie dodatkowe kolumny bez sortowania po nich: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Zapytanie, które czyta tylko te kolumny, może wtedy dostać odpowiedź z samego indeksu, przez Index Only Scan.
Indeksy unikalne i częściowe: reguła dla części wierszy
CREATE UNIQUE INDEX odrzuca zduplikowane wartości. Indeks częściowy dodaje WHERE i indeksuje tylko pasujące do niego wiersze. Jest mniejszy i może sprawić, że reguła unikalności dotyczy tylko części wierszy:
Oba wiersze są przyjęte, bo tylko jeden z nich nie jest usunięty. Trzeci aktywny wiersz z tym samym adresem e-mail kończy się błędem duplicate key value violates unique constraint "accounts_email_live". Zapytanie używa indeksu częściowego tylko wtedy, gdy jego WHERE implikuje warunek indeksu, tutaj WHERE NOT deleted.
Indeks na wyrażeniu
Indeks może przechowywać wynik wyrażenia. Zapytanie musi powtórzyć to samo wyrażenie, żeby z niego skorzystać, a indeks na email nie pomaga przy WHERE lower(email) = ...:
Plan używa users_email_lower_idx, bo WHERE dokładnie powtarza lower(email). Wyrażenie musi być niezmienne (immutable), więc now() albo funkcja zależna od ustawienia zostaje odrzucona.
Listowanie, budowanie i usuwanie indeksów na działającej bazie
pg_indexes wypisuje każdy indeks z poleceniem, które by go odtworzyło:
Klucz główny i ograniczenie UNIQUE utworzyły własne indeksy, users_pkey i users_email_key. W psql \d users pokazuje tę samą listę pod kolumnami tabeli.
Na tabeli, do której trwają zapisy, buduj i usuwaj indeksy z CONCURRENTLY, żeby nie blokować zapisów. Te formy nie mogą działać w bloku transakcji. Nieudana współbieżna budowa zostawia indeks INVALID, który trzeba usunąć i utworzyć ponownie:
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx; -- rebuild a bloated index
Najczęściej zadawane pytania
Czy PostgreSQL tworzy indeksy automatycznie?
Tylko dla PRIMARY KEY, UNIQUE i ograniczeń wykluczających. Kolumna klucza obcego nie dostaje indeksu, więc dodaj go sam, gdy po niej złączasz albo usuwasz wiersze z tabeli, do której się odwołuje.
Dlaczego PostgreSQL nie używa mojego indeksu?
Częste powody: tabela jest mała, więc przeczytanie jej w całości jest tańsze; zapytanie zwraca dużą część wierszy; WHERE opakowuje kolumnę w funkcję albo rzutowanie, których indeks nie ma; albo statystyki są nieaktualne (uruchom ANALYZE). EXPLAIN pokazuje, który plan został wybrany.
Ile indeksów to za dużo?
Nie ma stałej liczby. Każdy indeks przyspiesza część odczytów i spowalnia każdy INSERT oraz większość UPDATE na tabeli, a do tego zajmuje miejsce na dysku. pg_stat_user_indexes.idx_scan pokazuje, jak często każdy indeks jest używany, więc możesz usunąć te, które zostają na zerze.
Czy CREATE INDEX blokuje tabelę?
Zwykłe CREATE INDEX blokuje INSERT, UPDATE i DELETE na tabeli do końca, choć odczyty działają dalej. CREATE INDEX CONCURRENTLY pozwala na zapisy w trakcie budowy, trwa dłużej i nie może działać w bloku transakcji.