Optimalizace PostgreSQL dotazů: Od pomalých po sub-milisekundové

Minulý měsíc jsem ve 2 ráno dostal zprávu na Slacku: „Dashboard timeoutuje." Připojil jsem se přes SSH na produkční server, otevřel pg_stat_activity a našel jediný dotaz skenující 14 milionů řádků, aby vrátil 12 výsledků. Oprava byl parciální index, jehož vytvoření trvalo 30 sekund. Dotaz přešel ze 4,2 sekund na 0,3 milisekundy.

To je ta věc s výkonem PostgreSQL. Problémy téměř nikdy nejsou exotické. Jsou to chybějící indexy, špatné statistiky, dotazy, které dávaly smysl, když tabulka měla 10 000 řádků, ale teď jich má 10 milionů. Opravy jsou obvykle jednoduché — jakmile víte, kde hledat.

Tento příspěvek je vše, co jsem se naučil o optimalizaci PostgreSQL dotazů z provozování produkčních databází. Žádná teorie bez praxe. Každá technika zde mi ušetřila reálný čas na reálných systémech.

Mentalita ladění: Nehádejte, měřte#

Největší chyba, kterou vidím vývojáře dělat s pomalými dotazy, je hádání. „Možná potřebujeme index na tom sloupci." „Možná je ten JOIN pomalý." „Možná bychom měli přidat více RAM."

Přestaňte hádat. PostgreSQL má jeden z nejlepších analyzátorů dotazů ze všech databází. Používejte ho.

EXPLAIN — Plán#

Prostý EXPLAIN vám ukáže, co PostgreSQL plánuje udělat, aniž by dotaz skutečně spustil:

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 vám řekne plán — sken indexu — a odhadovaný náklad. Ale je to predikce. PostgreSQL se může v počtu řádků naprosto mýlit. Viděl jsem odhady „1 řádek", když skutečný výsledek byl 50 000 řádků. Takový špatný odhad se kaskádovitě promítne do hrozných voleb plánu.

Prostý EXPLAIN použijte, když chcete rychlý pohled na plán bez skutečného spuštění dotazu. To je důležité, když analyzujete DELETE, který by modifikoval data, nebo dotaz, jehož spuštění trvá 30 minut.

EXPLAIN ANALYZE — Pravda#

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

Nyní vidíte skutečný čas provádění a skutečný počet řádků. Tady se děje skutečné ladění. Porovnejte rows=1 (odhadovaný) s rows=1 (skutečný) — shodují se, takže plánovač udělal dobré rozhodnutí. Když se neshodují, to je vaše první vodítko.

Varování: EXPLAIN ANALYZE dotaz skutečně spustí. Pokud analyzujete UPDATE nebo DELETE, zabalte ho do transakce:

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

EXPLAIN (ANALYZE, BUFFERS) — Úplný obrázek#

Toto skutečně používám 90 % času:

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

Výstup BUFFERS je kritický. shared hit=312 znamená, že 312 stránek přišlo z buffer cache (RAM). read=45 znamená, že 45 stránek muselo být přečteno z disku. Pokud vidíte hodně read vzhledem k hit, vaše shared_buffers mohou být příliš malé, nebo je tabulka příliš velká, aby zůstala v cache.

Také používám FORMAT JSON, když potřebuji vložit plán do explain.dalibo.com pro vizualizaci. Stromové zobrazení dělá složité plány mnohem lépe čitelné:

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

Možnosti EXPLAIN, které skutečně používám#

Zde je kompletní diagnostický dotaz, který spouštím jako první při jakémkoli vyšetřování pomalého dotazu:

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

• TIMING — časování per-node (ve výchozím nastavení zapnuto s ANALYZE, ale jsem explicitní)
• VERBOSE — zobrazí seznamy výstupních sloupců a kvalifikovaná jména tabulek se schématem

Čtení plánů dotazů jako detektiv#

Každý plán dotazu je strom. PostgreSQL ho čte zdola nahoru: nejhlouběji odsazené uzly se provedou jako první, předávajíce výsledky nahoru. Pochopení typů skenů je základ všeho ostatního.

