Optimizing Slow PostgreSQL Production Queries

Context

Slow queries in production affect latency, throughput, and operational stability. For a Software Engineer role, you are expected to explain not just how to add indexes, but how to diagnose the real bottleneck before changing schema or SQL.

Question

You are asked to describe how you would optimize a slow-running PostgreSQL query in a production environment used by a Telus Digital platform. Explain how you would investigate the issue safely, what evidence you would collect, how you would use EXPLAIN / EXPLAIN ANALYZE, and how you would decide whether the problem is caused by joins, filtering, sorting, stale statistics, or poor indexing. Then describe the indexing strategies you would consider, including when to use composite, partial, covering, and expression indexes, and when partitioning may help.

Scope guidance

The interviewer expects a structured production-focused answer: diagnosis first, then query changes, then index design, then validation and trade-offs such as write overhead, storage cost, and lock risk.

Execution Plan Analysis

In PostgreSQL, optimization starts with the execution plan, not with guessing. EXPLAIN shows the planner's chosen path, while EXPLAIN ANALYZE shows actual runtime, row counts, and where estimates diverge from reality.

EXPLAIN ANALYZE
SELECT customer_id, COUNT(*)
FROM interaction_events
WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY customer_id;

Index Selection Strategy

Indexes should match access patterns, not just columns that look important. Equality filters, range filters, join keys, sort keys, and multi-column predicates often need different index shapes such as single-column, composite, or partial indexes.

CREATE INDEX idx_events_customer_time
ON interaction_events (customer_id, event_time DESC);

Statistics and Cardinality

A slow query is sometimes caused by bad planner estimates rather than missing indexes. If table statistics are stale or data distribution is skewed, PostgreSQL may choose a sequential scan or nested loop when a different plan would be faster.

ANALYZE interaction_events;

Covering and Expression Indexes

A covering index with INCLUDE can reduce heap access for read-heavy queries, while expression indexes help when predicates apply functions to columns. Both are useful only when they align with repeated query patterns.

CREATE INDEX idx_lower_email
ON customer_profiles (LOWER(email));

Partitioning as a Performance Tool

Partitioning is not a universal replacement for indexing. It helps most when queries consistently prune large portions of data, such as time-based event tables, and when maintenance operations benefit from smaller partitions.

CREATE TABLE interaction_events (
  event_id BIGINT,
  event_time TIMESTAMP NOT NULL,
  customer_id BIGINT
) PARTITION BY RANGE (event_time);