This page needs a newer browser to run its tools. You can still read everything here.

Query plans and indexes interview questions

Interview questions on query plans and indexes: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

15 questions: 5 warm-ups and 10 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

How do EXPLAIN and EXPLAIN ANALYZE differ, and when is EXPLAIN ANALYZE unsafe to run?

Answered in the lesson Reading EXPLAIN ANALYZE in PostgreSQL 18
Fresher

Why can an index-only scan in PostgreSQL still read the table?

Answered in the lesson B-tree indexes: dedup, INCLUDE and skip scan
Fresher

What is a partial index, and when will PostgreSQL use it?

Answered in the lesson Index design: partial, expression, write cost
Fresher to mid

When would you choose GIN over a B-tree in PostgreSQL?

Answered in the lesson GIN, GiST, SP-GiST, BRIN, hash and bloom
Fresher to mid

What does CREATE INDEX CONCURRENTLY do differently from CREATE INDEX, and what does it cost?

Answered in the lesson Building and checking indexes online

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A join of orders to customers takes 40 seconds. EXPLAIN ANALYZE shows a Nested Loop whose outer scan estimated 20 rows and returned 2,000,000, and whose inner index scan ran with loops=2000000. Explain what went wrong and how you would fix it without forcing a plan.
What it tests

Comparing estimated with actual rows, tracing a poor join choice to a misestimate, and fixing what the planner knows with ANALYZE, statistics targets or extended statistics.

First step

Compare estimated and actual rows node by node: the outer scan expected 20 rows and produced two million, so a nested loop of cheap index lookups looked best and ran two million times. Then find why the estimate was low, such as stale statistics or two correlated conditions multiplied as if independent, and fix that.
Follow-ups
- How would you show that the two conditions of the filter are correlated?
- What does CREATE STATISTICS change for the planner, and what does it not change?
- When would you turn off nested loops in a session, and why only there?
From the lesson Reading EXPLAIN ANALYZE in PostgreSQL 18
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly report sorts and groups 50 million rows. Its EXPLAIN ANALYZE shows Sort Method external merge with Disk 3.1GB and a HashAggregate with Batches 256. The server has 64 GB of RAM and work_mem = 4MB for everyone. What do you change, and what do you check before and after?
What it tests

Reading the spill lines of a plan, sizing memory for one query instead of the whole server, and knowing hash_mem_multiplier and how to watch temporary files.

First step

Confirm the spill from the plan's Disk, Batches and temp buffer lines, then give the report more memory where it runs, with SET LOCAL work_mem in its transaction, sized from the plan, never for every session. Check the server's free memory and how many such reports run at once before choosing the value.
Follow-ups
- Why does the hash aggregate get more memory than the sort at the same work_mem?
- How do you find every query that spills, not only this one?
- When is more memory the wrong fix?
From the lesson Reading EXPLAIN ANALYZE in PostgreSQL 18
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A tickets table gets three frequent queries: an owner's open tickets newest first with their titles, an owner's tickets in a date range, and a count of tickets per status for one owner. You may create at most two indexes. Which do you create, and how do you prove they work?
What it tests

Ordering index columns by equality, range and sort, using INCLUDE for returned columns, weighing write cost against the number of indexes, and checking the plans.

First step

Write down each query's equality, range, sort and returned columns. All three start with owner, so one index led by owner can serve several of them; decide what follows it from the sort and the range, add returned columns as INCLUDE only if an index-only scan is likely, then check every plan with EXPLAIN ANALYZE.
Follow-ups
- Why not one index per query?
- What would make you choose a partial index for the open tickets?
- How do Heap Fetches change your decision about INCLUDE?
From the lesson B-tree indexes: dedup, INCLUDE and skip scan
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A dashboard query uses an Index Only Scan, yet EXPLAIN ANALYZE shows Heap Fetches 480,000 for 500,000 rows and the query is no faster than before the covering index was added. Explain why and what you would do.
What it tests

Understanding the visibility map's role in index-only scans, how updates and VACUUM change it, and when a covering index is not worth its cost.

First step

