Synthetic record for one slow query: plan, timings and a rows-identical check

The invented setup

Everything below is invented for illustration. It is not a measurement of any real system and not a customer delivery. A table of orders has 2,000,000 invented rows. The query lists the 20 most recent orders for one customer. The target time agreed in writing is 50 milliseconds, taken as the median of five runs on a staging copy.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

query:   SELECT * FROM orders WHERE customer_id = :id ORDER BY created_at DESC LIMIT 20;
params:  id = 42 (many orders), id = 7 (few orders), id = 999999 (none)
copy:    staging, 2,000,000 rows, personal data replaced

Before and after, hand-written

The two plans below are simplified and written by hand to show the shape of the record. A real record would paste the plan text the database printed.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

BEFORE (median of 5 runs: 410 ms for id 42)
  Limit
    -> Sort by created_at DESC
         -> Seq Scan on orders  (filter: customer_id = 42)
            estimated rows: 3   actual rows: 1,840   (reads the whole table)

CHANGE
  CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at DESC);
  undo: DROP INDEX CONCURRENTLY orders_customer_created_idx;

AFTER (median of 5 runs: 3 ms for id 42)
  Limit
    -> Index Scan on orders_customer_created_idx
         (customer_id = 42)   actual rows: 20

The checks that make it acceptable

Speed alone is not acceptance. Each line below is a check you can repeat.

• Result rows identical for all three parameter sets, compared by row count and a checksum of the ordered rows.
• Median of five runs at or under the 50 millisecond target for every parameter set, not only the best case.
• The estimated rows (3) and actual rows (1,840) in the before plan are called out, because that gap is why the plan was poor.
• Write cost reported: inserting 10,000 invented rows took 6% longer with the index than without. This figure is invented; a real record reports the measured one.
• An undo script exists and was run on the staging copy.

What it does not prove, and how to ask

A staging result does not promise the same time in production, where data volume, cache state and load differ. It also says nothing about queries other than this one. The fixed-scope slow-query job produces a record of this shape for one real query, on a staging copy you prepare, starting from £295 as an untested proposal, with payment after you sign off. Send the engine and version, the query with invented values and its plain plan, never credentials or real rows.