---
title: "How to explain EXPLAIN ANALYZE execution plans in PostgreSQL and MySQL."
description: "Highlight PostgreSQL EXPLAIN (ANALYZE, BUFFERS) or MySQL EXPLAIN outputs and identify sequential table scans, bad row estimates, and missing indexes."
canonical_url: "https://vezz.me/use-cases/explain-database-query-execution-plans/"
type: "pseo-use-cases"
category: "use-cases"
topic: "Database Performance & SQL"
intent: "Best for backend engineers, database administrators, and data analysts diagnosing slow SQL queries directly from logs or query explainers."
---

# How to explain EXPLAIN ANALYZE execution plans in PostgreSQL and MySQL.

Highlight PostgreSQL EXPLAIN (ANALYZE, BUFFERS) or MySQL EXPLAIN outputs and identify sequential table scans, bad row estimates, and missing indexes.

> **Best fit:** Best for backend engineers, database administrators, and data analysts diagnosing slow SQL queries directly from logs or query explainers.

## Live Webpage Walkthrough

- **Source:** PostgreSQL EXPLAIN (ANALYZE, BUFFERS) Query Plan Output (`explain.dalibo.com/plan/example-seq-scan`)
- **Recommended Context Mode:** Detailed

**Highlighted Passage:**
> Hash Join  (cost=4215.10..18920.45 rows=1250 width=84) (actual time=48.210..312.450 rows=820 loops=1)
  Hash Cond: (o.user_id = u.id)
  Buffers: shared hit=1210 read=8420
  ->  Seq Scan on orders o  (cost=0.00..12890.00 rows=482000 width=48) (actual time=0.035..184.210 rows=482000 loops=1)
        Filter: (status = 'pending'::text)
        Rows Removed by Filter: 472000
  ->  Hash  (cost=3890.10..3890.10 rows=26000 width=36) (actual time=14.120..14.120 rows=26000 loops=1)

**User Question (Shortcut):** "Where is the bottleneck in this query plan and what index will fix it?"

**Vezz me On-Screen Answer:**
1. **The Bottleneck**: The **`Seq Scan on orders o`** took 184ms and read **8,420 disk pages** because it scanned all **482,000 rows** sequentially—only to discard **472,000 rows** (`Rows Removed by Filter`)!
2. **Why it's slow**: Only 10,000 orders are `'pending'` (2% of the table), yet Postgres had to do a full table scan.
3. **The Fix**: Add a partial index on pending orders:  
   `CREATE INDEX idx_orders_pending ON orders (user_id) WHERE status = 'pending';`  
   This shrinks the index to just 10k rows and replaces the 184ms Seq Scan with an instantaneous Index Scan.

**Suggested Follow-Ups:**
- "Why did the query planner choose a Hash Join instead of a Nested Loop here?"
- "What does `Buffers: shared hit=1210 read=8420` tell us about buffer cache hit ratio?"

## Step-by-step workflow

1. **Highlight the EXPLAIN ANALYZE output in your browser**: Select the tree output inside pgAdmin, DBeaver web viewers, Datadog Database Monitoring, or terminal logs.
2. **Ask for the node with the highest actual time and disk reads**: Press your shortcut and ask Vezz me which node consumed the most actual time and where estimated rows differed wildly from actual rows.
3. **Request the exact composite or partial DDL index statement**: Ask Vezz me for the specific `CREATE INDEX` syntax that targets the filter conditions and join keys.

## Decode nested execution plans in 10 seconds

Reading raw PostgreSQL or MySQL query plans requires parsing cost numbers, loop multipliers, and buffer cache hits.

- Identifies costly Sequential Scans filtering out 90%+ of rows that should use an index
- Spots row-estimation discrepancies (e.g., planner estimated 1 row but got 50,000) that trigger catastrophic Nested Loops
- Suggests exact index definitions including partial indexes, covering indexes (`INCLUDE`), and expression indexes

## Diagnosing Slow Queries

Comparing in-page query plan breakdown with mental parsing and GUI visualizers.

| Approach | Strengths | Tradeoffs |
| --- | --- | --- |
| Vezz me on EXPLAIN Text | Isolates the single slowest node, explains row-filter waste, and outputs index DDL in 10s | Requires pasting or highlighting the plan text |
| GUI Plan Visualizers | Drawing colorful tree graphs | Still requires you to know how to interpret buffer reads and cost formulas |
| Manual line-by-line reading | Deep DB tuning mastery | Easy to overlook loop multipliers (`loops=1000`) that multiply execution time 1,000x |

## Frequently asked questions

### Does Vezz me work on both PostgreSQL and MySQL query plans?

Yes! It accurately parses PostgreSQL `EXPLAIN (ANALYZE, BUFFERS)` trees as well as MySQL 8.0 `EXPLAIN ANALYZE` and traditional tabular format.

### Can it diagnose lock contention and vacuum issues?

Yes. Highlight autovacuum logs, lock wait alerts, or buffer cache statistics and ask Vezz me for the operational root cause.

### Which preset should I use for database queries?

Use our Regex & SQL Deconstructor preset or Senior Engineer Code Reviewer preset.

## Related guides

- [Regex & SQL Deconstructor Preset](https://vezz.me/prompts/regex-and-sql-deconstructor/): Deconstruct SQL queries, CTEs, and window functions token-by-token.
- [Decode Regex, SQL & Config Files](https://vezz.me/use-cases/decode-regex-sql-and-config-files/): General workflow for parsing complex database and syntax patterns.
- [Senior Engineer Code Reviewer Preset](https://vezz.me/prompts/senior-engineer-code-reviewer/): Review queries for indexing, NULL traps, and N+1 query patterns.
- [Vezz me for Software Developers](https://vezz.me/for/software-developers/): Browser workflows for backend and database engineers.