Seq Scan — Úplný sken tabulky#

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

Sekvenční sken čte každý jednotlivý řádek v tabulce. Pro milionovou tabulku to znamená každou stránku na disku.

Ale zde je nuance: Seq Scan není vždy špatný. Pokud vybíráte 30 % nebo více tabulky, sekvenční sken je ve skutečnosti rychlejší než sken indexu, protože sekvenční I/O je mnohem rychlejší než náhodné I/O. PostgreSQL to ví. Pokud volí Seq Scan, i když máte index, zkontrolujte, jak selektivní je vaše klauzule WHERE.

Kdy je to problém: když vybíráte malý zlomek řádků z velké tabulky a neexistuje žádný index.

Index Scan — Cílené vyhledání#

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

Jde do B-tree indexu, najde odpovídající záznamy, pak načte skutečné řádky z tabulkové haldy. Každé načtení řádku je operace náhodného I/O do haldy. To je skvělé pro vysoce selektivní dotazy, ale každé načtení z haldy má svůj náklad.

Index Only Scan — Svatý grál#

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

Toto je nejrychlejší typ skenu. PostgreSQL získá vše, co potřebuje, pouze z indexu, nikdy se nedotkne tabulkové haldy. Vidíte to, když všechny sloupce, které SELECTujete a na které aplikujete WHERE, jsou v indexu.

Háček: Heap Fetches: 0 znamená, že mapa viditelnosti je aktuální. Pokud má vaše tabulka hodně mrtvých řádků (nebylo nedávno provedeno vacuum), PostgreSQL musí stále kontrolovat haldu pro ověření viditelnosti řádků. To je jeden z důvodů, proč autovacuum ovlivňuje výkon, nejen diskový prostor.

Bitmap Scan — Střední cesta#

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')

Bitmapové skeny jsou odpovědí PostgreSQL na problém „příliš mnoho řádků na sken indexu, příliš málo na sekvenční sken." Vytvoří bitmapu stránek, které obsahují odpovídající řádky, seřadí je podle fyzického umístění a pak je načte v pořadí. To převádí náhodné I/O na sekvenční I/O.

Často uvidíte bitmapové skeny, když se kombinují dva nebo více indexů:

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')

Tento BitmapAnd kombinuje dva oddělené indexy. PostgreSQL tím říká „nemám kompozitní index, ale mohu zkombinovat tyto dva jednosloupcové indexy." Funguje to, ale řádný kompozitní index by byl rychlejší.

Odhalení špatných odhadů#

Číslo jedna, co hledám v plánu dotazu, jsou nesouhlasné odhady:

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

Plánovač odhadl 1 řádek. Skutečný výsledek byl 48 753 řádků. To je chyba o pět řádů. Plánovač zvolil Nested Loop, protože si myslel, že joinuje proti 1 řádku. S 48 753 řádky by byl Hash Join řádově rychlejší.

Běžné příčiny špatných odhadů:

• Zastaralé statistiky: Spusťte ANALYZE na tabulce
• Korelované sloupce: Plánovač předpokládá, že hodnoty sloupců jsou nezávislé. Pokud status = 'shipped' a created_at > '2026-01-01' jsou korelované (většina nedávných objednávek je odeslaná), plánovač podhodnotí kombinovanou selektivitu
• Vlastní funkce ve WHERE: Plánovač používá výchozí odhad selektivity (obvykle 0,5 % pro rovnost, 33 % pro rozsah), když nemůže analyzovat funkci
• Parametrizované dotazy s generickými plány: Po 5 provedeních může PostgreSQL přepnout na generický plán, který nebere v úvahu skutečnou hodnotu parametru

Když vidíte špatné odhady, oprava je obvykle jedna z: spusťte ANALYZE, vytvořte rozšířené statistiky, přepište dotaz nebo použijte CTE jako optimalizační bariéru.

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

Strategie indexů: Správný index pro správnou úlohu#

