Optymalizacja zapytań SQL w PostgreSQL — od 8 sekund do 120 ms

Spis treści

Wyobraź sobie: zapytanie działa świetnie przez pierwsze tygodnie, bo tabela ma 10 000 wierszy. Potem przychodzi koniec miesiąca, danych przybywa, a czas wykonania skacze z 200 ms do 8 sekund. Użytkownicy dzwonią, menadżer pyta, ty przeszukujesz logi.

Ten wpis to praktyczny przewodnik po diagnozowaniu i naprawianiu wolnych zapytań w PostgreSQL — bez zbędnej teorii. Zobaczysz narzędzia, konkretne anty-patterny i jeden duży przykład refaktoryzacji z mierzalnym wynikiem.

EXPLAIN ANALYZE — zacznij tutaj, zawsze
#

Zanim cokolwiek zmienisz, musisz wiedzieć dlaczego zapytanie jest wolne. Do tego służy EXPLAIN ANALYZE.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM applications WHERE status = 'approved';

Przykładowy output:

Seq Scan on applications  (cost=0.00..46323.45 rows=97234 width=64)
                          (actual time=0.23..245.12 rows=97234 loops=1)
  Filter: (status = 'approved')
  Rows Removed by Filter: 1902766
Execution Time: 248.56 ms

Co tu ważne:

Pole	Co oznacza
`cost=0.00..46323.45`	Szacowany koszt plannera (jednostki umowne, nie ms)
`actual time=0.23..245.12`	Rzeczywisty czas: start pierwszego wiersza → koniec
`rows=97234`	Ile wierszy faktycznie zwrócono
`Rows Removed by Filter: 1902766`	Czerwona flaga — 95% danych skanowanych bezużytecznie
`Seq Scan`	Pełny skan tabeli — często sygnał braku lub nieużywanego indeksu

Kluczowa zasada: porównuj rows z planu z actual rows. Duża rozbieżność = nieaktualne statystyki. Naprawisz to przez ANALYZE applications; lub czekając na autovacuum.

Anti-patterny, które zabijają wydajność
#

1. Korelowane podzapytania
#

To najczęstszy i najbardziej kosztowny błąd — szczególnie u programistów, którzy myślą logiką ORM-a.

-- ŹLE: dla każdego użytkownika PostgreSQL wykonuje 2 osobne zapytania
SELECT 
    u.full_name,
    (SELECT COUNT(*) FROM applications a WHERE a.user_id = u.id) AS app_count,
    (SELECT SUM(p.amount) FROM payments p 
     JOIN applications a ON p.application_id = a.id 
     WHERE a.user_id = u.id AND p.status = 'paid') AS total_paid
FROM users u
WHERE u.region = 'mazowieckie';

Jeśli zapytanie zwróci 10 000 użytkowników, PostgreSQL wykona 20 000 dodatkowych zapytań. EXPLAIN pokaże loops=10000 obok każdego SubPlanu.

-- DOBRZE: jeden skan, jedno przejście przez dane
SELECT 
    u.full_name,
    s.app_count,
    s.total_paid
FROM users u
JOIN (
    SELECT
        a.user_id,
        COUNT(*)                                                      AS app_count,
        COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'paid'), 0) AS total_paid
    FROM   applications a
    LEFT JOIN payments p ON p.application_id = a.id
    GROUP BY a.user_id
) s ON u.id = s.user_id
WHERE u.region = 'mazowieckie';

2. Funkcje na kolumnach z indeksem
#

Klasyczny błąd, który całkowicie wyłącza indeks:

-- ŹLE — indeks na submitted_at jest ignorowany, PostgreSQL musi obliczyć DATE() dla każdego wiersza
WHERE DATE(submitted_at) = '2024-01-15'
WHERE EXTRACT(YEAR FROM submitted_at) = 2024
WHERE LOWER(email) = 'jan@example.com'

-- DOBRZE — indeks działa
WHERE submitted_at >= '2024-01-15' AND submitted_at < '2024-01-16'
WHERE submitted_at >= '2024-01-01' AND submitted_at < '2025-01-01'
-- Dla LOWER: stwórz funkcyjny indeks raz
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

