This report query is slow | Engineer Atlas

You asked

“The monthly shipped-orders report for a region takes forty seconds. The query is below; can you make it fast?”

The model answered

1-- SELECT id, customer_id, total, created_at
2--   FROM orders
3--  WHERE status = 'shipped'
4--    AND region_id = $1
5--    AND created_at >= $2 AND created_at < $3
6--  ORDER BY total DESC;
7 
8CREATE INDEX idx_orders_status     ON orders (status);
9CREATE INDEX idx_orders_region_id  ON orders (region_id);
10CREATE INDEX idx_orders_created_at ON orders (created_at);
11CREATE INDEX idx_orders_total      ON orders (total DESC);

Assistant · I indexed each column the query filters on and the column it sorts by, so the planner can use an index for every predicate and avoid the full table scan.

Classify each statement

0/8 classified. Some statements are true — the point is telling which.

The actual plan for the slow query was not examined before proposing a fix.

The index on total DESC lets the database skip the sort step.

Given one index per column, the planner will combine them to satisfy all three predicates efficiently.

An index on the most selective equality predicate, region_id, is likely to improve this query on its own.

Which column order to use in a composite index depends on row counts and value distributions the ask did not include.

An index on status alone is unlikely to be chosen by the planner for this query.

Each added index is a structure that must be vacuumed, rebuilt occasionally, and paid for in storage and cache memory.

Every insert and every update to an indexed column on orders now maintains four additional structures.