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:
- Measure – get the plan (
EXPLAIN,EXPLAIN ANALYZEin PostgreSQL, the query profile in a warehouse) and find the step that handles the most rows. - Reduce rows early – push filters into WHERE, avoid
SELECT *, and aggregate the many side before joining. - 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';
- Check joins – are join keys indexed and of the same data type? Implicit type casts can disable an index.
- Avoid repeated work – replace correlated subqueries that run per row with a single aggregated CTE; prefer
UNION ALLoverUNIONwhen duplicates are impossible. - 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.