PostgreSQL nie może użyć indeksu B-tree, gdy wartość kolumny jest modyfikowana w locie — planista musi przeliczać ją dla każdego z milionów wierszy.

3. SELECT * — pozornie niewinny, realnie kosztowny
#

-- ŹLE
SELECT * FROM applications WHERE user_id = 42;

-- DOBRZE
SELECT id, status, submitted_at FROM applications WHERE user_id = 42;

Trzy powody żeby nigdy nie pisać SELECT * w kodzie produkcyjnym:

Zbędny I/O — pobierasz kolumny, których nigdy nie użyjesz
Covering indexes przestają działać — indeks pokrywający może obsłużyć zapytanie bez dostępu do heap (tabeli). SELECT * zawsze wymaga heap fetch
Pułapka na przyszłość — gdy ktoś doda kolumnę z dużym JSONB, wszystkie zapytania z SELECT * natychmiast spowalniają

4. IN z podzapytaniem vs EXISTS vs JOIN
#

-- WOLNE dla dużych zbiorów — PostgreSQL materializuje cały wynik podzapytania
WHERE user_id IN (SELECT id FROM users WHERE region = 'mazowieckie')

-- SZYBCIEJ — PostgreSQL może zatrzymać skanowanie przy pierwszym trafieniu
WHERE EXISTS (
    SELECT 1 FROM users u 
    WHERE u.id = a.user_id AND u.region = 'mazowieckie'
)

-- NAJCZĘŚCIEJ NAJSZYBCIEJ — planner ma najwięcej swobody w wyborze strategii
JOIN users u ON u.id = a.user_id AND u.region = 'mazowieckie'

IN działa świetnie dla małych, statycznych list (np. status IN ('approved', 'pending')). Dla podzapytań — preferuj EXISTS lub JOIN.

5. DISTINCT jako “plaster” na złe zapytanie
#

DISTINCT prawie zawsze jest sygnałem, że JOIN generuje duplikaty i problem leży głębiej.

-- ŹLE — DISTINCT maskuje problem, a do tego jest drogi
SELECT DISTINCT u.full_name
FROM users u
JOIN applications a ON a.user_id = u.id
WHERE a.status = 'approved';

-- DOBRZE — EXISTS sprawdza warunek bez generowania duplikatów
SELECT u.full_name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM applications a 
    WHERE a.user_id = u.id AND a.status = 'approved'
);

Indeksy — kiedy pomagają, kiedy nie
#

Composite indexes — kolejność kolumn jest nieprzypadkowa
#

-- Ten indeks obsługuje zapytania:
--   WHERE status = 'approved'                              ✓
--   WHERE status = 'approved' AND submitted_at > '2024-01' ✓
--   WHERE submitted_at > '2024-01'                         ✗ (nie używa indeksu!)
CREATE INDEX idx_apps_status_date ON applications (status, submitted_at);

Zasada: kolumny z warunkiem równości (=) idą pierwsze, potem kolumny z zakresem (>, <, BETWEEN). PostgreSQL może przejść do konkretnej pozycji w indeksie tylko jeśli kolumny po lewej są dokładnie dopasowane.

Partial indexes — potężne i niedoceniane
#

Jeśli zapytania zawsze filtrują po tym samym stałym warunku, stwórz indeks tylko na tej podgrupie:

-- Zamiast indeksować wszystkie 2M wierszy...
CREATE INDEX idx_apps_status ON applications (status);

-- ...indeksuj tylko te, na których faktycznie pracujesz:
CREATE INDEX idx_apps_approved ON applications (submitted_at, user_id)
    WHERE status = 'approved';

Korzyści: ułamek rozmiaru pełnego indeksu, szybsze INSERT/UPDATE, szybsze zapytania na tej podgrupie.

Kiedy indeks NIE jest używany
#

Funkcja na kolumnie — WHERE DATE(submitted_at) = ... (omówione wyżej)
Mała tabela — pełny skan może być szybszy niż indeks + heap fetch
Niska selektywność — jeśli 80% wierszy ma status = 'active', indeks nie pomoże
Nieaktualne statystyki — uruchom ANALYZE tabela; przed diagnostyką
Zbyt mały work_mem — sort/hash join spada na dysk zamiast użyć indeksu

