EXPLAIN vám klamal: PostgreSQL Prepared Statement Plan Cliff

Plan cliff je realny; narazil som na neho najprv v produkcii. “Query používa index scan. Skontroloval som EXPLAIN. Je to v poriadku.” Vývojár bol frustrovaný. Jeho query bežal v 5ms mesiace a teraz trval 30 sekúnd. Rovnaký query, rovnaká tabuľka, rovnaké indexy. EXPLAIN ukazoval index scan s odhadovanou cenou 0.43. Produkcia horela.

Spýtal som sa ho ako spustil EXPLAIN. Skopíroval query z aplikácie, nahradil parameter literálnou hodnotou a spustil EXPLAIN ANALYZE. Perfektný plán. Potom som ho požiadal aby skontroloval pg_stat_statements. Query bol vykonaný 847,000 krát ten týždeň. Vtedy som vedel: narazili na prepared statement plan cliff.

PostgreSQL query plánovač má feature o ktorej sa väčšina vývojárov nikdy nedozvie: po tom čo bol prepared statement vykonaný dostatočne veľakrát, plánovač sa môže rozhodnúť prestať generovať parameter-špecifické “custom plány” a prepnúť na jeden “generic plán” ktorý funguje pre akúkoľvek hodnotu parametra. Dáva to zmysel pre väčšinu queries—prečo preplánovávať každé vykonanie ak je plán vždy rovnaký? Ale pre queries so skosenými dátovými distribúciami môže byť generic plán katastrofálne zlý.

Query filtroval podľa tenant_id. Jeden tenant mal 50 miliónov riadkov. Väčšina tenantov mala pod 1,000. Custom plán—generovaný s vedomím skutočného parametra—používal index na tenant_id pre malých tenantov a sekvenčný scan pre obrovského tenanta. Generic plán, nútený vybrať jednu stratégiu bez znalosti hodnoty parametra, vybral sekvenčný scan (pretože to je optimálne pre priemerný prípad). Každý query teraz robil full table scan bez ohľadu na tenanta.

Prostredie: PostgreSQL 14+, JDBC/pgJDBC, HikariCP, vysokoobjemové multi-tenant aplikácie

Pochopenie Plan Cachingu

Custom Plány vs Generic Plány

Životný cyklus Prepared Statement:

Prvých 5 vykonaní: "Custom Plány"
  EXECUTE prep_stmt('tenant_small');
    → Plánovač vidí 'tenant_small', vie že má 100 riadkov
    → Generuje plán: Index Scan on tenant_id_idx
    → Vykonanie: 2ms

Po 5 vykonaniach: "Generic Plán" zvažovaný
  Plánovač porovnáva:
    - Priemerná cena custom plánu: 0.5
    - Cena generic plánu: 50.0 (na základe štatistík)
  Generic plán je horší → ďalej používaj custom plány

Po mnohých vykonaniach s rôznymi parametrami:
  Plánovač pozoruje: custom plány majú vždy rovnaký tvar
  → Prepne na generic plán natrvalo
  → Generic plán zvolený pre "priemernú" hodnotu parametra

Problém: "Priemerný" tenant má 50K riadkov
         Generic plán: Sequential Scan
         Ale tenant_small má 100 riadkov!
         Sekvenčný scan na 100M riadkovej tabuľke pre 100 riadkov = katastrofa

Ako sa prepnutie stane

-- Plánovač sleduje tieto štatistiky per prepared statement:
-- - Počet vykonaní
-- - Ceny custom plánov
-- - Cena generic plánu

-- Rozhodujúce pravidlo (zjednodušené):
IF generic_plan_cost <= 1.1 * avg_custom_plan_cost THEN
  USE generic_plan
ELSE
  USE custom_plan
END IF

-- Multiplikátor 1.1 súvisí s plan_cache_mode
-- Default: auto (plánovač rozhoduje)
-- Možnosti: force_custom_plan, force_generic_plan

Reálny spúšťač

Časová os cliffu:

T+0:     Nový deployment aplikácie
         Pool vytvára čerstvé spojenia
         Prepared statements začínajú na 0 vykonaniach

T+1h:    Statementy vykonané 5+ krát každý
         Plánovač stále používa custom plány
         Všetko rýchle

