AI: SQL plan & optimization
This course reads PostgreSQL execution plans captured from a Rails app's development database. It covers the difference between EXPLAIN and EXPLAIN ANALYZE, and how to read cost estimates against measured times and rows. Three of the app's queries follow: a sequential scan with a sort, a bitmap index scan that still sorts, and a CTE feeding a window function. A wrong row estimate is traced to missing table statistics and fixed with ANALYZE. The last lessons cover prompting an AI tool to read a plan and checking its suggested fix.
What you'll learn
- ✓The difference between EXPLAIN and EXPLAIN ANALYZE, and why ANALYZE on a write performs it
- ✓Reading plan nodes such as Seq Scan, Bitmap Heap Scan, Sort, HashAggregate and WindowAgg
- ✓Why a used index can still leave a separate Sort step
- ✓How missing table statistics produce wrong row estimates, and how ANALYZE corrects them
- ✓Prompting an AI tool with a full plan and checking its answer against the plan and schema
What EXPLAIN and EXPLAIN ANALYZE show
"ActiveRecord querying: N+1 detection" taught reading this app's log/development.log for a burst of repeated queries: one label appearing dozens of times, identical except for one varying id. That's a pattern across many queries. This course starts one level down, at what a single query's execution…
Read the full lesson free →Curriculum
7 lessonsWhat EXPLAIN and EXPLAIN ANALYZE show
Explains how EXPLAIN shows estimates without running a query, while EXPLAIN ANALYZE executes it and adds measurements.
Reading a plan's anatomy: scan types, cost, and rows
Reads the plan for the active courses query: a Seq Scan, a Sort, cost estimates and measured rows.
An index scan that still needs a sort
Shows why a bitmap scan on a composite index loses its ordering and still needs a cheap Sort.
Reading joins and aggregates in a plan
Reads a CTE plan with HashAggregate, Sort, WindowAgg and an outer Filter applied after the ranking.
When the planner's estimate is wrong
Traces an estimate of 43 groups against 3 actual rows to missing statistics and fixes it with ANALYZE.
Prompting AI to read a query plan
Shows how to paste a full plan into an AI tool and check its answer with three tests.
Putting it together: from raw plan to a verified fix
Combines the workflow and checks any suggested fix by capturing a fresh EXPLAIN ANALYZE and reading its numbers.