Query Optimization & Sargability — SQL Course

What you'll learn

Make predicates index-friendly, reduce work early, optimize joins and sorts, and verify improvements with repeatable measurements. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Sargable predicates to a realistic data question
Apply Predicate pushdown to a realistic data question
Apply Join optimization to a realistic data question
Apply Sort and memory to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Sargable predicate	A condition an index can search directly	Compare raw indexed values to transformed parameters where possible
Work reduction	Removing rows or columns before expensive steps	Push selective predicates to the earliest semantically safe point
Benchmark	Repeatable measurement under defined conditions	Compare latency distribution, buffers, and plan—not one warm run

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the measured query optimization question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Use a half-open timestamp range

Avoiding a function on the indexed column lets a normal created_at index support the predicate.

daily_orders.sql

SELECT count(*)
FROM sales.orders
WHERE created_at >= TIMESTAMPTZ '2026-08-01 00:00:00Z'
  AND created_at <  TIMESTAMPTZ '2026-08-02 00:00:00Z';

-- Avoid: WHERE created_at::date = DATE '2026-08-01'

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Rewriting SQL for elegance without a baseline can make performance worse or optimize a path that does not matter.

Independent workshop

Build a review-ready measured query optimization lab against the course commerce dataset.

Your finished workshop must include:

Sargable predicates
Predicate pushdown
Join optimization
Sort and memory
N+1 queries
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Sargable predicate: Compare raw indexed values to transformed parameters where possible
Work reduction: Push selective predicates to the earliest semantically safe point
Benchmark: Compare latency distribution, buffers, and plan—not one warm run

Quick check

1. Which rule best applies to Sargable predicate?

2. Which rule best applies to Work reduction?

3. Which rule best applies to Benchmark?

Next: Transactions, ACID & Savepoints