3.4 ★ core [J][A][I] 15 interview Q's

Indexes & query performance

B-tree indexes, the composite-index leftmost-prefix rule, covering indexes, when indexes hurt, reading EXPLAIN, and the N+1 query problem.

A query that’s correct can still be unusably slow. Indexes are the single biggest lever on read performance — and the leftmost-prefix rule is the single most common thing developers get wrong about them. This chapter is about making the database do less work, and proving it with EXPLAIN.

B-tree vs hash: why B-tree is the default

The default index is a B-tree (really a B+ tree) — the exact structure from the DSA Trees chapter, chosen for databases for the same reason: it’s shallow and high-fanout, so finding a row takes only a handful of disk reads (O(log n)), and because the leaf nodes are kept in sorted order and linked together, the database can walk a range efficiently. That’s the key property: a B-tree serves equality (= 5), ranges (BETWEEN, <, >), prefix matches (LIKE 'abc%'), and ORDER BY — all from one structure, because the data is already sorted.

A B+ tree index. Internal nodes hold only separator keys to route the search; all actual entries live in the leaf level, kept in sorted order and linked left-to-right. An equality lookup descends root → leaf in O(log n); a range scan finds the start leaf, then follows the leaf links sideways without ever climbing back up — which is why one structure serves both = and BETWEEN/ORDER BY.

A hash index maps a key to a bucket in O(1) average time, which sounds faster — but it only supports equality. It has no notion of order, so it can’t help a range scan or an ORDER BY at all. That single limitation is why B-tree is the default and hash indexes are a niche tool.

Index type	Equality (<code>=</code>)	Range / <code>ORDER BY</code>	Lookup cost	When
B-tree	yes	yes	`O(log n)`	the default — almost always this
Hash	yes	no	`O(1)` avg	rare; equality-only hot paths

The range/ORDER BY column is why B-tree wins by default.

Composite indexes & the leftmost-prefix rule

A composite index on (A, B, C) is sorted by A, then by B within equal A, then by C. Think of a phone book sorted by last name, then first name. You can look up by last name alone, or last + first — but you cannot efficiently find everyone with a given first name, because the book isn’t sorted that way.

That’s the leftmost-prefix rule: an index on (A, B, C) serves queries that filter on a leading prefix — A, (A, B), or (A, B, C) — but not B alone, nor (B, C), nor C.

Why the leftmost-prefix rule exists, on an index over (customer_id, status). Entries are sorted by customer_id first, then status within each customer. A filter on customer_id (or customer_id + status) lands on a contiguous block the engine can seek to. A filter on status alone is scattered across the whole index — the matching greens are interleaved everywhere — so there's nothing contiguous to seek, and the planner falls back to a full scan.

The leftmost-prefix rule — the #1 index mistake

Given an index on (customer_id, status):

-- USES the index (leading column present):
WHERE customer_id = 9
WHERE customer_id = 9 AND status = 'shipped'

-- CANNOT use it (skips the leading column):
WHERE status = 'shipped'

A filter on status alone falls back to a Seq Scan despite the index existing. Order columns by selectivity and usage: put equality/highly-selective columns first, range columns last. An index on (status, customer_id) would be the right choice if you mostly filter by status.

Covering indexes

If an index contains every column a query touches, the database never needs to visit the table — it answers entirely from the index. This is an index-only scan and it’s dramatically faster because it skips the table-heap lookup.

Turning a query index-only

For this query:

SELECT status, customer_id
FROM   orders
WHERE  customer_id = 9;

an index on just (customer_id) finds the three matching rows (orders 11, 12, 20) but must then fetch status from the table for each. An index on (customer_id, status) covers the query — both the filtered and selected columns live in the index — so it’s answered index-only. The rule of thumb: a covering index = the WHERE/JOIN/ORDER BY columns plus the SELECTed columns.

When indexes help vs hurt

Indexes are not free; the same structure that accelerates reads taxes every write. Reach for them deliberately:

Situation	Index?	Why
High-selectivity filter on a big table	yes	turns a full scan into a few-row seek
Column used in JOIN / WHERE / ORDER BY a lot	yes	the hot read path is what indexes are for
Low-cardinality column (boolean, 4-value status)	usually no	barely narrows; planner may scan anyway
Tiny table (tens of rows)	no	scanning beats the index indirection
Write-heavy table, rarely filtered column	no	every INSERT/UPDATE pays to maintain it

Indexes trade write cost + storage for read speed — add them where reads are hot and selective.

Write amplification. Every INSERT/UPDATE/DELETE must update every index on the table. A table with eight indexes pays eight times on each write.
Storage. Each index is a full copy of its columns, often a sizable fraction of the table.
Low-cardinality columns. An index on a boolean or a status with four values barely narrows anything — the planner may ignore it and Seq Scan anyway.
Tiny tables. Scanning 50 rows is faster than the index indirection; the planner knows this and skips the index.

Reading EXPLAIN

EXPLAIN shows the planner’s chosen strategy; EXPLAIN ANALYZE actually runs it and reports real timings. Read the tree bottom-up (inner nodes run first). Two things to hunt for:

A Seq Scan with high “Rows Removed by Filter.” The database read the whole table and threw most of it away — a textbook “add an index” signal.
Estimated rows wildly different from actual rows. The planner is working off stale statistics and may pick a bad plan; run ANALYZE to refresh them.

What an index changes in the plan

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 9;

Without an index on customer_id, the plan is a Seq Scan on orders with a Filter: (customer_id = 9) and many “Rows Removed by Filter.” After CREATE INDEX idx_orders_customer ON orders(customer_id);, the same query becomes an Index Scan using idx_orders_customer, reading only the three matching rows. On a 20-row table the difference is invisible; on 20 million rows it’s the difference between a timeout and a millisecond.

The N+1 query problem

This is the performance bug you’ll actually hit on the job, and it’s usually the ORM’s fault, not the database’s. Lazy-loading ORMs fetch a list of parents in one query, then fire one more query per parent to load its children — 1 + N round-trips. For 100 orders, that’s 101 queries where 1 or 2 would do.

Spotting and fixing N+1

SELECT * FROM orders;                       -- 1 query: 100 orders
-- then, per order, lazily:
SELECT * FROM order_items WHERE order_id=1; -- query 2
SELECT * FROM order_items WHERE order_id=2; -- query 3
...                                          -- query 101  → N+1

Fix with eager loading — tell the ORM to fetch children in one JOIN or one batched IN (...) query up front:

Rails: Order.includes(:order_items)
Django: select_related (FK/one) / prefetch_related (many)
Hibernate (JPA): JOIN FETCH
SQLAlchemy: joinedload / selectinload

The tell in your logs is a burst of near-identical queries differing only by an id.

Connection pooling

Opening a database connection is expensive (TCP handshake, auth, backend process spin-up). A connection pool keeps a fixed set of connections open and hands them out to requests, returning them to the pool when done — instead of opening one per request. It bounds the load on the database (a DB has a hard max_connections limit) and removes per-request setup latency. For serverless/high-fan-in workloads you often add an external pooler (e.g. PgBouncer) in front.

From the trenches Use The Index, Luke!

Markus Winand’s Use The Index, Luke! — the canonical book on SQL indexing — opens its case studies with the same wound over and over: a query that was fast in development crawls in production, and the cause is almost never a missing index. It’s an index the database can’t use because of how the query is written. The recurring culprits: filtering on the second column of a composite index while skipping the first (the leftmost-prefix violation); wrapping the indexed column in a function (WHERE UPPER(name) = 'AVA' can’t use a plain index on name — the engine would have to UPPER every row first); and an implicit type cast (comparing an indexed numeric column to a string parameter) that forces a full scan. The lesson that runs through the whole book: an index is not used because it exists — it’s used because the query’s filter matches its sort order. Before adding a second index to “fix” slowness, run EXPLAIN and check whether the index you already have is being passed over, and why.

