Context

Large analytical queries can become slow as data volume grows, especially in customer reporting and product usage analysis. Interviewers want to see whether you can diagnose the real bottleneck instead of suggesting generic fixes.

Question

You are asked to explain how you would optimize a SQL query that is taking too long to run on a large dataset in PostgreSQL. Walk through how you would investigate the issue, what parts of the query and schema you would inspect, and which changes you would consider across joins, aggregations, filters, CTEs, indexes, and execution plans. If relevant, refer to customer analytics workloads such as queries over Apidel Technologies customer events, accounts, and subscription activity.

Scope guidance

Go beyond saying "add an index." The interviewer expects a structured explanation of how you would use EXPLAIN or EXPLAIN ANALYZE, identify expensive operations, reduce scanned data, choose better join strategies, and validate that the optimized query is actually faster without changing the result.

Execution Plan Analysis

The first step is to inspect how PostgreSQL is actually running the query rather than guessing. EXPLAIN ANALYZE shows the chosen plan, row estimates versus actual rows, timing, join methods, sorts, and scans, which helps identify the true bottleneck.

EXPLAIN ANALYZE
SELECT c.customer_id, SUM(e.event_value)
FROM customer_events e
JOIN customers c ON c.customer_id = e.customer_id
WHERE e.event_time >= DATE '2024-01-01'
GROUP BY c.customer_id;

Reducing Data Early

A common optimization is to filter and aggregate as early as possible so later joins process fewer rows. This is especially important in event tables where scanning unnecessary history can dominate runtime.

WITH filtered_events AS (
  SELECT customer_id, event_value
  FROM customer_events
  WHERE event_time >= DATE '2024-01-01'
)
SELECT customer_id, SUM(event_value)
FROM filtered_events
GROUP BY customer_id;

Index Strategy

Indexes help when predicates, join keys, and sort keys are selective enough to avoid large scans or expensive sorts. The right index depends on access pattern; indexing every column is not helpful and can slow writes.

CREATE INDEX idx_customer_events_customer_time
ON customer_events (customer_id, event_time);

Join and Aggregation Costs

Large joins and aggregations often drive query cost. You should check whether joins are multiplying rows unexpectedly, whether the join type is appropriate, and whether grouping can happen before joining to dimension tables.

SELECT e.customer_id, SUM(e.event_value)
FROM customer_events e
GROUP BY e.customer_id;

Validation After Optimization

Optimization is not complete until you confirm both correctness and performance improvement. A faster query that changes row counts, duplicates data, or drops NULL-preserving behavior is not a valid optimization.

EXPLAIN ANALYZE
WITH customer_totals AS (
  SELECT customer_id, SUM(event_value) AS total_value
  FROM customer_events
  WHERE event_time >= DATE '2024-01-01'
  GROUP BY customer_id
)
SELECT *
FROM customer_totals;