Course 6 — Query Optimization Fundamentals

Module 01

Finding What to Optimize

Core · 4 topics

"The database feels slow" is not a starting point — it's a symptom without a location. Before anything is EXPLAINed, the first job is triaging pg_stat_statements into a ranked, reproducible list of what to fix first.

Book reference: Chapter 9 — Indexing Strategy

Triage with pg_stat_statementsRank queries by cumulative total time to find where optimization actually pays off.

Separating CPU-Bound from I/O-Bound TimeTell apart queries slow from disk reads and queries slow from computation.

A Fast Recap: the Cost Model and StatisticsRefresh how planner cost estimates and table statistics interact.

From a Ranked List to a PlanTurn a prioritized query list into a concrete optimization plan.

Free preview: Reading EXPLAIN

Query execution pipeline — Trace the path a query takes from parsing to execution.

Module 01 · pg_stat_statements triage

select query,
       calls,
       round(total_exec_time::numeric, 1) as total_ms,
       round(mean_exec_time::numeric, 1)  as mean_ms,
       round((100 * total_exec_time
              / sum(total_exec_time) over ())::numeric, 1)
                                           as pct_total
  from pg_stat_statements
 order by total_exec_time desc
 limit 10;

Top 10 statement shapes ranked by cumulative execution time and share of total load.

Module 02

Indexing for Performance

Core · 7 topics

An index gives the planner a new access path: instead of scanning every page, it can follow the B-tree straight to matching rows. This module covers every index type f1db, geoname, and hashtag put to use.

Book reference: Chapter 9 — Indexing Strategy

Index types in f1db; GiST kNN search on geoname; GIN array containment on hashtagChoose the right index type for your data shape and access pattern.

Selectivity and index choicePredict when the planner will choose an index over a full table scan.

Composite indexes and column order; partial indexesDesign multi-column and partial indexes that your queries can actually use.

Index-only scans and the visibility mapUnderstand when a query can be answered entirely from the index.

Covering indexes with INCLUDEBuild covering indexes that satisfy queries without touching the table.

BRIN: physical order on geoname IDsUse BRIN indexes on data whose physical order already matches query order.

When an index won't helpRecognize the queries no index can speed up, and why.

Free preview: Indexing Strategy

Index types — Match the index variant to the query it needs to serve.

Module 02 · Partial index targeting

-- This query scans all results to find
-- race winners — a partial index on
-- positionorder = 1 would eliminate it.
--
-- CREATE INDEX ON f1db.results (raceid)
-- WHERE positionorder = 1;
explain (analyze, buffers)
select res.raceid,
       res.driverid,
       res.constructorid
  from f1db.results res
 where res.positionorder = 1
 order by res.raceid;

Raceid, driverid, and constructorid for every race winner, ordered by raceid.

Module 03

Query Rewriting Techniques

Core · 5 topics

Small changes in SQL can have large performance impacts. The most effective rewrites work with the planner's cost model — reducing intermediate result sets and removing barriers it can't see through.

Book reference: Chapter 9 — Indexing Strategy

Predicate pushdownRewrite queries so filters are applied as early as possible.

EXISTS vs IN for subqueriesChoose the right subquery form to avoid unnecessary row materialization.

CTE optimization fencesControl whether a CTE is a planning barrier or a transparent subquery.

Window function and aggregate rewritesRewrite window and aggregate queries into forms the planner executes more cheaply.

Removing unnecessary work: DISTINCT, COUNT semanticsRemove hidden deduplication work that adds overhead without changing results.

Free preview: Query Rewriting & Anti-Patterns

Plan tree showing predicate pushdown — See where filters land in the plan tree.

Module 03 · Predicate pushdown

-- Inline predicates give the planner freedom
-- to push them to the earliest scan.
explain (analyze, buffers)
select r.name, r.date,
       d.surname as winner
  from f1db.races   r
  join f1db.results res
    on res.raceid        = r.raceid
   and res.positionorder = 1
  join f1db.drivers d
    on d.driverid        = res.driverid
 where r.year = 2017;

