Główna Odkrywanie Tech Antystres
🇬🇧 English 🇪🇸 Español 🇩🇪 Deutsch 🇫🇷 Français 🇵🇱 Polski 🇨🇿 Česky 🇨🇳 中文 🇰🇷 한국어
Powrót do strony głównej

PostgreSQL: Optymalizacja zapytań i zrozumienie działania indeksów

Dowiedz się, dlaczego PostgreSQL ignoruje indeksy. Szczegółowa analiza EXPLAIN ANALYZE, selektywności, korelacji i zaawansowanych typów indeksów w celu zwiększenia wydajności baz danych.

By Sudonull Editorial Team • Published • Updated

PostgreSQL: Jak osiągnąć maksymalną wydajność z indeksami
Advertisement 728x90

PostgreSQL: Kiedy indeksy zawodzą? Optymalizacja z EXPLAIN ANALYZE

Utworzenie indeksu nie zawsze gwarantuje jego wykorzystanie przez PostgreSQL. Deweloperzy często napotykają sytuacje, w których, pomimo istnienia indeksu, zapytania wykonują się wolno, uciekając się do pełnego skanowania tabeli (Seq Scan). Zrozumienie mechanizmów działania planisty zapytań PostgreSQL, interpretacja metryk EXPLAIN ANALYZE oraz znajomość czynników wpływających na wybór strategii wykonania są kluczowe dla optymalizacji wydajności baz danych. W tym artykule szczegółowo omówimy te aspekty na rzeczywistych przykładach z dużą ilością danych.

Przygotowanie: 4 miliony wierszy do eksperymentów

Aby zademonstrować działanie indeksów, stworzymy testową tabelę t_test bez klucza podstawowego i indeksów, zawierającą 4 miliony rekordów. Pozwoli to wyraźnie pokazać różnicę w wydajności przed i po indeksowaniu.

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

Wykonując zapytanie bez indeksu, na przykład EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;, zobaczymy Seq Scan, który przegląda wszystkie 4 miliony wierszy, zajmując około 126 milisekund. To klasyczny przykład problemu, który indeksy mają rozwiązywać.

Google AdInline article slot

Zrozumienie EXPLAIN i metryki cost

EXPLAIN ANALYZE to podstawowe narzędzie do analizy planów wykonania zapytań w PostgreSQL. Dostarcza szczegółowych informacji o tym, jak planista zamierza wykonać zapytanie, włączając wybrane operatory (np. Seq Scan, Index Scan), czas wykonania oraz, co szczególnie ważne, metrykę cost.

Rozważmy wynik cost=0.00..71622.00 z poprzedniego przykładu. Ta liczba to nie rzeczywisty czas w milisekundach, lecz umowny współczynnik, którego PostgreSQL używa do porównywania różnych planów wykonania. Można go przedstawić jako „papugi” — abstrakcyjne jednostki kosztu. Dla czystości eksperymentu warto wyłączyć równoległość:

SET max_parallel_workers_per_gather TO 0;

Koszt składa się z kilku komponentów, takich jak liczba bloków na dysku i koszt przetwarzania każdego wiersza:

Google AdInline article slot
SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 bloki po 8 KB
SHOW cpu_tuple_cost;    -- 0.01 (koszt przetwarzania wiersza)
SHOW cpu_operator_cost; -- 0.0025 (koszt operatora/funkcji)

Przybliżona formuła kosztu dla naszego Seq Scan będzie wyglądać następująco:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

Wynik będzie zbliżony do 71622. Ważne jest, aby zrozumieć, że cost nie uwzględnia specyfiki sprzętowej systemu, dlatego porównywanie cost dwóch różnych zapytań w celu oceny rzeczywistego czasu wykonania jest niepoprawne. Jednak w ramach jednego zapytania jest on przydatny do identyfikacji najdroższych części planu.

Podstawowe użycie indeksów: BTree i jego zalety

Najczęściej spotykany typ indeksu w PostgreSQL to BTree. Zapewnia on efektywne wyszukiwanie, sortowanie i wysoką współbieżność. Stwórzmy indeks BTree na kolumnie id:

Google AdInline article slot
CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