Indexy nejsou zadarmo. Každý index zpomaluje zápisy, spotřebovává diskový prostor a musí být udržován. Cílem není „indexovat vše" — je to „indexovat přesně to, co potřebujete."

B-tree — Výchozí tahoune#

B-tree je výchozí a zvládá naprostou většinu případů. Podporuje rovnost a rozsahové operátory (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

CREATE INDEX idx_orders_created_at ON orders (created_at);

Pořadí sloupců v B-tree indexu je nesmírně důležité pro kompozitní indexy. Nejlevější sloupec je primární řazení, pak další a tak dále. Index na (a, b, c) dokáže efektivně odpovědět na:

• 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

Nemůže efektivně odpovědět na:

• WHERE b = 2 (přeskakuje první sloupec)
• WHERE c = 3 (přeskakuje první dva sloupce)
• WHERE a = 1 AND c = 3 (mezera uprostřed — podmínka a se použije, podmínka c vyžaduje filtr)

Představte si to jako telefonní seznam řazený podle příjmení, pak křestního jména. Rychle najdete všechny položky „Novák", nebo konkrétně „Novák, Jan." Ale nemůžete rychle najít všechny „Jan" bez prohledání celého seznamu.

Pravidlo: dejte podmínky rovnosti jako první, pak rozsahové podmínky, pak sloupce řazení.

-- 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);

Pokrývající indexy s INCLUDE#

PostgreSQL 11+ umožňuje přidat neklíčové sloupce k indexu pomocí INCLUDE. Tyto sloupce jsou uloženy v listových stránkách, ale nejsou součástí struktury B-tree. Umožňují index-only skeny bez nafukování stromu:

-- 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);

Nyní může PostgreSQL splnit celý dotaz pouze z indexu. Bez INCLUDE by musel načíst total a status z haldy. Rozdíl na studené cache může být dramatický — viděl jsem dotazy přejít z 50 ms na 0,2 ms jen přidáním sloupců INCLUDE.

Parciální indexy — Indexujte pouze to, co je důležité#

Toto je moje nejoblíbenější funkce PostgreSQL a ta, o které většina vývojářů neví.

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

Tento index je malý ve srovnání s plným indexem na created_at. Obsahuje pouze řádky, kde status = 'pending'. Dotazy odpovídající klauzuli WHERE používají tento malý, rychlý index. Dotazy, které neodpovídají, ho ignorují.

Reálný příklad z produkce: měl jsem tabulku sessions s 50 miliony řádků. Pouze asi 200 000 bylo aktivních (neexpirovaných). Plný index na user_id měl 1,2 GB. Parciální index WHERE expires_at > now() měl 8 MB. Dotaz přešel z 12 ms na 0,1 ms, protože celý index se vešel do cache.

-- 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 poslední je neuvěřitelně užitečný. Umožňuje vám mít unikátní omezení emailu na aktivní uživatele, zatímco stejný email se může objevit ve smazaných záznamech.

GIN indexy — Fulltextové vyhledávání a JSONB#

GIN (Generalized Inverted Index) je odpovědí, když potřebujete hledat uvnitř hodnot — polí, JSONB dokumentů nebo vektorů fulltextového vyhledávání.

-- 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));

GIN indexy jsou velké a pomalé na vytvoření, ale bleskurychlé na dotazování. Stojí za to pro čtecí zátěže se složitými dotazy na obsah.

Pro JSONB existuje i jsonb_path_ops, který je menší a rychlejší pro dotazy na obsah @>, ale nepodporuje operátory existence (?, ?|, ?&):

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

GiST indexy — Geometrické a rozsahové typy#

GiST (Generalized Search Tree) zpracovává překrývající se datové typy: geometrické tvary, rozsahy, fulltextové vyhledávání (alternativa k GIN, menší ale pomalejší).

-- 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');

Hash indexy — Niche, ale užitečné#

Hash indexy jsou užitečné pouze pro porovnání rovnosti. Od PostgreSQL 10 jsou WAL-logované a bezpečné proti pádům. Jsou menší než B-tree pro široké sloupce a mírně rychlejší pro čistou rovnost:

