EXPLAIN ANALYZE: PostgreSQL クエリ プランの読み取りと最適化
運用環境では 8 秒かかるクエリですが、多くの場合、適切なインデックスを使用することで 50 ミリ秒に最適化できます。
少し書き直し。 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
主要なメトリクスを読み取る
コスト: 見積もりと現実
各ノードには 2 つの値のペアが表示されます。 (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');
Seq スキャン 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 - ニュースとパフォーマンス
- 第２条（本）: EXPLAIN ANALYZE - クエリプランの読み取り
- 第 3 条: 高度なインデックス作成 - 部分、カバリング、BRIN、GIN
- 第 4 条: テーブルのパーティショニング - 数十億行の管理
- 記事 5: 接続プーリング - PgBouncer と Pgpool-II