Po utworzeniu indeksu cost zapytania znacząco spada (z 71 622 do 8.45), a czas pobierania danych skraca się do ułamków milisekundy. Indeksy BTree są również efektywne dla operacji sortowania i znajdowania wartości minimalnych/maksymalnych:

  • Sortowanie: PostgreSQL może użyć indeksu do wykonania ORDER BY, po prostu przechodząc przez indeks w odpowiednim kierunku (Index Scan Backward dla DESC) i zatrzymując się po osiągnięciu LIMIT.

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

  • Min/Max: Do określenia min(id) lub max(id) planista używa Index Only Scan, odczytując pierwszy lub ostatni rekord z indeksu.

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

Obsługa wielu warunków: Bitmap Scan

PostgreSQL jest w stanie efektywnie przetwarzać zapytania z wieloma warunkami OR na jednym indeksie, używając Bitmap Scan.

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

W tym przypadku PostgreSQL wykonuje Bitmap Index Scan dla każdego warunku, następnie łączy uzyskane wyniki w mapę bitową za pomocą BitmapOr i dopiero potem odwołuje się do głównej tabeli (Bitmap Heap Scan) w celu pobrania pełnych wierszy. Pozwala to uniknąć wielokrotnego skanowania tabeli i zoptymalizować dostęp do danych.

Dlaczego planista ignoruje indeks: Selektywność i statystyki

Jedną z najczęstszych przyczyn, dla których indeks nie jest używany, jest niska selektywność warunku zapytania. Selektywność to odsetek wierszy w tabeli, które spełniają dany warunek. Jeśli warunek dotyczy dużej części tabeli, planista może uznać, że Seq Scan będzie bardziej efektywny niż Index Scan.

Rozważmy przykład. Stwórzmy indeks na polu name:

CREATE INDEX idx_name ON t_test (name);