-- 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%';

V praxi hash indexy používám zřídka. B-tree zvládá rovnost v pohodě a neschopnost provádět rozsahové dotazy nebo řazení dělá hash indexy příliš nepružné pro většinu reálných případů.

Kdy indexy škodí#

Každý index má svou cenu:

• Zesílení zápisů: Každý INSERT aktualizuje každý index na tabulce. Tabulka s 8 indexy znamená 8 dalších zápisů na každý INSERT
• Blokování HOT aktualizací: Aktualizace Heap-Only Tuple (HOT) jsou důležitá optimalizace, kdy PostgreSQL může aktualizovat řádek bez aktualizace indexů, ale pouze pokud se nezměnil žádný indexovaný sloupec. Více indexů = více šancí na blokování HOT aktualizací
• Režie vacuumu: Více indexů znamená, že vacuum trvá déle
• Režie plánovače: Více indexů znamená, že plánovač má více možností k vyhodnocení

Pravidelně audituji indexy na produkčních tabulkách:

-- 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;

Našel jsem vícegigabajtové indexy, které byly vytvořeny během jednorázové migrace a nikdy znovu použity. Jejich odstranění znatelně zrychlilo zápisy.

-- 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;

Problém N+1 dotazů#

Na tohle narazí každý vývojář používající ORM. Je to nejběžnější problém s výkonem, který ladím.

Jak N+1 vypadá#

# 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}")

Toto vygeneruje:

-- 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 dotazů místo 1 nebo 2. Každý dotaz je jednotlivě rychlý, třeba 0,5 ms. Ale 501 z nich se sečte na 250 ms samotného databázového času, plus latence síťového round-tripu pro každý z nich.

Detekce N+1 v logách#

Nejrychlejší způsob, jak zachytit N+1 dotazy, je dočasně povolit logování příkazů:

-- 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();

Pak se podívejte do logů. N+1 je nezaměnitelné — uvidíte stovky identických dotazů s různými hodnotami parametrů v rychlém sledu.

Cílenější přístup pro vývoj:

-- 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;

Toto zaloguje plný výstup EXPLAIN ANALYZE pro jakýkoli dotaz přesahující 100 ms, včetně dotazů uvnitř funkcí.

Oprava: Eager loading nebo JOINy#

Přístup ORM — řekněte ORM, aby načetl související data předem:

# 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()
)

Přístup surového SQL — prostě použijte JOIN:

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

Jeden dotaz. Hotovo.

JOINy vs více dotazů#

Zde probíhá legitimní debata. Někdy jsou dva dotazy lepší než JOIN:

-- 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);

Přístup s JOINem duplikuje data objednávek pro každou položku. Pokud má každá objednávka 20 položek a řádek objednávky je široký, to je 20násobný přenos dat. Přístup se dvěma dotazy odešle každou objednávku přesně jednou.

Mé pravidlo: používejte JOINy pro vztahy one-to-one, zvažte oddělené dotazy pro one-to-many, když strana „one" je široká. Ale vždy benchmarkujte — síťový round-trip druhého dotazu často stojí více než duplikovaná data.

Běžné přepisy dotazů#

Některé dotazy jsou pomalé ne kvůli chybějícím indexům, ale kvůli tomu, jak jsou napsané. Plánovač PostgreSQL je dobrý, ale není to kouzlo.

Subquery vs JOIN vs CTE#

Tyto tři přístupy mohou produkovat velmi odlišné plány:

-- 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;

V PostgreSQL 12+ jsou CTE obvykle inlinované (plánovač s nimi zachází jako s poddotazy), takže výkon je identický. Ale v PostgreSQL 11 a níže jsou CTE optimalizační bariéry — plánovač je materializuje a nemůže přes ně protlačit predikáty. Pokud jste stále na PG 11, vyhýbejte se CTE pro výkonnostně kritické dotazy.

EXISTS vs IN vs JOIN#

