W tabeli z milionem wierszy to samo zapytanie SELECT może trwać pięćdziesiąt milisekund albo mniej niż jedną. Jedyna różnica: baza danych albo czyta każdy wiersz, żeby znaleźć te, których szukasz, albo skacze od razu do nich. Właśnie to umożliwia indeks — i płacisz za niego przy każdym zapisie.

Sam indeks to druga, posortowana kopia jednej lub więcej kolumn, przechowywana obok tabeli i aktualizowana automatycznie przy każdej zmianie tabeli. Baza danych używa jej, żeby dotrzeć do pasujących wierszy bez dotykania reszty.

Dlaczego zapytanie bez indeksu czyta całą tabelę

Bez indeksu WHERE customer_id = 42 zmusza Postgresa do przeczytania każdego wiersza i sprawdzenia warunku na każdym z nich. To jest skanowanie sekwencyjne (Seq Scan). Przy tysiącu wierszy nawet tego nie zauważysz. Przy milionie wierszy, gdzie prawie nic nie pasuje, czytasz i odrzucasz 999 989 wierszy, żeby dostać 11.

Możesz to sprawdzić sam. Uruchom kontener Postgresa, skonfigurowany tak jak w Co To Jest Docker Compose i Jak Go Używać:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

1
2

docker compose up -d db
docker compose exec db psql -U postgres

Potem zbuduj tabelę na tyle dużą, żeby było widać różnicę:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_cents integer NOT NULL
);

INSERT INTO orders (customer_id, status, created_at, total_cents)
SELECT
  (random() * 50000)::int,
  (ARRAY['pending', 'paid', 'shipped', 'refunded'])[(floor(random() * 4) + 1)::int],
  now() - (random() * interval '365 days'),
  (random() * 20000)::int
FROM generate_series(1, 1000000);

ANALYZE orders;