read the writeup ↗ use-the-index-luke.com

Key points

A relational index is a B+ tree — the same structure from DSA Trees — giving O(log n) lookups plus cheap range scans and ORDER BY. Hash indexes do equality only.
Leftmost-prefix rule: an index on (A,B,C) serves A, (A,B), (A,B,C) — never B alone or (B,C). Selective/equality columns first, range columns last.
A covering index holds every column the query touches → index-only scan, no table fetch.
Indexes cost on every write and consume storage; skip them on low-cardinality columns and tiny tables.
Read EXPLAIN bottom-up; a Seq Scan with high “rows removed by filter” means “add an index,” and big estimate-vs-actual gaps mean “run ANALYZE.” Costs are planner units, not ms.
N+1: lazy ORMs fire 1 + N queries; fix with eager loading (includes/select_related/JOIN FETCH/joinedload).
Connection pooling reuses pre-opened connections instead of one-per-request.

Run the EXPLAIN ANALYZE example in the playground, add the index, and re-run it to watch the Seq Scan turn into an Index Scan.

01 Learning objectives

0 / 7 done

02 Curated reading

Use The Index, Luke!
essential 40m — The definitive guide to SQL indexing and the leftmost-prefix rule.
PostgreSQL docs — Using EXPLAIN
essential 20m — How to actually read a query plan.

03 SQL playground

SQL playground · ecommerce.db

04 Knowledge check

knowledge check5 questions · pass ≥ 70%

01medium
A hash index supports ___, while a B-tree index supports ___.
02medium
EXPLAIN shows a Seq Scan with “Rows Removed by Filter: 1.9M”. The usual fix is:
03medium
Adding indexes always improves overall performance.
04hard
Given a composite index on (country, city, age), which queries can use it efficiently (leftmost-prefix rule)?
05hard
An ORM lazily loads each parent's children, firing 1 + N queries. This is the:

05 Interview questions

browse all ↗

What gets asked on this topic — tap a card for how to approach it, the follow-ups, and the trap. Company tags are best-effort & sourced.

