Optimizing Long-Running Production Queries

Context

Long-running queries on a heavily used PostgreSQL production system can block other workloads, increase infrastructure cost, and create downstream reporting delays. In a Coca-Cola Consolidated environment, this often affects operational reporting and warehouse-facing data flows.

Question

You are asked how you would approach optimizing a query that is taking hours to run on a heavily utilized production database. Explain how you would investigate the issue safely, what evidence you would gather first, how you would use PostgreSQL tools such as EXPLAIN (ANALYZE, BUFFERS), and how you would decide between rewriting the query, adding or changing indexes, adjusting table design, or moving the workload elsewhere.

Scope guidance

Go beyond generic advice like “add an index.” The interviewer expects a structured production-safe approach that covers query plans, join strategy, filtering, statistics, locking risk, and trade-offs between fast fixes and durable solutions.

Execution Plan Analysis

The first step is to understand where time is actually being spent. In PostgreSQL, EXPLAIN and EXPLAIN (ANALYZE, BUFFERS) show whether the problem is a sequential scan, bad join order, sort spill, misestimated row counts, or repeated work inside nested loops.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, SUM(ol.extended_amount)
FROM orders o
JOIN order_lines ol ON ol.order_id = o.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.order_id;

Index Strategy

Indexes help when they match selective filters, join keys, or sort patterns, but they are not free. In a write-heavy production system, every new index adds maintenance overhead, so you should justify it with the access pattern shown in the plan.

CREATE INDEX CONCURRENTLY idx_orders_order_date_customer
ON orders (order_date, customer_id);

Query Rewriting

Some slow queries are slow because they process too much data too early. Rewriting to filter sooner, aggregate before joining, remove unnecessary DISTINCT, or replace correlated subqueries with set-based logic can reduce work dramatically.

WITH recent_orders AS (
  SELECT order_id, customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT ro.customer_id, COUNT(*) AS order_count
FROM recent_orders ro
GROUP BY ro.customer_id;

Production-Safe Tuning

On a heavily utilized system, the tuning process must avoid making the incident worse. That means testing on a replica or lower environment when possible, using CREATE INDEX CONCURRENTLY, checking lock impact, and validating improvements with actual runtime evidence.

CREATE INDEX CONCURRENTLY idx_shipments_route_date
ON shipments (route_id, delivery_date);

Workload Placement

Sometimes the query is valid but belongs in a different system. If the query scans large fact tables for analytics, the right fix may be to move it to a reporting replica, materialized view, or warehouse layer rather than forcing OLTP PostgreSQL to serve analytical workloads.

CREATE MATERIALIZED VIEW daily_route_volume AS
SELECT route_id, delivery_date, SUM(case_volume) AS total_case_volume
FROM deliveries
GROUP BY route_id, delivery_date;