Walk through how you diagnose a slow SQL query using `EXPLAIN (ANALYZE, BUFFERS)`. Explain the Equality-Before-Range rule for composite B+ Tree indexes.

A strong answer

A strong candidate frames Walk through how you diagnose a slow SQL query using EXPLAIN (ANALYZE, BUFFERS). Explain the Equality-Before-Range rule for composite B+ Tree indexes. around the exact memory layout, compiler semantics, and hardware execution model covered in [Composite Index Column Order (Equality Before Range), Covering Indexes (INCLUDE), and Reading EXPLAIN (ANALYZE.]. Explain loops, rows removed by filter, and Buffers: shared hit/read, contrast OFFSET N ($O(N)$ scan-and-discard) with Keyset Pagination (WHERE (created_at, id) < ($last_ts, $last_id) LIMIT 50` in $O(\log N)$), and show how Partial Indexes shrink index size by 99%.

Your orders dashboard runs SELECT id, total_cents FROM orders WHERE tenant_id = $1 AND status = 'PAID' AND created_at >= $2 ORDER BY created_at DESC LIMIT 50. A developer creates CREATE INDEX idx_bad ON orders (created_at, tenant_id, status). Even though all three WHERE columns are in the index, the query takes 420 ms and reads 18,000 8 KB pages! Why did putting created_at first ruin the index, and how do we get this query down to 3 buffer hits (0.08 ms)?

When engineers encounter this bottleneck in production, a surface-level API description rarely explains what went wrong. In real workloads, every line of code executes against concrete hardware constraints: fixed-width memory cells, CPU cache lines, kernel descriptor tables, network socket buffers, or accelerator memory buses. Without a clear mental model of Composite B+ Tree Lexicographical Ordering (Equality -> Range), Covering Indexes (INCLUDE), and EXPLAIN (ANALYZE, BUFFERS), debugging degrades into guesswork—tuning timeouts, adding retries, or scaling instances blindly while the underlying architectural flaw remains untouched.

In a production interview, always connect the specification rule to its concrete runtime consequence: state what lives on the stack versus the heap or static storage segment, how the compiler or operating system validates alignments, lifetimes, and error codes, and what happens under adversarial or boundary inputs (such as zero-length buffers, integer overflow, concurrent mutation, or partial I/O). Backing your explanation with a small trace of register/memory state transitions immediately separates senior systems engineers from candidates who only memorized syntax.

What the interviewer is listening for

• Mastery of core invariant: Order multi-column B+ Tree indexes using the Equality-Before-Range/Sort rule to scan a single contiguous leaf interval.
• Mastery of core invariant: Eliminate random heap lookups using Covering Indexes (INCLUDE) and PostgreSQL's Visibility Map (Heap Fetches: 0).
• Mastery of core invariant: Diagnose query plans with EXPLAIN (ANALYZE, BUFFERS) and replace slow OFFSET pagination with $O(\log N)$ Keyset Pagination.
• Hardware and ABI awareness: Explaining how sizes, alignments, calling conventions, or kernel boundaries behave on real 64-bit targets rather than relying on implementation-defined assumptions.
• Defensive engineering: Naming the exact compiler flags (-Wall -Wextra -Werror), static analyzers, or runtime sanitizers (-fsanitize=address,undefined,thread) that verify correctness in CI.

Common mistakes

• Explaining only surface syntax without tracing the underlying memory addresses, ownership lifetime, or kernel/user-space state transitions.
• Assuming undefined behavior (such as signed overflow, uninitialized reads, strict-aliasing violations, or dangling pointers) will "just work" because it passed an unoptimized -O0 debug build.
• Ignoring return codes, partial reads/writes, alignment padding, or cleanup paths when an allocation or syscall fails midway through a function.