T+24h:   High-traffic statementy vykonané 10,000+ krát
         Plánovač "sebavedomý" v odhade ceny generic plánu
         Prepína na generic plán

T+24h1m: Prvý query s neobvyklým parametrom dostane generic plán
         30 sekundový čas query
         Kaskádové timeouty

Prečo sme to nevideli predtým?
  - Connection pool recycle resetuje prepared statements
  - Nočný DB reštart vyčistil plan cache
  - Nižšia prevádzka znamenala že custom plány pretrvali dlhšie

Diagnostika Plan Cliffu

Detekcia použitia Generic Plánu

-- Skontroluj či sa používajú generic plány
-- Tento query skúma pg_prepared_statements
SELECT name, statement,
       generic_plans, custom_plans,
       CASE WHEN generic_plans > 0 THEN 'GENERIC' ELSE 'CUSTOM' END as plan_type
FROM pg_prepared_statements
WHERE generic_plans > 0
ORDER BY generic_plans DESC;

-- Problém: pg_prepared_statements je connection-lokálne
-- Musíš kontrolovať na každom backend spojení

-- Alternatíva: Hľadaj vysokú varianciu v pg_stat_statements
SELECT query,
       calls,
       mean_exec_time,
       stddev_exec_time,
       stddev_exec_time / NULLIF(mean_exec_time, 0) as cv
FROM pg_stat_statements
WHERE calls > 1000
  AND stddev_exec_time / NULLIF(mean_exec_time, 0) > 1
ORDER BY stddev_exec_time DESC
LIMIT 20;

-- Vysoký koeficient variancie (CV) = rovnaký query s divoky rôznymi časmi
-- Toto je smoking gun pre generic plan problémy

Porovnaj EXPLAIN vs Realitu

-- Krok 1: EXPLAIN s literálom (ukazuje custom plán)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE tenant_id = 'small_tenant';
-- Ukazuje: Index Scan using tenant_id_idx (cost=0.43..8.45)

-- Krok 2: EXPLAIN s parametrom (ukazuje čo by prepared urobil)
PREPARE test_stmt AS SELECT * FROM orders WHERE tenant_id = $1;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) EXECUTE test_stmt('small_tenant');
-- Prvých 5 krát: Rovnaké ako literál (custom plán)
-- Po 5 krát: Môže prepnúť na Seq Scan (generic plán)!

-- Krok 3: Vynúť generic plán aby si videl čo produkcia používa
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) EXECUTE test_stmt('small_tenant');
-- Ukazuje: Seq Scan on orders (cost=0.00..2847234.56)
-- Toto je čo produkcia skutočne vykonáva!
RESET plan_cache_mode;

Nájdi stĺpce s vysokou skosenosťou

-- Identifikuj stĺpce kde je generic plán rizikový
SELECT
    schemaname,
    tablename,
    attname,
    n_distinct,
    most_common_vals[1:3] as top_values,
    most_common_freqs[1:3] as top_frequencies
FROM pg_stats
WHERE schemaname = 'public'
  AND n_distinct > 100
  AND most_common_freqs[1] > 0.1  -- Top hodnota je >10% dát
ORDER BY most_common_freqs[1] DESC;

-- Ak jedna hodnota má 50% riadkov ale je 1000 distinct hodnôt:
-- Generic plán optimalizuje pre "priemerný" prípad
-- Custom plán môže vybrať inak pre outliery

Oprava

Možnosť 1: Vynúť Custom Plány

-- Per-session: Vynúť custom plány
SET plan_cache_mode = force_custom_plan;

-- Perzistentne: V postgresql.conf
plan_cache_mode = force_custom_plan

-- Nevýhoda: Re-plánuje každé vykonanie
-- Pridáva ~1-2ms per query na planning overhead
-- Stojí to za to pre skosené dáta, drahé pre uniformné dáta

Možnosť 2: Vypni Server-Side Prepared Statements

// pgJDBC: Vypni server-side prepare
Properties props = new Properties();
props.setProperty("prepareThreshold", "0");

// HikariCP config
HikariConfig config = new HikariConfig();
config.addDataSourceProperty("prepareThreshold", "0");

// Toto posiela queries ako simple queries zakaždým
// Plánovač vždy vidí literálne hodnoty → vždy optimálny plán
// Nevýhoda: Mierne vyšší network overhead, planning overhead

