Post-Mortem Framework · Query Performance: Index Intersection Misuse

Index Intersection Misuse

SEV-3, Limited Impact

Isolated propagation · query · Affects 0 scenario(s)

Severity Classification

Classified as PARTIAL based on failure mode severity.

Propagation Chain

Origin component

Index Intersection Misuse begins at the source component. Trigger: Hot query with multiple equality predicates lacks a composite index.

Immediate (T+0) · Signal: Alert

Blast Radius

Elevated latency for the specific query pattern using index intersection. If the query is on a hot path (user authentication, item lookup, order retrieval), the elevated latency affects that feature proportionally. Buffer cache pressure from the larger I/O footprint of the intersection plan can indirectly affect other queries sharing the same database instance.

Contributing Factors

Trigger Condition: Hot query with multiple equality predicates lacks a compositoperational

This operational trigger enables Index Intersection Misuse: Hot query with multiple equality predicates lacks a composite index

Trigger Condition: Schema changes remove a composite index leaving only single-operational

This operational trigger enables Index Intersection Misuse: Schema changes remove a composite index leaving only single-column indexes

Trigger Condition: Query planner's cost model incorrectly favors index intersecoperational

This operational trigger enables Index Intersection Misuse: Query planner's cost model incorrectly favors index intersection over composite index

Remediation Plan

ImmediateRun EXPLAIN (ANALYZE, BUFFERS) on the affected query to confirm BitmapAnd/index_

Run EXPLAIN (ANALYZE, BUFFERS) on the affected query to confirm BitmapAnd/index_merge

Effort: Minutes to hours (on-call response)

ImmediateIdentify the predicate columns and design the correct composite index column ord

Identify the predicate columns and design the correct composite index column order

Effort: Minutes to hours (on-call response)

ImmediateCREATE INDEX CONCURRENTLY (PostgreSQL) to build without locking

CREATE INDEX CONCURRENTLY (PostgreSQL) to build without locking

Effort: Minutes to hours (on-call response)

Short-TermCreate composite index on the combined predicate columns

CREATE INDEX CONCURRENTLY ON table (col_a, col_b) WHERE col_a IS NOT NULL. Column order in composite index matters: leftmost prefix is used for range scans. For equality predicates, put higher-selectivity columns first. For range predicates, the range column should be last in the composite index.

Effort: 1 day to 1 week

Short-TermRegular EXPLAIN analysis of hot queries

After each schema change or significant data growth, re-analyze the execution plans of high-frequency queries. Add query plan review to the deployment checklist. Tools: pg_stat_statements (PostgreSQL), slow query log (MySQL).

Effort: 1 day to 1 week

Short-TermDisable index intersection for the specific query (workaround)

PostgreSQL: SET enable_bitmapscan = off for the session to force a sequential scan or alternative plan. Not a long-term fix: add the composite index instead. Used only when an emergency plan hint is needed while the correct index is built.

Effort: 1 day to 1 week

Short-TermAdd alerting for documented detection signals

Configure alerts for: alert. Set thresholds to fire at 70% of critical level to allow response before full failure.

Effort: 1–3 days

Long-TermArchitecture review for Index Intersection Misuse resilience

Conduct a structured architecture review focused on preventing recurrence. Review topology for blast radius reduction, mitigation coverage, and observability gaps. Consider whether the current architecture scenario should evolve.

Effort: 1–2 sprints

This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.