Indeksy, które nie są używane, możesz znaleźć w pg_stat_user_indexes. Nieużywane indeksy kosztują każdy INSERT i UPDATE — regularnie sprawdzaj i usuwaj zbędne.

Duży przykład: raport z 8 sekund do 120 ms
#

Kontekst
#

Platforma świadczeń. Potrzebujemy miesięcznego raportu: użytkownicy, którzy złożyli zatwierdzone wnioski w bieżącym miesiącu, z łączną kwotą zrealizowanych wypłat.

-- Schemat (uproszczony)
-- users:        ~500 000 wierszy
-- applications: ~2 000 000 wierszy  (status: 'pending' | 'approved' | 'rejected')
-- payments:     ~1 500 000 wierszy  (status: 'pending' | 'paid' | 'failed')

Zapytanie — wersja “jakoś działa”
#

SELECT DISTINCT
    u.full_name,
    u.region,
    (SELECT COUNT(*)
     FROM   applications a2
     WHERE  a2.user_id = u.id) AS total_applications,
    (SELECT SUM(p.amount)
     FROM   payments p
     JOIN   applications a3 ON p.application_id = a3.id
     WHERE  a3.user_id = u.id
       AND  p.status = 'paid') AS total_paid
FROM users u
WHERE u.id IN (
    SELECT user_id
    FROM   applications
    WHERE  status = 'approved'
      AND  submitted_at::date >= DATE_TRUNC('month', NOW())::date
)
ORDER BY total_paid DESC NULLS LAST;

Czas wykonania na produkcji: ~8 200 ms

EXPLAIN ANALYZE — diagnostyka
#

Sort  (cost=289451.23..289651.23 rows=80000 width=128)
      (actual time=8092.34..8099.78 rows=18432 loops=1)
  Sort Method: external merge  Disk: 2840kB          ← ① sort spada na dysk
  ->  HashAggregate  [DISTINCT]
        ->  Hash Semi Join
              ->  Seq Scan on users u                 ← ② skan 500k wierszy
                    (actual time=0.12..48.34 rows=500000 loops=1)
              ->  Hash
                    ->  Seq Scan on applications      ← ③ skan 2M wierszy
                          Filter: ((submitted_at)::date >= ...)
                          Rows Removed by Filter: 1902766  ← ④ 95% danych wyrzucone
                          (actual time=0.23..245.12 rows=97234 loops=1)
        SubPlan 1                                     ← ⑤ wykonuje się 18 432 razy
          ->  Aggregate on applications a2
                (actual time=0.289 rows=1 loops=18432)
        SubPlan 2                                     ← ⑥ wykonuje się 18 432 razy
          ->  Hash Join on payments + applications
                (actual time=0.412 rows=1 loops=18432)

Planning Time: 3.456 ms
Execution Time: 8243.678 ms

Analiza problemów
#

#	Problem	Skutek
①	Sort spada na dysk (`external merge`)	Operacja I/O zamiast RAM
②	`Seq Scan on users` — 500k wierszy	Brak filtrowania przed JOINem
③	`Seq Scan on applications` — 2M wierszy	`submitted_at::date` blokuje indeks
④	1 902 766 wierszy odfiltrowanych	95% pracy wykonanej na marne
⑤⑥	SubPlany wykonują się ×18 432	Korelowane podzapytania = N+1 w SQL

Refaktoryzacja
#

Krok 1 — napraw warunek daty (odblokuje indeks):

-- Przed
submitted_at::date >= DATE_TRUNC('month', NOW())::date

-- Po
submitted_at >= DATE_TRUNC('month', NOW())
AND submitted_at <  DATE_TRUNC('month', NOW()) + INTERVAL '1 month'

Krok 2 — dodaj właściwe indeksy:

-- Partial index — indeksuje tylko zatwierdzone wnioski (ułamek pełnej tabeli)
CREATE INDEX CONCURRENTLY idx_apps_approved_submitted
    ON applications (submitted_at, user_id)
    WHERE status = 'approved';

