SQL for Analytics
Advanced 14 min read SQL for Analytics

SQL Query Optimization

Making warehouse queries fast and cheap

Slow analytical queries usually suffer from predictable causes: missing predicates, non-sargable filters, heavy DISTINCT over big tables, and queries pulling more than asked. EXPLAIN and row-count discipline fix most of them.

EXPLAINquery optimizationindexespartition pruningperformanceEXPLAIN ANALYZE
Prerequisites: SQL Aggregations
Before we start

What you will learn

  • Read EXPLAIN / query profile output
  • Apply the fastest wins: filters, sargability, column selection
  • Design queries that use engine features (partition pruning, aggregation pushdown)
Introduction

What is this?

Query optimization is the practice of structuring SQL so the engine scans less and aggregates earlier. The fastest wins are usually cheap: filter high early, avoid non-sargable predicates (function on the column), select only needed columns, avoid DISTINCT and correlated subqueries on big data, and let billable analytics engines prune partitions. EXPLAIN reveals what the planner actually does.

Motivation

Why do you need it?

  • Warehouse costs follow bytes scanned — efficient queries are cheaper and faster.
  • Dashboards degrade when queries regress; optimization keeps SLAs.
  • A 1000x speedup usually costs one line (a WHERE on a partition column).
  • Understanding the plan turns 'why slow?' into a solvable engineering problem.
Think about it

Mental model

Imagine

A library catalog guiding a librarian

Think of

The query planner + indexes

The librarian won't open every shelf to find one book if the catalog (index) points directly there. Filtering early (ask for the right shelf) and using catalog entries is how queries avoid scanning the whole library.

Mechanics

How it works

  • WHERE clauses on partition/date columns let warehouses prune entire storage chunks.
  • Sargable predicates (col = x) use indexes; wrapping columns in functions (UPPER(col)) disables them.
  • EXPLAIN (ANALYZE, COSTS) shows the plan: Seq Scan vs index vs partitions, row estimates, actual times.
  • Pushdown: aggregations and filters that the engine can execute in storage don't ship bytes to your cluster.
  • LIMIT everywhere helps exploration: never materialize a billion rows to look at 10.

Where query time goes and how to cut it.

Scan
Filter
Join
Aggregate
Return
Hands-on

Examples

Before and after

Before and after
-- Slow: scans everything, function on column, selects *
SELECT * FROM events
WHERE UPPER(country) = 'US'
  AND day BETWEEN '2026-01-01' AND '2026-01-31';
 
-- Fast: sargable, date-limited, narrow projection
SELECT user_id, day, amount
FROM events
WHERE country = 'US'
  AND day BETWEEN '2026-01-01' AND '2026-01-31'
LIMIT 100;

Removing the function on the column lets the engine use filtering, the date window prunes partitions, and SELECT * shrinks to three columns.

Real world

In production

  • A dashboard query that scanned 400GB dropped to 4GB by adding a date partition filter.
  • A join slowed by cartesian surprise was fixed with a pre-aggregated daily fact.
  • A reporting query got 40x faster by replacing COUNT(DISTINCT user) over a year with a precomputed DAU model.
Guidelines

Best practices

  • Filter on the largest driver first: usually the date/partition column.
  • Keep predicates sargable — compare raw columns, not wrapped ones.
  • Return only the columns consumed; avoid SELECT * in production.
  • Pre-aggregate heavy rollups into modeled tables for recurring dashboards.
Watch out

Common mistakes

  • Applying a function to the indexed column, killing the index.
  • SELECT * dragging megabytes of columns into a Jupyter notebook.
  • Aggregating before filtering — grouping a table you could have pruned first.
  • Joining at the widest grain and paying for a cartesian blowup.
When things break

Debugging tips

  • Run EXPLAIN (ANALYZE, COSTS) and read rows vs actual rows — estimates that differ wildly hint at stale stats.
  • Slow query? Add a date filter first; 90% of analyst queries are fixed there.
  • Compare bytes scanned before/after each change to track the win.
  • When DISTINCT slows a query, canonicalize identity as a pre-aggregated unique key.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which predicate is sargable (index-friendly)?

2

Adding a date range filter usually reduces warehouse bytes scanned.

Go deeper

Official documentation

PostgreSQL EXPLAIN
docs
BigQuery best practices
docs