Optimize Slow Sales Summary Query

Context

At Acme Retail, a simple reporting query has become slow as the sales table has grown. Interviewers use this kind of question to test whether you understand practical SQL performance tuning, not just syntax.

Core Question

Explain how you would optimize a slow SQL query in PostgreSQL to improve performance. Your answer should cover:

1. How you would identify the main bottleneck
2. What changes you would make to the SQL itself
3. How indexes can help, and when they do not
4. How you would validate that the optimized query is actually better

You may reference common patterns such as unnecessary SELECT *, filtering late instead of early, functions on indexed columns, avoidable sorting, and inefficient aggregations.

Scope Guidance

Keep the discussion at an easy interview level: focus on a clear, structured optimization process, practical examples, and the most common performance mistakes candidates should recognize in PostgreSQL.

Use EXPLAIN ANALYZE First

Optimization should start with measurement. In PostgreSQL, EXPLAIN ANALYZE shows the execution plan, actual row counts, timing, and whether the database is scanning far more data than expected.

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM sales
WHERE sale_date >= DATE '2024-01-01'
GROUP BY customer_id;

Filter Early and Return Fewer Columns

Queries often slow down because they read more rows and columns than needed. Restricting rows in the WHERE clause and selecting only required columns reduces I/O, memory use, and sort work.

SELECT customer_id, amount
FROM sales
WHERE status = 'completed';

Indexes Help Search, Not Everything

Indexes are most useful for selective filters, joins, and some sorts. They are less helpful when a query needs most rows in a table, and they can be bypassed if the query applies functions to indexed columns.

CREATE INDEX idx_sales_sale_date ON sales (sale_date);

Avoid Preventing Index Usage

Wrapping indexed columns in functions can make PostgreSQL ignore an otherwise useful index. Rewriting predicates into index-friendly ranges often improves performance significantly.

SELECT *
FROM sales
WHERE sale_date >= DATE '2024-01-01'
  AND sale_date < DATE '2024-02-01';

Validate Improvements, Do Not Guess

A query is only optimized if the new version is measurably better. Compare execution plans, runtime, rows scanned, and whether the result set remains correct after changes.

EXPLAIN ANALYZE
SELECT region, SUM(amount)
FROM sales
WHERE status = 'completed'
GROUP BY region;