# psycopg2: Použi client-side parameter binding
# Defaultne psycopg2 používa server-side prepared statements

# Možnosť 1: Použi mogrify na vytvorenie literálneho SQL
query = cursor.mogrify("SELECT * FROM orders WHERE tenant_id = %s", (tenant_id,))
cursor.execute(query)

# Možnosť 2: Vypni prepared statements v connection stringu
conn = psycopg2.connect("postgresql://...?prepare_threshold=0")

Možnosť 3: Hintuj plánovač

-- Použi explicitné hinty (vyžaduje pg_hint_plan extension)
-- Vynúti konkrétny plán bez ohľadu na generic/custom

/*+ IndexScan(orders tenant_id_idx) */
SELECT * FROM orders WHERE tenant_id = $1;

-- Alebo použi CTE trick na vynútenie poradia vyhodnotenia
WITH tenant_filter AS MATERIALIZED (
    SELECT * FROM orders WHERE tenant_id = $1
)
SELECT * FROM tenant_filter WHERE ...;

Možnosť 4: Rozdeľ na viacero queries

-- Namiesto jedného query ktorý ide generic:
SELECT * FROM orders WHERE tenant_id = $1 AND status = $2;

-- Rozdeľ podľa známej distribúcie:
-- Pre malých tenantov (index-friendly):
SELECT * FROM orders WHERE tenant_id = $1 AND status = $2;

-- Pre veľkých tenantov (partition scan):
SELECT * FROM orders_large_tenants WHERE tenant_id = $1 AND status = $2;

-- Aplikácia rozhodne ktorý query na základe veľkosti tenanta
-- Každý query môže byť optimalizovaný pre svoj use case

Možnosť 5: Konfigurácia Connection Poolu

// Vynúť connection recycling na reset prepared statement cache
HikariConfig config = new HikariConfig();
config.setMaxLifetime(600000);     // 10 minút
config.setIdleTimeout(300000);     // 5 minút
config.setKeepaliveTime(60000);    // 1 minúta

// Kratší lifetime = častejší reset plan cache
// Trade-off: Connection establishment overhead

Možnosť 6: Oprav štatistiky

-- Ak je skosenosť problém, zlepši štatistiky

-- Zvýš statistics target pre skosený stĺpec
ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 1000;
ANALYZE orders;

-- Vytvor rozšírené štatistiky pre korelácie
CREATE STATISTICS orders_stats ON tenant_id, status FROM orders;
ANALYZE orders;

-- Pre extrémnú skosenosť, vytvor čiastočné indexy
CREATE INDEX orders_large_tenant_idx ON orders (status)
  WHERE tenant_id = 'large_tenant';

Reálne vzory

Multi-Tenant aplikácie

-- Bežný vzor ktorý narazí na cliff:
SELECT * FROM events
WHERE tenant_id = $1
  AND created_at > $2
ORDER BY created_at DESC
LIMIT 100;

-- Tenant A: 50 miliónov eventov (mal by full scan nedávnej partície)
-- Tenant B: 1,000 eventov (mal by index scan)

-- Generic plán vyberie jednu stratégiu pre všetkých tenantov
-- Buď všetci tenanti robia pomalý index scan, alebo všetci pomalý full scan

-- Oprava: Vynúť custom plány pre tento query
-- Alebo: Particionuj podľa tenant_id
-- Alebo: Oddelené tabuľky pre veľkých tenantov

Time-Based Queries

-- Query na nedávne dáta ktorý ide zle:
SELECT * FROM logs WHERE created_at > $1;

-- Parameter je zvyčajne "posledná hodina" → malý result set
-- Generic plán optimalizovaný pre "priemerný" time range
-- Keď niekto queryuje "posledný rok" → index scan na obrovskom range

-- Alebo opačne:
-- Parameter je zvyčajne "posledný rok" → veľký result set
-- Generic plán: seq scan
-- Keď niekto queryuje "poslednú minútu" → seq scan na 1B riadkoch

Monitoring

Prometheus Alerty

groups:
- name: prepared-statement-cliff
  rules:
  - alert: QueryLatencyVarianceVysoka
    expr: |
      (
        pg_stat_statements_stddev_exec_time_seconds
        / pg_stat_statements_mean_exec_time_seconds
      ) > 2
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "Query {{ $labels.query }} má vysokú varianciu latencie"
      description: "Možný generic plan cliff - rovnaký query trvá divoky rôzne časy"