Tohle se objevuje neustále:

-- 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;

Pro velké tabulky EXISTS často vyhrává, protože se zkratuje. Verze IN musí nejprve vytvořit celý seznam order_id vratek. Verze JOIN může produkovat duplikáty, vyžadující DISTINCT, což přidá krok řazení nebo hashování.

Mé výchozí pravidlo: používejte EXISTS pro kontrolu existence souvisejících řádků. Je to sémanticky nejjasnější a obvykle nejrychlejší.

Ale existuje protiklad. Pokud je sada výsledků poddotazu malá a potřebujete ji pro více podmínek:

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

Vyhýbání se SELECT *#

Tohle není jen preference stylu kódu. Má to reálné dopady na výkon:

-- 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';

S SELECT *:

• Nemůžete používat index-only skeny (všechny sloupce by musely být v indexu)
• Přenáší více dat po síti
• Spotřebovává více paměti pro řazení a hashování
• Pokud někdo později přidá 10MB BYTEA sloupec, vaše existující dotazy se tiše zpomalí

Window funkce vs poddotazy#

Window funkce jsou jednou z nejsilnějších funkcí PostgreSQL a téměř vždy překonají korelované poddotazy:

-- 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';

Další běžný vzor — získání posledního řádku na skupinu:

-- 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 je můj oblíbený pro tento vzor. Je stručný, čitelný a PostgreSQL ho dobře optimalizuje se správným indexem:

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

Stránkování done right#

OFFSET je past pro velké datové sady:

-- 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;

Oprava je keyset stránkování (též nazývané stránkování založené na kurzoru):

-- 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;

Toto je vždy rychlé, protože využívá index k přeskočení přímo na správnou pozici, bez ohledu na to, na které „stránce" jste. Kompromis je, že nemůžete skočit na libovolné číslo stránky, ale pro nekonečný scroll nebo rozhraní „další stránka" je keyset stránkování striktně lepší.

Pro složitá řazení:

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

Toto využívá porovnání hodnot řádků, které PostgreSQL efektivně zpracovává s kompozitním indexem na (price, id).

Statistiky tabulek a vacuuming#

Plánovač dotazů PostgreSQL dělá rozhodnutí na základě statistik o vašich datech. Špatné statistiky vedou ke špatným plánům. Je to tak jednoduché.

ANALYZE: Aktualizace statistik#

-- 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';

Hodnota correlation je zvláště zajímavá. Pohybuje se od -1 do 1 a měří, jak dobře se fyzické pořadí řádků shoduje s logickým pořadím sloupce. Korelace blízká 1 nebo -1 znamená, že data jsou fyzicky seřazena podle tohoto sloupce, což činí rozsahové skeny velmi efektivní (sekvenční I/O). Korelace blízká 0 znamená náhodné I/O pro rozsahové dotazy.

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

Výchozí cíl statistik je 100, což znamená, že PostgreSQL vzorkuje 300 * 100 = 30 000 řádků. Pro sloupce s mnoha odlišnými hodnotami nebo nerovnoměrnými distribucemi zvýšení na 500 nebo 1000 dává plánovači lepší data za cenu mírně delších časů ANALYZE.

Ladění autovacuumu#

Autovacuum dělá dvě věci: uvolňuje místo po mrtvých řádcích (smazané nebo aktualizované řádky) a aktualizuje statistiky. Na vytížených tabulkách jsou výchozí nastavení autovacuumu často příliš konzervativní.

-- 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;

Pokud vidíte tabulky s miliony mrtvých řádků a poslední vacuum bylo před hodinami, váš autovacuum zaostává.

Pro tabulky s vysokým obratem (jako sessions, job fronty nebo metriky) nastavuji autovacuum per-tabulku:

-- 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)
);

Nadýmání tabulek#

Když PostgreSQL aktualizuje řádek, nemodifikuje ho na místě — vytvoří novou verzi a starou označí jako mrtvou. Vacuum uvolní mrtvé řádky, ale místo je znovu použito pouze touto tabulkou. Soubor tabulky na disku se nezmenší.