Milion wierszy, brak indeksu na customer_id. Poproś o zamówienia jednego klienta. Poniższe przykłady najpierw wyłączają workery równoległe (SET max_parallel_workers_per_gather = 0), żeby plan pozostał w jednym węźle — na maszynie wielordzeniowej Postgres mógłby inaczej podzielić to samo skanowanie między Gather a kilka workerów Parallel Seq Scan, co jest tym samym pomysłem, tylko z większą liczbą procesów działających naraz:

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Seq Scan on orders  (cost=0.00..20834.00 rows=21 width=31) (actual time=1.066..53.238 rows=11 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 999989
 Planning Time: 0.301 ms
 Execution Time: 53.284 ms

Rows Removed by Filter: 999989 to jest rachunek do zapłacenia. Postgres przeczytał całą tabelę, żeby zwrócić 11 wierszy.

Co naprawdę przechowuje indeks B-tree

Tabela bez indeksu to pudełko z paragonami, wrzucanymi jeden na drugi w kolejności, w jakiej przyszły. Znalezienie wszystkich paragonów jednego klienta oznacza opróżnienie pudełka.

Indeks B-tree to te same paragony, ułożone w szafce, posortowane według klienta. Najniższy poziom, leaf pages, przechowuje każdą zindeksowaną wartość razem ze wskaźnikiem do jej wiersza na dysku. Nad liśćmi znajduje się kilka stron wewnętrznych, każda z najniższą wartością zakresu i wskaźnikiem do niego. Strony Postgresa mają domyślnie 8 KB, a jedna strona wewnętrzna może wskazywać na setki dzieci, więc trzy lub cztery poziomy pokrywają dziesiątki milionów wierszy. Wyszukiwanie czyta jedną stronę na poziom i trafia na właściwy liść. Kilka odczytów strony zamiast przejścia przez całą tabelę.

Dodaj indeks i uruchom to samo zapytanie:

1

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5
6
7

 Bitmap Heap Scan on orders  (cost=4.59..85.69 rows=21 width=31) (actual time=0.217..0.295 rows=11 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=11
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.58 rows=21 width=0) (actual time=0.090..0.090 rows=11 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.979 ms
 Execution Time: 0.449 ms

Czas spada z 53 ms do 0,45 ms, około 120 razy szybciej, a szacowany koszt z 20834 do 86. Seq Scan zniknął, i nie ma już linii Rows Removed by Filter: nie było nic do odrzucenia.

Postgres wybrał Bitmap Heap Scan zamiast zwykłego Index Scan. Przy około 20 pasujących wierszach rozrzuconych po tabeli taniej jest najpierw wyciągnąć wszystkie pasujące wpisy z indeksu (linia Bitmap Index Scan), posortować je według strony fizycznej, a potem odwiedzić każdą stronę heapu tylko raz (to pokazuje Heap Blocks: exact=11), niż skakać do heapu osobno dla każdego wiersza. Odfiltruj do jednego wiersza (porównanie równości na unikalnej kolumnie, na przykład id = 42) i zamiast tego zobaczysz zwykły Index Scan. W obu przypadkach indeks robi to samo: dociera do pasujących wierszy bez czytania tych, które nie pasują.

Jak działa kolejność kolumn w indeksie złożonym

Indeks może obejmować więcej niż jedną kolumnę, a kolejność, w jakiej je wymienisz, decyduje, jakie zapytania może obsłużyć. CREATE INDEX ON orders (customer_id, status) sortuje najpierw według customer_id, potem według status wewnątrz każdego customer_id. Może więc odpowiedzieć na:

  • WHERE customer_id = 42, używając pierwszej kolumny.
  • WHERE customer_id = 42 AND status = 'paid', używając obu.

Nie może wydajnie odpowiedzieć samo na WHERE status = 'paid'. status jest posortowany tylko wewnątrz grupy każdego klienta, więc nie istnieje jeden posortowany ciąg wierszy 'paid', do którego drzewo mogłoby skoczyć. To jest reguła najbardziej lewego prefiksu (leftmost prefix rule): indeks złożony pomaga zapytaniu tylko wtedy, gdy ogranicza ono swoje kolumny od lewej, po kolei, bez przerwy.

Dwie zasady wyboru kolejności. Po pierwsze, umieść z przodu kolumnę, po której twoje zapytania filtrują samodzielnie, bo to jedyna, którą indeks obsłuży sam. Po drugie, umieść kolumny porównywane przez = przed kolumną porównywaną przez zakres (>, <, BETWEEN), bo wszystko po kolumnie zakresowej przestaje zawężać wyszukiwanie. Dla tej tabeli customer_id (około 50 000 różnych wartości) idzie przed status (cztery wartości): szukasz klientów cały czas, i nigdy nie chcesz przeglądać wszystkich zamówień 'paid' bez rozróżniania klienta.

Jeśli potrzebujesz też szybkiego wyszukiwania tylko po status, zbuduj drugi indeks. Nie zmieniaj kolejności pierwszego.

Kiedy index-only scan pomija tabelę

Powyższy Bitmap Heap Scan nadal dotyka tabeli — to właśnie robi Heap Blocks: exact=11. Indeks zawiera customer_id i wskaźnik do każdego wiersza; SELECT * chce też status, total_cents i created_at, a te istnieją tylko w tabeli. Klauzula INCLUDE wkłada dodatkowe kolumny do samego indeksu:

1
2
3

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (status, total_cents);

status i total_cents jadą jako payload. Nie są częścią klucza sortowania, więc WHERE status = ... nie może ich użyć do wyszukiwania. Ale poproś tylko o kolumny, które indeks teraz ze sobą niesie:

1

EXPLAIN ANALYZE SELECT customer_id, status, total_cents FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Index Only Scan using idx_orders_customer_covering on orders  (cost=0.42..4.79 rows=21 width=15) (actual time=0.071..0.073 rows=11 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.442 ms
 Execution Time: 0.108 ms

Index Only Scan i Heap Fetches: 0: każdy wiersz przychodzi prosto z indeksu, bez żadnej wizyty w tabeli. To jest index-only scan.

Heap Fetches: 0 zależy od visibility map tabeli, która zaznacza strony, których wiersze są widoczne dla każdej transakcji — skanowanie pomija heap tylko dla oznaczonych stron. VACUUM ustawia te oznaczenia (zwykle autovacuum, który działa według własnego harmonogramu, nie czegoś, co uruchamiasz ręcznie), a każdy zapis na stronie znowu je czyści. W tabeli z dużą liczbą niedawnych zapisów wiele stron nie jest oznaczonych, Heap Fetches rośnie powyżej zera, a Postgres i tak odwiedza heap dla tych wierszy. Indeksy pokrywające zwracają się najbardziej w tabelach czytanych dużo częściej niż zapisywanych.

Ile kosztuje indeks i dlaczego Postgres go ignoruje

Każdy INSERT zapisuje wpis w każdym indeksie tabeli, a UPDATE, który zmienia zindeksowaną kolumnę, też zapisuje nowe wpisy. Pięć indeksów oznacza pięć dodatkowych struktur do utrzymania przy każdym z tych zapisów. Indeksy zajmują też miejsce na dysku. Porównaj oba rozmiary bezpośrednio:

1
2

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_relation_size('idx_orders_customer_id')) AS index_size;

A czasem budujesz indeks, a planner go nie używa. Rzadko jest to bug Postgresa. Zwykle chodzi o jedno z tych:

  • Funkcja opakowuje kolumnę. WHERE lower(email) = '[email protected]' nie może użyć zwykłego indeksu na email, bo indeks przechowuje surowe wartości, nie lower(email). Zindeksuj wyrażenie (CREATE INDEX ON users (lower(email))) albo filtruj po surowej kolumnie.
  • Wiodący wildcard. WHERE name LIKE '%smith' nie daje drzewu żadnego prefiksu do wyszukania. LIKE 'smith%' daje.
  • Niska selektywność. Indeks na wartości logicznej podzielonej 50/50 nic nie oszczędza. Postgres i tak przeczytałby połowę indeksu i większość stron tabeli, więc planner słusznie wybiera skanowanie sekwencyjne.
  • Nieaktualne statystyki. Planner wybiera między typami skanowania na podstawie statystyk, które ANALYZE zbiera w pg_statistic. Po masowym ładowaniu bez ANALYZE jego szacunki liczby wierszy mogą być mocno chybione. Uruchamiaj go po każdym dużym ładowaniu, tak jak w konfiguracji powyżej.

Kiedy widzisz Seq Scan tam, gdzie spodziewałeś się Index Scan, sprawdź zapytanie względem tej listy, zanim tkniesz indeks.

Kiedy dodać indeks, a kiedy sobie odpuścić

Dodaj go, gdy kolumna pojawia się w WHERE, JOIN albo ORDER BY na tabeli na tyle dużej, że skanowanie kosztuje realny czas, a filtr jest selektywny: zawęża wynik do małego wycinka tabeli, nie do połowy. Odpuść sobie, gdy tabela jest na tyle mała, że skanowanie jest już szybkie, gdy tabela ma dużo zapisów i mało odczytów, albo gdy kolumna niczego nie zawęża.

Nie dodawaj go na wyczucie. Uruchom najpierw EXPLAIN ANALYZE na wolnym zapytaniu. Jeśli już pokazuje Index Scan i nadal jest wolne, indeks nie jest twoim problemem: przepisz zapytanie albo zcachuj wynik w Redis. Jeśli pokazuje Seq Scan na dużej tabeli z selektywnym filtrem, zbuduj indeks dokładnie na kolumnach, których używa WHERE, w kolejności wymaganej przez regułę najbardziej lewego prefiksu, a potem uruchom EXPLAIN ANALYZE jeszcze raz, żeby potwierdzić, że plan się zmienił.

Powiązane artykuły