Query na detekciu cliff kandidátov

-- Nájdi queries pravdepodobne postihnuté cliffom
WITH query_stats AS (
    SELECT
        queryid,
        query,
        calls,
        mean_exec_time,
        stddev_exec_time,
        min_exec_time,
        max_exec_time
    FROM pg_stat_statements
    WHERE calls > 100
)
SELECT
    queryid,
    LEFT(query, 100) as query_preview,
    calls,
    round(mean_exec_time::numeric, 2) as mean_ms,
    round(stddev_exec_time::numeric, 2) as stddev_ms,
    round(min_exec_time::numeric, 2) as min_ms,
    round(max_exec_time::numeric, 2) as max_ms,
    round((max_exec_time / NULLIF(min_exec_time, 0))::numeric, 0) as max_min_ratio
FROM query_stats
WHERE stddev_exec_time > mean_exec_time  -- Vysoká variancia
   OR max_exec_time > 10 * mean_exec_time -- Extrémne outliery
ORDER BY max_min_ratio DESC NULLS LAST
LIMIT 20;

-- max_min_ratio > 100 = takmer určite plan cliff

Checklist

## PostgreSQL Prepared Statement Plan Cliff

### Detekcia
- [ ] Skontroluj pg_stat_statements pre high variance queries
- [ ] Porovnaj EXPLAIN (literál) vs EXPLAIN EXECUTE (prepared)
- [ ] Použi plan_cache_mode = force_generic_plan na zobrazenie skutočného plánu
- [ ] Identifikuj stĺpce so skosenými distribúciami

### Okamžitá oprava
- [ ] SET plan_cache_mode = force_custom_plan (ak je akceptovateľný overhead)
- [ ] Alebo: Nastav prepareThreshold=0 v JDBC driveri
- [ ] Recykluj spojenia na reset plan cache

### Dlhodobé riešenia
- [ ] Zlepši štatistiky na skosených stĺpcoch
- [ ] Zváž particionovanie pre multi-tenant tabuľky
- [ ] Použi pg_hint_plan pre kritické queries
- [ ] Monitoruj varianciu latencie queries

### Prevencia
- [ ] Reviewuj nové queries na citlivosť na skosenosť
- [ ] Testuj s produkčnou dátovou distribúciou
- [ ] Nastav alerty na varianciu latencie
- [ ] Dokumentuj kritické queries a ich požiadavky na plán

Záver

Prepared statement plan cliff je jeden z najfrustrujúcejších PostgreSQL gotchas pretože vaše debugovacie nástroje vás aktívne zavádzajú. Spustenie EXPLAIN s literálnymi hodnotami ukazuje optimálny custom plán—presne čo by ste očakávali. Ale produkcia používa generic plán ktorý bol optimalizovaný pre “priemerné” parametre, a váš špecifický parameter je ďaleko od priemeru.

Root cause je rozumná optimalizácia ktorá zlyháva pre skosené dáta. PostgreSQL plánovač sa snaží pomôcť cachovaním jedného plánu namiesto re-plánovania každého vykonania. Pre uniformné dátové distribúcie to funguje perfektne. Pre multi-tenant aplikácie, time-series dáta, alebo akúkoľvek tabuľku s niekoľkými hodnotami obsahujúcimi väčšinu riadkov, môže byť generic plán o rády pomalší ako optimálny custom plán.

Kľúčové princípy:

1. EXPLAIN s literálmi ukazuje custom plán, nie čo produkcia používa—vždy testuj s force_generic_plan
2. Vysoká variancia latencie = pravdepodobný plan cliff—rovnaký query trvá 5ms niekedy a 30s inokedy
3. Multi-tenant a time-series dáta sú vysoké riziko—skosené distribúcie lámú generic plan predpoklady
4. prepareThreshold=0 je najbezpečnejší default pre skosené dáta—zabraňuje server-side plan cachingu úplne
5. Monitoruj query stddev, nie len mean—variancia ti povie viac ako priemer

Skontroluj tvoj pg_stat_statements pre high-variance queries. Cliff možno je len jeden traffic spike ďaleko.

Súvisiace články

• PostgreSQL Autovacuum Tuning - Údržba štatistík tabuliek
• Connection Pool Sizing - Konfigurácia poolu