DE Query Optimization

A query that returns the correct result in 45 minutes is not good enough in a production pipeline that must complete in 5 minutes. Query optimization is the discipline of making SQL queries run faster and use fewer resources without changing their output. Data engineers who understand optimization keep pipelines on schedule and prevent costly compute overruns in cloud data warehouses.

How a Database Executes a Query

Before optimizing, understand what happens when a database receives a SQL query. The database parses the SQL text, generates multiple possible execution plans (ways to retrieve the data), estimates the cost of each plan, and chooses the plan it believes will be fastest. This process runs through the query optimizer — an internal engine that makes most optimization decisions automatically. But the optimizer can only work with what the engineer provides: good table statistics, proper indexes, and well-written queries.

The Iceberg Analogy

A slow query is like an iceberg. What you see above the surface is a few lines of SQL. What causes the slowness hides below: missing indexes, full table scans, unnecessary joins, exploding row counts from cross joins, poor partitioning. Optimization means diving below the surface and understanding the structure of the execution plan.

Using EXPLAIN to Understand Query Plans

Most databases support an EXPLAIN command that shows the query execution plan without actually running the query. Data engineers read EXPLAIN output to identify bottlenecks.

EXPLAIN SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;

EXPLAIN output (simplified):
HashAggregate  (cost=12450.00)
  -> Seq Scan on orders  (cost=0.00..10500.00)
        Filter: (order_date >= '2024-01-01')

"Seq Scan" means the database reads every row in the table.
This is expensive on large tables and signals a missing index.

Indexes: The Fastest Optimization

An index on a frequently filtered or joined column allows the database to jump directly to matching rows rather than scanning the entire table. Adding the right index often reduces query time from minutes to milliseconds.

-- Without index: Seq Scan reads all 10 million rows
SELECT * FROM orders WHERE order_date = '2024-05-15';

-- Add index on order_date
CREATE INDEX idx_orders_date ON orders(order_date);

-- After index: Index Scan reads only matching rows (perhaps 5,000)
SELECT * FROM orders WHERE order_date = '2024-05-15';

Composite Indexes

When queries filter on multiple columns together, a composite index covering those columns together outperforms separate single-column indexes.

-- Query filters on both customer_id and order_date together
SELECT * FROM orders
WHERE customer_id = 'C001' AND order_date >= '2024-01-01';

-- Composite index covers both filter columns
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

Reduce Data Early with Filters

The earlier a query filters out rows, the fewer rows subsequent operations must process. Filtering inside a subquery or CTE before joining to other tables reduces the join workload dramatically.

-- BAD: Join first, then filter (joins all 10M rows)
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';

-- BETTER: Filter orders first, then join (joins only 50K filtered rows)
WITH recent_orders AS (
    SELECT customer_id, amount
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT c.name, ro.amount
FROM customers c
JOIN recent_orders ro ON c.customer_id = ro.customer_id;

Avoid SELECT * in Production

SELECT * reads every column from the table, including those the query does not need. On wide tables in a columnar warehouse, this wastes significant I/O. Selecting only needed columns reduces the data the engine must read and transmit.

-- BAD: reads all 80 columns
SELECT * FROM fact_sales WHERE sale_date = '2024-05-15';

-- BETTER: reads only 3 needed columns
SELECT sale_date, product_id, revenue
FROM fact_sales WHERE sale_date = '2024-05-15';

Partitioning: Physical Data Organization

Large tables in data warehouses partition into smaller physical segments based on a column value — usually a date. A query that filters by date skips all partitions outside the date range, reading only the relevant data.

-- Table partitioned by month
-- Query scans ONLY the May 2024 partition (1/12th of the data)
SELECT product_id, SUM(revenue)
FROM fact_sales
WHERE sale_month = '2024-05'
GROUP BY product_id;

Without partitioning: scans all 5 years of history
With partitioning by month: scans 1 month only

Avoid Functions on Indexed Columns in WHERE

Wrapping a filtered column in a function prevents the database from using an index on that column. The function must evaluate for every row, forcing a full table scan.

-- BAD: YEAR() function prevents index use on order_date
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- GOOD: Range filter uses the index on order_date
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Join Order and Selectivity

Join the most selective table first — the one that filters out the most rows. Joining a million-row fact table to a small 50-row dimension table is fast. Joining two million-row tables and then filtering is slow. Most modern query optimizers handle this automatically, but explicit filtering before large joins always helps.

Caching and Materialized Views

When the same expensive query runs repeatedly, materializing its results prevents recalculation. A materialized view precomputes and stores the result of a complex query. Subsequent reads hit the stored result instead of rerunning the full computation.

-- Create a materialized view for a heavy aggregation
CREATE MATERIALIZED VIEW monthly_revenue_summary AS
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    region,
    SUM(revenue) AS total_revenue
FROM fact_sales
GROUP BY 1, 2;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW monthly_revenue_summary;

Summary

Query optimization improves execution speed and reduces compute cost without changing query results. Key techniques include adding indexes on filtered and joined columns, pushing filters early to reduce row counts before joins, selecting only needed columns, using table partitioning to skip irrelevant data, and materializing expensive repeated queries. Understanding the query execution plan through EXPLAIN gives data engineers the visibility needed to target optimizations precisely.