Optymalizacja zapytań PostgreSQL: Od wolnych do submilisekundowych

W zeszłym miesiącu dostałem wiadomość na Slacku o 2 w nocy: "Dashboard się zawiesza." Wbiłem się przez SSH na produkcyjny serwer, otworzyłem pg_stat_activity i znalazłem pojedyncze zapytanie skanujące 14 milionów wierszy, żeby zwrócić 12 wyników. Rozwiązaniem był partial index, którego utworzenie zajęło 30 sekund. Zapytanie spadło z 4,2 sekundy do 0,3 milisekundy.

I o to właśnie chodzi z wydajnością PostgreSQL. Problemy prawie nigdy nie są egzotyczne. To brakujące indeksy, nieaktualne statystyki, zapytania, które miały sens, gdy tabela miała 10 000 wierszy, ale teraz ma ich 10 milionów. Rozwiązania zazwyczaj są proste — kiedy wiesz, gdzie szukać.

Ten wpis to wszystko, czego nauczyłem się o optymalizacji zapytań PostgreSQL, prowadząc produkcyjne bazy danych. Żadnej teorii bez praktyki. Każda technika opisana tutaj zaoszczędziła mi realny czas na prawdziwych systemach.

Mentalność debugowania: Nie zgaduj, mierz#

Największy błąd, jaki widzę u developerów przy wolnych zapytaniach, to zgadywanie. "Może potrzebujemy indeksu na tej kolumnie." "Może JOIN jest wolny." "Może powinniśmy dodać więcej RAM-u."

Przestań zgadywać. PostgreSQL ma jeden z najlepszych analizatorów zapytań spośród wszystkich baz danych. Używaj go.

EXPLAIN — Plan działania#

Zwykły EXPLAIN pokazuje, co PostgreSQL planuje zrobić, bez faktycznego uruchamiania zapytania:

sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

To pokazuje plan — index scan — i szacowany koszt. Ale to prognoza. PostgreSQL może się całkowicie mylić co do liczby wierszy. Widziałem szacunki "1 wiersz", gdy faktyczny wynik to 50 000 wierszy. Taki błąd w szacunkach kaskadowo prowadzi do fatalnych wyborów planu.

Używaj zwykłego EXPLAIN, gdy chcesz szybko zerknąć na plan bez faktycznego uruchamiania zapytania. Ma to znaczenie, gdy analizujesz DELETE, który zmodyfikowałby dane, albo zapytanie, które wykonuje się 30 minut.

EXPLAIN ANALYZE — Prawda#

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Teraz widzisz faktyczny czas wykonania i faktyczną liczbę wierszy. Tutaj zaczyna się prawdziwe debugowanie. Porównaj rows=1 (szacowane) z rows=1 (faktyczne) — zgadzają się, więc planner podjął dobrą decyzję. Kiedy się nie zgadzają, to twoja pierwsza wskazówka.

Uwaga: EXPLAIN ANALYZE faktycznie uruchamia zapytanie. Jeśli analizujesz UPDATE lub DELETE, opakuj je w transakcję:

sql

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Pełny obraz#

To jest to, czego faktycznie używam w 90% przypadków:

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Wynik BUFFERS jest kluczowy. shared hit=312 oznacza, że 312 stron trafiło z buffer cache (RAM). read=45 oznacza, że 45 stron musiało zostać odczytanych z dysku. Jeśli widzisz dużo read w stosunku do hit, twoje shared_buffers mogą być za małe albo tabela jest zbyt duża, żeby zmieścić się w cache.

Używam też FORMAT JSON, gdy potrzebuję wkleić plan do explain.dalibo.com w celu wizualizacji. Widok drzewa sprawia, że złożone plany są znacznie łatwiejsze do odczytania:

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Opcje EXPLAIN, których faktycznie używam#

Oto pełne zapytanie diagnostyczne, które uruchamiam jako pierwsze przy każdym badaniu wolnego zapytania:

sql

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

TIMING — pomiar czasu per węzeł (domyślnie włączony z ANALYZE, ale wolę być jawny)
VERBOSE — pokazuje listy kolumn wyjściowych i nazwy tabel z kwalifikatorem schematu

Czytanie planów zapytań jak detektyw#