Race names, dates, and 2017 winners with all predicates placed inline in the join.

Module 04

Common Anti-Patterns

Core · 3 topics

Some slow queries are not exotic at all — they are the same handful of mistakes, written daily in every codebase. Each one has a recognizable EXPLAIN signature and a mechanical fix.

Book reference: Chapter 9 — Indexing Strategy

Non-sargable predicatesSpot predicates that silently defeat index use and know how to fix them.

OFFSET pagination vs keyset paginationImplement pagination that stays fast regardless of which page you request.

SELECT * and row width impactLimit column selection to what queries actually need.

Free preview: Query Rewriting & Anti-Patterns

B-tree and non-sargable predicates — See how a predicate form can defeat index access.

Module 04 · Keyset pagination

-- Keyset: seek straight to the last key seen.
-- The B-tree descends once and reads exactly
-- one page of rows — page 5000 costs the
-- same as page 1.
explain (analyze, buffers)
select geonameid, name
  from geoname.geoname
 where geonameid > 10150618
 order by geonameid
 limit 20;

Twenty geoname rows starting after a known geonameid boundary, ordered by geonameid.

Module 05 · Advanced

Statistics: Correlated Columns

Advanced · 4 topics

Every planner estimate assumes columns are independent unless told otherwise — selectivity(a=1 AND b=2) is computed as if knowing one told you nothing about the other. CREATE STATISTICS fixes that.

Book reference: Chapter 9 — Indexing Strategy

Misestimation from correlated columnsFix row-count misestimates caused by columns that are not statistically independent.

ndistinct: multi-column GROUP BY cardinality; mcv: correlated but not functionally dependentChoose the right extended-statistics kind for the misestimate you're seeing.

Expression statistics; confirming the fix in pg_stats_extVerify a CREATE STATISTICS fix actually changed the planner's estimate.

SET STATISTICS: one column's sample; choosing the right toolPick between per-column sample size and extended statistics for a given problem.

Free preview: Reading EXPLAIN

Estimated vs actual row counts — Spot estimate divergence and trace it to a correlated-column problem.

Module 05 · Correlated column misestimate

-- geoname.class and .feature are strongly
-- correlated: 'PPL' always implies class 'P'.
-- The planner multiplies selectivities as
-- if they were independent and lands far
-- below the actual row count.
--
-- create statistics geoname_class_feature
--   (dependencies) on class, feature
--   from geoname.geoname;
explain (analyze, buffers)
select name, population
  from geoname.geoname
 where class   = 'P'
   and feature = 'PPL';

EXPLAIN ANALYZE plan for geoname rows matching both class 'P' and feature 'PPL'.

Module 06 · Advanced

Configuration Tuning

Advanced · 5 topics

Indexes, rewrites, and statistics change what the planner knows and what access paths exist. Configuration changes what the planner believes about the hardware itself — memory, cost, and parallelism settings.

Book reference: Chapter 9 — Indexing Strategy

work_mem: sorts and hashesSize work_mem to keep sorts and hash joins from spilling to disk.

effective_cache_sizeTell the planner how much of the database the OS actually keeps cached.

random_page_cost and seq_page_costTune cost model settings to match your storage's real random-access performance.

Parallel queryConfigure when and how aggressively the planner reaches for parallel workers.

Reading these settings togetherCombine memory, cost, and parallelism settings without fighting each other.

Free preview: Indexing Strategy

Planner configuration parameters — See how memory, cost, and parallelism settings interact.

Module 06 · Cost and memory settings

select name,
       setting,
       unit,
       short_desc
  from pg_settings
 where name in (
   'work_mem',
   'effective_cache_size',
   'random_page_cost',
   'seq_page_cost',
   'max_parallel_workers_per_gather'
 )
 order by name;

Name, setting, unit, and description for five memory, cost, and parallelism parameters.

Module 07 · Advanced

Systematic Optimization Workflow

Advanced · 5 topics