Postupem času může intenzivně aktualizovaná tabulka mít významné nadýmání — tabulka je na disku mnohem větší, než vyžadují živá data. To znamená více stránek ke skenování, více I/O, větší tlak na cache.

-- 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;

Pro těžce nadýmané tabulky je jaderná možnost VACUUM FULL — přepíše celou tabulku. Ale bere exkluzivní zámek, takže to nemůžete udělat na živé produkční tabulce bez výpadku. Lepší přístup je pg_repack, který dělá totéž bez zamykání:

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

Pooling připojení#

Zde je něco, co překvapí mnoho vývojářů: připojení PostgreSQL jsou drahá. Každé připojení vytváří nový proces (ne vlákno), spotřebuje asi 5-10 MB paměti a má nezanedbatelnou režii forku.

Výchozí max_connections je 100. Pokud máte aplikační server s 20 workery, každý otevírající 5 připojení, jste už na limitu. Přidejte procesor na pozadí, monitorovací nástroj a migraci běžící někde jinde, a máte problém.

Proč potřebujete pooler připojení#

Bez pooleru, pokud vaše aplikace potřebuje zvládnout 500 souběžných požadavků, potřebujete 500 připojení PostgreSQL. To je 5 GB paměti jen pro režii připojení a výkon PostgreSQL se výrazně zhoršuje nad několik set připojení kvůli režii správy procesů.

S PgBouncerem před PostgreSQL se těch 500 aplikačních připojení mapuje na možná 20 skutečných připojení PostgreSQL. Pooler řadí požadavky do fronty, když jsou všechna databázová připojení obsazena.

Konfigurace PgBouncer#

[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

Transakční vs session režim#

• Transakční režim (pool_mode = transaction): Připojení se vrací do poolu po každé transakci. To je to, co chcete 95 % času. Maximální znovupoužití připojení.
• Session režim (pool_mode = session): Připojení je drženo po celou klientskou session. Použijte, pokud potřebujete prepared statementy, příkazy SET, LISTEN/NOTIFY nebo jiné funkce na úrovni session.
• Statement režim (pool_mode = statement): Připojení se vrací po každém příkazu. Příliš restriktivní pro většinu aplikací — nemůžete ani použít explicitní transakce.

Háček s transakčním režimem: nemůžete používat prepared statementy (jsou stavem na úrovni session), nemůžete používat SET pro session proměnné a LISTEN/NOTIFY nebude fungovat podle očekávání. Většina ORM má způsob, jak zakázat prepared statementy.

Pro Node.js s ovladačem pg:

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,
});

Vzorec pro velikost poolu#

Existuje běžný vzorec pro optimální velikost poolu připojení PostgreSQL:

optimal_connections = (core_count * 2) + effective_spindle_count

Pro moderní server se 4 jádry a SSD (1 efektivní spindle):

optimal = (4 * 2) + 1 = 9

To se zdá kontraintuitivně nízké. Ale PostgreSQL je CPU-bound na většině moderního hardwaru (SSD jsou dostatečně rychlé, že I/O zřídka tvoří úzké hrdlo). Více připojení než tohle vede k režii přepínání kontextu, která věci ve skutečnosti zpomaluje.

V praxi obvykle nastavuji default_pool_size na 2-3násobek tohoto čísla pro zvládnutí nárazového provozu, s vědomím, že při špičkové zátěži budou některé dotazy čekat ve frontě PgBounceru, místo aby všechny najednou zasáhly PostgreSQL.

Praktický checklist: Přesné kroky pro každý pomalý dotaz#

Zde je můj skutečný proces, když dostanu hlášení „tento dotaz je pomalý." Tyto kroky následuji v tomto pořadí, pokaždé.

Krok 1: Získejte skutečný dotaz#

Ne „endpoint je pomalý" — skutečný SQL. Pokud používáte ORM, povolte logování dotazů:

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

Nebo zkontrolujte pg_stat_statements pro top dotazy podle celkového času:

-- 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 je nejhodnotnější rozšíření pro výkon PostgreSQL. Pokud ho nepoužíváte, nainstalujte ho hned:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Krok 2: Spusťte EXPLAIN (ANALYZE, BUFFERS)#

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

Hledejte:

1. Špatné odhady řádků — skutečné řádky se velmi liší od odhadovaných
2. Seq Scany na velkých tabulkách — potenciální chybějící index
3. Nested Loops s mnoha řádky — měl by to být Hash Join nebo Merge Join
4. Vysoké čtení bufferů — studená cache nebo příliš velká tabulka
5. Operace řazení přetékající na disk — zvyšte work_mem nebo přidejte index pro řazení

Krok 3: Zkontrolujte statistiky tabulky#

-- 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';

Pokud je last_analyze staré nebo n_dead_tup je vysoké vzhledem k n_live_tup, spusťte:

ANALYZE orders;

Pak znovu spusťte EXPLAIN ANALYZE. Pokud se plán změní, zastaralé statistiky byly problém.

Krok 4: Zkontrolujte existující indexy#

-- 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žná index existuje, ale není používán. Možná je pořadí sloupců špatné pro váš dotaz.

Krok 5: Vytvořte nebo upravte indexy#

Na základě plánu dotazu vytvořte vhodný index. Testujte s EXPLAIN ANALYZE před a po.

-- 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>;

Na produkčních tabulkách vždy používejte CONCURRENTLY. Běžný CREATE INDEX bere plný zámek tabulky, který blokuje všechny zápisy.

Krok 6: Zvažte přepisy dotazů#

Pokud index existuje a statistiky jsou čerstvé, ale dotaz je stále pomalý, podívejte se na samotný dotaz:

• Může být poddotaz přepsán jako JOIN?
• Způsobuje OFFSET problémy? Přepněte na keyset stránkování
• Vybíráte více sloupců, než potřebujete?
• Může se korelovaný poddotaz stát window funkcí?
• Brání CTE plánovači v optimalizaci?

Krok 7: Zkontrolujte nastavení serveru#

Pro specifické vzory dotazů záleží na nastavení serveru:

-- 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;

Pokud změna work_mem opraví problém s řazením na disku, zvažte jeho globální zvýšení. Ale buďte opatrní — je to per-operace, ne per-připojení. Složitý dotaz s 10 operacemi řazení a work_mem = 256MB by mohl spotřebovat 2,5 GB pro jediný dotaz.

Krok 8: Monitorujte po opravě#

Neopravujte a nezapomeňte. Ověřte, že oprava vydrží:

-- 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: Rychlé výhry, které aplikuji na každou novou databázi#

Toto jsou nastavení a postupy, které aplikuji na každou databázi PostgreSQL, kterou nastavuji, ještě než se objeví jakékoli problémy s výkonem:

-- 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

A monitorovací dotaz, který spouštím týdně:

-- 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;

Závěrečné myšlenky#

Optimalizace dotazů PostgreSQL není černá magie. Je to systematický proces:

1. Měřte — nehádejte. EXPLAIN (ANALYZE, BUFFERS) je váš nejlepší přítel.
2. Pochopte plán — naučte se číst typy skenů, typy joinů a odhady řádků.
3. Indexujte strategicky — správný index na správných sloupcích, ve správném pořadí. Parciální indexy a pokrývající indexy jsou nedostatečně využívané superschopnosti.
4. Pište lepší dotazy — EXISTS přes IN pro kontroly existence, keyset stránkování přes OFFSET, window funkce přes korelované poddotazy.
5. Udržujte databázi — ladění autovacuumu, aktualizace statistik, pooling připojení.
6. Monitorujte průběžně — pg_stat_statements vám řekne, kde vaše databáze tráví čas. Kontrolujte to pravidelně.

Rozdíl mezi dotazem, který trvá 4 sekundy, a tím, který trvá 0,3 milisekundy, je zřídka hardware. Téměř vždy je to znalost — vědět, kde hledat a co změnit. A teď víte, kde hledat.