Każdy plan zapytania to drzewo. PostgreSQL czyta je od dołu do góry: najgłębiej zagnieżdżone węzły wykonują się jako pierwsze, przekazując wyniki w górę. Zrozumienie typów skanów to fundament wszystkiego innego.

Seq Scan — Pełne skanowanie tabeli#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

Sequential scan czyta każdy wiersz w tabeli. Dla tabeli z milionem wierszy oznacza to każdą stronę na dysku.

Ale jest niuans: Seq Scan nie zawsze jest zły. Jeśli wybierasz 30% lub więcej tabeli, sequential scan jest faktycznie szybszy niż index scan, ponieważ sekwencyjne I/O jest znacznie szybsze niż losowe I/O. PostgreSQL to wie. Jeśli wybiera Seq Scan, gdy masz indeks, sprawdź, jak selektywna jest naprawdę twoja klauzula WHERE.

Kiedy jest problemem: gdy wybierasz niewielki ułamek wierszy z dużej tabeli i nie ma indeksu.

Index Scan — Celowane wyszukiwanie#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Idzie do indeksu B-tree, znajduje pasujące wpisy, a następnie pobiera faktyczne wiersze z heap tabeli. Każde pobranie wiersza to losowa operacja I/O na heap. To świetne dla wysoce selektywnych zapytań, ale każde pobranie z heap ma swój koszt.

Index Only Scan — Święty Graal#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

To najszybszy typ skanu. PostgreSQL pobiera wszystko, czego potrzebuje, wyłącznie z indeksu, nigdy nie dotykając heap tabeli. Widzisz to, gdy wszystkie kolumny, po których robisz SELECT i WHERE, są w indeksie.

Haczyk: Heap Fetches: 0 oznacza, że visibility map jest aktualna. Jeśli twoja tabela ma dużo martwych krotek (dawno nie vacuumowana), PostgreSQL i tak musi sprawdzić heap, żeby zweryfikować widoczność wierszy. To jeden z powodów, dla których autovacuum ma znaczenie dla wydajności, a nie tylko dla miejsca na dysku.

Bitmap Scan — Złoty środek#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap scan to odpowiedź PostgreSQL na problem "zbyt wiele wierszy na index scan, zbyt mało na seq scan". Buduje bitmapę stron zawierających pasujące wiersze, sortuje je według fizycznej lokalizacji, a następnie pobiera je po kolei. To zamienia losowe I/O w sekwencyjne I/O.

Często zobaczysz bitmap scany, gdy łączone są dwa lub więcej indeksów:

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Ten BitmapAnd łączy dwa osobne indeksy. To PostgreSQL mówi "nie mam indeksu złożonego, ale mogę połączyć te dwa indeksy jednokolumnowe." Działa, ale odpowiedni indeks złożony byłby szybszy.

Wykrywanie błędnych szacunków#

Rzecz numer jeden, której szukam w planie zapytania, to niedopasowane szacunki:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

Planner oszacował 1 wiersz. Faktyczny wynik to 48 753 wiersze. To błąd szacunku o pięć rzędów wielkości. Planner wybrał Nested Loop, bo myślał, że joinuje z 1 wierszem. Przy 48 753 wierszach Hash Join byłby o rzędy wielkości szybszy.

Typowe przyczyny błędnych szacunków:

Nieaktualne statystyki: Uruchom ANALYZE na tabeli
Skorelowane kolumny: Planner zakłada, że wartości kolumn są niezależne. Jeśli status = 'shipped' i created_at > '2026-01-01' są skorelowane (większość ostatnich zamówień jest wysłana), planner niedoszacowuje łączną selektywność
Niestandardowe funkcje w WHERE: Planner używa domyślnego szacunku selektywności (zwykle 0,5% dla równości, 33% dla zakresu), gdy nie może przeanalizować funkcji
Sparametryzowane zapytania z generic plans: Po 5 wykonaniach PostgreSQL może przejść na generyczny plan, który nie uwzględnia faktycznej wartości parametru

Gdy widzisz błędne szacunki, rozwiązanie to zazwyczaj jedno z: uruchom ANALYZE, utwórz rozszerzone statystyki, przepisz zapytanie albo użyj CTE jako bariery optymalizacji.

sql

-- Create extended statistics for correlated columns
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Strategia indeksów: Właściwy indeks do właściwego zadania#

