Optimization Basics: SQL Analysis Chapter 8

Why this chapter matters

Fast, stable queries keep dashboards responsive and reduce infrastructure cost under growth.

What you will learn

Read basic EXPLAIN output to spot expensive operations.
Use indexes that match common filter and join predicates.
Avoid anti-patterns that defeat index usage.

Understand the core ideas

Optimization begins with evidence, not assumptions. In PostgreSQL, EXPLAIN reveals planned operations such as sequential scans, index scans, hash joins, and sorts, which helps identify where cost concentrates. First verify query correctness and row grain, then optimize. A fast wrong query is still wrong. Once correctness is locked, inspect whether expensive steps align with business intent. For dashboard queries that repeatedly filter by date and customer, indexes on those predicates often provide the highest return. Keep predicates sargable, meaning the indexed column appears directly in comparisons, because wrapping indexed columns in functions can block index usage. Use selective filters early when possible, and avoid pulling unused columns that increase IO. Performance work should be incremental so each change can be measured and explained.

EXPLAIN ANALYZE runs the query and reports actual timing and row counts, letting you compare planner estimates to reality. Large estimate mismatches often indicate outdated statistics or skewed distributions. In PostgreSQL, running ANALYZE can improve estimates, but schema and query design still matter most for durable gains. Validate optimization changes with representative data volume and similar concurrency to production use, because tiny local datasets can hide bottlenecks. Also ensure semantic equivalence by comparing result sets before and after changes, especially when rewriting joins or filters. A practical pattern is to add one index, rerun plan and timing, then keep or revert based on measurable improvement. Optimization discipline is about preserving output grain and logic while reducing resource cost, not about chasing plan shapes that look advanced.

Key terms

execution plan: The strategy PostgreSQL chooses to retrieve and process rows for a query.
sargable predicate: A filter expression that allows PostgreSQL to use an index efficiently.
selectivity: How strongly a filter reduces row count relative to the full table.
statistics: Metadata PostgreSQL uses to estimate row counts and choose query plans.

Speed up a dashboard orders query

Use orders(order_id, customer_id, status, created_at, total) with one row per order. Query filters paid orders in a date range for one customer segment and aggregates totals by day.

Run EXPLAIN ANALYZE on the baseline aggregation query and note if PostgreSQL performs a sequential scan across the full orders table.
After inspecting the plan, test indexes with equality columns first and then the most selective range condition, such as (status, customer_id, created_at) versus (status, created_at). Compare both with EXPLAIN ANALYZE instead of assuming one order is best.
Rerun EXPLAIN ANALYZE and compare actual timing plus row counts at scan nodes to confirm the index is used and work reduced.
Confirm semantic equivalence by comparing grouped output between old and new query forms for the same parameter window.

Result:

EXPLAIN ANALYZE SELECT date_trunc('day', created_at)::date AS day_start, SUM(total) AS daily_revenue FROM orders WHERE status = 'paid' AND created_at >= TIMESTAMP '2026-01-01' AND created_at < TIMESTAMP '2026-02-01' AND customer_id BETWEEN 1000 AND 5000 GROUP BY date_trunc('day', created_at)::date ORDER BY day_start;

lets you compare candidate indexes. Keep an index only when measured plans reduce work for the real workload while preserving one row per day output grain.

A common misconception

Claim: Adding indexes always speeds every query.

Correction: Indexes help only when query predicates and join patterns can use them. They also add write overhead, so add targeted indexes based on measured workload.

Lessons in this chapter

Plan-first optimizationInspect PostgreSQL execution plans before changing query structure. Read the full guide →
Index strategy fundamentalsMap frequent predicates to practical index definitions.
Sargable filter patternsWrite predicates that allow index scans when possible.
Safe performance testingBenchmark with representative data and verify result equivalence.

Study task

Take a slow PostgreSQL dashboard query, run EXPLAIN, add one targeted index, and compare plan and runtime before and after.

Chapter checkpoint

What PostgreSQL command helps you inspect how a query will execute?

EXPLAIN shows the planned execution strategy, and EXPLAIN ANALYZE also runs the query with timing.

Optimization Basics