SQL performance tuning
Advice for this query, not generic best practice
You are a database expert. This SQL takes [elapsed time] on [table name, roughly X rows]. Please: 1) name the most likely bottleneck (read the execution plan); 2) give the optimized SQL; 3) say which indexes are needed and what they cost once added (slower writes, disk space). Do not give me generic advice like 'avoid SELECT *'. ```sql [paste your SQL] ```
How to use
Ask which line of the execution plan changed after the fix. Without that, you know the query got faster but not why.
Good for
- A single query misses its SLA (say 2 seconds) on a table with millions of rows, and you have the EXPLAIN output.
- You are reviewing a heavy query that will run in a report or a scheduled job before it ships.
Not for
- The table has a few thousand rows and the query returns in 30 ms: optimization has negative value, so leave it alone.
- The slowness is lock contention or an exhausted connection pool, not the SQL: check pg_stat_activity and the slow log first.
Example
Input
SELECT o.id, o.created_at, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE date_trunc('day', o.created_at) = '2026-03-01'
ORDER BY o.created_at DESC
LIMIT 50;
-- orders: about 8,000,000 rows, takes 6.4 s
-- EXPLAIN shows Seq Scan on ordersWhat you get
Point out that date_trunc disables the index on created_at and the predicate should become `>= '2026-03-01' AND < '2026-03-02'`; next, that ORDER BY cannot use an index and needs (created_at DESC) or (created_at, user_id), with the write cost stated. After the rewrite you should see Seq Scan become Index Scan and the time drop to hundreds of milliseconds.
Common mistakes
- Giving the SQL without the plan and timing: it can only guess the bottleneck from syntax.
- Asking it to 'optimize this' can silently turn a LEFT JOIN into an INNER JOIN, changing the row count without warning.
Try tweaking it like this
- Paste the raw `EXPLAIN (ANALYZE, BUFFERS)` output — the bottleneck goes from guesswork to reading numbers.
- Add 'give me three rewrites with their trade-offs, then I will pick' so it does not default to the most aggressive rewrite.