SQL optimizer
SELECT *
FROM orders
WHERE customer_id = 42
AND DATE_TRUNC('day', created_at) >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;
●Preparing the analysisdone145ms
●Checking query behaviordone1.1s
●Checking query behaviordone1.3s
●Analyzing query inefficienciesdone37ms
●Checking query behaviordone274ms
●Analyzing query inefficienciesdone30ms
📊 Estimated impact
| Version | Estimated execution time | Improvement |
|---|---|---|
| Original | 6.70 ms | — |
| Rewrite + recommended index | 0.54 ms | 12.5× faster / 92% less time |
Actual gains depend on data distribution and existing indexes; these estimates include adding the composite index below.
🔎 What changed
- Removed
DATE_TRUNC()fromcreated_at, allowing PostgreSQL to use the timestamp directly as an index range condition. - Preserved the cutoff: midnight 30 calendar days ago, not a rolling 720-hour window.
- The composite index supports filtering by customer and reading newest orders first, allowing PostgreSQL to stop after 50 matches without sorting.
🗂️ Recommended indexes
Skip this if an equivalent index already exists. An index on (customer_id, created_at) with ascending order also works through a backward scan.
CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx
ON orders (customer_id, created_at DESC);✅ Optimized query
SELECT *
FROM orders
WHERE customer_id = 42
AND created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;1m 5s