Context

Slow SQL queries affect dashboards, APIs, and scheduled reports. In PostgreSQL, optimization usually requires understanding both the SQL itself and how the database executes it.

Question

Explain how you would optimize a slow-running query in PostgreSQL. Your answer should cover:

1. How you would identify where the query is spending time
2. What you would check in the execution plan
3. Common query-level fixes, such as filtering earlier or reducing unnecessary work
4. When indexing helps and when it does not
5. How you would validate that the optimized query is actually better

Scope Guidance

Keep the discussion practical and interview-focused. The interviewer expects a structured approach rather than a list of random tuning ideas. You do not need to cover deep PostgreSQL internals, but you should be able to explain the main optimization workflow clearly and mention concrete tools such as EXPLAIN or EXPLAIN ANALYZE.

Execution Plans

The first step in optimization is to inspect how PostgreSQL executes the query. EXPLAIN shows the planned operations, while EXPLAIN ANALYZE shows actual runtime, row counts, and where the plan differs from reality.

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

Index Usage

Indexes help when PostgreSQL needs to filter, join, or sort a subset of rows efficiently. They are less useful when most rows must be scanned anyway, or when the query applies functions that prevent effective index use.

CREATE INDEX idx_orders_order_date ON orders(order_date);

Query Rewriting

A slow query can often be improved by reducing the amount of data processed. Common fixes include selecting only needed columns, filtering earlier, avoiding unnecessary DISTINCT, and simplifying expressions in WHERE clauses.

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

Cardinality and Row Estimates

PostgreSQL chooses plans based on estimated row counts. If estimates are far from actual values, the optimizer may choose a poor strategy such as a sequential scan or an inefficient join method.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'shipped';

Validation and Benchmarking

Optimization is not complete until you confirm the new version is faster and still correct. Compare execution time, I/O behavior, and result accuracy before and after the change using the same workload conditions.

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