Context

Slow SQL queries affect dashboards, reports, and application response times. Interviewers ask this question to see whether you can diagnose performance issues methodically instead of guessing.

Question

Explain how you would optimize a SQL query that is running slowly in PostgreSQL. Your answer should cover:

1. How you would identify the real bottleneck
2. What parts of the query and schema you would inspect first
3. How indexing, filtering, and aggregation patterns affect performance
4. How you would validate that your changes actually improved the query

Scope Guidance

Keep the answer practical and structured. The interviewer is not looking for deep PostgreSQL internals, but they do expect a clear workflow: inspect the execution plan, reduce unnecessary work, check indexes, and confirm improvements with measurement.

Execution Plans

The first step in optimization is understanding where the database spends time. In PostgreSQL, EXPLAIN and EXPLAIN ANALYZE show whether the query is scanning too many rows, sorting large datasets, or performing expensive aggregations.

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

Indexing

Indexes help PostgreSQL find rows faster when filtering, joining, or ordering by specific columns. They are most useful when the query touches a small subset of rows; adding indexes blindly can increase write overhead without improving performance.

CREATE INDEX idx_orders_order_date ON orders(order_date);

Reducing Work Early

A common optimization is to filter rows as early as possible and avoid selecting unnecessary columns. Less data flowing through scans, sorts, and aggregations usually means faster execution.

SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= DATE '2024-01-01'
GROUP BY customer_id;

Aggregation Efficiency

Grouping large datasets can be expensive, especially if the query aggregates more rows than needed. Restricting the input set first and grouping only required columns can significantly reduce cost.

SELECT status, COUNT(*) AS order_count
FROM orders
WHERE status IN ('paid', 'shipped')
GROUP BY status;

Measure After Changes

Optimization is not complete until you verify the result. After each change, compare execution plans and runtimes to confirm that the new version is actually faster and still returns correct results.

EXPLAIN ANALYZE
SELECT status, COUNT(*)
FROM orders
WHERE status = 'paid'
GROUP BY status;