Indexes & EXPLAIN

Without an index, PostgreSQL reads every row of a table to find matches (a sequential scan). An index is a sorted structure — like the index at the back of a book — that jumps straight to the matching rows.

EXPLAIN ANALYZE runs a query and shows the plan PostgreSQL chose and the real time taken. Look for Seq Scan on large tables, and compare estimated rows with actual rows.

Index the columns you filter, join and sort by. Primary keys and UNIQUE constraints are indexed automatically, but foreign keys are not — index them yourself. A multi-column index on (a, b) helps filters on a or on a AND b. Every index slows writes and uses disk, so add them for real queries, not "just in case".

indexes.sqlSQL

CREATE TABLE exam_entries (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    candidate  text     NOT NULL,
    centre     text     NOT NULL,
    subject_id int      NOT NULL,
    year       smallint NOT NULL,
    score      smallint NOT NULL
);

INSERT INTO exam_entries (candidate, centre, subject_id, year, score)
SELECT 'S' || lpad(g::text, 7, '0'),
       'P' || lpad((g % 4000)::text, 4, '0'),
       1 + g % 12,
       2016 + (g / 4000) % 10,
       (g * 37) % 101
FROM generate_series(1, 500000) AS g;

ANALYZE exam_entries;

EXPLAIN ANALYZE
SELECT * FROM exam_entries WHERE candidate = 'S0123456';
-- (Parallel) Seq Scan on exam_entries ... every row is read to find one

CREATE INDEX idx_exam_entries_candidate ON exam_entries (candidate);

EXPLAIN ANALYZE
SELECT * FROM exam_entries WHERE candidate = 'S0123456';
-- Index Scan using idx_exam_entries_candidate ... (a fraction of a millisecond)

CREATE INDEX idx_exam_entries_centre_year ON exam_entries (centre, year);

EXPLAIN ANALYZE
SELECT subject_id, avg(score)
FROM exam_entries
WHERE centre = 'P0420' AND year = 2025
GROUP BY subject_id;

-- Foreign keys are not indexed automatically
CREATE INDEX idx_results_subject ON results (subject_id);
CREATE INDEX idx_payments_student ON payments (student_id);

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'exam_entries';

Runs in your browser · PostgreSQL

Key points

EXPLAIN ANALYZE shows what the database actually did and how long it took.
Index columns used in WHERE, JOIN and ORDER BY — including foreign keys.
Column order matters in multi-column indexes; each index costs write speed.

Exercise

On exam_entries, measure WHERE year = 2024 AND score >= 90 ORDER BY score DESC LIMIT 10 before and after creating an index on (year, score). Then check whether the (centre, year) index helps a query that filters on year only, and explain why.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Before the index, PostgreSQL scans the whole table. An index on (year, score) lets it jump to 2024 and read the highest scores first, so ORDER BY score DESC LIMIT 10 stops after ten entries.

The (centre, year) index is sorted by centre first. A filter on year alone can't jump to the right place — it's like looking up a first name in a phone book sorted by surname — so PostgreSQL ignores the index or would have to read all of it.

index-solution.sqlSQL

EXPLAIN ANALYZE
SELECT candidate, score FROM exam_entries
WHERE year = 2024 AND score >= 90
ORDER BY score DESC LIMIT 10;
-- Before: (Parallel) Seq Scan on exam_entries ... reads every row

CREATE INDEX idx_exam_entries_year_score ON exam_entries (year, score);

EXPLAIN ANALYZE
SELECT candidate, score FROM exam_entries
WHERE year = 2024 AND score >= 90
ORDER BY score DESC LIMIT 10;
-- After: Index Scan Backward using idx_exam_entries_year_score ... a fraction of a millisecond

-- Does (centre, year) help a filter on year alone? Remove the new index to see.
DROP INDEX idx_exam_entries_year_score;
EXPLAIN SELECT count(*) FROM exam_entries WHERE year = 2024;
-- Seq Scan: the index's first column (centre) is not filtered

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding