Skip to main content

Model Artifact: Query-Plan Review

Scenario​

A Semester 6 learner reviews a slow dashboard query before proposing an index.

Completed learner-quality example​

Query purpose​

Show the 25 most recent paid orders for one tenant, including customer email and total amount.

Baseline plan summary​

Seq Scan on orders  (rows=1,240,000)
  Filter: tenant_id = $1 AND status = 'paid'
Sort by created_at DESC
Nested Loop to customers by primary key
Limit 25

Problem​

The database scans every order for every dashboard load, filters to one tenant and status, sorts the surviving rows, and then keeps only 25. The expensive work happens before the LIMIT can help.

Proposed change​

Add a composite index that matches the equality filters first and the ordering second:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_desc
ON orders (tenant_id, status, created_at DESC);

Expected improved plan​

Index Scan using idx_orders_tenant_status_created_desc on orders
  Index Cond: tenant_id = $1 AND status = 'paid'
Limit 25
Nested Loop to customers by primary key

Tradeoffs​

  • Write cost increases for order inserts and status updates.
  • Index size is acceptable because it supports a high-traffic dashboard path.
  • If only paid orders are queried, a partial index on status = 'paid' may be smaller, but the composite full index also supports admin status filters.

Validation plan​

Compare EXPLAIN (ANALYZE, BUFFERS) before and after on staging data, require reduced shared-buffer reads, and verify p95 dashboard latency under representative tenant sizes.

How to read this example​

  • Passing: Describes the query purpose, current plan, problem, and proposed index.
  • Strong: Explains why LIMIT was ineffective before the index and orders index columns intentionally.
  • Portfolio-worthy: Includes operational tradeoffs and a validation plan beyond estimated cost.