Context

At Splice, product growth analysis often relies on large event tables such as app activity, subscription changes, and engagement logs. On massive datasets, a correct SQL query can still be unusable if it scans too much data or joins inefficiently.

Core question

Explain how you would optimize a SQL query that is running too slowly on a massive PostgreSQL dataset. In your answer, cover:

1. How you would diagnose the bottleneck before changing the query
2. How you would evaluate joins, filters, aggregations, CTEs, subqueries, and window functions
3. What indexing, partitioning, or data-model changes you might consider
4. How you would validate that the optimized query is both faster and still correct

Scope guidance

The interviewer is looking for a practical, structured explanation rather than generic advice. Focus on PostgreSQL-specific reasoning, trade-offs, and the kinds of query patterns a Product Growth Analyst might use on Splice event and subscription data.

Use EXPLAIN ANALYZE first

The first step is to inspect the actual execution plan instead of guessing. In PostgreSQL, EXPLAIN ANALYZE shows where time is spent, how many rows flow through each step, and whether the planner chose inefficient sequential scans, sorts, or join strategies.

EXPLAIN ANALYZE
SELECT user_id, COUNT(*)
FROM splice_app_events
WHERE event_time >= DATE '2024-01-01'
GROUP BY user_id;

Reduce data as early as possible

Slow queries often process far more rows than necessary before filtering or aggregating. Pushing selective filters earlier, pre-aggregating before joins, and avoiding unnecessary columns can significantly reduce memory, sort cost, and join volume.

WITH filtered_events AS (
  SELECT user_id, session_id
  FROM splice_app_events
  WHERE event_time >= DATE '2024-01-01'
    AND surface_name = 'Splice Desktop'
)
SELECT user_id, COUNT(DISTINCT session_id)
FROM filtered_events
GROUP BY user_id;

Choose indexes that match access patterns

Indexes help only when they align with the query's predicates, join keys, and sort order. For large analytical tables, composite indexes on common filter-plus-join patterns can be more effective than many single-column indexes.

CREATE INDEX idx_splice_app_events_surface_time_user
ON splice_app_events (surface_name, event_time, user_id);

Be careful with CTEs, window functions, and DISTINCT

These constructs are useful but can become expensive at scale. Window functions require ordering within partitions, DISTINCT can trigger large sorts or hashes, and multi-level CTEs may hide opportunities to simplify or pre-aggregate the data flow.

SELECT
  user_id,
  event_time,
  LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prior_event_time
FROM splice_app_events;

Validate both performance and correctness

A faster query is not useful if it changes business logic. Good optimization includes comparing row counts, aggregates, edge cases, and execution plans before and after the change to confirm the result is equivalent and materially faster.

WITH old_result AS (
  SELECT user_id, COUNT(*) AS event_count
  FROM splice_app_events
  GROUP BY user_id
),
new_result AS (
  SELECT user_id, COUNT(*) AS event_count
  FROM splice_app_events
  GROUP BY user_id
)
SELECT *
FROM old_result
EXCEPT
SELECT *
FROM new_result;