How to Read Execution Plans: Solving the 'Slow Query' Mystery in Postgres and MySQL

Table of Contents

Why Your Query is Slow (And Why RAM Won’t Fix It)

I once spent an entire afternoon staring at a query that took 18.4 seconds to fetch just 452 rows. My first instinct was the classic developer reflex: ‘The server needs more RAM.’ Of course, doubling the memory did nothing. The database was still crawling, and I was flying blind. It’s easy to treat a database like a magic box where SQL goes in and data comes out. But when performance tanks, you have to see exactly how the engine is processing your request.

This is where the Execution Plan comes in. Think of it as the GPS instructions for your data. If you can’t read these instructions, you’re just guessing which index to add or which join to rewrite. After years of debugging PostgreSQL and MySQL, I’ve found that 90% of slow queries can be solved by understanding a few specific indicators in the EXPLAIN output.

What Exactly is an Execution Plan?

Before running commands, it helps to know what happens behind the scenes. When you send a query, the Optimizer evaluates your tables, row counts (statistics), and available indexes. It then calculates the ‘cost’ of different paths. Should it scan the whole table? Should it use an index? Should it join Table A to Table B first, or the other way around?

The Execution Plan is the final decision. It is a tree of operations where each node represents a specific step, like an ‘Index Scan’ or a ‘Hash Join’. Data flows from the leaves of the tree up to the root. Understanding this flow is the key to spotting efficiency leaks.

PostgreSQL: Getting the Real Story with EXPLAIN ANALYZE

In PostgreSQL, the basic EXPLAIN command shows what the optimizer thinks will happen. I rarely use it alone. Instead, I rely on EXPLAIN ANALYZE. This actually executes the query and provides the real-world timing.

-- My go-to command for performance debugging
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE u.email = '[email protected]';

When you review the output, prioritize these three metrics:

Actual Time: This is the literal measurement in milliseconds. If the ‘Actual Time’ is significantly higher than the ‘Cost’ estimate, your table statistics are likely stale.
Buffers: This tells you how much data was read from the shared buffer cache versus the disk. High ‘Read’ numbers indicate you are hitting the disk too hard.
Seq Scan vs. Index Scan: A ‘Seq Scan’ (Sequential Scan) on a table with 1 million rows means the database is reading every single block on the disk. This is usually your first target for an index.

MySQL: Visualizing the Optimizer’s Logic

MySQL 8.0 brought a massive improvement with EXPLAIN ANALYZE. It provides a tree-like output that is far easier to read than the old tabular format. It shows you exactly where the time is being spent during execution.

-- Modern MySQL 8.0 approach
EXPLAIN ANALYZE 
SELECT u.name, count(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id;

If you are working on an older version, the ‘type’ column is your best friend. Look for these specific values:

system/const: Excellence. The query found one row instantly.
eq_ref/ref: Good. The engine is using an index to find rows efficiently.
ALL: A Full Table Scan. This is the database equivalent of searching every page in a library for one specific quote. It’s almost always a bottleneck.

Common Bottlenecks and Warning Signs

When reviewing a plan, look for patterns that signal trouble. A common issue is a Nested Loop on two large tables. Imagine Table A and Table B both have 100,000 rows. A nested loop might try to perform 10 billion comparisons. Usually, a Hash Join is faster here. However, the optimizer might choose the slower loop if it incorrectly thinks one table is small because of outdated statistics.

Another warning sign is ‘Using temporary’ or ‘Using filesort’ in MySQL. This happens when the database can’t use an index to sort your ORDER BY or GROUP BY clause. It is forced to pull data into memory or disk to sort it manually. For large datasets, this can turn a 50ms query into a 5-second one.

The Data Import Workflow Tip

Performance issues often pop up during data migrations. When I need to convert CSV files to JSON for testing or imports, I use toolcraft.app/en/tools/data/csv-to-json. It runs entirely in your browser, keeping your data private. It’s a great way to quickly prepare mock data to verify that your execution plans scale as expected before you hit production.

Hands-on Practice: Fixing a Slow Query

Suppose a product search is taking 2.5 seconds. You run EXPLAIN and see this fragment:

-- PostgreSQL Output Fragment
Filter: (price > 150.00)
Rows Removed by Filter: 842,000
Sequential Scan on products  (cost=0.00..28000.00 rows=1200 width=64)

The ‘Rows Removed by Filter’ is the smoking gun. PostgreSQL had to read 842,000 rows just to find the 1,200 you needed. This is a perfect candidate for a Composite Index on (category_id, price). Once added, the plan should switch to an Index Scan, and the execution time will likely drop to under 10ms.

Four Rules for Better Performance

Run it twice: The first run reads from disk; the second reads from the buffer cache. Always use the ‘warm’ performance for your baseline.
Refresh your stats: In PostgreSQL, run ANALYZE; after large updates. In MySQL, use ANALYZE TABLE;. An optimizer is only as smart as the metadata it has.
Stop using SELECT *: Fetching unneeded columns can prevent ‘Index Only Scans.’ If all requested data is in the index, the database doesn’t even have to touch the main table heap.
Watch for Type Mismatches: If your column is a VARCHAR but you query it with WHERE id = 123 (an integer), the database may ignore the index to run a conversion function on every single row.

Conclusion

Mastering execution plans changed how I write code. I no longer hope a query will be fast; I can prove it. Make EXPLAIN a standard part of your local development. Any query with multiple joins or filters on large tables deserves a quick check. It takes a little practice to learn the jargon, but the performance gains are immediate. Your users—and your server’s CPU—will notice the difference.