Explain that an index-only scan skips the heap only for pages marked all-visible, so 480,000 fetches mean most pages have changed since the last VACUUM. Then check how often the table changes and when it was last vacuumed, and decide between vacuuming it more often and dropping the payload columns.
Follow-ups
- How do you see how much of the table is all-visible?
- Why might a plain VACUUM leave some heap fetches?
- When is the covering index the wrong tool for this table?
From the lesson B-tree indexes: dedup, INCLUDE and skip scan
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

After a sprint that added five indexes to a busy inventory table, its updates are slower, WAL volume has doubled and replicas lag at peak. Each index was added for a real query. How do you find which indexes cost the most, and what would you change?
What it tests

Knowing how inserts and updates maintain indexes, what HOT updates need, how to measure WAL per statement and per table, and how to trade query speed against write cost.

First step

Measure before choosing: check n_tup_upd against n_tup_hot_upd for the table, find which of the new indexes contain columns that the frequent updates change, and measure the WAL of a typical update with EXPLAIN (ANALYZE, WAL) inside a rolled-back transaction, with and without each suspect index.
Follow-ups
- Why can an index on a column that never changes still slow updates?
- What does fillfactor have to do with it?
- How would you serve the original queries with fewer indexes?
From the lesson Index design: partial, expression, write cost
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A 40 GB index on a payments table shows idx_scan = 0 in pg_stat_user_indexes on the primary. A colleague wants to drop it tonight to save disk and write time. What do you check first, and how do you drop it safely if it really is unused?
What it tests

Reading index usage statistics correctly, knowing what they do not cover, recognising indexes that enforce rules, and removing an index without blocking the table.

First step

Establish what the zero covers: when the statistics were last reset, whether the window includes month-end and other periodic jobs, and what the same index shows on every replica. Then check that it is not unique, a primary key or the support of a constraint, before planning DROP INDEX CONCURRENTLY.
Follow-ups
- Where do you see when the statistics were last reset?
- What would you do if a forgotten report needed the index after all?
- Is there a way to test the effect of the drop before doing it?
From the lesson Index design: partial, expression, write cost
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

An append-only log table holds two billion rows and its B-tree on logged_at takes 40 GB. Most queries ask for one hour or one day. A colleague proposes replacing the B-tree with BRIN. How do you decide, and what could go wrong?
What it tests

Knowing what BRIN stores and why it depends on physical order, checking correlation, reading lossy plans, and foreseeing the operations that break the order.

First step

Check that logged_at follows the physical order, with pg_stats.correlation near 1, and that the queries are ranges. Then build the BRIN index beside the B-tree, compare both plans for an hour and a day with EXPLAIN ANALYZE, looking at lossy heap blocks and Rows Removed by Index Recheck, before dropping anything.
Follow-ups
- Which events would break the correlation later?
- How would you choose pages_per_range?
- What happens to rows inserted after the last summarisation?
From the lesson GIN, GiST, SP-GiST, BRIN, hash and bloom
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A search page filters a 50-million-row listings table on any combination of eight optional fields: city, type, bedrooms, furnished, pets, parking, floor and owner. Users pick two or three at a time. How do you index it?
What it tests

Matching index methods to query patterns, knowing bitmap combinations of single indexes, multicolumn ordering limits and bloom indexes, and measuring instead of guessing.

First step

Collect the real combinations and their selectivity first. A multicolumn B-tree serves only combinations that start with its leading columns, so compare single-column B-trees combined by bitmap scans, a few multicolumn indexes for the most frequent combinations, and one bloom index over the equality fields, on realistic queries.
Follow-ups
- When does PostgreSQL combine two indexes for one query?
- What does a bloom index give up?
- How does the frequency of updates change your choice?
From the lesson GIN, GiST, SP-GiST, BRIN, hash and bloom
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A CREATE INDEX CONCURRENTLY on a 300 GB table started six hours ago and still has not finished, while the table's writes look normal. How do you see what it is doing, and what are your options?
What it tests

Reading pg_stat_progress_create_index phases, finding the transactions a build waits for, and knowing the safe ways to stop and retry a concurrent build.

First step

