Technical interview guide

Indexing & Query Performance

Why some queries are instant and others scan the whole table — and how an index (usually a B-tree) changes that.

Read: 25 min
Practice MCQs: 25
Interview QA: 25
Edition: v3
Editorial status: Reviewed
Relevant for: AI EngineerBackend EngineerBusiness Intelligence DeveloperCloud ArchitectComputational BiologistCybersecurity EngineerData AnalystData ArchitectData EngineerData ScientistForward Deployed Engineer (FDE)Full-Stack DeveloperSoftware EngineerSolutions ArchitectTechnical Product Manager

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Interview QA

Treat each question like a live interview question: answer out loud first (structure, assumptions, tradeoffs), then open the model answer to spot gaps and rehearse a tighter follow-up.

Curated: 2026-10-05 · Written: 2026-09-11 · Reviewed: 2026-10-03

QA-1

Explain what an index is and why you would not index every column.

QA-2

What is sargability, and how does it affect index usage?

QA-3

How do you choose the column order for a composite index?

QA-4

Walk through how you would diagnose a slow query.

QA-5

Explain index-only scans and when they do not deliver the expected benefit.

QA-6

When would you use a partial index?

QA-7

How do you decide between index types beyond the default B-tree?

QA-8

How would you add an index to a large production table safely?

QA-9

Why might a query that used an index yesterday stop using it today?

QA-10

How do you find indexes that should be removed?

QA-11

Explain how pagination affects query performance and what you would recommend.

QA-12

What is the relationship between vacuum, bloat and index performance?

QA-13

How would you index a table that is written constantly and read rarely?

QA-14

Explain what statistics the planner uses and how they go wrong.

QA-15

A query is fast for most customers and slow for one. How do you investigate?

QA-16

How would you decide whether a slow query needs an index or a rewrite?

QA-17

Explain how joins are executed and what that means for indexing.

QA-18

How do you set up monitoring so query performance problems are found before users report them?

QA-19

What would you check before concluding that a database needs bigger hardware?

QA-20

Explain how you would test that a performance fix actually worked.

QA-21

How would you explain to a product manager why a feature's query is slow?

QA-22

What performance considerations apply to a table that grows without bound?

QA-23

Explain the difference between an index scan and a bitmap scan.

QA-24

How would you review a colleague's proposed index?

QA-25