EXPLAIN ANALYZE: PostgreSQL 쿼리 계획 읽기 및 최적화
프로덕션에서 8초가 걸리고 올바른 인덱스를 사용하면 50ms로 최적화될 수 있는 쿼리입니다.
약간의 재 작성. EXPLAIN ANALYZE 그 이유를 정확히 알려주는 도구
PostgreSQL은 스캔하는 행 수, 사용하는 인덱스, 소비 위치 등 특정 방식으로 쿼리를 실행합니다.
90%의 시간. 쿼리 계획을 읽는 방법을 배우는 것은 PostgreSQL 개발자에게 가장 영향력 있는 기술입니다.
무엇을 배울 것인가
- EXPLAIN ANALYZE의 출력을 읽고 비용이 많이 드는 노드를 식별합니다.
- 순차 스캔, 인덱스 스캔, 인덱스 전용 스캔의 차이점
- PostgreSQL이 중첩 루프, 해시 조인 또는 병합 조인을 선택하는 경우
- 업데이트된 통계로 잘못된 플래너 추정치를 수정하는 방법
- 사용
auto_explain느린 쿼리를 자동으로 포착하기 위해
쿼리 계획의 구조
쿼리 계획 및 작업 트리: 각 노드는 작업(스캔, 조인, 정렬)을 나타냅니다. 하위 노드는 상위 노드에 데이터를 제공합니다. PostgreSQL은 트리를 아래에서 위로 실행합니다.
-- EXPLAIN ANALYZE: aggiunge il tempo reale e le righe effettive
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
o.id AS ordine_id,
u.email,
SUM(ol.quantita * ol.prezzo_unitario) AS totale
FROM ordini o
JOIN utenti u ON u.id = o.utente_id
JOIN ordini_linee ol ON ol.ordine_id = o.id
WHERE o.stato = 'completato'
AND o.creato_il >= '2026-01-01'
GROUP BY o.id, u.email
ORDER BY totale DESC
LIMIT 100;
일반적인 출력(주석 포함):
Limit (cost=45823.12..45823.37 rows=100 width=48)
(actual time=892.341..892.363 rows=100 loops=1)
-> Sort (cost=45823.12..46073.12 rows=100000 width=48)
(actual time=892.340..892.350 rows=100 loops=1)
Sort Key: (sum((ol.quantita * ol.prezzo_unitario))) DESC
Sort Method: top-N heapsort Memory: 32kB
-> HashAggregate (cost=40000.00..42500.00 rows=100000 ...)
(actual time=820.123..880.234 rows=98432 loops=1)
Group Key: o.id, u.email
Batches: 5 Memory Usage: 4096kB Disk Usage: 1024kB <- PROBLEMA!
-> Hash Join (cost=1200.00..35000.00 rows=500000 ...)
(actual time=45.123..650.234 rows=512000 loops=1)
Hash Cond: (ol.ordine_id = o.id)
-> Seq Scan on ordini_linee ol <- Sequential Scan!
(actual time=0.012..280.123 rows=2100000 loops=1)
-> Hash (cost=800.00..800.00 rows=32000 ...)
(actual time=44.123..44.123 rows=31845 loops=1)
Buckets: 32768 Batches: 1 Memory: 2048kB
-> Hash Join (cost=100.00..800.00 ...)
(actual time=2.123..38.123 ...)
Hash Cond: (o.utente_id = u.id)
-> Index Scan using idx_ordini_stato_data on ordini o
(actual time=0.234..15.123 rows=31845 loops=1)
Index Cond: ((stato = 'completato') AND ...)
-> Hash (cost=50.00..50.00 rows=4000 ...)
...
Planning Time: 3.456 ms
Execution Time: 892.987 ms
주요 지표 읽기
비용: 추정과 현실
각 노드에는 두 쌍의 값이 표시됩니다. (cost=startup..total rows=stima width=N) 나는
르 플래너 견적, 하는 동안 (actual time=startup..total rows=reali loops=N)
그것들은 가치이다 정말로 자신을 측정해 보세요. 추정과 현실의 괴리가 큰 경우
기획자가 통계에 대해 잘못된 정보를 가지고 있습니다.
-- Discrepanza grave: stima 1000 righe, realta 500000
-- Questo causa scelta errata del tipo di join e degli indici
--
-- (cost=... rows=1000 ...) <- planner si aspettava 1000 righe
-- (actual ... rows=500000 ...) <- ma ne ha trovate 500000
--
-- Soluzione: aggiornare le statistiche
ANALYZE VERBOSE ordini_linee;
-- Per controllare le statistiche attualmente in uso
SELECT
tablename,
attname,
n_distinct,
correlation,
most_common_vals,
most_common_freqs
FROM pg_stats
WHERE tablename = 'ordini_linee'
AND attname IN ('ordine_id', 'stato');
시퀀스 스캔 vs 인덱스 스캔 vs 인덱스 전용 스캔
스캔 유형은 성능에 큰 영향을 미칩니다. PostgreSQL은 유형 기반을 선택합니다. 예상 선택성과 테이블 크기.
-- Quando PostgreSQL sceglie Sequential Scan nonostante un indice esista:
-- 1. Troppe righe restituite (>5-10% della tabella): seq scan e piu veloce
-- 2. Statistiche obsolete: il planner crede ci siano poche righe
-- 3. work_mem insufficiente per materializzare il result set
-- 4. L'indice non copre le colonne del WHERE
-- Forzare l'Index Scan per debugging (NON usare in produzione)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = on;
-- Index-Only Scan: il piu efficiente (nessun accesso alla tabella heap)
-- Richiede che tutte le colonne nel SELECT siano nell'indice
-- E che le pagine siano nel visibility map (dopo autovacuum)
-- Esempio: creare un covering index per abilitare index-only scan
CREATE INDEX idx_ordini_covering
ON ordini (stato, creato_il)
INCLUDE (utente_id, totale); -- colonne extra non usate nel filtro
-- Verificare se il visibility map e aggiornato
VACUUM ordini; -- o aspettare autovacuum
비용이 많이 드는 노드 식별
병목 현상을 찾는 가장 빠른 방법은 다음을 사용하는 것입니다. FORMAT JSON 도구로
시각적으로 또는 수동으로 노드를 검색하십시오. actual time 키가 크다.
-- Usare EXPLAIN con FORMAT JSON e pgMustard / explain.dalibo.com
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;
-- Pattern da cercare nell'output:
-- 1. "Disk Usage" > 0 in HashAggregate o Sort: work_mem troppo basso
-- 2. "Rows Removed by Filter" molto alto: indice mancante o inefficiente
-- 3. "loops" > 1 su Seq Scan: Nested Loop con tabella grande
-- 4. Stima vs reale molto diversa: statistiche obsolete
-- Calcolo del "costo effettivo" di un nodo:
-- actual_total_time * loops = tempo totale del nodo
-- Se un nodo con loops=50000 ha actual_time=0.05ms,
-- il costo totale e 2500ms!
조인 유형 및 사용 시기
중첩된 루프 조인
"외부" 테이블의 각 행을 반복하고 "내부" 테이블에서 각 행을 살펴봅니다. 내부가 작고 인덱스가 있을 때 탁월합니다. 두 테이블이 모두 크면 재앙이 됩니다.
-- Nested Loop: ideale per join su PK con pochi record
-- outer: 100 ordini recenti -> inner: users con indice su id
-- Costo: 100 * O(log N) = molto efficiente
-- Quando diventa un problema:
-- outer: 50.000 righe -> inner: 50.000 righe -> 2.5 miliardi di confronti!
-- Il planner dovrebbe preferire Hash Join in questo caso
-- Se vedi Nested Loop su tabelle grandi, verifica:
-- 1. Statistiche: il planner credeva di trovare poche righe
-- 2. join_collapse_limit: aumentare se il planner non esplora tutte le alternative
-- 3. enable_nestloop = off per testare se Hash/Merge Join sarebbe meglio
SET enable_nestloop = off;
EXPLAIN ANALYZE ...;
SET enable_nestloop = on;
해시 조인
더 작은 테이블에서 해시 테이블을 구성한 다음 더 큰 테이블을 스캔하여 서신. 사용 가능한 인덱스가 없는 중간/대형 테이블의 조인에 효율적입니다.
-- Hash Join con spill to disk (Batches > 1): aumentare work_mem
-- "Batches: 5 Memory Usage: 4096kB Disk Usage: 1024kB"
-- significa che la hash table non entra in memoria
-- Calcolare la work_mem necessaria
-- La hash table occupa circa: righe * larghezza_media * 2
-- Aumentare per sessione specifica (non globalmente)
SET work_mem = '256MB';
EXPLAIN ANALYZE ...;
SET work_mem = DEFAULT;
-- Oppure aumentare solo per le query analitiche
ALTER ROLE analyst_user SET work_mem = '256MB';
병합 조인
조인 키를 기준으로 두 입력을 모두 정렬해야 합니다. PostgreSQL은 다음과 같은 경우에 이를 선택합니다. 두 테이블 모두 순서를 보장하는 인덱스가 있습니다.
계획자 추정 수정
잘못된 추정은 최적이 아닌 쿼리 계획의 가장 일반적인 원인입니다. 이를 수정하는 기술은 다음과 같습니다.
-- 1. Aumentare il target di statistiche per colonne ad alta cardinalita
ALTER TABLE ordini ALTER COLUMN stato SET STATISTICS 500; -- default: 100
ANALYZE ordini;
-- 2. Creare statistiche estese per colonne correlate
-- (PostgreSQL non sa che stato='completato' implica certi range di date)
CREATE STATISTICS stat_ordini_stato_data (dependencies, ndistinct)
ON stato, creato_il FROM ordini;
ANALYZE ordini;
-- 3. Verificare le statistiche con pg_statistic_ext
SELECT
stxname,
stxkeys::int[] AS colonne,
stxdependencies,
stxndistinct
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON pg_statistic_ext_data.stxoid = pg_statistic_ext.oid
WHERE stxrelid = 'ordini'::regclass;
-- 4. Forzare il re-planning disabilitando la plan cache
-- (utile per query parametriche con distribuzione skewed)
SET plan_cache_mode = force_generic_plan;
auto_explain: 느린 쿼리를 자동으로 포착
프로덕션 환경에서는 모든 쿼리에 대해 EXPLAIN을 수동으로 실행할 수 없습니다. auto_explain
시간 임계값을 초과하는 쿼리 계획을 자동으로 기록합니다.
-- Abilitare auto_explain in postgresql.conf
# shared_preload_libraries = 'auto_explain' # richiede riavvio
# auto_explain parametri
auto_explain.log_min_duration = '500ms' # logga query > 500ms
auto_explain.log_analyze = true # mostra i tempi reali
auto_explain.log_buffers = true # mostra I/O
auto_explain.log_timing = true # timing dettagliato
auto_explain.log_triggers = false # non loggare trigger
auto_explain.log_verbose = false # output compatto
auto_explain.log_format = 'text' # json per tools
-- In alternativa: abilitare per sessione (senza riavvio)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = true;
-- Ora ogni query > 200ms nel log di PostgreSQL includera il piano
-- Cercare in /var/log/postgresql/postgresql-17-main.log:
-- duration: 892.987 ms plan:
-- ...Seq Scan on ordini_linee (actual time=0.012..280.123)...
실용적인 최적화 작업 흐름
프로덕션에서 식별된 느린 쿼리를 최적화하기 위한 체계적인 프로세스는 다음과 같습니다.
-- STEP 1: Identificare la query con pg_stat_statements
SELECT
LEFT(query, 100) AS q,
calls,
ROUND(mean_exec_time::numeric, 1) AS mean_ms,
ROUND(max_exec_time::numeric, 1) AS max_ms,
ROUND(total_exec_time::numeric / 1000, 1) AS total_s
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;
-- STEP 2: Ottenere il piano con EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ;
-- STEP 3: Identificare il nodo piu costoso
-- Cercare: actual time alto, Seq Scan su tabelle grandi, Batches > 1
-- STEP 4: Verificare gli indici esistenti
SELECT
indexname,
indexdef,
pg_relation_size(indexrelid) AS size_bytes
FROM pg_indexes
WHERE tablename = 'ordini_linee';
-- STEP 5: Creare l'indice mancante
-- CONCURRENTLY: non blocca le scritture durante la creazione
CREATE INDEX CONCURRENTLY idx_ordini_linee_ordine_id
ON ordini_linee (ordine_id)
INCLUDE (quantita, prezzo_unitario);
-- STEP 6: Verificare che il nuovo piano usi l'indice
EXPLAIN (ANALYZE, BUFFERS)
SELECT ol.ordine_id, SUM(ol.quantita * ol.prezzo_unitario)
FROM ordini_linee ol
WHERE ol.ordine_id IN (SELECT id FROM ordini WHERE stato = 'completato')
GROUP BY ol.ordine_id;
-- STEP 7: Misurare il miglioramento con pg_stat_statements
-- Attendere almeno 100 esecuzioni, poi confrontare mean_exec_time
SELECT pg_stat_statements_reset();
-- ... attendere ...
SELECT calls, mean_exec_time, max_exec_time FROM pg_stat_statements WHERE ...;
안티 패턴: 프로덕션에서 플래너를 변경하지 마세요.
피하다 SET enable_seqscan = off o SET enable_hashjoin = off 생산 중.
이러한 설정은 다른 상황에서 최적인 전략을 비활성화합니다. 올바른 해결책
항상 통계를 업데이트하거나, 적절한 인덱스를 생성하거나, 쿼리를 다시 작성하세요.
쿼리 계획을 위한 시각적 도구
복잡한 쿼리 계획의 경우 시각적 도구를 사용하여 분석을 가속화합니다. 가장 좋은 것은 무료입니다.
- explain.dalibo.com: EXPLAIN의 JSON 출력을 붙여넣고 대화형 그래프를 가져옵니다. 가장 느린 노드는 빨간색으로 강조 표시됩니다.
- pg겨자: 구체적인 제안이 포함된 분석 안내("X열의 인덱스 고려").
- pspg: EXPLAIN 출력에 읽기 쉽게 색상을 지정하는 psql용 호출기입니다.
-- Copiare l'output JSON per explain.dalibo.com
\pset format unaligned
\pset tuples_only on
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;
-- Output e un JSON singolo che puoi incollare direttamente
결론
EXPLAIN ANALYZE PostgreSQL의 내부 작동 방식을 보여주는 렌즈입니다.
순차 스캔과 인덱스 스캔을 읽는 방법을 배우고, 해시 조인이 더 나은 경우를 이해합니다.
중첩 루프 및 추정치와 현실 간의 불일치를 식별하여 개발자를 변화시킵니다.
"SQL 작성"에서 "데이터베이스 최적화"로.
다음 글에서는 고급 인덱싱에 대해 더 자세히 살펴보겠습니다: 부분 인덱스, 커버 인덱스, 시계열 검색의 경우 BRIN, 전체 텍스트 검색의 경우 GIN입니다. 다음은 결합된 도구입니다. 쿼리 계획을 이해하면 극적인 최적화가 가능해집니다.
시리즈: 데이터베이스 엔지니어링 및 최적화
- 기사 1: PostgreSQL 17/18 - 뉴스 및 성능
- 제2조(본): EXPLAIN ANALYZE - 쿼리 계획 읽기
- 조항 3: 고급 색인 생성 - 부분, 포함, BRIN, GIN
- 기사 4: 테이블 분할 - 수십억 개의 행 관리
- 기사 5: 연결 풀링 - PgBouncer 대 Pgpool-II