TopolearnYou can't tune a query you can't read the plan of; EXPLAIN is the main tool for database performance work.
SQL says what you want, not how to get it. The query planner (optimizer) enumerates possible plans — which index or scan to use, which order to join tables, which join algorithm — estimates the cost of each from table statistics, and runs the cheapest.
The building blocks you'll see in a plan:
- Scans: sequential scan (read everything), index scan (follow index to rows), index-only scan, bitmap scan (collect row locations from an index, then read pages in order).
- Joins: nested loop (for each outer row, look up matches — great when the outer side is small and the inner side indexed), hash join (build a hash table of the smaller input, probe with the other — great for large unsorted inputs and equality joins), merge join (walk two sorted inputs together).
- Sort, aggregate, limit nodes on top.
EXPLAIN shows the chosen plan with estimated rows and cost; EXPLAIN ANALYZE actually runs the query and adds actual rows and timings. The single most useful thing to look for is a big gap between estimated and actual rows: the planner chose based on a wrong guess. Fixes include refreshing statistics (ANALYZE), adding a missing index, rewriting a predicate so it can use an index, or extended statistics for correlated columns.
Plans change as data grows, so a query that was fast at launch can flip to a different plan later.
Example
CREATE TABLE author (id int PRIMARY KEY, name text);
CREATE TABLE book (id int PRIMARY KEY, author_id int REFERENCES author(id), title text);
INSERT INTO author SELECT g, 'a' || g FROM generate_series(1, 1000) g;
INSERT INTO book SELECT g, 1 + g % 1000, 't' || g FROM generate_series(1, 100000) g;
CREATE INDEX book_author_idx ON book (author_id);
ANALYZE author; ANALYZE book;
EXPLAIN ANALYZE
SELECT a.name, b.title FROM author a JOIN book b ON b.author_id = a.id
WHERE a.id = 5;
-- Nested Loop (rows=100) -- one author, then index lookups into book
-- -> Index Scan using author_pkey on author a (rows=1)
-- -> Bitmap Heap Scan on book b (rows=100)
EXPLAIN SELECT a.name, count(*) FROM author a JOIN book b ON b.author_id = a.id
GROUP BY a.name;
-- HashAggregate -> Hash Join (big inputs: build a hash table of author)Remember
- The planner picks the cheapest plan using estimated row counts
- Nested loop for small outer + indexed inner; hash join for big equality joins
- Compare estimated vs actual rows in EXPLAIN ANALYZE to find bad plans
Watch out for
- Running EXPLAIN ANALYZE on a data-modifying query outside a rolled-back transaction: it really executes.