Query optimization
On this page 8 topics
Overview
Topics
- EXPLAIN ANALYZE
- execution plans
- selectivity
- statistics
Learn first
4 h of practice (estimate)
Learning outcomes
- Read
EXPLAIN (ANALYZE, BUFFERS)output: scan types, join algorithms, estimates vs actuals. - Explain selectivity, statistics, and why the planner chooses a sequential scan.
- Find slow queries with
pg_stat_statementsand fix them by rewriting or indexing. - Recognize ORM-generated queries in plans and fix them at the source.
Lessons
Lab and exercises
Lab
MID-013-LAB1 · Slow query clinic
Outcomes: 1–4 · Time: ~3 h On a loaded Shop database (10 M orders), identify the five slowest statements with pg_stat_statements, including one ORM-generated query (N+1 or SELECT * from a repository). Fix each by rewriting, indexing, or changing the application code. Acceptance: before/after EXPLAIN (ANALYZE, BUFFERS) plans and timings recorded for each of the five.
Exercises (assessment: plan-reading test)
| ID | Outcome | Task |
|---|---|---|
| MID-013-EX01 | 1 | Annotate a given plan: scan types, join algorithm, the node with the largest time, estimate vs actual gaps. |
| MID-013-EX02 | 1 | Explain what loops=1000 and Buffers: shared read=50000 tell you. |
| MID-013-EX03 | 2 | Explain why WHERE active = true (95% true) uses a sequential scan, and what would change for active = false. |
| MID-013-EX04 | 2 | Fix a bad estimate on correlated columns using CREATE STATISTICS. |
| MID-013-EX05 | 3 | Reset pg_stat_statements, run a mixed workload, and list the top three queries by total time. |
| MID-013-EX06 | 3 | Rewrite WHERE date_trunc('day', created_at) = ... and a deep OFFSET query into index-friendly forms. |
| MID-013-EX07 | 4 | Show the SQL a Spring Data repository method generates (SQL logging) and fix an N+1 with a join fetch or projection. |
Show solutions
- EX01 Name the nodes bottom-up; the slowest node is the one whose actual time minus children is largest; flag nodes where estimated rows differ from actual by 10x or more.
- EX02 The node ran 1000 times (multiply per-loop time by 1000); about 50 000 pages (about 400 MB at 8 kB) were read from outside shared buffers.
- EX03 An index would not narrow 95% of the rows, so the sequential scan is cheaper; for
false(5%) an index or partial index can win. - EX04
CREATE STATISTICS s (dependencies) ON a, b FROM t; ANALYZE t;then re-check estimates. - EX05
SELECT pg_stat_statements_reset();then query the view ordered bytotal_exec_time. - EX06 Half-open range on the raw column; keyset pagination
WHERE (created_at, id) < (?, ?). - EX07
spring.jpa.show-sqlor SQL logger; replace lazy loop withJOIN FETCH, entity graph, or a DTO projection. - The "
EXPLAINwithoutANALYZE" mistake is exercised in EX01 and EX02.
Quiz
Outcomes: O1 reading plans · O2 selectivity and statistics · O3 finding and fixing slow queries · O4 ORM queries.
A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.
Practical part
Plan-reading test: given three plans, name the bottleneck node and the fix; plus exercises MID-013-EX01 to EX07 and the lab. Pass: quiz 8/10, test bottlenecks identified correctly, and lab before/after evidence for all five queries.
Common mistakes
EXPLAIN without ANALYZE; functions on indexed columns; OFFSET pagination over deep pages.
Assessment
Plan-reading test.
Career relevance
The MID graduation gate's performance bottleneck.