Read pg_stat_progress_create_index for the phase and its counters: a waiting phase with lockers_total above lockers_done means it is blocked by other transactions, a building phase with blocks_done rising means it is working. For waits, find the blocking session through current_locker_pid and pg_stat_activity.
Follow-ups
- What do you do with the blocking session, and how do you prevent the next one?
- What is left behind if you cancel the build, and how do you clean it up?
- How would you make the build itself faster next time?
From the lesson Building and checking indexes online
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Senior to staff

After an operating-system upgrade on a replica that was promoted, some customer searches by name return nothing, though the rows exist. Explain the likely cause, how you would prove it, and how you would repair the cluster with the least downtime.
What it tests

Understanding how collation changes corrupt text indexes, using amcheck to prove it, and repairing indexes online, with the prevention that follows.

First step

Suspect B-tree indexes on text whose libc collation sorts differently after the upgrade: the index order no longer matches the comparison rules, so lookups miss rows. Prove it with bt_index_check, heapallindexed on, on the indexes of the affected columns, then rebuild those that fail with REINDEX INDEX CONCURRENTLY.
Follow-ups
- Why did the problem appear only after the replica was promoted?
- How do you find every index that depends on a collation?
- What would prevent this on the next upgrade?
From the lesson Building and checking indexes online
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

Reading EXPLAIN ANALYZE in PostgreSQL 18 Free lesson, Pro answers: 10 minutes
B-tree indexes: dedup, INCLUDE and skip scan Free lesson, Pro answers: 10 minutes
Index design: partial, expression, write cost Free lesson, Pro answers: 10 minutes
GIN, GiST, SP-GiST, BRIN, hash and bloom Free lesson, Pro answers: 10 minutes
Building and checking indexes online Free lesson, Pro answers: 10 minutes

All PostgreSQL in Depth interview questions · The PostgreSQL in Depth track

Warm-up questions

How do EXPLAIN and EXPLAIN ANALYZE differ, and when is EXPLAIN ANALYZE unsafe to run?

Why can an index-only scan in PostgreSQL still read the table?

What is a partial index, and when will PostgreSQL use it?

When would you choose GIN over a B-tree in PostgreSQL?

What does CREATE INDEX CONCURRENTLY do differently from CREATE INDEX, and what does it cost?

Questions with model answers

A join of orders to customers takes 40 seconds. EXPLAIN ANALYZE shows a Nested Loop whose outer scan estimated 20 rows and returned 2,000,000, and whose inner index scan ran with loops=2000000. Explain what went wrong and how you would fix it without forcing a plan.

A nightly report sorts and groups 50 million rows. Its EXPLAIN ANALYZE shows Sort Method external merge with Disk 3.1GB and a HashAggregate with Batches 256. The server has 64 GB of RAM and work_mem = 4MB for everyone. What do you change, and what do you check before and after?

A tickets table gets three frequent queries: an owner's open tickets newest first with their titles, an owner's tickets in a date range, and a count of tickets per status for one owner. You may create at most two indexes. Which do you create, and how do you prove they work?

A dashboard query uses an Index Only Scan, yet EXPLAIN ANALYZE shows Heap Fetches 480,000 for 500,000 rows and the query is no faster than before the covering index was added. Explain why and what you would do.

After a sprint that added five indexes to a busy inventory table, its updates are slower, WAL volume has doubled and replicas lag at peak. Each index was added for a real query. How do you find which indexes cost the most, and what would you change?

A 40 GB index on a payments table shows idx_scan = 0 in pg_stat_user_indexes on the primary. A colleague wants to drop it tonight to save disk and write time. What do you check first, and how do you drop it safely if it really is unused?

An append-only log table holds two billion rows and its B-tree on logged_at takes 40 GB. Most queries ask for one hour or one day. A colleague proposes replacing the B-tree with BRIN. How do you decide, and what could go wrong?

A search page filters a 50-million-row listings table on any combination of eight optional fields: city, type, bedrooms, furnished, pets, parking, floor and owner. Users pick two or three at a time. How do you index it?

A CREATE INDEX CONCURRENTLY on a 300 GB table started six hours ago and still has not finished, while the table's writes look normal. How do you see what it is doing, and what are your options?

After an operating-system upgrade on a replica that was promoted, some customer searches by name return nothing, though the rows exist. Explain the likely cause, how you would prove it, and how you would repair the cluster with the least downtime.