Query Profiling

By the end of this lesson you'll be able to find the slow query that's actually hurting you, read its execution plan, fix it with an index or rewrite, and verify the win — using the right tool for PostgreSQL, MySQL, or SQL Server. This is how senior engineers stop guessing and start measuring.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

The Profiling Mindset

"Profiling" means measuring where a query spends its time so you fix the real bottleneck instead of a guess. The cardinal rule: measure first, optimise second. Every engine gives you two kinds of tool — an aggregator that ranks all your queries by cost, and a plan reader that dissects one query.

A doctor doesn't operate on a hunch. First a chart of all patients flags who is sickest (that's pg_stat_statements / the slow log / Query Store), then a scan shows what's wrong inside that one patient (that's EXPLAIN ANALYZE / the execution plan). You treat, then re-scan to confirm. Same four steps, every time.

Those four steps are the spine of this whole lesson:

• Find the slow query — rank by total time, not the single slowest run.
• Read its plan — find the costliest node and why it's costly.
• Fix it — add or fix an index, refresh statistics, or rewrite the query.
• Verify — re-run the plan and confirm the numbers actually fell.

1. Find — rank queries by total time

The instinct is to hunt for the query with the worst single run. That's a trap. The query that hurts is the one whose cumulative time is highest: calls × average . pg_stat_statements records exactly that, grouping queries by shape (the literal values are stripped out), so thousands of executions roll up into one rankable row.

2. Read — EXPLAIN (ANALYZE, BUFFERS)

Once you know which query, ask the engine how it runs it. Plain EXPLAIN shows the planner's guess . EXPLAIN ANALYZE actually executes the query and reports the real timings and row counts, and BUFFERS adds the I/O story: which pages came from memory versus disk.

A plan is a tree of nodes , read inside-out: the deepest, most-indented node runs first and feeds its parent. Here's realistic output for that customers/orders query before any tuning. Read it slowly — every number is a clue:

Walk through what each line is screaming at you:

