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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

"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:

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:

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.

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.

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary