Workload
Which queries cost the most database time, and which proven index fixes recover it. Fix the top of the list first.
Recorded from a real local run: the demo traffic generator against the sample PostgreSQL database (40 s of traffic, 10 query shapes), then the product's own batch flow benchmarked the top queries in isolated clones. The per-day figure assumes that traffic rate continues around the clock; real workloads differ. Run locally to import your own pg_stat_statements.
Ranked Fix Queue
4 Benchmark-Validated indexes could recover approximately 6.7 hours of query execution time/day.
Estimated query execution time saved, not CPU time. Extrapolated from isolated sample benchmarks; combined indexes have not been benchmarked together.
CREATE INDEX ON orders (customer_id, created_at DESC) INCLUDE (id, total_amount)Helps 1 query shape · 5.0 hours estimated/day
CREATE INDEX ON orders (customer_id) INCLUDE (created_at)Helps 1 query shape · 1.2 hours estimated/day
CREATE INDEX ON payments (order_id)Helps 1 query shape · 26.5 minutes estimated/day
CREATE INDEX ON orders (status, id, customer_id, created_at)Helps 1 query shape · 3.0 minutes estimated/day
Ranked by total time
| # | Query | Calls | Mean | Estimated p95 | Total time | Share of total | Actions |
|---|---|---|---|---|---|---|---|
| 1 | SELECT id, customer_id, payload, created_at FROM events WHERE event_type = ? ORDER BY payloadevents | 45 | 243 ms | 573 ms | 11 s | 44.8% | |
| 2 | SELECT id, total_amount FROM orders WHERE customer_id = ? AND created_at >= ? ORDER BY created_at DESCorders | 1,500 | 6 ms | 8 ms | 8.7 s | 35.5% | |
| 3 | SELECT c.id, (SELECT MAX(o.created_at) FROM orders AS o WHERE o.customer_id = c.id) AS last_order FROM customers AS c WHERE c.segment = ? AND c.id <= ?customers, orders | 6 | 336 ms | 419 ms | 2 s | 8.2% | |
| 4 | SELECT o.id, p.amount, p.method FROM orders AS o JOIN payments AS p ON p.order_id = o.id WHERE o.customer_id = ?orders, payments | 100 | 11 ms | 16 ms | 1.1 s | 4.4% | |
| 5 | SELECT * FROM order_items AS i JOIN orders AS o ON o.id = i.order_id JOIN customers AS c ON c.id = o.customer_id JOIN products AS p ON p.id = i.product_id WHERE o.status = ? AND o.created_at >= ?customers, order_items, orders, products | 15 | 34 ms | 45 ms | 511 ms | 2.1% | |
| 6 | SELECT o.id, o.total_amount FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE c.country = ? AND o.currency = ?customers, orders | 40 | 11 ms | 17 ms | 433 ms | 1.8% | |
| 7 | SELECT id, customer_id, total_amount FROM orders WHERE status = ? AND created_at >= ? AND created_at < ?orders | 60 | 5 ms | 7 ms | 307 ms | 1.3% | |
| 8 | SELECT id, email FROM customers WHERE LOWER(email) = ?customers | 120 | 2 ms | 3 ms | 258 ms | 1.1% | |
| 9 | SELECT id, status, total_amount FROM orders WHERE id = ?orders | 6,000 | 0.02 ms | 0.04 ms | 104 ms | 0.4% | |
| 10 | SELECT id, email, country FROM customers WHERE id = ?customers | 6,000 | 0.02 ms | 0.18 ms | 101 ms | 0.4% |