Indeksy nie są darmowe. Każdy indeks spowalnia zapisy, zajmuje miejsce na dysku i wymaga konserwacji. Celem nie jest "indeksuj wszystko" — tylko "indeksuj dokładnie to, czego potrzebujesz."

B-tree — Domyślny koń roboczy#

B-tree to domyślny typ i obsługuje zdecydowaną większość przypadków. Wspiera operatory równości i zakresu (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

sql

CREATE INDEX idx_orders_created_at ON orders (created_at);

Kolejność kolumn w B-tree ma ogromne znaczenie dla indeksów złożonych. Najbardziej lewa kolumna to główne sortowanie, potem kolejna i tak dalej. Indeks na (a, b, c) może efektywnie obsłużyć:

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 5
WHERE a = 1 ORDER BY b

Nie może efektywnie obsłużyć:

WHERE b = 2 (pomija pierwszą kolumnę)
WHERE c = 3 (pomija dwie pierwsze kolumny)
WHERE a = 1 AND c = 3 (luka w środku — warunek a użyty, warunek c wymaga filtrowania)

Pomyśl o tym jak o książce telefonicznej posortowanej po nazwisku, potem po imieniu. Szybko znajdziesz wszystkie wpisy "Kowalski" albo konkretnie "Kowalski, Jan". Ale nie znajdziesz szybko wszystkich wpisów "Jan" bez przeszukania całej książki.

Zasada: najpierw warunki równości, potem warunki zakresu, potem kolumny sortowania.

sql

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Good: equality first, then range, then sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Bad: range before equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Covering Indexes z INCLUDE#

PostgreSQL 11+ pozwala dodawać kolumny inne niż klucz do indeksu za pomocą INCLUDE. Te kolumny są przechowywane na stronach liści, ale nie są częścią struktury B-tree. Umożliwiają index-only scany bez rozdmuchiwania drzewa:

sql

-- We frequently run:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Teraz PostgreSQL może obsłużyć całe zapytanie wyłącznie z indeksu. Bez INCLUDE musiałby pobierać total i status z heap. Różnica na zimnym cache może być dramatyczna — widziałem zapytania spadające z 50ms do 0,2ms tylko przez dodanie kolumn INCLUDE.

Partial Indexes — Indeksuj tylko to, co się liczy#

To moja absolutnie ulubiona funkcja PostgreSQL i ta, o której większość developerów nie wie.

sql

-- Only 2% of orders are 'pending', but we query them constantly
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Ten indeks jest malutki w porównaniu z pełnym indeksem na created_at. Zawiera tylko wiersze, gdzie status = 'pending'. Zapytania pasujące do klauzuli WHERE korzystają z tego małego, szybkiego indeksu. Zapytania, które nie pasują, ignorują go.

Prawdziwy przykład z produkcji: miałem tabelę sessions z 50 milionami wierszy. Tylko około 200 000 było aktywnych (niewygasłych). Pełny indeks na user_id miał 1,2 GB. Partial index WHERE expires_at > now() miał 8 MB. Zapytanie spadło z 12ms do 0,1ms, ponieważ cały indeks mieścił się w cache.

sql

-- Another common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique constraint only on active records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Ten ostatni jest niesamowicie przydatny. Pozwala mieć unikalny constraint na emailu dla aktywnych użytkowników, jednocześnie dopuszczając ten sam email w usuniętych rekordach.

Indeksy GIN — Pełnotekstowe wyszukiwanie i JSONB#

GIN (Generalized Inverted Index) to odpowiedź, gdy musisz wyszukiwać wewnątrz wartości — tablic, dokumentów JSONB albo wektorów pełnotekstowego wyszukiwania.

sql

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Now this is fast:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

Indeksy GIN są duże i wolne w budowie, ale błyskawicznie szybkie w zapytaniach. Opłacają się dla obciążeń z dużą ilością odczytów i złożonymi zapytaniami o zawieranie.

Dla JSONB istnieje też jsonb_path_ops, który jest mniejszy i szybszy dla zapytań o zawieranie @>, ale nie obsługuje operatorów istnienia (?, ?|, ?&):

sql

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

Indeksy GiST — Typy geometryczne i zakresowe#

GiST (Generalized Search Tree) obsługuje nakładające się typy danych: kształty geometryczne, zakresy, pełnotekstowe wyszukiwanie (alternatywa dla GIN, mniejszy, ale wolniejszy).

sql

-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Find overlapping bookings:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Indeksy Hash — Niszowe, ale przydatne#

Indeksy hash są przydatne wyłącznie do porównań równościowych. Od PostgreSQL 10 są logowane w WAL i bezpieczne po awarii. Są mniejsze niż B-tree dla szerokich kolumn i nieco szybsze dla czystej równości:

sql

-- Good for equality-only lookups on large text columns
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- This is fast:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- This CANNOT use the hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

W praktyce rzadko używam indeksów hash. B-tree radzi sobie z równością dobrze, a niemożność wykonywania zapytań zakresowych lub sortowania sprawia, że indeksy hash są zbyt mało elastyczne dla większości rzeczywistych przypadków użycia.

Kiedy indeksy szkodzą#

Każdy indeks ma swój koszt:

Wzmocnienie zapisów: Każdy INSERT aktualizuje każdy indeks na tabeli. Tabela z 8 indeksami oznacza 8 dodatkowych zapisów na INSERT
Blokowanie HOT updates: Heap-Only Tuple (HOT) updates to istotna optymalizacja, w której PostgreSQL może zaktualizować wiersz bez aktualizacji indeksów, ale tylko jeśli żadna indeksowana kolumna się nie zmieniła. Więcej indeksów = więcej szans na blokowanie HOT updates
Obciążenie vacuum: Więcej indeksów oznacza, że vacuum trwa dłużej
Obciążenie plannera: Więcej indeksów oznacza, że planner ma więcej opcji do ewaluacji

Regularnie audytuję indeksy na produkcyjnych tabelach:

sql

-- Find unused indexes
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- exclude primary key and unique constraints
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Znajdowałem wielogigabajtowe indeksy, które zostały utworzone podczas jednorazowej migracji i nigdy więcej nie zostały użyte. Ich usunięcie zauważalnie przyspieszyło zapisy.

sql

-- Find duplicate indexes (same columns, same order)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

Problem zapytań N+1#

Każdy developer korzystający z ORM w końcu na to trafia. To najczęstszy problem wydajnościowy, który debuguję.

Jak wygląda N+1#

python

# Python / SQLAlchemy — the classic trap
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # This triggers a separate query for EACH order
    print(f"Customer: {order.customer.name}")

To generuje:

sql

-- Query 1: Get all pending orders
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Get customer for order 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Get customer for order 500
SELECT * FROM customers WHERE id = 600;

501 zapytań zamiast 1 lub 2. Każde zapytanie jest szybkie indywidualnie, może 0,5ms. Ale 501 z nich sumuje się do 250ms samego czasu bazy danych, plus opóźnienie sieciowe round-trip dla każdego z nich.

Wykrywanie N+1 w logach#

Najszybszy sposób na wyłapanie zapytań N+1 to tymczasowe włączenie logowania zapytań:

sql

-- Log all queries that take more than 0ms (i.e., all queries)
SET log_min_duration_statement = 0;
 
-- Better for production: log only slow queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms threshold
SELECT pg_reload_conf();

Potem przejrzyj logi. N+1 jest nie do pomylenia — zobaczysz setki identycznych zapytań z różnymi wartościami parametrów w szybkiej sekwencji.

Bardziej ukierunkowane podejście na środowisku deweloperskim:

sql

-- Enable auto_explain for slow queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

To loguje pełny wynik EXPLAIN ANALYZE dla każdego zapytania powyżej 100ms, w tym zapytań wewnątrz funkcji.

Rozwiązanie: Eager Loading lub JOINy#

Podejście ORM — powiedz ORM-owi, żeby załadował powiązane dane z góry:

python

# SQLAlchemy — joinedload fetches customers in the same query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Podejście z czystym SQL — po prostu użyj JOINa:

sql

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Jedno zapytanie. Gotowe.

JOINy vs osobne zapytania#

Tu jest uzasadniona dyskusja. Czasem dwa zapytania są lepsze niż JOIN:

sql

-- Approach 1: Single JOIN (might produce duplicate data if one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approach 2: Two queries (less data transfer if orders have many columns)
SELECT * FROM orders WHERE status = 'pending';
-- application collects order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

Podejście z JOINem duplikuje dane zamówienia dla każdego elementu. Jeśli każde zamówienie ma 20 elementów, a wiersz zamówienia jest szeroki, to 20-krotny transfer danych. Podejście z dwoma zapytaniami wysyła każde zamówienie dokładnie raz.

Moja zasada: używaj JOINów dla relacji jeden-do-jednego, rozważ osobne zapytania dla jeden-do-wielu, gdy strona "jeden" jest szeroka. Ale zawsze benchmarkuj — opóźnienie sieciowe round-trip drugiego zapytania często kosztuje więcej niż zduplikowane dane.

Typowe przepisywanie zapytań#

Niektóre zapytania są wolne nie z powodu brakujących indeksów, ale z powodu tego, jak są napisane. Planner PostgreSQL jest dobry, ale to nie magia.

Subquery vs JOIN vs CTE#

Te trzy podejścia mogą generować bardzo różne plany:

sql

-- Subquery in WHERE — often fine, sometimes terrible
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — usually the best choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialized in PG 11 and below, optimized in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

W PostgreSQL 12+ CTE są zazwyczaj inlinowane (planner traktuje je jak subqueries), więc wydajność jest identyczna. Ale w PostgreSQL 11 i niższych CTE to bariery optymalizacji — planner je materializuje i nie może przepchać predykatów przez nie. Jeśli wciąż jesteś na PG 11, unikaj CTE w zapytaniach krytycznych wydajnościowo.

EXISTS vs IN vs JOIN#

To pojawia się ciągle:

sql

-- EXISTS — stops at first match, great for checking existence
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN with subquery — builds full result set first
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — can produce duplicates if returns has multiple rows per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Dla dużych tabel EXISTS często wygrywa, ponieważ przerywa wcześnie. Wersja z IN musi zbudować całą listę order_id ze zwrotów przed filtrowaniem. Wersja z JOIN może produkować duplikaty, wymagając DISTINCT, co dodaje krok sortowania lub hashowania.

Moje domyślne podejście: używaj EXISTS do sprawdzania istnienia powiązanych wierszy. Jest najbardziej jasne semantycznie i zazwyczaj najszybsze.

Ale jest kontrprzykład. Jeśli zbiór wyników subquery jest mały i potrzebujesz go do wielu warunków:

sql

-- If the subquery returns few rows, IN is perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

Unikanie SELECT *#

To nie jest tylko kwestia stylu kodu. Ma realne implikacje wydajnościowe:

sql

-- Bad: fetches all 25 columns including a TEXT column with kilobytes of data
SELECT * FROM products WHERE category = 'electronics';
 
-- Good: fetches only what you need
SELECT id, name, price FROM products WHERE category = 'electronics';

Z SELECT *:

Nie można użyć index-only scans (wszystkie kolumny musiałyby być w indeksie)
Transferujesz więcej danych przez sieć
Używasz więcej pamięci na sortowanie i hashowanie
Jeśli ktoś później doda kolumnę 10MB BYTEA, twoje istniejące zapytania po cichu stają się wolniejsze

Window Functions vs Subqueries#

Window functions to jedna z najpotężniejszych funkcji PostgreSQL i prawie zawsze przewyższają skorelowane subqueries:

sql

-- Slow: correlated subquery runs once per row
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Fast: window functions compute in a single pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Inny częsty wzorzec — pobieranie najnowszego wiersza per grupa:

sql

-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Fast: DISTINCT ON (PostgreSQL-specific, extremely useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Also fast: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON to moje ulubione podejście do tego wzorca. Jest zwięzłe, czytelne, a PostgreSQL dobrze je optymalizuje z właściwym indeksem:

sql

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Paginacja zrobiona dobrze#

OFFSET to pułapka dla dużych zbiorów danych:

sql

-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Page 500: slow — PostgreSQL must scan and discard 9,980 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Page 5000: very slow — scans 99,980 rows to return 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

Rozwiązaniem jest paginacja kluczowa (keyset pagination, zwana też cursor-based pagination):

sql

-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Next page: use the last id from the previous page
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

To jest zawsze szybkie, ponieważ używa indeksu, żeby przeskoczyć bezpośrednio na właściwą pozycję, niezależnie od tego, na której "stronie" jesteś. Kompromis polega na tym, że nie możesz przeskoczyć do dowolnego numeru strony, ale dla nieskończonego scrolla lub interfejsów "następna strona" paginacja kluczowa jest zdecydowanie lepsza.

Dla złożonych kolejności sortowania:

sql

-- Keyset pagination with multiple sort columns
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

To wykorzystuje porównanie wartości wierszowych (row-value comparison), które PostgreSQL obsługuje efektywnie z indeksem złożonym na (price, id).

Statystyki tabel i vacuuming#

Planner zapytań PostgreSQL podejmuje decyzje na podstawie statystyk o twoich danych. Złe statystyki prowadzą do złych planów. To takie proste.

ANALYZE: Aktualizacja statystyk#

sql

-- Analyze a single table
ANALYZE orders;
 
-- Analyze the entire database
ANALYZE;
 
-- See current statistics for a column
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Wartość correlation jest szczególnie interesująca. Mieści się w zakresie od -1 do 1 i mierzy, jak dobrze fizyczna kolejność wierszy odpowiada logicznej kolejności kolumny. Korelacja bliska 1 lub -1 oznacza, że dane są fizycznie posortowane po tej kolumnie, co sprawia, że skany zakresowe są bardzo efektywne (sekwencyjne I/O). Korelacja bliska 0 oznacza losowe I/O dla zapytań zakresowych.

sql

-- Increase statistics target for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Domyślny cel statystyczny to 100, co oznacza, że PostgreSQL próbkuje 300 * 100 = 30 000 wierszy. Dla kolumn z wieloma unikalnymi wartościami lub nierównym rozkładem zwiększenie tego do 500 lub 1000 daje plannerowi lepsze dane kosztem nieco dłuższego czasu ANALYZE.

Strojenie autovacuum#

Autovacuum robi dwie rzeczy: odzyskuje miejsce po martwych krotkach (usunięte lub zaktualizowane wiersze) i aktualizuje statystyki. Na obciążonych tabelach domyślne ustawienia autovacuum są często zbyt zachowawcze.

sql

-- Check autovacuum status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Jeśli widzisz tabele z milionami martwych krotek, a ostatni vacuum był godziny temu, twój autovacuum nie nadąża.

Dla tabel z dużym churnem (jak sesje, kolejki zadań czy metryki) ustawiam per-tabelowe ustawienia autovacuum:

sql

-- More aggressive autovacuum for high-write tables
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze at 0.5% changes (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- faster vacuum (default 2ms in newer PG)
);

Rozdęcie tabel (Table Bloat)#

Kiedy PostgreSQL aktualizuje wiersz, nie modyfikuje go w miejscu — tworzy nową wersję i oznacza starą jako martwą. Vacuum odzyskuje martwe wiersze, ale przestrzeń jest ponownie wykorzystywana tylko przez tę tabelę. Plik tabeli na dysku się nie zmniejsza.

Z czasem intensywnie aktualizowana tabela może mieć znaczące rozdęcie — tabela jest znacznie większa na dysku niż wymagają tego żywe dane. To oznacza więcej stron do skanowania, więcej I/O, większą presję na cache.

sql

-- Estimate table bloat (simplified version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Dla poważnie rozdętych tabel opcja nuklearna to VACUUM FULL — przepisuje całą tabelę. Ale wymaga wyłącznej blokady, więc nie można tego zrobić na żywej produkcyjnej tabeli bez downtime'u. Lepszym podejściem jest pg_repack, który robi to samo bez blokowania:

bash

pg_repack --table orders --no-kill-backend -d mydb

Connection Pooling#

Oto coś, co zaskakuje wielu developerów: połączenia PostgreSQL są kosztowne. Każde połączenie tworzy nowy proces (nie wątek), zużywa około 5-10 MB pamięci i ma nietrywialne obciążenie forka.

Domyślne max_connections to 100. Jeśli masz serwer aplikacji z 20 workerami, z których każdy otwiera 5 połączeń, już jesteś na limicie. Dodaj procesor zadań w tle, narzędzie monitoringu i migrację działającą gdzieś, i masz problem.

Dlaczego potrzebujesz poolera połączeń#

Bez poolera, jeśli twoja aplikacja musi obsługiwać 500 jednoczesnych żądań, potrzebujesz 500 połączeń PostgreSQL. To 5 GB pamięci tylko na narzut połączeń, a wydajność PostgreSQL znacząco spada powyżej kilkuset połączeń z powodu narzutu zarządzania procesami.

Z PgBouncerem przed PostgreSQL te 500 połączeń aplikacji mapuje się na może 20 faktycznych połączeń PostgreSQL. Pooler kolejkuje żądania, gdy wszystkie połączenia bazodanowe są zajęte.

Konfiguracja PgBouncer#

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Tryb Transaction vs Session#

Tryb transaction (pool_mode = transaction): Połączenia są zwracane do puli po każdej transakcji. To jest to, czego chcesz w 95% przypadków. Maksymalne ponowne wykorzystanie połączeń.
Tryb session (pool_mode = session): Połączenia są trzymane przez całą sesję klienta. Użyj tego, jeśli potrzebujesz prepared statements, komend SET, LISTEN/NOTIFY lub innych funkcji na poziomie sesji.
Tryb statement (pool_mode = statement): Połączenia są zwracane po każdym zapytaniu. Zbyt restrykcyjne dla większości aplikacji — nie możesz nawet używać jawnych transakcji.

Haczyk z trybem transaction: nie możesz używać prepared statements (to stan na poziomie sesji), nie możesz używać SET do zmiennych sesyjnych, a LISTEN/NOTIFY nie będzie działać zgodnie z oczekiwaniami. Większość ORM-ów ma sposób na wyłączenie prepared statements.

Dla Node.js z driverem pg:

javascript

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // PgBouncer port, not PostgreSQL port
  database: 'mydb',
  max: 20,     // matches PgBouncer default_pool_size
  statement_timeout: 30000,
  // Disable prepared statements for PgBouncer transaction mode
  prepare: false,
});

Wzór na rozmiar puli#

Jest powszechny wzór na optymalny rozmiar puli połączeń PostgreSQL:

optimal_connections = (core_count * 2) + effective_spindle_count

Dla nowoczesnego serwera z 4 rdzeniami i SSD (1 efektywny spindle):

optimal = (4 * 2) + 1 = 9

To wydaje się kontraintuicyjnie niskie. Ale PostgreSQL jest ograniczony przez CPU na większości nowoczesnego sprzętu (SSD są na tyle szybkie, że I/O rzadko jest wąskim gardłem). Więcej połączeń niż to prowadzi do narzutu przełączania kontekstu, który faktycznie spowalnia działanie.

W praktyce zazwyczaj ustawiam default_pool_size na 2-3x tę liczbę, żeby obsłużyć skokowy ruch, rozumiejąc, że przy szczytowym obciążeniu część zapytań będzie czekać w kolejce PgBouncera zamiast jednocześnie uderzać w PostgreSQL.

Praktyczna checklista: Dokładne kroki dla każdego wolnego zapytania#

Oto mój faktyczny proces, gdy dostaję raport "to zapytanie jest wolne". Wykonuję te kroki w tej kolejności, za każdym razem.

Krok 1: Zdobądź faktyczne zapytanie#

Nie "endpoint jest wolny" — faktyczny SQL. Jeśli używasz ORM-a, włącz logowanie zapytań:

sql

-- Temporarily log all queries over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Albo sprawdź pg_stat_statements dla najcięższych zapytań według łącznego czasu:

sql

-- Top 10 queries by total execution time
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements to najcenniejsze rozszerzenie dla wydajności PostgreSQL. Jeśli go nie uruchamiasz, zainstaluj je teraz:

sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Krok 2: Uruchom EXPLAIN (ANALYZE, BUFFERS)#

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <the slow query>;

Szukaj:

Błędnych szacunków wierszy — faktyczna liczba wierszy bardzo różni się od szacowanej
Seq Scanów na dużych tabelach — potencjalnie brakujący indeks
Nested Loops z wieloma wierszami — powinien być Hash Join lub Merge Join
Dużych buffer reads — zimny cache lub zbyt duża tabela
Operacji sortowania rozlewających się na dysk — zwiększ work_mem lub dodaj indeks do sortowania

Krok 3: Sprawdź statystyki tabeli#

sql

-- When were stats last updated?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Jeśli last_analyze jest stare lub n_dead_tup jest wysoki w stosunku do n_live_tup, uruchom:

sql

ANALYZE orders;

Potem ponownie uruchom EXPLAIN ANALYZE. Jeśli plan się zmienił, problemem były nieaktualne statystyki.

Krok 4: Sprawdź istniejące indeksy#

sql

-- What indexes exist on this table?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Może indeks istnieje, ale nie jest używany. Może kolejność kolumn jest niewłaściwa dla twojego zapytania.

Krok 5: Utwórz lub zmodyfikuj indeksy#

Na podstawie planu zapytania utwórz odpowiedni indeks. Testuj z EXPLAIN ANALYZE przed i po.

sql

-- Create the index concurrently (doesn't lock the table)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verify the index is used
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;

Zawsze używaj CONCURRENTLY na produkcyjnych tabelach. Zwykłe CREATE INDEX zakłada pełną blokadę tabeli, która blokuje wszystkie zapisy.

Krok 6: Rozważ przepisanie zapytania#

Jeśli indeks istnieje i statystyki są aktualne, ale zapytanie wciąż jest wolne, przyjrzyj się samemu zapytaniu:

Czy subquery można przepisać jako JOIN?
Czy OFFSET powoduje problemy? Przejdź na paginację kluczową
Czy wybierasz więcej kolumn niż potrzebujesz?
Czy skorelowany subquery można zamienić na window function?
Czy CTE uniemożliwia plannerowi optymalizację?

Krok 7: Sprawdź ustawienia serwera#

Dla konkretnych wzorców zapytań ustawienia serwera mają znaczenie:

sql

-- work_mem: memory for sorts and hash joins (per operation, not per connection)
-- Default is 4MB, which is too low for complex queries
SET work_mem = '64MB';  -- try this and re-run EXPLAIN ANALYZE
 
-- effective_cache_size: tells planner how much disk cache to expect
-- Set to ~75% of total RAM
SHOW effective_cache_size;
 
-- random_page_cost: ratio of random I/O to sequential I/O
-- Default is 4.0, set to 1.1-1.5 for SSDs
SHOW random_page_cost;

Jeśli zmiana work_mem naprawia problem sortowania na dysk, rozważ zwiększenie go globalnie. Ale bądź ostrożny — to per operacja, nie per połączenie. Złożone zapytanie z 10 operacjami sortowania i work_mem = 256MB może użyć 2,5 GB dla pojedynczego zapytania.

Krok 8: Monitoruj po naprawie#

Nie naprawiaj i nie zapominaj. Sprawdź, czy naprawa się utrzymuje:

sql

-- Reset pg_stat_statements to see fresh data after your changes
SELECT pg_stat_statements_reset();
 
-- Check back in an hour/day
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Bonus: Szybkie wygrane, które stosuję na każdej nowej bazie#

To są ustawienia i praktyki, które stosuję na każdej bazie danych PostgreSQL, którą konfiguruję, zanim pojawią się jakiekolwiek problemy wydajnościowe:

sql

-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Enable slow query logging (50ms is my threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Increase work_mem from the anemic default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Set effective_cache_size to 75% of RAM
-- For a 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Set shared_buffers to 25% of RAM
-- For a 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Improve autovacuum responsiveness
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Apply changes
SELECT pg_reload_conf();
-- Note: shared_buffers requires a restart

I zapytanie monitorujące, które uruchamiam co tydzień:

sql

-- Tables that need attention
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Podsumowanie#

Optymalizacja zapytań PostgreSQL to nie czarna magia. To systematyczny proces:

Mierz — nie zgaduj. EXPLAIN (ANALYZE, BUFFERS) to twój najlepszy przyjaciel.
Rozumiej plan — naucz się czytać typy skanów, typy joinów i szacunki wierszy.
Indeksuj strategicznie — właściwy indeks na właściwych kolumnach, we właściwej kolejności. Partial indexes i covering indexes to niedoceniane supermoce.
Pisz lepsze zapytania — EXISTS zamiast IN do sprawdzania istnienia, paginacja kluczowa zamiast OFFSET, window functions zamiast skorelowanych subqueries.
Utrzymuj bazę danych — strojenie autovacuum, aktualizacja statystyk, connection pooling.
Monitoruj ciągle — pg_stat_statements mówi ci, na co twoja baza danych wydaje czas. Sprawdzaj regularnie.

Różnica między zapytaniem trwającym 4 sekundy a takim trwającym 0,3 milisekundy to rzadko sprzęt. To prawie zawsze wiedza — wiedzieć, gdzie szukać i co zmienić. A teraz wiesz, gdzie szukać.