Query Performance Analyzer

Query Performance Analysis — listRecentOrdersWithItems

Engine: Postgres 15 · Source: src/api/orders.ts:42 (Drizzle ORM)

Current Query

// src/api/orders.ts:42
const orders = await db.select().from(orders)
  .where(eq(orders.userId, userId))
  .orderBy(desc(orders.createdAt))
  .limit(50);

for (const order of orders) {
  order.items = await db.select().from(items).where(eq(items.orderId, order.id));
}

The Drizzle output: 51 separate statements, no JOIN.

Limit  (cost=0.43..3.10 rows=50 width=128)
  ->  Index Scan Backward using orders_user_created_idx on orders
        Index Cond: (user_id = $1)
-- then 50 × :
Seq Scan on items  (cost=0.00..18452.40 rows=2 width=84)
  Filter: (order_id = $1)
  Rows Removed by Filter: 419998

The per-item Seq Scan is the bottleneck — items has 420k rows and no index on order_id.

Plan Compare

Recommended Rewrite

const rows = await db
  .select({
    order: orders,
    item: items,
  })
  .from(orders)
  .leftJoin(items, eq(items.orderId, orders.id))
  .where(eq(orders.userId, userId))
  .orderBy(desc(orders.createdAt), asc(items.createdAt))
  .limit(500); // 50 orders × ~10 items avg

// Group in app code
const grouped = groupByOrder(rows);

-- Required index
CREATE INDEX CONCURRENTLY items_order_created_idx
  ON items (order_id, created_at);

The composite index satisfies both the join predicate and the secondary sort. Adding created_at lets the planner avoid a separate sort step.

Other Recommendations

Follow-ups

Heuristic analysis from static source + table-size context. Confirm with EXPLAIN (ANALYZE, BUFFERS) on a representative dataset before promoting the rewrite.

This sample illustrates the skill's output format. Names, metrics, and operational details are illustrative unless the artifact explicitly analyzes public information.