Jeśli szukamy nieistniejącej nazwy (EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';), indeks zostanie użyty, a rows będzie równe 1, ponieważ PostgreSQL zawsze oczekuje co najmniej jednego wiersza. Jednakże, jeśli zapytanie obejmuje dużą część tabeli, na przykład 'hans' OR 'paul' (co stanowi 100% naszej testowej tabeli):

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

W tym przypadku PostgreSQL wykona Seq Scan. Przyczyna jest prosta: skanowanie całego indeksu, a następnie przechodzenie do tabeli dla każdego z 4 milionów wierszy jest droższe niż po prostu sekwencyjne odczytanie całej tabeli. Planista podejmuje decyzje na podstawie statystyk dotyczących rozkładu danych. Jeśli statystyki są nieaktualne (np. po dużej ilości zmian), decyzja może być nieoptymalna.

Wpływ fizycznego rozmieszczenia danych: Korelacja i CLUSTER

Efektywność indeksu silnie zależy od fizycznego rozmieszczenia danych na dysku. Jeśli dane, do których odwołuje się indeks, są mocno rozproszone, może to znacząco spowolnić pobieranie. Stwórzmy kopię naszej tabeli, ale z losową kolejnością wierszy:

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Porównajmy wydajność zapytania pobierającego pierwsze 10 000 rekordów dla oryginalnej (uporządkowanej) i zrandomizowanej tabeli:

  • Oryginalna tabela (dane w kolejności):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

Tutaj zobaczymy niską liczbę odwołań do buforów (np. Buffers: shared hit=3 read=82), co wskazuje na sekwencyjne odczytywanie danych.

  • Zrandomizowana tabela (dane w nieuporządkowanej kolejności):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

W tym przypadku liczba odwołań do buforów będzie znacznie wyższa (np. Buffers: shared hit=801 read=7210). Dzieje się tak, ponieważ dane są rozproszone, a PostgreSQL musi wykonywać wiele losowych odczytów z dysku, co znacząco zwiększa czas wykonania. Planista może nawet przełączyć się na Bitmap Heap Scan.

Korelacja

PostgreSQL śledzi stopień uporządkowania danych za pomocą metryki korelacji, która jest dostępna w pg_stats:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;
  • correlation ~ 1: Dane są fizycznie uporządkowane, co pozwala na sekwencyjne odczytywanie bloków z dysku.
  • correlation ~ 0: Dane są losowo rozproszone, co prowadzi do wielu pojedynczych odwołań do dysku dla każdego wiersza.

CLUSTER

Komenda CLUSTER pozwala fizycznie posortować dane w tabeli według wskazanego indeksu:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

Po CLUSTER pobieranie danych ponownie stanie się szybkie. Jednak CLUSTER ma istotne wady:

  • Blokowanie tabeli: Operacja blokuje całą tabelę, włącznie z SELECT, na czas wykonania.
  • Ograniczenie: Działa tylko na jednym indeksie.
  • Brak automatycznego utrzymania: Kolejność danych nie jest automatycznie utrzymywana; po nowych wstawieniach lub aktualizacjach dane mogą ponownie stać się nieuporządkowane.

Optymalizacja poprzez Index Only Scan i INCLUDE

Gdy zapytanie odwołuje się tylko do kolumn, które są w pełni zawarte w indeksie, PostgreSQL może wykonać Index Only Scan. Pozwala to uniknąć odwoływania się do głównej tabeli (heap), co znacząco przyspiesza wykonanie zapytania.

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Tutaj id jest już w indeksie idx_id, dlatego Index Only Scan jest możliwy. Jednakże, jeśli zapytamy o wszystkie kolumny, włączając name, którego nie ma w indeksie idx_id:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL wykona zwykły Index Scan, ponieważ będzie musiał odwołać się do tabeli po kolumnę name. Aby zapewnić Index Only Scan nawet dla SELECT *, można użyć indeksu pokrywającego z klauzulą INCLUDE:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Teraz name jest uwzględnione w indeksie, i ponownie wykonywany jest Index Only Scan, co minimalizuje odwołania do dysku.

Zaawansowane techniki indeksowania: Indeksy Funkcyjne i Częściowe

Oprócz standardowych indeksów BTree, PostgreSQL oferuje bardziej wyspecjalizowane rozwiązania:

  • Indeksy funkcyjne: Pozwalają indeksować wyniki funkcji. Jedynym wymogiem jest, aby funkcja była deterministyczna (zawsze zwracała ten sam wynik dla tych samych danych wejściowych).

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

Typowy scenariusz użycia to indeksowanie lower(email) dla wyszukiwania bez uwzględniania wielkości liter.

  • Indeksy częściowe: Indeksy, które obejmują tylko podzbiór wierszy tabeli, spełniających określony warunek WHERE.

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

Taki indeks będzie znacznie mniejszy i będzie rzadziej aktualizowany, co jest przydatne, gdy główna część danych rzadko uczestniczy w zapytaniach wyszukiwania.

Inne typy indeksów: GiST i pg_trgm

PostgreSQL obsługuje różne typy indeksów, z których każdy jest zoptymalizowany pod kątem konkretnych zadań. Oprócz BTree, istnieją GIN, GiST, SP-GiST, BRIN, Bloom. Na przykład, indeksy GiST są często używane dla danych geoprzestrzennych, wyszukiwania pełnotekstowego i innych złożonych typów danych.

Rozszerzenie pg_trgm pozwala na wykonywanie wyszukiwania rozmytego, dzieląc ciągi znaków na trigramy i obliczając odległość między nimi. Jest to przydatne do wyszukiwania z literówkami lub częściowego dopasowania ciągów znaków.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- liczba od 0 do 1
SELECT show_trgm('abcdef');

Co ważne

  • EXPLAIN ANALYZE – Twój najlepszy przyjaciel: Używaj go do zrozumienia planów zapytań i identyfikacji wąskich gardeł. cost to umowna metryka, przydatna do porównywania części jednego planu.
  • Selektywność decyduje o wyborze: Indeksy są efektywne dla zapytań o wysokiej selektywności (mało wierszy). Przy niskiej selektywności (wiele wierszy) PostgreSQL może preferować Seq Scan.
  • Fizyczne rozmieszczenie danych ma znaczenie: Wysoka korelacja między logicznym a fizycznym porządkiem danych poprawia wydajność Index Scan. CLUSTER może pomóc, ale ma poważne wady.
  • Index Only Scan i INCLUDE: Używaj tych mechanizmów do minimalizowania odwołań do głównej tabeli, gdy wszystkie niezbędne kolumny są już w indeksie.
  • Zaawansowane indeksy: Indeksy funkcyjne i częściowe pozwalają tworzyć bardziej wyspecjalizowane i efektywne struktury dla konkretnych scenariuszy.

— Editorial Team

Advertisement 728x90

Czytaj dalej