-- Covering index dla płatności — obsługuje JOIN bez dostępu do heap
CREATE INDEX CONCURRENTLY idx_payments_app_status_amount
    ON payments (application_id, status, amount);

Krok 3 — przepisz zapytanie:

WITH monthly_approved AS (
    -- Szybki skan przez partial index, zamiast filtrowania 2M wierszy
    SELECT DISTINCT user_id
    FROM   applications
    WHERE  status       = 'approved'
      AND  submitted_at >= DATE_TRUNC('month', NOW())
      AND  submitted_at <  DATE_TRUNC('month', NOW()) + INTERVAL '1 month'
),
user_stats AS (
    -- Jedno przejście przez dane zamiast N korelowanych podzapytań
    SELECT
        a.user_id,
        COUNT(*)                                                      AS total_applications,
        COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'paid'), 0) AS total_paid
    FROM   applications a
    LEFT JOIN payments p ON p.application_id = a.id
    WHERE  a.user_id IN (SELECT user_id FROM monthly_approved)
    GROUP BY a.user_id
)
SELECT
    u.full_name,
    u.region,
    us.total_applications,
    us.total_paid
FROM   users u
JOIN   user_stats us ON u.id = us.user_id
ORDER BY us.total_paid DESC NULLS LAST;

EXPLAIN po optymalizacji
#

Sort  (cost=12834.56..12884.56 rows=20000 width=128)
      (actual time=118.34..119.56 rows=18432 loops=1)
  Sort Method: quicksort  Memory: 2340kB             ← sort w RAM
  ->  Hash Join
        ->  Seq Scan on users u
              (actual time=0.12..34.56 rows=500000 loops=1)
        ->  HashAggregate  [user_stats]
              ->  Hash Join
                    ->  Index Scan using idx_apps_approved_submitted  ← partial index
                          Index Cond: (submitted_at >= ... AND ...)
                          (actual time=0.23..8.45 rows=18432 loops=1)
                    ->  Index Scan using idx_payments_app_status_amount
                          (actual time=0.12..18.34 rows=73456 loops=1)

Planning Time: 2.134 ms
Execution Time: 121.234 ms

8 243 ms → 121 ms — poprawa ×68

Checklist
#

Nigdy nie wrapuj kolumn indeksowanych w funkcje — użyj zakresu
Korelowane podzapytania → JOIN z GROUP BY lub CTE
EXPLAIN (ANALYZE, BUFFERS) zanim cokolwiek zmienisz
SELECT * tylko w REPL — nigdy w kodzie produkcyjnym
Partial indexes dla stałych, często używanych filtrów
W composite index: kolumny z = pierwsze, zakresy na końcu
FILTER (WHERE ...) zamiast CASE w agregacjach — czyściej i szybciej
CREATE INDEX CONCURRENTLY — bez blokowania tabeli na produkcji
Monitoruj pg_stat_user_indexes — nieużywane indeksy kosztują każdy write
ANALYZE po bulk-insertach, jeśli nie chcesz czekać na autovacuum

Dobra optymalizacja to nie hackowanie pod benchmark. To rozumienie, jak planner PostgreSQL myśli — i pisanie kodu, który mu w tym pomaga.

EXPLAIN ANALYZE — zacznij tutaj, zawsze#

Anti-patterny, które zabijają wydajność#

1. Korelowane podzapytania#

2. Funkcje na kolumnach z indeksem#

3. SELECT * — pozornie niewinny, realnie kosztowny#

4. IN z podzapytaniem vs EXISTS vs JOIN#

5. DISTINCT jako “plaster” na złe zapytanie#

Indeksy — kiedy pomagają, kiedy nie#

Composite indexes — kolejność kolumn jest nieprzypadkowa#

Partial indexes — potężne i niedoceniane#

Kiedy indeks NIE jest używany#

Duży przykład: raport z 8 sekund do 120 ms#

Kontekst#

Zapytanie — wersja “jakoś działa”#

EXPLAIN ANALYZE — diagnostyka#

Analiza problemów#

Refaktoryzacja#

EXPLAIN po optymalizacji#

Checklist#