Tuning starts with measurement. EXPLAIN (ANALYZE, BUFFERS) shows each step's real time and how many pages were read. Large gaps between estimated and actual rows usually mean outdated statistics — run ANALYZE. In production, the pg_stat_statements extension shows which queries use the most total time.
B-tree is the default index, but the right variant matters: a partial index covers only the rows you query (WHERE fee_balance > 0), an expression index matches a computed condition (lower(email)), and a covering index (INCLUDE) lets PostgreSQL answer from the index alone (an Index Only Scan).
BRIN indexes are tiny and suit huge, append-only tables where values follow physical order — such as timestamps in logs. GIN indexes serve jsonb, arrays and full-text search.
-- Partial index: only students who owe fees
CREATE INDEX idx_students_owing ON students (fee_balance) WHERE fee_balance > 0;
-- Expression index: case-insensitive email lookups
CREATE UNIQUE INDEX idx_students_email_lower ON students (lower(email));
SELECT id, full_name FROM students WHERE lower(email) = lower('Amina@Example.com');
-- Covering index: answer from the index alone
CREATE INDEX idx_exam_entries_candidate_cov ON exam_entries (candidate) INCLUDE (score);
VACUUM ANALYZE exam_entries;
EXPLAIN (ANALYZE, BUFFERS)
SELECT candidate, score FROM exam_entries WHERE candidate BETWEEN 'S0100000' AND 'S0100100';
-- Index Only Scan using idx_exam_entries_candidate_cov ... Heap Fetches: 0
-- BRIN index for a large time-ordered table
CREATE TABLE gate_log (
id bigint GENERATED ALWAYS AS IDENTITY,
card_no int NOT NULL,
logged_at timestamptz NOT NULL
);
INSERT INTO gate_log (card_no, logged_at)
SELECT g % 900, timestamptz '2026-01-01' + g * interval '10 seconds'
FROM generate_series(1, 1000000) AS g;
CREATE INDEX idx_gate_log_brin ON gate_log USING brin (logged_at);
ANALYZE gate_log;
EXPLAIN ANALYZE
SELECT count(*) FROM gate_log
WHERE logged_at >= '2026-02-01' AND logged_at < '2026-02-02';
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('gate_log', 'idx_gate_log_brin');Key points
- Measure with
EXPLAIN (ANALYZE, BUFFERS)andpg_stat_statementsbefore changing anything. - Partial, expression and covering indexes fit specific query patterns precisely.
- BRIN = tiny indexes for huge, naturally ordered tables; GIN for jsonb/arrays/text search.
Exercise
Find a query on exam_entries that does a sequential scan, design the smallest index that fixes it (consider partial and covering options), and compare index sizes with pg_relation_size.
Show solution
Try the exercise yourself first — then compare your approach with this one.
Take a report that only cares about top scores: "how many candidates per centre scored 90+ in 2025?". It scans the whole table. A partial index that stores only rows with score >= 90 answers it from a small fraction of the table, and is much smaller than a full index on the same columns.
EXPLAIN ANALYZE
SELECT centre, count(*) FROM exam_entries
WHERE year = 2025 AND score >= 90
GROUP BY centre;
-- (Parallel) Seq Scan on exam_entries ... every row is read
-- Smallest index for this query: only the high-scoring rows
CREATE INDEX idx_exam_entries_top ON exam_entries (year, centre) WHERE score >= 90;
-- For comparison: the same columns without the WHERE clause
CREATE INDEX idx_exam_entries_year_centre ON exam_entries (year, centre);
ANALYZE exam_entries;
EXPLAIN ANALYZE
SELECT centre, count(*) FROM exam_entries
WHERE year = 2025 AND score >= 90
GROUP BY centre;
-- Index Only Scan using idx_exam_entries_top ... much faster
SELECT indexrelid::regclass AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indexrelid::regclass::text IN ('idx_exam_entries_top', 'idx_exam_entries_year_centre');
DROP INDEX idx_exam_entries_year_centre; -- keep only the index the query needs