EXPLAIN & Query Performance
Read the PostgreSQL query plan with EXPLAIN, see real costs with EXPLAIN ANALYZE, and learn when an index turns a 30-second query into a 30-millisecond one.
- EXPLAIN
- EXPLAIN ANALYZE
- INDEX
- performance
- planner
Use this when you need to:
- Diagnose a slow report — which join is the bottleneck? where is the planner choosing a sequential scan?
- Validate that a query uses an existing index instead of falling back to a full-table scan
- Compare query plans before and after a refactor to confirm you actually made it faster
- Catch accidental full-table scans on large tables before they hit production
A query that returns the right answer in 30 seconds is wrong if a dashboard needs it in 100ms. EXPLAIN shows the plan PostgreSQL chose to run your query — the steps, their estimated row counts, and the join order. EXPLAIN ANALYZE actually executes the query and reports real timings. Reading these plans is the single highest-leverage skill for moving from "writes SQL" to "tunes SQL".
EXPLAIN — See the Plan
EXPLAIN SELECT p.id, p.last_name, COUNT(*) AS course_count FROM ods_person p JOIN ods_stu_course_sec scs ON scs.scs_student = p.id WHERE scs.scs_term = '2025FA' GROUP BY p.id, p.last_name; -- Output (read bottom-up): -- HashAggregate (cost=56.06..58.26 rows=220 width=23) -- Group Key: p.id -- -> Hash Join (cost=20.23..53.81 rows=450 width=15) -- Hash Cond: ((scs.scs_student)::text = (p.id)::text) -- -> Index Scan using ods_stu_course_sec_term_idx on ods_stu_course_sec scs (cost=0.28..32.66 rows=450 width=8) -- Index Cond: ((scs_term)::text = '2025FA'::text) -- -> Hash (cost=17.20..17.20 rows=220 width=15) -- -> Seq Scan on ods_person p (cost=0.00..17.20 rows=220 width=15)
Read EXPLAIN bottom-up: the deepest nodes run first, each parent consumes its children. Here PostgreSQL reads all 220 people with a Seq Scan and builds a hash table from them, uses an index on scs_term to fetch only the 450 Fall 2025 registrations, joins the two, then groups. The cost numbers are arbitrary planner units, and yours may differ slightly as table statistics change — focus on the SHAPE (which scans, which joins) and the row estimates first, costs second.
EXPLAIN ANALYZE — Real Timings
EXPLAIN alone shows estimates. EXPLAIN ANALYZE runs the query and reports actual rows + actual milliseconds for every step — letting you spot where the planner guessed wrong.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ods_stu_course_sec WHERE scs_term = '2025FA'; -- Look for these red flags: -- "Seq Scan" on a large table with a selective filter (missing index) -- "actual rows" wildly different from "rows" estimate (stale stats; run ANALYZE) -- "Rows Removed by Filter" much greater than rows returned (filter applied late) -- "loops=N" with N high on a Nested Loop (consider a Hash Join instead)
When to Add an Index
A B-tree index on a column is worth adding when the query filters that column to a small fraction of rows (< 10% of the table is a good rule of thumb), and the table has more than a few thousand rows. Indexes also speed up JOINs, ORDER BY, and the WHERE side of correlated subqueries. The practice database already has the indexes the plan above uses, and learners cannot create new ones there, so the timings below are illustrative: what the same change looks like on a production-sized registration table.
-- Before: Seq Scan on a registration table with 5M rows -- (illustrative timings; the practice copy has 1,980 rows) EXPLAIN ANALYZE SELECT COUNT(*) FROM ods_stu_course_sec WHERE scs_student = '0100042'; -- Execution Time: 320 ms -- Add an index on the filter column -- (the practice database already has this one: ods_stu_course_sec_student_idx) CREATE INDEX ods_stu_course_sec_student_idx ON ods_stu_course_sec (scs_student); -- After: Index Scan returns the same rows EXPLAIN ANALYZE SELECT COUNT(*) FROM ods_stu_course_sec WHERE scs_student = '0100042'; -- Execution Time: 1.4 ms -- Composite index for queries that filter on multiple columns together CREATE INDEX ods_stu_course_sec_term_student_idx ON ods_stu_course_sec (scs_term, scs_student);
Indexes are not free — every INSERT/UPDATE/DELETE has to maintain them. On a write-heavy table, only add indexes that pay for themselves on the read side. Use pg_stat_user_indexes to find indexes that are never used.
Cheap Wins
-- 1. SELECT only the columns you actually need (avoid SELECT *) -- 2. Push filters down: WHERE before JOIN beats JOIN then filter -- 3. Avoid functions on indexed columns: -- BAD : WHERE LOWER(email) = '[email protected]' -- index unusable -- GOOD: WHERE email ILIKE '[email protected]' -- or store lowercased -- 4. Use EXISTS instead of COUNT(*) > 0 when checking existence: -- BAD : SELECT (SELECT COUNT(*) FROM e WHERE e.sid = s.id) > 0 -- GOOD: SELECT EXISTS (SELECT 1 FROM e WHERE e.sid = s.id) -- 5. Run ANALYZE after big data loads so the planner has fresh stats
On your own institution's database, a retention dashboard runs the same student-cohort query every minute and is taking 8 seconds. The query joins the course-registration table (ods_stu_course_sec here) to the person table (ods_person) filtered by term (scs_term). Run EXPLAIN ANALYZE on it, identify the Seq Scan on the larger table, add an index that eliminates it, and re-run EXPLAIN ANALYZE to confirm the plan now uses an Index Scan and the execution time drops below 100ms. Investigation steps: 1. EXPLAIN ANALYZE SELECT ... — note total ms and look for Seq Scan 2. Identify the filtered column that narrows the table the most (the term column is usually a good candidate for time-bounded queries) 3. CREATE INDEX ... ON ods_stu_course_sec (scs_term); 4. EXPLAIN ANALYZE the same SELECT — confirm Index Scan replaces Seq Scan and total time is dramatically lower 5. If still slow, add a composite index covering BOTH the filter and join column: (scs_term, scs_student)
Practise this on real university data
Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.