Problem and context
A multi-tenant events table has a B-tree index on (tenantid, createdat). A new query supplies only a created_at range, and older versions often choose a sequential scan. Using PostgreSQL 18 skip scans, explain how the optimizer can use the suffix column, how to measure the benefit, and why this is not a universal replacement for a purpose-built index.
What the interviewer evaluates
The key distinction is the B-tree leftmost-prefix rule and the skip-scan strategy. PostgreSQL 18 can enumerate distinct leading-column values and perform suffix searches for each one. Cost depends on prefix cardinality, suffix selectivity, table/index correlation, and statistics. The candidate should prove the result with EXPLAIN (ANALYZE, BUFFERS).
Clarifying questions to ask first
Data distribution
Ask for tenant count, rows per tenant, time-range width, and whether data is clustered by time. Many distinct prefix values can make repeated searches more expensive than a sequential scan.
Workload and version
Confirm the server is PostgreSQL 18, query frequency, permission to add an index, and concurrent-write behavior. A skip scan is a plan choice, not a SQL guarantee.
Measurement baseline
Confirm existing EXPLAIN output, buffer hit rate, execution time, and a cold-cache baseline. Compare actual plans rather than estimated cost or one warm-cache run.
30-second answer framework
“The traditional rule for (tenantid, createdat) wants a tenantid predicate first. PostgreSQL 18 can, when the cost is favorable, try each distinct tenantid and use the createdat range to skip unrelated index ranges. I would refresh statistics and compare skip scan, sequential scan, and a dedicated (createdat) index with EXPLAIN ANALYZE BUFFERS. High prefix cardinality, wide ranges, or poor correlation can make skip scans slower.”
Detailed solution steps
Step 1: Map index columns to predicates
List index order, equality predicates, range predicates, and ordering requirements. Skip scans help a multicolumn B-tree when an early column lacks a useful restriction but a later column has a selective predicate; they do not change physical key ordering.
Step 2: Explain prefix-enumeration cost
The optimizer can treat each distinct leading value as an implicit search entry and look up the suffix range. The number of probes is influenced by prefix cardinality and estimation error; higher cardinality means more random access and repeated positioning.
Step 3: Refresh statistics and inspect the plan
Run ANALYZE so distinct counts, histograms, and correlation statistics reflect current data. Capture EXPLAIN (ANALYZE, BUFFERS, SETTINGS) with actual rows, shared hits, reads, plan nodes, and relevant optimizer settings.
Step 4: Build comparable baselines
On the same data snapshot, compare the existing index with a skip scan, a sequential scan, and a new suffix-column index. Test cold and warm cache, narrow and wide ranges, and tenant skew instead of relying on one sample.
Step 5: Account for covering and heap cost
If the query projects many non-index columns, heap visits after the skip scan may dominate. Check whether the index covers the projection, whether the visibility map enables an index-only scan, and whether random heap access erases filtering gains.
Step 6: Manage plan stability
Growth changes prefix cardinality and selectivity, so the optimizer may switch between skip scan, sequential scan, and another index. Record plan fingerprints and p95 latency; adjust statistics targets or add an index matching the dominant access path when needed.
Step 7: Plan upgrade and rollback
After upgrading to PostgreSQL 18, recollect statistics and replay representative traffic. Monitor buffer reads, CPU, lock waits, and tail latency; if plans regress, return to a stable index or query shape before deciding whether to retain skip-scan plans.
High-quality sample answer
For (tenantid, createdat), I treat skip scan as a cost-based plan: enumerate tenantid values, then search the createdat range for each. I would run ANALYZE and compare EXPLAIN ANALYZE BUFFERS against a sequential scan and a (created_at) index under cold and warm cache and different range widths. If prefix cardinality, heap visits, or range width makes repeated probes expensive, I would add a suffix or covering index and monitor plan stability after the upgrade.
Common mistakes
- Mistake: Assuming any multicolumn index efficiently filters its suffix. → Why: The leftmost-prefix rule still matters and skip scans are cost-based. → Fix: Validate the actual plan and distribution.
- Mistake: Treating skip scan as a new index type. → Why: It is an optimizer access strategy. → Fix: State that the physical index is unchanged.
- Mistake: Comparing only estimated cost. → Why: Statistics can be wrong. → Fix: Measure ANALYZE BUFFERS across cache states.
- Mistake: Ignoring heap and covering costs. → Why: Fast filtering may still require many row fetches. → Fix: Evaluate index-only eligibility and heap access.
Follow-up questions and answers
Follow-up 1: If there are only two tenants, will skip scan always be chosen?
No. Suffix selectivity, page correlation, cache state, and estimated cost still matter; a sequential scan may be cheaper.
Follow-up 2: Does skip scan jump over every nonmatching leaf page?
It performs multiple searches using distinct prefix values and avoids unrelated ranges, but each search still has positioning and possible heap-fetch cost. The jump is not free.
Follow-up 3: Why can the plan still be wrong after ANALYZE?
Multicolumn correlation, skew, parameter values, and cache state exceed the basic statistics model. Use extended statistics, representative parameter replay, and long-term p95 monitoring.
Follow-up 4: When is a direct (created_at) index better?
When suffix-only queries are a stable primary path, prefix cardinality is high, ranges are wide, or heap fetches dominate, a dedicated index avoids repeated prefix probes. Balance that against write amplification, storage, and other queries using the original index.