EXPLAIN ANALYZE für Postgres: 2026 jede Zeile lesen

EXPLAIN ANALYZE returns a wall of text. Most engineers read the top operator and the slowest line and guess. The story is in the middle. Here’s a node-by-node guide to every operator you’ll meet in production, with the four red-flag patterns that diagnose 80% of slow queries in five seconds.

How to read the plan shape

Plans are trees, printed bottom-up. Indented children execute first, parents combine their results. Always run with three options:

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

ANALYZE actually runs the query (so do this on a non-production replica or wrap in a transaction + rollback). BUFFERS gives you the cache-hit story. VERBOSE adds qualifier expressions you sometimes need.

Operators you’ll see

• Seq Scan — read the whole table. Fine on small tables, alarming on big ones unless followed by a Filter that returns most rows.
• Index Scan — read the index, then the heap row by row. Wins when output is small relative to table.
• Index Only Scan — read the index alone (covered query). Best case for OLTP.
• Bitmap Heap Scan — gather index matches into a bitmap, then read heap pages once each. Wins for medium-cardinality predicates.
• Nested Loop — for each outer row, look up matching inner rows. Linear in outer × inner; murderous if outer is big and inner has no index.
• Hash Join — build a hash table from one side, probe with the other. Wins on large equijoins.
• Merge Join — both sides sorted, walk in lockstep. Wins when both sides come pre-sorted from indexes.
• Sort — reorder rows. Watch for “external merge Disk” — that’s a spill.
• Hash Aggregate — GROUP BY via hash table. Watch for “Disk: Nx” — also a spill.

Four red-flag patterns

• Rows estimate way off. “rows=10 actual=10000” means the planner has bad statistics. Run ANALYZE table; consider ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 for skewed columns.
• Lossy bitmaps. “Heap Blocks: exact=N lossy=M” with M > 0 meanswork_mem overflowed; bitmap downgraded from row-precision to page-precision. Raise work_mem for that session.
• External merge Disk. Sort or hash spilled to tempfile. Raise work_mem or rewrite to avoid the sort.
• Nested Loop on big inner without index. The classic O(n²) trap. Add the index on the join key, or force a hash join via SET enable_nestloop = off for the session and confirm.

BUFFERS: the column you should always include

Buffers: shared hit=1024 read=512 dirtied=8 written=8

• shared hit: pages found in shared_buffers (free, fast).
• shared read: pages fetched from disk (or OS page cache). The cost line.
• dirtied / written: rare on SELECTs but real on writes.
• Cache-hit ratio per query = hit / (hit + read). Below 0.9 sustained = working set exceeds shared_buffers.

FAQ