By this point you have a diagnosis and a full toolbox — indexes, rewrites, extended statistics, configuration. This module is the discipline that turns fixes into verified, monitored improvements.

Book reference: Chapter 9 — Indexing Strategy

Fix: one change at a timeApply a single targeted fix and measure its effect before making another.

Worked examples: join selectivity, materialized CTEs, decomposing a joinWalk through three real fixes end to end, from diagnosis to verified result.

Verify: compare plansConfirm each optimization actually improved the plan before moving on.

Production monitoring: closing the loopKeep pg_stat_statements as an ongoing feedback loop, not a one-time triage.

Rollback disciplineKnow how to safely back out a fix that didn't help.

Free preview: Reading EXPLAIN · Query Rewriting & Anti-Patterns · Indexing Strategy

Optimization workflow — Follow each step of a repeatable optimization cycle.

Module 07 · Baseline plan comparison

-- Step 1: capture the default plan and cost.
-- Step 2: apply one fix, then compare actual
-- time against the captured baseline.
explain (analyze, buffers)
select d.surname,
       sum(res.points) as points
  from f1db.results res
  join f1db.drivers d
    on d.driverid = res.driverid
 group by d.driverid, d.surname
 order by points desc
 limit 10;

Total career points per driver ranked descending, limited to the top 10.

Module 08 · Architect

Performance Architecture

Architect · 6 topics

At scale, query optimization becomes part of system design. Partitioning strategy, parallelism, materialized views, and denormalization determine whether individual query fixes hold up under load.

Book reference: Chapter 9 — Indexing Strategy

Partitioning and partition pruning; the reporting workload patternLeverage table partitioning to skip irrelevant data at planning time.

Chinook: optimizing with foreign keysUse declared foreign keys to give the planner join-elimination opportunities.

Performance budgets and caching; materialized viewsSet performance targets per query and cache results inside the database.

Generated columns; JSON and array indexing strategiesIndex JSONB documents and arrays for the access patterns they're actually queried with.

Scaling out with Citus; three rules for denormalizingKnow when to shard, and when denormalization is the simpler fix.

When not to optimizeKnow when a query is good enough and when to stop optimizing.

Free preview: Reading EXPLAIN · Query Rewriting & Anti-Patterns · Indexing Strategy

Citus coordinator and worker architecture — Scale out with Citus: coordinator, workers, and sharded data.

Module 08 · Partition inventory

select parent.relname  as parent_table,
       child.relname   as partition_name,
       pg_size_pretty(
         pg_relation_size(child.oid)
       )               as partition_size
  from pg_inherits
  join pg_class parent
    on parent.oid = pg_inherits.inhparent
  join pg_class child
    on child.oid  = pg_inherits.inhrelid
  join pg_namespace n
    on n.oid      = parent.relnamespace
 where n.nspname = 'f1db'
 order by parent.relname, child.relname;

Every f1db partition's name and physical size, grouped by parent table.

Module	Title	Focus
Mod 01Core	Finding What to Optimize	pg_stat_statements triage, CPU vs I/O time
Mod 02Core	Indexing for Performance	B-tree, GiST, GIN, BRIN, partial, covering, INCLUDE
Mod 03Core	Query Rewriting Techniques	Predicate pushdown, EXISTS vs IN, CTE fences
Mod 04Core	Common Anti-Patterns	Non-sargable predicates, OFFSET, SELECT *
Mod 05Advanced	Statistics: Correlated Columns	CREATE STATISTICS, ndistinct, mcv, pg_stats_ext
Mod 06Advanced	Configuration Tuning	work_mem, effective_cache_size, cost settings
Mod 07Advanced	Systematic Optimization Workflow	One change at a time, verify, production monitoring
Mod 08Architect	Performance Architecture	Partitioning, caching, JSON/arrays, Citus

Query Optimization Fundamentals

Finding What to Optimize

Indexing for Performance

Query Rewriting Techniques

Common Anti-Patterns

Statistics: Correlated Columns

Configuration Tuning

Systematic Optimization Workflow

Performance Architecture

All 6 courses. One package.