Skip to content

Performance & indexing · Hard

A report query is slow. How would you go about optimising it?

Short answer

Start by reading the execution plan rather than guessing. Then reduce the data early: filter before joining, select only needed columns, and aggregate before joins that fan out. Keep predicates index-friendly by not wrapping indexed columns in functions, add or adjust indexes for the real filter and join columns, and consider pre-aggregated summary tables for heavy dashboards.

Detailed answer

A structured answer beats a list of tricks:

  1. Measure – get the plan (EXPLAIN, EXPLAIN ANALYZE in PostgreSQL, the query profile in a warehouse) and find the step that handles the most rows.
  2. Reduce rows early – push filters into WHERE, avoid SELECT *, and aggregate the many side before joining.
  3. Make predicates sargable (search-argument-able). A function on the column hides it from the index:
CREATE INDEX IF NOT EXISTS idx_orders_date ON orders (order_date);

-- Not index-friendly: the function must be evaluated on every row
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE strftime('%Y', order_date) = '2026';
CREATE INDEX IF NOT EXISTS idx_orders_date ON orders (order_date);

-- Index-friendly: a plain range on the raw column
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
  1. Check joins – are join keys indexed and of the same data type? Implicit type casts can disable an index.
  2. Avoid repeated work – replace correlated subqueries that run per row with a single aggregated CTE; prefer UNION ALL over UNION when duplicates are impossible.
  3. Change the shape of the problem – for dashboards that hit the same heavy aggregation all day, a scheduled summary table or materialised view is often the real fix.

In columnar warehouses (BigQuery, Snowflake, Redshift) the levers differ — partitioning, clustering and scanning fewer columns matter more than B-tree indexes. Mentioning that shows breadth.