Commonly asked mid concept very common What is a B-tree index and why does it support range queries when a hash index doesn't?
A B-tree keeps keys in sorted order across a balanced, shallow tree, giving O(log n) lookups. Because the keys are ordered, the engine can do equality and range scans (>, <, BETWEEN), prefix LIKE 'abc%', and serve ORDER BY for free.
A hash index maps a key to a bucket via a hash — O(1) equality lookups, but the hash destroys ordering, so it can only do =, never ranges or sorts. B-tree is the default for exactly this versatility.
Follow-ups they push on
- Why can a B-tree help `ORDER BY` avoid a sort step?
- When is a hash index actually the better choice?
Red flag Saying hash indexes are 'always faster' (only for point equality) or that B-trees are O(1).
source: Use The Index, Luke! — Anatomy of an Index ↗
Commonly asked mid concept very common Explain the leftmost-prefix rule for a composite index on (a, b, c). Which queries can it serve?
A concatenated index is sorted by a, then b, then c — like a phone book by surname then first name. So it serves queries that filter on a leading prefix: a; a AND b; a AND b AND c.
It does not efficiently serve b alone or (b, c) — there's no leading a, so the order is useless and the engine falls back to a scan. Practical rule: put equality/most-selective columns first, the column you range/sort on last.
Follow-ups they push on
- Where do you place a column you do a range scan on within the index?
- Can the index still help an `a = ? AND c = ?` query (skipping b)?
Red flag Believing a composite index helps any subset of its columns, especially trailing ones like `(b, c)` without `a`.
source: Use The Index, Luke! — Concatenated Keys ↗
Commonly asked mid concept common What is a covering index / index-only scan, and why is it fast?
A covering index contains every column a query needs — both the filter/sort columns and the selected columns — so the engine can answer entirely from the index and never touch the heap/table. That's an 'index-only scan'.
It's fast because it skips the random table fetch per matched row (the most expensive part of an index lookup). Postgres lets you tack non-key payload columns on with INCLUDE (...); MySQL/InnoDB exposes it via the EXPLAIN 'Using index' note.
Follow-ups they push on
- Difference between key columns and INCLUDE columns in a covering index?
- Why might Postgres still hit the heap despite a covering index (visibility map)?
Red flag Thinking any index that matches the WHERE is 'covering' — it must also contain the SELECTed columns to avoid the table fetch.
source: Use The Index, Luke! — Index-Only Scan ↗
Commonly asked mid concept common When do indexes hurt rather than help?
Indexes are not free:
- Writes slow down — every INSERT/UPDATE/DELETE must update every affected index.
- Storage — each index is a copy of its columns plus row pointers.
- Low cardinality — an index on a boolean/status with few distinct values rarely beats a scan (the planner may ignore it).
- Tiny tables — a seq scan of a few pages is faster than an index round-trip.
- Unused/redundant indexes still cost on every write.
So index for read patterns you actually have, and drop ones EXPLAIN never picks.
Follow-ups they push on
- How would you find unused indexes in production?
- Why might the planner ignore an index on a low-selectivity column?
Red flag 'Just index every column' — it bloats writes and storage and the planner won't use most of them.
source: Use The Index, Luke! — The Where Clause / index downsides ↗
Commonly asked senior debug common You run EXPLAIN and see a Seq Scan with 'Rows Removed by Filter: 9,900,000'. What does that tell you and what do you do?
A sequential scan read the whole table and the filter threw away almost all of it — the query is selective but there's no index for it, so it's reading 10M rows to keep 100. Add an index on the filtered column(s) so the planner can do an index scan instead.
Read the plan bottom-up (inner nodes run first). Watch for a big gap between estimated and actual rows — that means stale statistics, so run ANALYZE. Remember the cost= numbers are arbitrary planner units, not milliseconds; use EXPLAIN (ANALYZE, BUFFERS) for real timings.
Follow-ups they push on
- Estimated rows say 5, actual say 5,000,000 — what's wrong and what's the fix?
- When is a Seq Scan actually the right plan?
Red flag Reading cost as milliseconds, ignoring the estimate-vs-actual divergence, or 'optimizing' a query the planner already handles well.
source: PostgreSQL docs — Using EXPLAIN ↗
Commonly asked mid concept very common What is the N+1 query problem and how do you fix it?
An ORM lazy-loads a relationship inside a loop: 1 query for the list of N parents, then 1 more query per parent to fetch its children — 1 + N round-trips. With 100 posts you fire 101 queries, each paying network + planning latency.
Fix with eager loading — pull the children in a single JOIN or batched IN query: Rails .includes, Django select_related/prefetch_related, Hibernate JOIN FETCH, SQLAlchemy joinedload. It's a query-count problem, not a slow-query problem; detect it by counting queries per request, not by EXPLAINing one.
Follow-ups they push on
- How would you detect N+1 in a running app?
- Trade-off: a single huge JOIN vs a batched IN(...) of two queries?
Red flag Trying to optimize the individual child query when the real issue is firing it N times; or not knowing the eager-load API for the ORM in use.
source: Use The Index, Luke! — N+1 problem (Join Operations) ↗
Commonly asked senior debug common A query filters `WHERE LOWER(email) = 'a@b.com'` (or `WHERE created_at::date = '2024-01-01'`) and ignores the index on the column. Why, and how do you fix it?
Wrapping the indexed column in a function makes the predicate non-sargable — the index is sorted on email, not on LOWER(email), so the engine can't use it and seq-scans.
Fixes: (1) create a functional/expression index matching the expression: CREATE INDEX ON users (LOWER(email));. (2) Rewrite to keep the column bare: for the date case, WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' is sargable and uses the plain index. Same trap with leading-wildcard LIKE '%x'.
Follow-ups they push on
- Why is `LIKE 'abc%'` sargable but `LIKE '%abc'` not?
- Implicit type casts (string column compared to a number) — same problem?
Red flag Not recognizing that a function/cast on the indexed column defeats the index, and reaching for query hints instead of an expression index or a sargable rewrite.
source: Use The Index, Luke! — Functions / sargable predicates ↗
Commonly asked junior concept common What is connection pooling and what problem does it solve?
Opening a DB connection is expensive — TCP handshake, TLS, auth, a backend process/thread. Doing it per request adds latency and can exhaust the DB's connection limit under load.
A connection pool keeps a set of pre-opened connections and hands one to each request, returning it to the pool when done. This caps total connections (protecting the DB), amortizes setup cost, and smooths spikes. Tools: PgBouncer (external), HikariCP (Java), plus most ORMs/drivers' built-in pools.
Follow-ups they push on
- How do you size a pool, and why isn't bigger always better?
- Transaction vs session pooling mode in PgBouncer?
Red flag Thinking a bigger pool always means more throughput — past the DB's CPU/IO capacity it causes contention; serverless functions multiplying pools is a classic source of connection exhaustion.
source: PostgreSQL docs — Connections and Authentication ↗
★ must-know Commonly asked mid concept common What is the difference between a clustered and a non-clustered (secondary) index, and how does it affect lookups?
A clustered index *is* the table: the rows are physically stored in the index's key order (the leaf nodes hold the full row). There's at most one per table — in MySQL/InnoDB it's the primary key. A range scan on the clustered key reads contiguous data, which is very fast.
A non-clustered (secondary) index is a separate structure whose leaves hold the key plus a pointer back to the row. So a secondary-index lookup that needs columns not in the index does a second hop — a bookmark lookup (InnoDB: look up the PK, then the clustered index). That's why a *covering* secondary index (one that includes all needed columns) is so much faster: it skips the second hop.
What a strong answer covers
- Clustered index = the table's rows stored in key order; at most one per table (InnoDB: the PK).
- Secondary index = separate B-tree of key -> row locator; many allowed.
- A secondary lookup needing extra columns does a second fetch (bookmark / clustered-index lookup).
- InnoDB secondary indexes store the PK as the row pointer — so a fat PK bloats every secondary index.
- Postgres heap tables differ: there's no clustered index, just heap + index TIDs.
Quick self-check
In MySQL/InnoDB, what does a secondary index's leaf node store as the pointer to the full row?
Follow-ups they push on
- In InnoDB, why does a large primary key make every secondary index bigger?
- How does Postgres's storage model differ from InnoDB's clustered table?
Red flag Assuming every index is a separate copy with a fixed pointer (engine-specific), or ignoring that a secondary lookup may need a costly second fetch to the base row.
source: MySQL docs — Clustered and Secondary Indexes ↗
Commonly asked senior debug common Your query filters on `status = 'active'` (95% of rows) and the planner does a Seq Scan instead of using the index. Is that a bug?
No — that's the planner being correct. The predicate is low-selectivity: it matches almost every row, so an index scan would do millions of random single-row fetches plus the index read, which is *slower* than one sequential pass. Indexes win only when they eliminate most of the table.
If instead you query the rare value (status = 'pending', 0.1% of rows), the index becomes worthwhile — that asymmetry is why a partial index (CREATE INDEX … WHERE status = 'pending') is the right tool for skewed columns. Verify with EXPLAIN (ANALYZE, BUFFERS); if the planner *wrongly* avoids an index, suspect stale stats and run ANALYZE.
What a strong answer covers
- Low selectivity (matching most rows) makes a full scan cheaper than scattered index fetches.
- Indexes pay off when they exclude the large majority of rows.
- Skewed columns: a partial index on the rare value(s) beats a full-column index.
- If the planner avoids an index it *should* use, suspect stale statistics — run ANALYZE.
Quick self-check
A boolean `is_deleted` column is true for 0.5% of rows. The best index strategy for `WHERE is_deleted = true` is…
Follow-ups they push on
- What is selectivity, and roughly what threshold flips the planner to a seq scan?
- When does a partial index beat a full index on the same column?
Red flag Force-hinting an index onto a non-selective predicate and making the query slower, or assuming 'index not used' is always a problem.
source: PostgreSQL docs — Partial Indexes ↗
Commonly asked senior concept common For `WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at`, what composite index would you build and in what column order?
Index (status, created_at) — equality column first, range/sort column last. The leading status = narrows the index to the matching slice, and within that slice the entries are already ordered by created_at, so the engine satisfies both the range filter and the ORDER BY from the index with no separate sort.
Flip it to (created_at, status) and the leading range column scatters the status values, so it can't use the index for the equality efficiently and may need a sort. The rule (from Use The Index, Luke!): equalities first, then the one range/order-by column — and you only get a sort-free ORDER BY if its column trails the equality columns in the index.
What a strong answer covers
- Order: equality predicate columns first, then the range/ORDER BY column.
- (status, created_at) lets one index serve filter + range + ordering with no sort step.
- A leading range column ruins the ability to use trailing equality columns and to skip the sort.
- Only one range column can be 'used' to bound the scan; further columns only refine within it.
Quick self-check
Best single composite index for `WHERE status = 'active' AND created_at > :d ORDER BY created_at`:
Follow-ups they push on
- Why can an index serve ORDER BY only when the sort column trails the equality columns?
- What happens to this index if you add a second range predicate?
Red flag Putting the range/sort column before the equality column, which forces a scan-and-sort and wastes the composite index.
source: Use The Index, Luke! — The Equality-First Rule (concatenated keys, ORDER BY) ↗
Commonly asked senior concept common Why is keyset (seek) pagination better than OFFSET for deep pages?
LIMIT 20 OFFSET 100000 still reads and discards the first 100,000 rows before returning 20 — cost grows linearly with the page number, so deep pages crawl. It can also skip or repeat rows if data changes between page loads.
Keyset (seek) pagination remembers the last row's sort key and asks for the next slice directly: WHERE (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. With an index on the sort key the database *seeks* straight to the spot — constant time regardless of depth — and it's stable under concurrent inserts. The cost is you can't jump to an arbitrary page number, only next/previous.
What a strong answer covers
- OFFSET scans and throws away all skipped rows — O(offset), so deep pages get slower.
- Keyset filters on the last seen sort key and seeks via the index — roughly constant time.
- Keyset is stable when rows are inserted/deleted between page requests; OFFSET can skip/duplicate.
- Trade-off: keyset supports next/prev, not random 'jump to page N'.
- Needs a unique, indexed tiebreaker (e.g. id) appended to the sort key.
Follow-ups they push on
- Why include `id` as a tiebreaker in the keyset comparison?
- When is OFFSET pagination still acceptable?
Red flag Using OFFSET for infinite scroll / deep pages (slow and prone to skipping rows under concurrent writes) and not realizing seek pagination needs a unique sort tiebreaker.
source: Use The Index, Luke! — Paging Through Results (seek method) ↗
Commonly asked senior debug common The estimated rows in EXPLAIN say 12 but actual says 4,000,000. What's wrong and how do you fix it?
A large gap between estimated and actual rows means the planner is working from stale or missing statistics, so it's likely choosing a bad plan (e.g. a nested loop sized for 12 rows that actually runs 4M times).
First fix: ANALYZE the_table; (or VACUUM ANALYZE) to refresh the stats the planner samples. If it's still off, the column may have correlated predicates the default per-column stats can't model — create extended statistics (CREATE STATISTICS … (dependencies/ndistinct)), or raise the sampling resolution with ALTER TABLE … ALTER COLUMN … SET STATISTICS. Always read these numbers with EXPLAIN (ANALYZE, BUFFERS) so you compare estimate vs actual on the same run.
What a strong answer covers
- Estimate-vs-actual divergence = the planner's row-count model is wrong, usually stale stats.
- First action: ANALYZE to refresh statistics.
- Correlated columns defeat per-column stats — use extended statistics (CREATE STATISTICS).
- Bad estimates cause bad join-method/order choices (nested loop where a hash join was right).
- Use EXPLAIN (ANALYZE, BUFFERS) to see estimate, actual rows, and real I/O together.
Follow-ups they push on
- Why does autovacuum sometimes not keep stats fresh enough on a hot table?
- What are extended statistics and when do you need them?
Red flag Rewriting the query when the real problem is stale stats, or trusting the planner's row estimate without checking it against EXPLAIN ANALYZE's actual rows.
source: PostgreSQL docs — Row Estimation / Statistics Used by the Planner ↗
Commonly asked senior design common How would you find and fix slow queries in a production database?
Find: turn on query collection — pg_stat_statements (Postgres) or the slow query log (MySQL) — and sort by total time (frequency x latency), not just single slowest, since a moderately slow query run millions of times dominates. APM traces help spot N+1 patterns.
Diagnose: run EXPLAIN (ANALYZE, BUFFERS) on the worst offenders; look for seq scans on big tables, bad row estimates, nested loops over many rows, and high buffer reads.
Fix: add/adjust an index (composite, covering, partial), rewrite to be sargable, fix N+1 with eager loading, refresh stats with ANALYZE, or cache/materialize expensive aggregates. Then re-measure — optimize the query that costs the most aggregate time first.
What a strong answer covers
- Capture queries with pg_stat_statements / the slow query log; rank by total time, not single-run time.
- Diagnose the top offenders with EXPLAIN (ANALYZE, BUFFERS).
- Common fixes: indexing, sargable rewrites, fixing N+1, refreshing stats, caching/materializing.
- Re-measure after each change — never optimize blind.
- A medium-slow query run constantly often beats the single slowest in total cost.
Follow-ups they push on
- Why rank by total time rather than the single slowest query?
- How do you catch an N+1 that no single EXPLAIN reveals?
Red flag Optimizing the single slowest query while ignoring a moderately slow one executed orders of magnitude more often, or tuning without measuring before/after.
source: PostgreSQL docs — pg_stat_statements ↗
Commonly asked mid concept occasional What's the difference between a B-tree, a hash, and a GIN/inverted index, and what is each best for?
B-tree — the default; ordered keys, serves equality, ranges, prefix LIKE, and ORDER BY. Use for almost everything scalar.
Hash — O(1) equality only, no ranges or ordering; rarely worth it over a B-tree except niche equality-heavy cases.
GIN (Generalized Inverted Index) — maps each *element* of a composite value to the rows containing it, so it's built for 'contains' queries over multi-valued columns: full-text search (tsvector), JSONB containment (@>), array membership. (GiST is its cousin for ranges/geometry/nearest-neighbor.)
Choose by the query shape: scalar ranges/sorts -> B-tree; element-membership in documents/arrays/text -> GIN.
What a strong answer covers
- B-tree: ordered, the all-purpose default (equality, range, sort, prefix LIKE).
- Hash: equality-only, no ordering — niche.
- GIN: inverted index for multi-valued columns — full-text, JSONB @>, array containment.
- GiST: ranges, geometry, nearest-neighbor / fuzzy search.
- Match the index type to the query operator, not by habit.
Follow-ups they push on
- Why can't a B-tree efficiently answer JSONB `@>` containment?
- When would you reach for GiST over GIN?
Red flag Putting a plain B-tree on a JSONB or array column and wondering why containment queries don't use it — those need a GIN index.
source: PostgreSQL docs — Index Types ↗