• Estimated vs actual rows. The Seq Scan on orders says rows=512 (the planner's guess) but actual rows=486110 . A ~1000× mismatch means the planner is flying blind — its statistics are stale . It chose a sequential scan because it thought only 512 rows matched; in reality almost half a million did.
• The costliest node. Subtract a node's start time from its end time. The Seq Scan / Hash Join branch runs from ~120 ms to ~690 ms — roughly 570 ms of the 813 ms total . That sequential scan over orders is the bottleneck; the Sort and Limit are cheap by comparison.
• Buffers (I/O). shared hit=44 means 44 pages were already in cache; read=18102 means 18,102 pages were pulled from disk . Disk reads are orders of magnitude slower than cache hits — a sea of read is a flashing sign that the query is scanning data it shouldn't have to touch.
• Wasted work. Rows Removed by Filter: 13290 plus the huge scan means the engine is reading rows only to throw them away. An index on the filtered column would let it skip straight to the matches.

For any plan, ask: (1) Is any actual rows wildly different from its estimate? → stale stats, run ANALYZE . (2) Is there a big Seq Scan with high shared read on a table you filter or join? → it wants an index. Those two questions catch the majority of slow queries.

Your Turn: diagnose the plan

No SQL to write here — read the EXPLAIN ANALYZE output in the comments and fill the blanks with the slow node and why it's slow. The answer key is in the comments so you can self-check.

3. Fix — refresh stats, then index

The plan gave you two leads: stale statistics and a missing index. Apply them cheapest-first. ANALYZE resamples the table so the planner's estimates match reality — that alone sometimes flips a bad plan into a good one. If a scan is still happening, an index lets the engine jump straight to matching rows instead of reading the whole table.

4. Verify — prove it got faster

A fix you didn't measure is a guess you got lucky with. Re-run the identical EXPLAIN (ANALYZE, BUFFERS) and check three things: the Seq Scan became an Index Scan , shared read dropped sharply, and actual rows now lines up with the estimate. Then reset pg_stat_statements so the next day's numbers measure the new world, not the old.

Here's the same query's plan after ANALYZE and the index — note the node, the buffers, and the time:

From 813 ms to 9 ms : the Seq Scan is now an Index Scan , read collapsed from 18,102 pages to 3, and the estimate (4,800) finally matches actual (4,821). That's a verified win — not a hopeful one.

Your Turn: find the top offender

Two blanks. Complete the pg_stat_statements query so it returns the single query pattern with the highest total execution time.

5. The same loop in MySQL

MySQL changes the tools, not the method. To find , use the slow query log (writes any query over long_query_time to a file) or query the Performance Schema , which aggregates by digest just like pg_stat_statements . To read , MySQL 8.0+ has its own EXPLAIN ANALYZE ; in plain EXPLAIN the type column is the headline — ALL means a full table scan, while ref / eq_ref / const mean it's using an index.

6. The same loop in SQL Server

SQL Server's Query Store is a built-in flight recorder: switch it on once and it keeps every query's plans and runtime stats over time, which makes it brilliant for spotting a query that regressed after a deploy. To find , rank sys.query_store_runtime_stats by total duration. To read , capture the actual execution plan ( SET STATISTICS XML ON , or "Include Actual Execution Plan" in SSMS) and look at the operator with the highest cost percentage — a thick connecting arrow means a lot of rows are flowing, often the sign of a missing index.

Common Mistakes (and the fix)

• Optimising without measuring: adding indexes "that feel right" before reading a plan. Every index slows down writes and uses disk. Profile first — let EXPLAIN ANALYZE tell you what the query actually does.
• Estimated rows ≠ actual rows: a large gap is almost always stale statistics , not a bug in your query. Run ANALYZE table; (PostgreSQL) or ANALYZE TABLE table; (MySQL) before you reach for anything fancier.
• Ignoring buffers / I/O: a query can look fast in CPU time yet hammer the disk. High shared read (or avg_logical_io_reads ) means you're reading pages you shouldn't — usually a missing index, not slow hardware.
• Profiling a cold cache: the first run reads everything from disk and looks terrible; the second run is all cache hits and looks great. Run a query 2–3 times and read the warm numbers, or you'll "fix" a problem that was just cold storage.
• Forgetting EXPLAIN ANALYZE executes: on an UPDATE / DELETE it really changes data. Wrap it: BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;

📘 Quick Reference

Job

PostgreSQL

MySQL

SQL Server

Read EXPLAIN plans inside-out: the deepest node runs first. Watch for Seq Scan (PG) / type: ALL (MySQL) on big tables, estimate-vs-actual gaps, and high disk reads.

Frequently Asked Questions

Q: What's the difference between EXPLAIN and EXPLAIN ANALYZE ?

Plain EXPLAIN shows the planner's predicted plan and costs without running anything. EXPLAIN ANALYZE actually executes the query and reports real timings and row counts — which is the only way to catch estimate-vs-actual mismatches. Because it runs the statement, be careful with writes.

Q: My query is fast in EXPLAIN ANALYZE but slow in production. Why?

Usually caching. Your test run warmed the cache, so everything was a buffer hit; production hits cold pages, or a different parameter value matches far more rows. Check BUFFERS for high shared read , and test with realistic parameter values, not a hand-picked easy one.

Q: Should I optimise the slowest query or the most frequent one?

Rank by total time = calls × average. A fast query run millions of times usually beats a slow query run rarely. That's exactly why pg_stat_statements , Performance Schema, and Query Store all let you sort by cumulative cost.

Q: pg_stat_statements isn't there — where is it?

It's an extension that must be preloaded: add pg_stat_statements to shared_preload_libraries in postgresql.conf , restart, then run CREATE EXTENSION pg_stat_statements; . Until then the view simply doesn't exist.

Mini-Challenge: profile & fix a timeout

Put the whole loop together — diagnose the root cause, write the command to confirm it, and propose the index. The expected fix is in the comments so you can check your reasoning.

🎉 Lesson Complete

• ✅ The loop is always the same: find → read → fix → verify
• ✅ Find by total time — pg_stat_statements , the slow log, or Query Store
• ✅ Read with EXPLAIN (ANALYZE, BUFFERS) ; watch estimate-vs-actual rows, the costliest node, and disk reads
• ✅ A big estimate/actual gap means stale stats → ANALYZE ; a big Seq Scan with high reads wants an index
• ✅ Always verify on a warm cache, then reset your counters
• ✅ Next: Buffer Pool & Memory — why those cache hits and disk reads happen in the first place

Practice quiz

Continue this course

• Previous: Analytical SQL
• Next: Buffer Pool & Memory