How PostgreSQL Executes Queries
The planner generates a plan tree from statistics and cost estimates. This module covers pg_stats, row-count estimates, join ordering, and how cost model parameters shape planning decisions.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Reading EXPLAIN
select name,
setting,
unit,
short_desc
from pg_settings
where name in (
'seq_page_cost',
'random_page_cost',
'cpu_tuple_cost',
'cpu_index_tuple_cost',
'cpu_operator_cost',
'effective_cache_size',
'work_mem'
)
order by name;Name, setting, unit, and description for seven planner cost and memory parameters.
Reading and Interpreting EXPLAIN
EXPLAIN reveals startup cost, total cost, estimated rows, and row width for every node. FORMAT JSON and the indentation model are covered alongside how to read a multi-node plan tree.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Reading EXPLAIN
explain (analyze, buffers, format text)
select r.name as race,
d.surname as winner
from f1db.races r
join f1db.results res
on res.raceid = r.raceid
join f1db.drivers d
on d.driverid = res.driverid
where r.year = 2017
and res.positionorder = 1
order by r.date;Actual and estimated rows, timing, and buffer hits for a three-table race-winners join.
Identifying Performance Bottlenecks
pg_stat_statements ranks queries by cumulative total time — the right starting point for any optimization session. EXPLAIN ANALYZE adds actual rows and timing; BUFFERS shows cache hits vs disk reads.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Reading EXPLAIN
-- 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.
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'.
Query Rewriting Techniques
Predicate pushdown, CTE fences, and subquery flattening change what the planner can see. Restructuring a query to remove barriers often yields more improvement than any index change.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Query Rewriting & Anti-Patterns
-- 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.
Common Anti-Patterns
Leading-wildcard LIKE, implicit type coercion, function-wrapped indexed columns, and NOT IN with nullable columns — each prevents index use in ways that are easy to miss in code review.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Query Rewriting & Anti-Patterns
-- 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.
Indexing for Performance
Partial indexes, covering indexes, expression indexes, and multi-column index column order. Index-only scans eliminate heap access entirely — when they're possible and when the visibility map blocks them.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Indexing Strategy
-- 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.
Advanced Optimization Patterns
Parallel query configuration, partition pruning, and BRIN for sequential data. This module covers when to reach for pg_hint_plan and when to fix the statistics instead.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Query Rewriting & Anti-Patterns
explain (analyze, buffers)
select d.surname,
d.nationality,
count(distinct r.circuitid) as circuits,
count(*) filter(
where res.positionorder=1) as wins,
sum(res.points) as career_pts,
min(r.year) as first_year,
max(r.year) as last_year
from f1db.drivers d
join f1db.results res
on res.driverid = d.driverid
join f1db.races r
on r.raceid = res.raceid
join f1db.circuits c
on c.circuitid = r.circuitid
group by d.driverid, d.surname,
d.nationality
order by career_pts desc
limit 20;Top 20 drivers ranked by career points, with win count, circuits visited, and active years.
Systematic Optimization Workflow
Observe → Diagnose → Fix → Verify: a repeatable cycle for closing the feedback loop on query performance. Performance SLAs, regression guards, and continuous monitoring with pg_stat_statements.
Book reference: Chapter 9 — Indexing Strategy
Free preview: Reading EXPLAIN · Query Rewriting & Anti-Patterns · Indexing Strategy
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.