7. Query Processing and Performance

A SQL statement says what result is wanted, and PostgreSQL has to work out how to compute it: which indexes to use, in what order to join tables, and how much memory each step may take. This chapter follows a query through that pipeline, explains the cost model and statistics that drive the planner's choices, teaches you to read execution plans, and covers the settings, application patterns, and monitoring tools that make a database fast and keep it that way. Most slow queries come from a bad estimate, a missing index, or too many round trips, and each of these shows up in tools covered here.

<a id="71-the-query-pipeline"></a>

7.1 The Query Pipeline

This subchapter traces a statement from the moment its text arrives at a backend process to the moment rows flow back to the client: parsing, rewriting, planning, and execution, followed by how prepared statements let PostgreSQL skip some of that work on repeated queries.

Parsing and the Parse Tree

A SQL string is just text, and a computer cannot execute text directly. Before anything else, PostgreSQL has to check that the text is well-formed SQL and turn it into a data structure that later stages can work with, much as a compiler turns source code into a syntax tree before generating machine code. That first stage is parsing, and its output is a parse tree.

The four stages. Every statement sent to a backend passes through the same pipeline:

  SQL text
     |
     v
 +---------+   raw parse tree   +----------+   query tree   +-----------+   query tree(s)
 | Parser  | -----------------> | Analyzer | -------------> | Rewriter  | ---------------+
 | (syntax)|                    | (meaning)|                | (views,   |                |
 +---------+                    +----------+                |  rules)   |                v
                                                            +-----------+          +-----------+
                                                                                   | Planner / |
   rows to client  <---------  +-----------+  <-------- plan tree  --------------  | Optimizer |
                               | Executor  |                                       +-----------+
                               +-----------+

Parsing itself has two steps:

Step	Input	Checks	Needs the catalog?	Output
Raw parsing (lexer + grammar, generated by Flex and Bison)	Text	Keywords, punctuation, nesting: is this valid SQL grammar?	No	Raw parse tree
Parse analysis (semantic analysis)	Raw parse tree	Do the tables and columns exist? Which function or operator matches these types? Does the user have privileges?	Yes	Query tree (Query node)

The split matters because raw parsing does not look up any tables. That is why it can run outside a transaction, and why a syntax error is reported before PostgreSQL even knows whether a table exists.

Errors from each step. The error messages tell you which step failed:

SELECT id FROM events WHER id = 1;            -- raw parser: grammar is wrong
-- ERROR:  syntax error at or near "id"
-- LINE 1: SELECT id FROM events WHER id = 1;
--                                    ^         -- WHER was read as a table alias, so "id" is unexpected

SELECT id FROM event WHERE id = 1;            -- analyzer: grammar fine, but no such table
-- ERROR:  relation "event" does not exist

SELECT id FROM events WHERE kind = 1;         -- analyzer: no "=" operator for (text, integer)
-- ERROR:  operator does not exist: text = integer
-- HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

What analysis resolves. The analyzer replaces names with object identifiers (OIDs) and fixes the meaning of every expression:

• Table names become entries in a range table, identified by OID (relid). Renaming the table later does not change an already-analyzed query.
• * is expanded into the list of columns that exist right now. A view defined as SELECT * does not pick up columns added later.
• Each column reference becomes a Var (range-table index, column number, type).
• Overloaded functions and operators are resolved to one specific OID, and implicit casts are inserted.
• Literals get a type: 7 becomes an int4 constant, '2025-12-01' becomes whatever the context requires.

Seeing the tree. Debug settings print each stage's tree into the server log, or into your session when client_min_messages is low enough:

SET debug_print_parse   = on;      -- also: debug_print_rewritten, debug_print_plan
SET debug_pretty_print  = on;      -- indent the output
SET client_min_messages = log;     -- show LOG messages in this session

SELECT kind FROM events WHERE id = 7;
-- LOG:  parse tree:
-- DETAIL:     {QUERY
--    :commandType 1                          -- 1 = SELECT
--    :rtable (
--       {RANGETBLENTRY
--       :eref {ALIAS :aliasname events :colnames ("id" "user_id" "kind" ...)}
--       :relid 16386                         -- the table's OID, not its name
--       ...
--    :jointree
--       {FROMEXPR
--       :quals
--          {OPEXPR
--          :opno 416                         -- the resolved operator: int8 = int4
--          :args (
--             {VAR :varno 1 :varattno 1 :vartype 20 ...}         -- events.id, type int8
--             {CONST :consttype 23 ... :constvalue 4 [ 7 0 0 ...]} -- literal 7, type int4

The output is verbose, but it shows the key point: after analysis, nothing in the tree refers to names anymore. The operator = has become operator 416 (bigint = integer), and the literal 7 has a type.

Sample data for this chapter. Most examples use the one-million-row events table built in Pages, Blocks, and Tuples and a users table for joins. Rebuild events with that script so it has only its primary key, then run:

DROP TABLE IF EXISTS users CASCADE;               -- replaces earlier users tables

CREATE TABLE users (
  id        int         PRIMARY KEY,
  name      text        NOT NULL,
  city      text        NOT NULL,
  country   char(2)     NOT NULL,                 -- determined by city: useful in 7.2
  plan      text        NOT NULL,                 -- 90 % free, 9 % pro, 1 % enterprise
  signed_up timestamptz NOT NULL
);

INSERT INTO users (id, name, city, country, plan, signed_up)
SELECT s.g, 'user_' || s.g, c.city, c.country,
       CASE WHEN s.r < 0.90 THEN 'free' WHEN s.r < 0.99 THEN 'pro' ELSE 'enterprise' END,
       timestamptz '2020-01-01 00:00+00' + random() * interval '5 years'
FROM (SELECT g, random() AS r, 1 + floor(random() * 16)::int AS k
      FROM generate_series(1, 100000) AS g) AS s
JOIN (VALUES (1, 'New York', 'US'),   (2, 'Chicago', 'US'),
             (3, 'Berlin', 'DE'),     (4, 'Munich', 'DE'),
             (5, 'Paris', 'FR'),      (6, 'Lyon', 'FR'),
             (7, 'Rome', 'IT'),       (8, 'Milan', 'IT'),
             (9, 'Madrid', 'ES'),     (10, 'Seville', 'ES'),
             (11, 'Sao Paulo', 'BR'), (12, 'Rio', 'BR'),
             (13, 'Mumbai', 'IN'),    (14, 'Delhi', 'IN'),
             (15, 'Tokyo', 'JP'),     (16, 'Osaka', 'JP')) AS c (k, city, country)
  ON c.k = s.k;                                   -- 16 cities, two per country

CREATE INDEX events_user_id_idx    ON events (user_id);
CREATE INDEX events_created_at_idx ON events (created_at);
VACUUM ANALYZE events, users;

The plans shown in this chapter come from PostgreSQL 18 with default settings. Costs, row counts, and timings on your machine will differ slightly because the data is random.

Key takeaways

• Every statement goes through parser, analyzer, rewriter, planner, and executor, in that order.
• The raw parser checks grammar only, without catalog access; parse analysis resolves names, types, operators, and privileges against the catalog.
• After analysis, the query tree refers to objects by OID, * is expanded, and every expression has a definite type.
• The error message tells you which stage rejected a statement: "syntax error" comes from the grammar, "does not exist" from analysis.

🧪 Practice

1. (Beginner) For each statement, predict whether the parser or the analyzer rejects it, then run it to check: SELEC 1;, SELECT nosuchcol FROM users;, SELECT 1 +;, SELECT length(42);.
2. (Intermediate) Create a view v AS SELECT * FROM users, then ALTER TABLE users ADD COLUMN note text. Does SELECT * FROM v show note? Explain the result using what analysis does with *.
3. (Interview) "Why does PostgreSQL report a syntax error in a statement that references a table that does not exist, instead of reporting the missing table?" Hint: Consider which stage needs the catalog and what order the stages run in.

Rewrite System and Rules

Some objects in PostgreSQL are not really tables. A view, for example, stores no rows; it is a saved query. Somewhere between parsing and planning, a reference to purchases (a view) has to be replaced by the query that defines it. That is the job of the rewriter, a stage that transforms query trees according to rules stored in the catalog before the planner sees them.

Analogy. The rewriter works like macro expansion in a programming language: a name is replaced with its definition, and the result is then compiled as if the programmer had written the expanded text.

How views are implemented. A view is an empty relation plus a rule named _RETURN of type ON SELECT ... DO INSTEAD. When a query mentions the view, the rewriter substitutes the view's query tree as a subquery:

CREATE VIEW purchases AS
  SELECT id, user_id, amount, created_at FROM events WHERE kind = 'purchase';

SELECT rulename, ev_type FROM pg_rewrite WHERE ev_class = 'purchases'::regclass;
--  rulename | ev_type
-- ----------+---------
--  _RETURN  | 1                                  -- 1 = SELECT rule

EXPLAIN SELECT user_id, amount FROM purchases WHERE created_at >= '2025-12-01';
--  Index Scan using events_created_at_idx on events  (cost=0.42..1557.68 rows=1660 width=10)
--    Index Cond: (created_at >= '2025-12-01 00:00:00+00'::timestamp with time zone)
--    Filter: (kind = 'purchase'::text)          -- the view's WHERE, merged into the outer query

The plan never mentions the view. After rewriting, the query is SELECT ... FROM (SELECT ... FROM events WHERE kind = 'purchase') AS purchases WHERE created_at >= ..., and the planner then flattens the subquery so both conditions apply to one scan of events. This is why a view adds no runtime cost compared with writing its query inline.

Other work done by the rewriter

Feature	What the rewriter does
Views	Replaces the view with its defining query (_RETURN rule)
Auto-updatable views	Turns INSERT/UPDATE/DELETE on a simple view into the same command on its base table (9.4)
Row-level security	Adds policy expressions as security qualifiers (10.3)
User-defined rules	Applies CREATE RULE definitions: DO INSTEAD, DO ALSO, or DO NOTHING

User-defined rules. CREATE RULE lets you attach rewrite actions to INSERT, UPDATE, or DELETE on a table or view. A DO ALSO rule adds a second query; DO INSTEAD replaces the original:

CREATE TABLE event_archive (LIKE events);

CREATE RULE events_archive_on_delete AS ON DELETE TO events
  DO ALSO INSERT INTO event_archive SELECT OLD.*;    -- OLD refers to the rows being deleted

EXPLAIN (COSTS OFF) DELETE FROM events WHERE id = 5;
--  Insert on event_archive                         -- query 1: added by the rule
--    ->  Index Scan using events_pkey on events
--          Index Cond: (id = 5)
--
--  Delete on events                                -- query 2: the original statement
--    ->  Index Scan using events_pkey on events
--          Index Cond: (id = 5)

DROP RULE events_archive_on_delete ON events;

The plan shows the essential difference from a trigger: the rule produced a second query, which runs once for the whole statement, rather than code that runs once for each deleted row.

Rules vs. triggers. Rules are older than triggers and are rarely the right tool today:

	Rule	Trigger
Works on	The query tree, before planning	Each row (or statement), during execution
Volatile functions	May be evaluated more than once, since OLD/NEW are expressions copied into another query	Evaluated once, values are known
COPY	Ignored	Fired
Reasoning about correctness	Hard: the effect depends on how the query is written	Straightforward
Typical modern use	Views (internally)	Auditing, validation, derived data (9.3)

The PostgreSQL documentation itself recommends triggers over rules for most purposes. A classic surprise: a DO ALSO rule that logs NEW.id for a table with an identity column calls nextval a second time inside the rule's query, so the logged id does not match the inserted one.

Key takeaways

• The rewriter transforms query trees using rules from pg_rewrite before planning.
• Views are implemented as an ON SELECT DO INSTEAD rule named _RETURN; the planner then flattens the substituted subquery, so simple views cost nothing extra.
• Row-level security policies and updatable views are also applied at this stage.
• Prefer triggers to user-defined rules; rules rewrite queries, not rows, and behave surprisingly with volatile functions and COPY.

🧪 Practice

1. (Beginner) Run EXPLAIN SELECT * FROM purchases WHERE user_id = 42 and identify which conditions came from the view and which from your query.
2. (Intermediate) Create a view that uses DISTINCT or LIMIT and query it with an extra WHERE clause. Compare its plan with the inline query and explain why the planner can no longer merge everything into one scan.
3. (Interview) "A team uses a DO INSTEAD rule to redirect inserts from a legacy table to a new one. Bulk loads with COPY suddenly stop reaching the new table. Why?" Hint: Think about which statements go through the rewriter.

Planner and Optimizer Overview

For a single SQL statement there are usually many correct ways to compute the result. A three-table join can be done in several orders, each join can use one of three algorithms, and each table can be read with a sequential scan or one of several indexes. The answers are identical, but the running times can differ by a factor of a million. The planner (also called the optimizer) chooses among these alternatives. It is the part of PostgreSQL that makes declarative SQL practical.

Analogy. A navigation app knows many routes between two addresses. It estimates the travel time of each from road lengths and typical traffic, then picks the fastest. The planner does the same: it estimates a cost for each candidate plan from table sizes and statistics, and picks the cheapest. Like the navigation app, it is only as good as its information; with wrong traffic data, it confidently picks a bad route.

What the planner decides

Decision	Options
How to read each table	Sequential scan, index scan, index-only scan, bitmap scan, TID scan
Join order	Which tables are joined first; for N tables there are roughly N! orders
Join method	Nested loop, hash join, merge join
Aggregation	Hash aggregate or sort-based group aggregate
Parallelism	Whether to split the work across background workers
Subqueries	Whether to flatten them into joins, or run them as subplans

How it searches

1. Preprocessing. Simplify expressions (1 + 1 becomes 2), flatten subqueries and views where possible, convert IN (SELECT ...) and EXISTS into semi-joins (Rewriting Subqueries as Joins), and push WHERE conditions as far down as they can go.
2. Access paths. For each table, generate every reasonable way to read it and keep the cheapest ones (plus any that produce useful sort orders).
3. Join search. Build the join tree bottom-up with dynamic programming: find the best way to join every pair of tables, then every triple using the best pairs, and so on. For queries with many tables (geqo_threshold, default 12), this becomes too expensive, and PostgreSQL switches to a genetic query optimizer (GEQO) that searches heuristically.
4. Finishing. Add aggregation, sorting, LIMIT, and so on, and pick the cheapest complete plan.

The same query, two plans. The planner chose a nested loop for this join because only about 1 % of users are on the enterprise plan:

EXPLAIN
SELECT u.country, count(*)
FROM users AS u JOIN events AS e ON e.user_id = u.id
WHERE u.plan = 'enterprise'
GROUP BY u.country;
--  HashAggregate  (cost=5576.51..5576.59 rows=8 width=11)
--    Group Key: u.country
--    ->  Nested Loop  (cost=0.42..5527.86 rows=9730 width=3)
--          ->  Seq Scan on users u  (cost=0.00..2092.00 rows=973 width=7)        -- ~1 % of 100,000
--                Filter: (plan = 'enterprise'::text)
--          ->  Index Only Scan using events_user_id_idx on events e  (cost=0.42..3.42 rows=11 width=4)
--                Index Cond: (user_id = u.id)                                    -- one lookup per user

The enable_* settings forbid a strategy (more precisely, they add a huge cost penalty to it), which shows you what the planner's second choice would have been:

SET enable_nestloop = off;                 -- for experiments only, never in production config
EXPLAIN SELECT ... ;                       -- same query
--  Finalize GroupAggregate  (cost=17925.54..17927.93 rows=8 width=11)          -- 3x more expensive
--    ->  Gather Merge  ...  Workers Planned: 2
--          ->  ...  ->  Hash Join  (cost=2104.59..16905.05 rows=4054 width=3)
--                         Hash Cond: (e.user_id = u.id)
--                         ->  Parallel Index Only Scan using events_user_id_idx on events e
--                         ->  Hash  ->  Seq Scan on users u
RESET enable_nestloop;

The total cost rose from 5,577 to 17,928, which is why the planner preferred the nested loop. If plan = 'enterprise' matched half of all users instead, the estimates would flip and the hash join would win.

What the planner does not do

• It does not run the query to compare alternatives. All choices are based on estimates.
• It does not learn from previous executions. A bad estimate gives the same bad plan every time until statistics or the query change.
• It does not accept hints. PostgreSQL deliberately has no /*+ INDEX(...) */ syntax; the fix for a bad plan is better statistics, better indexes, or a rewritten query. (The pg_hint_plan extension adds hints for emergencies.)

Key takeaways

• The planner enumerates access paths, join orders, and join methods, estimates a cost for each, and picks the cheapest.
• It searches join orders with dynamic programming, switching to the genetic optimizer (GEQO) at 12 or more tables.
• Cost estimates depend on statistics; a wrong row-count estimate is the most common reason for a bad plan.
• enable_* settings are a diagnostic tool for seeing alternative plans, not a tuning knob.

🧪 Practice

1. (Beginner) Run EXPLAIN on the enterprise-users query, then change the filter to u.plan = 'free'. Which join method does the planner choose now, and why?
2. (Intermediate) Use SET enable_hashjoin = off and SET enable_mergejoin = off one at a time on the 'free' version of the query, and record the total cost of each alternative. Rank the three join methods by cost.
3. (Interview) "Why does PostgreSQL not support optimizer hints, and what do you do instead when the planner picks a bad plan?" Hint: Consider what happens to a hint when the data grows tenfold, and which inputs to the planner you can change.

Executor and Node Trees

The planner's output is a plan tree: a tree of nodes, each a small operator that knows how to produce rows. The executor runs that tree. Understanding how the nodes cooperate explains the shape of every EXPLAIN output and why some queries return their first row instantly while others make you wait.

The iterator (Volcano) model. Each node implements the same tiny interface: initialize, give me the next row, and shut down. A node produces rows by asking its children for theirs. The executor starts by asking the root node for a row, and requests flow down the tree while rows flow up:

                 client asks for rows
                         |
                         v
               +------------------+
               |  HashAggregate   |    "next row?" -> must read ALL input first (blocking)
               +------------------+
                         | next()
                         v
               +------------------+
               |   Nested Loop    |    for each outer row, rescans the inner side
               +------------------+
                  | next()      | next() (with u.id as parameter)
                  v             v
        +-------------+   +-----------------------------+
        | Seq Scan    |   | Index Only Scan             |
        | users       |   | events_user_id_idx          |
        +-------------+   +-----------------------------+
            leaf: reads pages      leaf: reads index pages

Analogy. A kitchen assembly line where every station only works when the next station asks for a plate. The dessert station does not start until a waiter asks for dessert. That is why LIMIT 1 on a large table can be very fast: once the root has one row, nobody asks for more.

Pipelined vs. blocking nodes. Most nodes pass rows on as soon as they have them. Some must consume their entire input before emitting the first row:

Kind	Examples	Effect
Pipelined (streaming)	Seq Scan, Index Scan, Nested Loop, Filter, Limit, Merge Join (once inputs are sorted)	First rows arrive quickly; little memory
Blocking	Sort, Hash (build side of a hash join), HashAggregate, Materialize (partly)	Nothing is returned until all input is consumed; uses work_mem

Startup and total cost. EXPLAIN shows two costs for each node, cost=startup..total. Startup is the estimated cost before the first row can be returned; total is the cost to return all rows. A blocking node has a high startup cost. For a query with LIMIT, the planner prefers plans with low startup costs, because it may never need the total.

EXPLAIN SELECT * FROM events ORDER BY created_at LIMIT 5;
--  Limit  (cost=0.42..0.61 rows=5 width=57)
--    ->  Index Scan using events_created_at_idx on events  (cost=0.42..36447.43 rows=1000000 width=57)
-- The index scan's total cost is ~36,000, but Limit stops after 5 rows, so the plan costs 0.61.

EXPLAIN SELECT * FROM events ORDER BY amount LIMIT 5;
--  Limit  (cost=22551.38..22551.96 rows=5 width=57)            -- startup cost: 22,551
--    ->  Gather Merge  (cost=22551.38..139017.97 rows=1000001 width=57)
--          Workers Planned: 2
--          ->  Sort  (cost=21551.36..22593.02 rows=416667 width=57)   -- no index on amount:
--                Sort Key: amount                                      -- must read everything first
--                ->  Parallel Seq Scan on events  (cost=0.00..14630.67 rows=416667 width=57)

Execution steps in a backend

1. ExecutorStart: build a state tree that mirrors the plan, open relations, allocate memory.
2. ExecutorRun: repeatedly pull rows from the root and send them to the destination (the client, a cursor, or an INSERT's target table).
3. ExecutorFinish and ExecutorEnd: fire AFTER triggers, release resources.

The auto_explain and pg_stat_statements modules (7.5) hook into exactly these calls to measure queries.

Expression evaluation. Within each node, expressions such as WHERE conditions and computed columns are compiled into a compact program of steps that a small interpreter runs for each row. For long analytical queries, PostgreSQL can instead compile them to machine code with JIT (JIT Compilation Controls).

Key takeaways

• The executor runs a tree of nodes; each node pulls rows from its children on demand (the iterator or Volcano model).
• Pipelined nodes stream rows; blocking nodes (Sort, Hash, HashAggregate) consume all input before returning anything.
• cost=startup..total: the planner favors low startup cost when a LIMIT or cursor means only the first rows are needed.
• A LIMIT stops the whole tree early, which is why an index that matches ORDER BY turns "sort a million rows" into "read five".

🧪 Practice

1. (Beginner) Run EXPLAIN SELECT * FROM events ORDER BY created_at LIMIT 5 and the same query with ORDER BY amount. Compare the startup costs of the top nodes and explain the difference.
2. (Intermediate) Draw the node tree for EXPLAIN SELECT kind, count(*) FROM events WHERE user_id < 100 GROUP BY kind ORDER BY count(*) DESC, marking each node as pipelined or blocking.
3. (Interview) "A query with ORDER BY ... LIMIT 10 takes 8 seconds, but removing LIMIT makes it take 9 seconds, not much longer. What is likely happening?" Hint: Look for a blocking node below the Limit, and consider which index would let the plan stream rows instead.

Prepared Statements and Plan Caching

Parsing, analysis, rewriting, and planning together often take a fraction of a millisecond, but for a query that itself executes in 50 microseconds and runs 10,000 times a second, that overhead can dominate. Prepared statements let a client send the statement text once, have PostgreSQL do the preparation once, and then execute it many times with different parameter values.

Analogy. A form letter with blanks. You write and proofread the letter once; afterwards you only fill in the name and address for each recipient.

Prepared statements in SQL

PREPARE user_events (int) AS                         -- parse + analyze + rewrite now
  SELECT kind, created_at FROM events WHERE user_id = $1 ORDER BY created_at;

EXECUTE user_events(42);                             -- plan (if needed) + execute
EXECUTE user_events(4242);                           -- no parsing again

SELECT name, parameter_types, generic_plans, custom_plans
FROM pg_prepared_statements;                         -- statements prepared in THIS session
--     name     | parameter_types | generic_plans | custom_plans
-- -------------+-----------------+---------------+--------------
--  user_events | {integer}       |             0 |            2

DEALLOCATE user_events;                              -- or DEALLOCATE ALL

Prepared statements from applications. Most drivers expose prepared statements through the extended query protocol, which separates the steps into protocol messages: Parse (create a statement), Bind (supply parameters, creating a portal), and Execute. The parameters travel separately from the SQL text, which also makes them immune to SQL injection (10.4):

import psycopg

with psycopg.connect("dbname=shop") as conn:
    for user_id in (42, 4242, 7):
        # psycopg 3 automatically prepares a query after it has run prepare_threshold
        # times (default 5) on this connection; prepare=True forces it immediately.
        rows = conn.execute(
            "SELECT kind, created_at FROM events WHERE user_id = %s ORDER BY created_at",
            (user_id,),
            prepare=True,
        ).fetchall()

// JDBC: the PostgreSQL driver switches to a named server-side statement
// after the same PreparedStatement has run prepareThreshold times (default 5).
try (PreparedStatement ps = conn.prepareStatement(
        "SELECT kind, created_at FROM events WHERE user_id = ? ORDER BY created_at")) {
    ps.setInt(1, 42);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { /* ... */ }
    }
}

The plan cache. A prepared statement keeps its analyzed and rewritten query tree in the backend's memory. Plans are cached per session and are never shared between connections. PL/pgSQL functions use the same mechanism for every static SQL statement inside them (9.2).

Invalidation. The cache tracks every object a statement depends on. If a table is altered, an index is created or dropped, or statistics change after ANALYZE, the cached plan is marked invalid and rebuilt on the next EXECUTE. If a column used by the statement is dropped, the next EXECUTE re-analyzes and may fail. You never get stale results from a cached plan, only extra planning work.

What prepared statements save, and their costs

	Benefit or cost
Parse, analyze, rewrite	Done once per session
Planning	Can be skipped by reusing a generic plan (next topic)
Network	Statement text is sent once; later executions send only parameters
Security	Parameters are never interpreted as SQL
Memory	Each session holds its own copies; thousands of statements across thousands of connections add up
Connection poolers	Named statements are tied to one server session; PgBouncer in transaction mode supports them only since version 1.21 (Connection Pooling with PgBouncer)

Key takeaways

• PREPARE/EXECUTE and the protocol-level Parse/Bind/Execute messages split preparation from execution so repeated queries skip parsing and analysis.
• Prepared statements live only in the session that created them; pg_prepared_statements lists them.
• Cached plans are invalidated automatically by DDL and new statistics, so they are always correct.
• Parameters are sent separately from the SQL text, which prevents SQL injection.

🧪 Practice

1. (Beginner) Prepare a statement that returns the user with a given id, execute it three times with different values, and inspect pg_prepared_statements.
2. (Intermediate) Using pgbench with a custom script (SELECT * FROM events WHERE id = :id with a random :id), compare transactions per second with -M simple and -M prepared. Explain the difference.
3. (Interview) "After a deploy that added an index, a service starts throwing errors from prepared statements that previously worked. Another deploy dropped a column the same day. Which change can cause errors from cached statements and which only causes replanning?" Hint: Think about what invalidation does when the query tree itself is no longer valid, versus when only the plan is outdated.

Generic vs. Custom Plans

A prepared statement can be planned in two ways. A custom plan is made for each execution using the actual parameter values. A generic plan is made once, without knowing the values, and reused for every execution. Custom plans are better informed; generic plans skip planning entirely. PostgreSQL has to decide, for each prepared statement, which trade-off is better.

Why the values matter. In the events table, kind is heavily skewed: 70 % of rows are 'view', but only 0.5 % are 'refund'. The best plan for counting refunds is not the best plan for counting views. Add an index to make the difference visible:

CREATE INDEX events_kind_idx ON events (kind);
ANALYZE events;

PREPARE by_kind (text) AS SELECT count(*) FROM events WHERE kind = $1;

EXPLAIN EXECUTE by_kind('refund');                     -- custom plan: knows the value
--  Aggregate  (cost=109.77..109.78 rows=1 width=8)
--    ->  Index Only Scan using events_kind_idx on events  (cost=0.42..98.10 rows=4667 width=0)
--          Index Cond: (kind = 'refund'::text)        -- the literal value appears in the plan

EXPLAIN EXECUTE by_kind('view');                       -- custom plan: 700,000 rows expected
--  Finalize Aggregate  (cost=12235.01..12235.02 rows=1 width=8)
--    ->  Gather  ...  Workers Planned: 2              -- parallel workers for the big count
--          ->  Partial Aggregate
--                ->  Parallel Index Only Scan using events_kind_idx on events  (... rows=290570 ...)
--                      Index Cond: (kind = 'view'::text)

A generic plan cannot know which kind it will get. It estimates for an "average" value; with four distinct kinds, that is a quarter of the table:

SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE by_kind('refund');
--  Finalize Aggregate  (cost=5029.72..5029.73 rows=1 width=8)
--    ->  Gather  ...  Workers Planned: 2              -- parallel plan for ~5,000 rows: wasteful
--          ->  Parallel Index Only Scan using events_kind_idx on events  (... rows=104167 ...)
--                Index Cond: (kind = $1)              -- $1: value unknown at planning time
RESET plan_cache_mode;

The default heuristic (plan_cache_mode = auto)

1. The first five executions always use custom plans, and PostgreSQL records the average cost of those plans.
2. From the sixth execution on, it builds a generic plan once and compares its estimated cost with the average custom-plan cost plus an allowance for planning time.
3. If the generic plan is not more expensive, it switches to the generic plan for good. Otherwise it keeps making custom plans and checks again later.

 executions 1-5             execution 6+
 +-------------------+      generic cost <= avg custom cost + planning overhead ?
 | custom plan each  | ---> yes: reuse generic plan from now on (no planning)
 | time; track cost  |      no : keep planning per execution (custom plans)
 +-------------------+

When the heuristic goes wrong. The decision is based on the values seen during the first five executions. If those happen to be the common value, the generic plan looks cheap by comparison, and every later execution, including rare values, uses it:

PREPARE by_kind2 (text) AS SELECT count(*) FROM events WHERE kind = $1;
EXECUTE by_kind2('view');   -- five times: each custom plan costs ~12,235
...
EXPLAIN EXECUTE by_kind2('refund');
--  Finalize Aggregate  (cost=5029.72..5029.73 ...)    -- generic plan (5,030 < 12,235), now fixed
--          ...  Index Cond: (kind = $1)

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
--    name   | generic_plans | custom_plans
-- ----------+---------------+--------------
--  by_kind2 |             1 |            5

Here the cost of the mistake is small, but with joins the generic plan can choose a nested loop where a hash join was needed, and a query that normally takes milliseconds suddenly takes minutes.

Controlling the choice

plan_cache_mode	Behavior	Use when
auto (default)	Heuristic above	Most workloads
force_custom_plan	Always plan with actual values	Skewed data where one plan does not fit all values; planning is cheap compared with execution
force_generic_plan	Always reuse the generic plan	Very frequent, simple queries where planning time dominates and all values behave alike

The setting can be changed per session, per role, per database, or per function (ALTER FUNCTION ... SET plan_cache_mode = force_custom_plan), so it can be targeted at the statements that suffer.

DROP INDEX events_kind_idx;      -- clean up: later examples assume events has no index on kind

Key takeaways

• A custom plan uses actual parameter values; a generic plan is built once with placeholders and reused.
• By default, PostgreSQL makes five custom plans, then switches to the generic plan if it is not estimated to be more expensive.
• With skewed data, a generic plan chosen on common values can be very wrong for rare ones, and vice versa.
• generic_plans and custom_plans in pg_prepared_statements show what happened; plan_cache_mode overrides the heuristic.

🧪 Practice

1. (Beginner) Prepare SELECT * FROM users WHERE id = $1, execute it seven times, and check generic_plans and custom_plans. Explain why switching to the generic plan is safe here.
2. (Intermediate) Repeat the by_kind2 experiment, but start with five executions for 'refund'. Does PostgreSQL switch to the generic plan? Compare the costs that drive the decision.
3. (Interview) "A query from an ORM is fast when you run it in psql with literal values but slow in production, where it runs as a prepared statement. How do you confirm the cause and fix it?" Hint: Use EXPLAIN EXECUTE after six executions, or auto_explain with the actual parameters, and look for $1 in the plan; then consider plan_cache_mode scoped to the affected role or function.

<a id="72-cost-model-and-statistics"></a>

7.2 Cost Model and Statistics

The planner chooses plans by comparing estimated costs, and those costs are computed from two inputs: a handful of cost parameters that describe the hardware, and statistics that describe the data. This subchapter explains both, shows how row counts are estimated from them, and covers the most common ways estimates go wrong.

Planner Cost Parameters

To compare a sequential scan with an index scan, the planner needs a common unit for very different kinds of work: reading pages in order, jumping to random pages, and spending CPU time on each row. PostgreSQL's unit is arbitrary: one sequential page read costs 1.0. Every other kind of work is priced relative to it by a set of configuration parameters.

Analogy. A shop that prices everything in one currency. It does not matter whether the currency is dollars or euros, only that the price of a coffee relative to a sandwich is right. Likewise, a cost of 20,464 has no unit of time; it only matters that it is larger or smaller than the cost of another plan.

The basic parameters

Parameter	Default	Price of
seq_page_cost	1.0	Reading one page as part of a sequential scan
random_page_cost	4.0	Reading one page out of order (index scans, heap fetches)
cpu_tuple_cost	0.01	Processing one row
cpu_index_tuple_cost	0.005	Processing one index entry
cpu_operator_cost	0.0025	Evaluating one operator or function call (for example, one = comparison)
parallel_setup_cost	1000	Starting parallel workers
parallel_tuple_cost	0.1	Passing one row from a worker to the leader
effective_cache_size	4 GB	Not a cost: an estimate of how much data is likely cached, used to discount repeated index page reads

Computing a cost by hand. A sequential scan reads every page and processes every row:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'events';
--  relpages | reltuples
-- ----------+-----------
--     10464 |     1e+06

SET max_parallel_workers_per_gather = 0;        -- keep the plan serial so the arithmetic is simple

EXPLAIN SELECT * FROM events;
--  Seq Scan on events  (cost=0.00..20464.00 rows=1000000 width=57)
--  10,464 pages x 1.0 (seq_page_cost)  +  1,000,000 rows x 0.01 (cpu_tuple_cost)  = 20,464

EXPLAIN SELECT * FROM events WHERE amount > 100;
--  Seq Scan on events  (cost=0.00..22964.00 rows=40892 width=57)
--    Filter: (amount > '100'::numeric)
--  20,464  +  1,000,000 comparisons x 0.0025 (cpu_operator_cost)  = 22,964

RESET max_parallel_workers_per_gather;

An index scan's cost formula is more involved: it adds the cost of descending the B-tree, reading index entries, and fetching heap pages. The number of heap page fetches depends on how well the physical order of the table matches the index order (the correlation statistic, next topic). With correlation near 1, matching rows sit on consecutive pages, so the cost approaches sequential reads; near 0, each row may need a random page read.

Why random_page_cost is 4. The default dates from spinning disks, where a random read was far more expensive than a sequential one, and assumes that most random reads are served from cache anyway. On SSDs and in the cloud, random reads are much cheaper, and setting random_page_cost to 1.1 to 1.5 is common (Effective Cache Size and Random Page Cost).

Where costs can be set

ALTER SYSTEM SET random_page_cost = 1.1;                    -- whole cluster
ALTER TABLESPACE fast SET (random_page_cost = 1.1);         -- one tablespace (e.g. NVMe)
ALTER DATABASE analytics SET cpu_tuple_cost = 0.02;         -- one database
SET random_page_cost = 1.1;                                 -- this session only, for experiments

Functions also have a cost (ALTER FUNCTION ... COST n, in units of cpu_operator_cost), which tells the planner how expensive a call is relative to other conditions (9.1).

Key takeaways

• Costs are in arbitrary units anchored to seq_page_cost = 1.0; only their relative sizes matter.
• A sequential scan costs pages x seq_page_cost + rows x cpu_tuple_cost, plus cpu_operator_cost for each condition evaluated.
• random_page_cost (default 4) controls how strongly the planner avoids random I/O; lower it on SSDs.
• Cost parameters can be set per cluster, tablespace, database, role, or session.

🧪 Practice

1. (Beginner) Compute by hand the cost of SELECT * FROM users (look up relpages and reltuples), then check your answer with EXPLAIN.
2. (Intermediate) Find a query on events that uses an index scan. Increase random_page_cost step by step (4, 8, 16, 32) until the planner switches to a sequential or bitmap scan. Explain what happened.
3. (Interview) "Two servers with identical data and PostgreSQL versions choose different plans for the same query. What would you compare?" Hint: Start with the planner parameters in pg_settings where source <> 'default', then statistics freshness and table sizes.

Table Statistics and pg_statistic

Cost formulas need to know how many rows each step will process. Counting them for every query would mean running the query. Instead, PostgreSQL keeps a compact summary of each column's data, and estimates from that summary. These statistics are the single most important input to the planner.

Analogy. A political poll. Asking 1,500 people gives a good estimate of how 100 million will vote, as long as the sample is random and recent. PostgreSQL samples a table's rows the same way and keeps a statistical profile of each column.

Two levels of statistics

Where	What	Updated by
pg_class.relpages, pg_class.reltuples	Table size in pages and estimated row count	VACUUM, ANALYZE, CREATE INDEX
pg_statistic (readable through the pg_stats view)	Per-column distribution	ANALYZE

pg_statistic stores values in generic array slots that are hard to read and is restricted to superusers, because it contains samples of real data. The pg_stats view presents the same information readably and shows only columns the current user may read.

What is kept for each column

pg_stats column	Meaning	Used for
null_frac	Fraction of rows that are NULL	IS NULL, and to scale every other estimate
avg_width	Average width in bytes	Memory needed for sorts and hashes
n_distinct	Number of distinct values; negative means a fraction of the row count (-1 = all unique)	Equality on values not in the MCV list, GROUP BY, joins
most_common_vals, most_common_freqs	The most common values (MCV list) and their frequencies	Equality and IN on common values
histogram_bounds	Values dividing the remaining (non-MCV) data into buckets of equal population	Ranges: <, >, BETWEEN
correlation	How well the physical row order matches the column's sort order (-1 to 1)	Cost of index scans

Reading the statistics for events

SELECT attname, null_frac, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'events'
ORDER BY attname;
--   attname   | null_frac  | n_distinct |  correlation
-- ------------+------------+------------+---------------
--  amount     | 0.94916666 |      25621 |  -0.020856798      -- 95 % NULL: only purchases and refunds
--  country    |          0 |          8 |    0.12877965
--  created_at |          0 |         -1 |             1      -- unique, and stored in time order
--  id         |          0 |         -1 |             1
--  kind       |          0 |          4 |     0.5554805
--  user_id    |          0 |      91916 | -0.0020035044      -- true value: 99,999 (sampling error)

SELECT most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'events' AND attname = 'kind';
--        most_common_vals       |           most_common_freqs
-- ------------------------------+----------------------------------------
--  {view,click,purchase,refund} | {0.69846666,0.2507,0.0452,0.005633333}

A histogram is drawn here for amount (101 boundaries, so 100 buckets, each holding about 1 % of the non-NULL values):

 histogram_bounds for events.amount (non-NULL values only)

   0.54   7.03  12.01  17.98  23.66   ...   480.87  485.97  491.03  495.73  499.70
    |------|------|------|------|-- ... --|-------|-------|-------|-------|
      1%     1%     1%     1%                 1%      1%      1%      1%
 Narrow buckets mean values are dense there; wide buckets mean they are sparse.

Correlation. created_at has correlation 1 because rows were inserted in time order, so a range on created_at reads consecutive heap pages. user_id has correlation near 0: the ten events of one user are spread over ten different pages. The planner prices an index scan on user_id with far more random page reads than one on created_at for the same number of rows.

Key takeaways

• pg_class holds table-level size estimates; pg_statistic holds per-column distributions, readable through pg_stats.
• Each column has a null fraction, distinct count, MCV list, histogram, and physical-order correlation.
• The MCV list answers equality on common values; the histogram answers ranges on the rest.
• Statistics come from a random sample, so they are approximate; small errors are normal.

🧪 Practice

1. (Beginner) Query pg_stats for every column of users. Which columns have an MCV list, and which have a histogram? Why does id have no MCV list?
2. (Intermediate) Run CLUSTER events USING events_user_id_idx on a copy of events, then ANALYZE it and compare the correlation of user_id and created_at before and after. Predict how the plan for WHERE user_id BETWEEN 1 AND 500 changes, then check.
3. (Interview) "Why is pg_statistic readable only by superusers while pg_stats is readable by everyone?" Hint: Think about what most_common_vals and histogram_bounds contain for a column such as salary or email.

ANALYZE and Autoanalyze

Statistics describe the data at the moment they were collected. After a large import, a bulk delete, or months of steady growth, they may describe a table that no longer exists. ANALYZE recollects them, and the autovacuum daemon runs it automatically when enough rows have changed.

What ANALYZE does

1. Reads a random sample of 300 x default_statistics_target rows: 30,000 rows with the default target of 100, however large the table is.
2. Computes null_frac, n_distinct, MCVs, histogram, and correlation for each column, and stores them in pg_statistic.
3. Updates pg_class.reltuples and relpages.
4. Builds any extended statistics defined on the table (Extended Statistics for Correlated Columns).

Because the sample size is fixed, ANALYZE on a billion-row table takes about as long as on a million-row one. It takes only a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue while it runs.

ANALYZE;                                   -- every table in the current database
ANALYZE events;                            -- one table
ANALYZE events (user_id, created_at);      -- only some columns
ANALYZE VERBOSE users;
-- INFO:  analyzing "public.users"
-- INFO:  "users": scanned 842 of 842 pages, containing 100000 live rows and 0 dead rows;
--        30000 rows in sample, 100000 estimated total rows
VACUUM ANALYZE events;                     -- vacuum, then analyze, in one command

Autoanalyze. The autovacuum launcher checks every table periodically. It runs ANALYZE on a table when the number of rows inserted, updated, or deleted since the last analyze exceeds:

 autovacuum_analyze_threshold + autovacuum_analyze_scale_factor x reltuples
           (default 50)                   (default 0.1)

 users  (100,000 rows):        50 + 0.1 x 100,000        =     10,050 changed rows
 events (1,000,000 rows):      50 + 0.1 x 1,000,000      =    100,050 changed rows
 a 500-million-row table:      50 + 0.1 x 500,000,000    = 50,000,050 changed rows

The last line shows the problem with the default for large tables: 50 million rows can change before statistics are refreshed. For big or fast-changing tables, set a smaller scale factor per table:

ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01,   -- 1 % instead of 10 %
                        autovacuum_analyze_threshold    = 1000);

Monitoring freshness

SELECT relname, n_live_tup, n_mod_since_analyze,
       last_analyze, last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 10;                                -- tables with the most changes since their last analyze

When to run ANALYZE yourself

Situation	Why autoanalyze is not enough
Right after a bulk load or CREATE TABLE ... AS	Queries in the same job run before autovacuum notices the table
After a large DELETE or UPDATE in a batch job	Same: the next step of the job plans with old statistics
After pg_upgrade	Optimizer statistics are not carried over by default before PostgreSQL 18; run vacuumdb --all --analyze-in-stages
Temporary tables	Autovacuum cannot see temporary tables at all
Partitioned parent tables	Autovacuum analyzes partitions, not the parent (Partition Maintenance Strategies)

Key takeaways

• ANALYZE samples 300 x statistics target rows per table, so its run time barely depends on table size, and it does not block reads or writes.
• Autovacuum analyzes a table after threshold + scale_factor x reltuples rows have changed; the default 10 % is too coarse for large tables.
• pg_stat_user_tables.n_mod_since_analyze and last_autoanalyze show how stale statistics are.
• Run ANALYZE explicitly after bulk loads, in batch jobs, on temporary tables, and on partitioned parents.

🧪 Practice

1. (Beginner) Run ANALYZE VERBOSE events and find the sample size in the output. Change default_statistics_target to 10 for your session and run it again. What changed?
2. (Intermediate) Compute after how many changed rows autoanalyze would process a 200-million-row table with default settings, and choose per-table settings that bring this down to about 1 million.
3. (Interview) "A nightly ETL job truncates a staging table, loads 5 million rows, and then joins it with other tables. The join is very slow, but the same query is fast when run manually the next morning. Explain." Hint: What statistics does the staging table have when the join is planned, and when does autoanalyze get to it?

Selectivity Estimation

Selectivity is the fraction of rows that satisfy a condition, a number between 0 and 1. The planner multiplies it by the table's row count to estimate how many rows a scan returns, and every later decision builds on those numbers: a join on 10 rows and a join on 10 million rows call for completely different methods.

Equality on a common value: use the MCV list. 'refund' is in the MCV list with frequency 0.005633:

EXPLAIN SELECT * FROM events WHERE kind = 'refund';
--  Seq Scan on events  (cost=0.00..22964.00 rows=5633 width=57)     -- 0.005633 x 1,000,000
--    Filter: (kind = 'refund'::text)
-- actual count: 5,086 (the sample over-represented refunds slightly)

Equality on another value: spread the rest evenly. user_id has no MCV list (no value is much more common than others), so every value is assumed to be equally common:

 selectivity = (1 - null_frac - sum(MCV freqs)) / (n_distinct - number of MCVs)
             = (1 - 0 - 0) / (91,916 - 0)
             = 0.0000109
 rows        = 0.0000109 x 1,000,000 = 10.9  ->  11

EXPLAIN SELECT * FROM events WHERE user_id = 42;
--  Bitmap Heap Scan on events  (cost=4.51..47.58 rows=11 width=57)
--    Recheck Cond: (user_id = 42)
-- actual count for user 42: 7

Ranges: interpolate in the histogram. For amount > 100, the planner finds the bucket containing 100, assumes values are spread evenly inside it, and adds up the fraction of buckets above it. It then multiplies by the fraction of rows that are not NULL:

 fraction of non-NULL amounts above 100 (from histogram)  ~ 0.80
 non-NULL fraction (1 - null_frac)                        = 0.0508
 rows ~ 0.80 x 0.0508 x 1,000,000                          ~ 40,892   (actual: 40,014)

Combining conditions. With no other information, the planner assumes columns are independent and multiplies selectivities:

Combination	Formula
A AND B	s(A) x s(B)
A OR B	s(A) + s(B) - s(A) x s(B)
NOT A	1 - s(A) (minus the NULL fraction)

EXPLAIN SELECT * FROM events WHERE kind = 'purchase' AND country = 'DE';
--  Gather  (cost=1000.00..18275.20 rows=5612 width=57)
--    ->  Parallel Seq Scan on events  (... rows=2338 ...)       -- per worker: 5,612 / 2.4
--          Filter: ((kind = 'purchase'::text) AND (country = 'DE'::bpchar))
--  0.0452 (purchase) x 0.12417 (DE) x 1,000,000 = 5,612   (actual: 5,519)

The independence assumption is correct here, because the data generator chose kind and country separately. When columns are related, it fails badly (next topic).

When there is nothing to go on: default selectivities. For conditions the statistics cannot describe, the planner falls back to hard-coded guesses:

Condition	Default selectivity
expr = constant on an expression with no statistics	0.005 (0.5 %)
expr < constant, expr > constant	0.3333 (one third)
Range expr BETWEEN a AND b	0.005
col LIKE '%abc%' without useful statistics	Derived from pattern heuristics; often far off
Function returning a set (RETURNS SETOF)	1,000 rows unless the function declares ROWS n

Join selectivity. For a.x = b.y, the planner compares the MCV lists of both columns when it has them; otherwise it uses 1 / max(n_distinct(a.x), n_distinct(b.y)). This is why bad n_distinct values hurt join estimates most.

Key takeaways

• Estimated rows = selectivity x estimated table rows; every later plan choice depends on this number.
• Equality uses the MCV list when the value is in it, otherwise spreads the remaining fraction evenly over the remaining distinct values.
• Ranges interpolate within histogram buckets and are scaled by the non-NULL fraction.
• Multiple conditions are assumed independent and their selectivities multiplied; conditions on expressions without statistics get fixed default guesses.

🧪 Practice

1. (Beginner) Use pg_stats to predict the estimated row count of SELECT * FROM events WHERE country = 'JP', then check with EXPLAIN.
2. (Intermediate) Predict the estimate for WHERE kind = 'refund' OR country = 'JP' using the formula for OR, and verify it. Then compare it with the actual count.
3. (Interview) "A query filters on WHERE date_trunc('day', created_at) = '2025-06-01' and the estimate is exactly 0.5 % of the table. What is going on, and how could you get a better estimate?" Hint: Which statistics exist for an expression, and which two features give the planner statistics on it?

Extended Statistics for Correlated Columns

The independence assumption breaks down whenever one column says something about another: a city determines its country, a ZIP code determines a city, a car model determines its manufacturer. Multiplying selectivities then double-counts the same restriction, and the estimate becomes much too small. Extended statistics, created with CREATE STATISTICS, teach the planner about such relationships.

The problem. In users, each of 16 cities appears in about 1/16 of rows, and each country in about 1/8. Every Berlin user is a German user, so city = 'Berlin' AND country = 'DE' has the same selectivity as city = 'Berlin' alone, about 6.3 %. The planner does not know this:

EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'Berlin' AND country = 'DE';
--  Seq Scan on users  (cost=0.00..2342.00 rows=768 width=35) (actual ... rows=6358.00 loops=1)
--    Filter: ((city = 'Berlin'::text) AND (country = 'DE'::bpchar))
--  estimate: 0.0625 x 0.125 x 100,000 ~ 768       actual: 6,358 (8x more)

EXPLAIN ANALYZE SELECT city, country, count(*) FROM users GROUP BY city, country;
--  HashAggregate  (cost=2592.00..2593.28 rows=128 width=17) (actual ... rows=16.00 loops=1)
--  estimate: 16 cities x 8 countries = 128 groups       actual: 16

An 8x underestimate in a filter is enough to send a join into a nested loop that should have been a hash join.

The fix

CREATE STATISTICS users_city_country (dependencies, ndistinct, mcv)
  ON city, country FROM users;
ANALYZE users;                                    -- extended statistics are built by ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'Berlin' AND country = 'DE';
--  Seq Scan on users  (cost=0.00..2342.00 rows=6190 width=35) (actual ... rows=6358.00 loops=1)

EXPLAIN ANALYZE SELECT city, country, count(*) FROM users GROUP BY city, country;
--  HashAggregate  (cost=2592.00..2592.16 rows=16 width=17) (actual ... rows=16.00 loops=1)

EXPLAIN SELECT * FROM users WHERE city = 'Berlin' AND country = 'FR';
--  Seq Scan on users  (cost=0.00..2342.00 rows=1 width=35)    -- the MCV list knows this pair never occurs

The three kinds

Kind	What it stores	Helps with
dependencies	Functional dependency degree: how often knowing column A determines column B	WHERE a = x AND b = y
ndistinct	Number of distinct combinations of the columns	GROUP BY a, b, DISTINCT a, b
mcv	Most common combinations of values with their frequencies	AND and OR conditions, including impossible combinations; ranges

If no kind is listed, all three are built. Inspect the results through pg_stats_ext:

SELECT statistics_name, n_distinct, dependencies
FROM pg_stats_ext WHERE statistics_name = 'users_city_country';
--   statistics_name   |  n_distinct  |     dependencies
-- --------------------+--------------+----------------------
--  users_city_country | {"3, 4": 16} | {"3 => 4": 1.000000}   -- column 3 (city) fully determines column 4 (country)

Statistics on expressions. Since PostgreSQL 14, CREATE STATISTICS also accepts a single expression, which gives the planner MCVs and a histogram for it without creating an index:

EXPLAIN SELECT count(*) FROM events WHERE payload->>'device' = 'ios';
--  ...  Parallel Seq Scan on events  (cost=0.00..16714.00 rows=2083 width=0)   -- 0.5 % default guess
--         actual: about 333,000 rows (one third)

CREATE STATISTICS events_device_stats ON (payload->>'device') FROM events;
ANALYZE events;
--  ...  Parallel Seq Scan on events  (cost=0.00..16714.00 rows=138639 width=0) -- x 2.4 workers ~ 333,000

Limitations

• Extended statistics apply within one table. They do not help with correlations across a join.
• They are used for equality and simple comparisons. Conditions the planner cannot match to them still use the independence assumption.
• Each statistics object costs ANALYZE time and catalog space; create them for column groups that actually appear together in slow queries, not for every pair.

Key takeaways

• By default, conditions on different columns are assumed independent, which underestimates rows when columns are correlated.
• CREATE STATISTICS ... (dependencies, ndistinct, mcv) ON a, b FROM t records the relationship; ANALYZE must run afterward.
• ndistinct fixes GROUP BY estimates, dependencies and mcv fix WHERE estimates; mcv also detects combinations that never occur.
• Statistics on a single expression give the planner real data for conditions on that expression.

🧪 Practice

1. (Beginner) Drop users_city_country, then compare estimated and actual rows for WHERE city = 'Tokyo' AND country = 'JP'. Re-create the statistics and compare again.
2. (Intermediate) Create statistics with only dependencies and check the estimate for city = 'Berlin' AND country = 'FR'. Then add mcv. Explain why the results differ.
3. (Interview) "An orders table has zip_code, city, and state. Queries filter on all three, and joins downstream are badly planned. How would you diagnose and fix this?" Hint: Compare estimated and actual rows for the filter alone; consider which kinds of extended statistics apply and which column groups to include.

Default Statistics Target

How detailed should the statistics be? Too little detail, and estimates for unevenly distributed data are poor. Too much, and ANALYZE becomes slow and planning itself takes longer, because the planner has to search longer MCV lists and histograms. The statistics target sets this trade-off.

What the target controls. With target N (default 100):

• The MCV list holds up to N values.
• The histogram has up to N buckets (N + 1 boundaries).
• ANALYZE samples 300 x N rows. (For the whole table, the largest target among its columns is used.)

Effect of a larger target. user_id has 99,999 distinct values, but the default sample of 30,000 rows underestimates that:

SELECT n_distinct, array_length(histogram_bounds::text::text[], 1) AS bounds
FROM pg_stats WHERE tablename = 'events' AND attname = 'user_id';
--  n_distinct | bounds
-- ------------+--------
--       91916 |    101

ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 1000;   -- this column only
ANALYZE events;                                                -- 230 ms before, 2,300 ms now

--  n_distinct | bounds | (plus an MCV list of 397 values)
-- ------------+--------
--       98365 |   1001

ALTER TABLE events ALTER COLUMN user_id SET STATISTICS -1;     -- -1: back to default_statistics_target
ANALYZE events;

The larger sample brought n_distinct closer to the truth, at ten times the ANALYZE cost.

Choosing a target

Setting	Use
Default 100	Almost all columns
500 to 1,000 on specific columns	Columns used in important WHERE or join conditions with skewed distributions, many distinct values, or rare values that matter (for example, a status with a few rare values among millions of 'done')
Up to 10,000 (the maximum)	Rarely justified; makes ANALYZE and planning slower
default_statistics_target globally higher	Usually a mistake: pays the cost on every column of every table
0	Collect no statistics for the column (for example, a large blob never filtered on)

Setting n_distinct directly. When sampling consistently gets n_distinct wrong, which is common for columns where a few values are very frequent and most are rare, you can override it:

ALTER TABLE events ALTER COLUMN user_id SET (n_distinct = 100000);   -- absolute value
ALTER TABLE events ALTER COLUMN user_id SET (n_distinct = -0.1);     -- or: 10 % of the row count
ANALYZE events;                                                      -- the override takes effect at the next ANALYZE
ALTER TABLE events ALTER COLUMN user_id RESET (n_distinct);          -- remove it again

A negative value scales with the table, so it stays correct as the table grows.

Key takeaways

• The statistics target (default 100) sets the MCV list size, histogram bucket count, and sample size (300 x target rows).
• Raise it per column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, not globally.
• Higher targets make ANALYZE slower and planning slightly slower; use them for skewed or high-cardinality columns that matter for important queries.
• n_distinct can be overridden per column when sampling consistently misjudges it.

🧪 Practice

1. (Beginner) Check the current statistics target of every column of events in pg_attribute.attstattarget (NULL or -1 means the default).
2. (Intermediate) Create a table where one status value is 99.9 % of rows and 50 other values share the rest. Compare the estimate for a rare status with targets 10, 100, and 1,000.
3. (Interview) "Someone proposes setting default_statistics_target = 10000 to fix bad estimates across the board. What do you answer?" Hint: Consider ANALYZE time and lock duration on large tables, planning time for every query, and which estimates a bigger sample cannot fix at all.

When Estimates Go Wrong

Most bad plans in practice are not caused by a weak planner, but by a wrong row estimate early in the plan. Once a scan's estimate is off by 100x, every join and aggregate above it is planned for the wrong amount of data. Knowing the common causes lets you go from "this query is slow" to "this estimate is wrong because..." quickly.

How to spot it. Run EXPLAIN ANALYZE and compare rows= in the estimate with rows= in the actual values for each node, from the bottom up. The lowest node with a large discrepancy is where the problem starts (Estimated vs. Actual Row Discrepancies).

Cause 1: stale statistics. A table was analyzed when it had 100 rows, all 'done', then 500,000 rows were loaded:

CREATE TABLE import_batch (id int, status text) WITH (autovacuum_enabled = off);  -- demo only
INSERT INTO import_batch SELECT g, 'done' FROM generate_series(1, 100) AS g;
ANALYZE import_batch;
INSERT INTO import_batch
SELECT g, CASE WHEN g % 100 = 0 THEN 'failed' ELSE 'pending' END
FROM generate_series(101, 500000) AS g;

EXPLAIN ANALYZE SELECT * FROM import_batch WHERE status = 'pending';
--  Gather  (cost=1000.00..5690.60 rows=1 width=9) (actual ... rows=494901.00 loops=1)
--    ->  Parallel Seq Scan on import_batch  (... rows=1 ...) (actual ... rows=247450.50 loops=2)
--          Filter: (status = 'pending'::text)
-- the statistics say every row is 'done', so 'pending' should match almost nothing

ANALYZE import_batch;
--  Seq Scan on import_batch  (cost=0.00..8953.00 rows=494967 width=11) (actual ... rows=494901.00 loops=1)
DROP TABLE import_batch;

PostgreSQL scaled the row count to the table's current size on disk, but the value distribution was still the old one.

Common causes and fixes

Cause	Symptom	Fix
Stale statistics	Estimates far off after bulk changes	ANALYZE; lower autovacuum_analyze_scale_factor
Correlated columns	Underestimates on multi-column filters	CREATE STATISTICS (dependencies, mcv)
Conditions on expressions (lower(email) = ..., payload->>'k' = ..., date_trunc(...) = ...)	Estimate exactly 0.5 % or 33 % of the table	Expression statistics or an expression index (which also gets statistics)
Skewed values not in the MCV list	Rare values over-, frequent values underestimated	Higher statistics target on that column
Bad n_distinct	Wrong join and GROUP BY estimates	Higher target, or ALTER COLUMN ... SET (n_distinct = ...)
Generic plans for prepared statements	Plan shows $1; bad only for some values	plan_cache_mode = force_custom_plan (Generic vs. Custom Plans)
Set-returning functions	Always 1,000 rows	ALTER FUNCTION ... ROWS n, or a planner support function
Correlations across a join (e.g. "orders of premium customers are larger")	Underestimate after the join	Rewrite, denormalize, or materialize an intermediate result and ANALYZE it
Volatile parameters such as now()	Usually fine, but ranges at the end of a time column can be off	Make sure the column is indexed (the planner then looks up the actual current maximum)

Last-resort tools. When the estimate cannot be fixed, you can still steer the plan:

• Split the query: write an intermediate result into a temporary table, ANALYZE it, and continue from there. The planner then works with real row counts.
• Use a MATERIALIZED CTE as an optimization fence (MATERIALIZED and NOT MATERIALIZED Hints).
• Use SET LOCAL enable_nestloop = off inside the transaction of one known-problematic query. Document why, and revisit it after upgrades.

Key takeaways

• Bad plans usually start with a bad row estimate at a low node; compare estimated and actual rows from the bottom of the plan upward.
• The usual causes are stale statistics, correlated columns, expressions without statistics, skew, and generic plans.
• Each cause has a targeted fix: ANALYZE, extended or expression statistics, higher targets, or plan_cache_mode.
• When estimates cannot be fixed, materialize intermediate results into an analyzed temporary table rather than forcing plans globally.

🧪 Practice

1. (Beginner) Run EXPLAIN ANALYZE SELECT * FROM users WHERE lower(name) = 'user_42'. What is the estimated row count, and where does that number come from?
2. (Intermediate) Write a PL/pgSQL function recent_user_ids() that RETURNS SETOF int and returns 20 ids. Join its result to events and look at the estimate. Fix it with ROWS 20 and compare plans.
3. (Interview) "A report query was fast for a year and is suddenly slow, with no code changes. Walk through your investigation." Hint: Start from EXPLAIN ANALYZE and the lowest node with a large estimate error; then check statistics freshness, data growth past a threshold that flipped a plan, and whether the query runs as a prepared statement.

<a id="73-reading-execution-plans"></a>

7.3 Reading Execution Plans

EXPLAIN is the most important performance tool in PostgreSQL: it shows the plan the planner chose and, with ANALYZE, what actually happened when it ran. This subchapter teaches you to read plans node by node: the output format and options, the scan, join, sort, and aggregate nodes you will meet most often, how to find where an estimate went wrong, and how parallel plans are reported.

EXPLAIN Basics

When a query is slow, guessing at the reason wastes time. EXPLAIN shows the plan the planner chose for a statement, without running it. Reading it is like reading the route a navigation app picked: you see each turn, and how long the app expected each leg to take.

A first plan

EXPLAIN
SELECT u.name, e.kind, e.created_at
FROM events AS e JOIN users AS u ON u.id = e.user_id
WHERE e.created_at >= '2025-12-14'
ORDER BY e.created_at;
--                                               QUERY PLAN
-- ------------------------------------------------------------------------------------------------------
--  Sort  (cost=3157.47..3159.19 rows=685 width=23)
--    Sort Key: e.created_at
--    ->  Hash Join  (cost=3092.43..3125.21 rows=685 width=23)
--          Hash Cond: (e.user_id = u.id)
--          ->  Index Scan using events_created_at_idx on events e  (cost=0.42..31.41 rows=685 width=17)
--                Index Cond: (created_at >= '2025-12-14 00:00:00+00'::timestamp with time zone)
--          ->  Hash  (cost=1842.00..1842.00 rows=100000 width=14)
--                ->  Seq Scan on users u  (cost=0.00..1842.00 rows=100000 width=14)

Reading the tree. Each line starting with -> is a node; indentation shows the parent-child relationship. Execution starts at the most deeply indented nodes (the leaves, which read tables) and results flow upward to the top node, which returns rows to the client. The same plan as a tree:

                    Sort (by created_at)
                          |
                      Hash Join  (e.user_id = u.id)
                     /          \
   Index Scan on events e        Hash          <- builds a hash table from its child
   (created_at >= '2025-12-14')    |
                               Seq Scan on users u

For a join, the first child is the outer side and the second is the inner side. In a hash join, the inner side is the one loaded into the hash table.

The numbers on each node

Field	Meaning
cost=3092.43..3125.21	Estimated startup cost (before the first row) and total cost (all rows), in planner units (Planner Cost Parameters). Costs are cumulative: a node's cost includes its children's
rows=685	Estimated rows this node outputs (after its own filters), per execution
width=23	Estimated average row width in bytes

Detail lines. Lines without -> describe the node above them:

Line	Meaning
Index Cond	Condition used to search the index
Filter	Condition checked on each row after it is read; rows that fail are discarded
Recheck Cond	Condition rechecked on heap rows in a bitmap scan
Hash Cond, Merge Cond, Join Filter	Join conditions
Sort Key, Group Key	Columns sorted or grouped by

The difference between Index Cond and Filter is often the key to a slow plan: an Index Cond narrows what is read, while a Filter throws away rows after paying to read them.

EXPLAIN on any statement. It works with INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS, and EXECUTE of a prepared statement. Plain EXPLAIN never runs the statement, so it is always safe.

Tools for large plans. Plans with dozens of nodes are easier to read in a visualizer. Paste the text (or JSON) output into explain.depesz.com, explain.dalibo.com, or a GUI client's plan viewer, which color nodes by time and highlight estimate errors.

Key takeaways

• EXPLAIN shows the chosen plan as an indented tree; leaves read tables and rows flow upward to the root.
• Each node shows startup and total cost, estimated output rows, and row width; costs include all child nodes.
• Index Cond limits what is read, Filter discards rows after reading them.
• Plain EXPLAIN does not execute the statement and is safe on anything, including DELETE.

🧪 Practice

1. (Beginner) Run EXPLAIN SELECT * FROM users WHERE id = 7 and EXPLAIN SELECT * FROM users WHERE name = 'user_7'. Explain the difference in node type and cost.
2. (Intermediate) Draw the tree for EXPLAIN SELECT u.country, count(*) FROM users u JOIN events e ON e.user_id = u.id WHERE e.created_at >= '2025-12-01' GROUP BY u.country, labeling the outer and inner side of each join.
3. (Interview) "In a plan, the top node has cost=0.42..1250000.00 but the query returns its first row in under a millisecond. How is that possible?" Hint: Look at the startup cost and think about how a client or LIMIT consuming only some rows interacts with a streaming plan.

EXPLAIN ANALYZE and Real Timings

Plain EXPLAIN shows the planner's expectations. To find out what really happened, EXPLAIN ANALYZE executes the statement, measures every node, and prints estimates and actual values side by side. It is the difference between a weather forecast and a weather report.

EXPLAIN ANALYZE
SELECT u.name, e.kind, e.created_at
FROM events AS e JOIN users AS u ON u.id = e.user_id
WHERE e.created_at >= '2025-12-14'
ORDER BY e.created_at;
--  Sort  (cost=3157.47..3159.19 rows=685 width=23) (actual time=42.880..42.917 rows=641.00 loops=1)
--    Sort Key: e.created_at
--    Sort Method: quicksort  Memory: 51kB
--    Buffers: shared hit=856 read=2
--    ->  Hash Join  (cost=3092.43..3125.21 rows=685 width=23) (actual time=42.262..42.719 rows=641.00 loops=1)
--          Hash Cond: (e.user_id = u.id)
--          ->  Index Scan using events_created_at_idx on events e  (... rows=685 ...) (actual time=0.036..0.174 rows=641.00 loops=1)
--                Index Cond: (created_at >= '2025-12-14 00:00:00+00'::timestamp with time zone)
--                Index Searches: 1
--          ->  Hash  (cost=1842.00..1842.00 rows=100000 width=14) (actual time=41.604..41.605 rows=100000.00 loops=1)
--                Buckets: 131072  Batches: 1  Memory Usage: 5712kB
--                ->  Seq Scan on users u  (... rows=100000 ...) (actual time=0.013..12.744 rows=100000.00 loops=1)
--  Planning Time: 0.216 ms
--  Execution Time: 43.093 ms

The actual values

Field	Meaning
actual time=42.262..42.719	Milliseconds until the node returned its first row, and until it returned its last row. Per loop, and including all children
rows=641.00	Rows actually returned, averaged per loop. PostgreSQL 18 prints two decimals so small averages are not rounded to 0
loops=1	How many times the node was executed. Inner sides of nested loops run many times
(never executed)	The node was never needed, for example the inner side of a join whose outer side returned no rows
Planning Time, Execution Time	Total time to plan, and to run (excluding sending rows to the client and parsing)

Since PostgreSQL 18, ANALYZE also includes BUFFERS output automatically (next topic).

Reading this plan. Almost all of the 43 ms is spent in the Hash node building a hash table of all 100,000 users (41.6 ms) to join 641 events. A nested loop with 641 primary-key lookups would have been cheaper. The planner overestimated the cost of those lookups, because it assumed every lookup would be a random page read from disk. This is a typical case for lowering random_page_cost on a server whose data is cached or on SSDs (Effective Cache Size and Random Page Cost).

Finding where the time goes. A node's time includes its children. To get the time spent in the node itself, subtract the children's times; when loops is greater than 1, multiply by loops first:

 Sort       43.09 ms total  -  Hash Join 42.72 ms           =  0.37 ms in Sort itself
 Hash Join  42.72 ms        -  Hash 41.61  -  Index Scan 0.17 =  0.94 ms joining
 Hash       41.61 ms        -  Seq Scan 12.74 ms              = 28.87 ms building the hash table

Caution: it really runs. EXPLAIN ANALYZE executes the statement completely, including data changes and triggers. Wrap DML in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE UPDATE events SET amount = 0 WHERE id = 10;
--  Update on events  (cost=0.42..8.44 rows=0 width=0) (actual time=0.219..0.220 rows=0.00 loops=1)
--    ->  Index Scan using events_pkey on events  (... actual ... rows=1.00 loops=1)
ROLLBACK;                                            -- the update is undone

Even rolled back, the statement took its locks, fired its triggers, and created dead row versions. Do not run it against production for statements with side effects outside the database (for example, a trigger that sends NOTIFY or calls an external system).

Measurement overhead. To time every node, PostgreSQL reads the system clock twice for every row a node returns. On some virtual machines, reading the clock is slow, and EXPLAIN ANALYZE can take several times longer than the query. Use TIMING OFF to keep the row counts but skip per-node timing; pg_test_timing measures the clock overhead on a machine.

EXPLAIN (ANALYZE, TIMING OFF) SELECT ... ;   -- shows (actual rows=641.00 loops=1) without times

Key takeaways

• EXPLAIN ANALYZE runs the statement and shows actual time, rows, and loops next to each estimate.
• Times and rows are per loop; multiply by loops for totals, and subtract children to find a node's own time.
• It executes DML for real: use BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.
• Timing overhead can be large; TIMING OFF keeps row counts while avoiding the clock calls.

🧪 Practice

1. (Beginner) Run EXPLAIN ANALYZE SELECT count(*) FROM events WHERE country = 'JP'. Which node consumes most of the execution time?
2. (Intermediate) Run the join from this topic with SET random_page_cost = 1.1 and compare plan and execution time. Then compute the self time of each node for the new plan.
3. (Interview) "A developer runs EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now() in production to see why the cleanup job is slow. What happened, and how should it have been done?" Hint: Consider what ANALYZE does to the statement, and which statement you would use to examine the plan safely.

BUFFERS, VERBOSE, and Format Options

Time alone does not tell you why a node is slow. Was it reading from disk, or from memory? Is it producing columns nobody uses? EXPLAIN takes options in parentheses that add this kind of detail, and output formats that tools can parse.

Syntax

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT) SELECT ... ;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT ... ;   -- options take ON/OFF

BUFFERS: where the data came from. Counts 8 KB pages per node: shared hit (found in shared buffers), read (requested from the operating system, which may still be in the OS cache), dirtied, written, and temp read/written (spill files). The same query twice, right after a server restart:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id BETWEEN 1000 AND 2000;
-- first run, cold cache:
--  Bitmap Heap Scan on events  (cost=166.62..11234.82 rows=11141 width=57) (actual time=2.835..49.969 rows=10037.00 loops=1)
--    Recheck Cond: ((user_id >= 1000) AND (user_id <= 2000))
--    Heap Blocks: exact=6459
--    Buffers: shared read=6473                       -- every page came from outside shared buffers
--    ->  Bitmap Index Scan on events_user_id_idx  (...) (actual time=1.511..1.512 rows=10037.00 loops=1)
--          Buffers: shared read=14
--  Execution Time: 50.901 ms

-- second run, warm cache:
--    Buffers: shared hit=6473                        -- same pages, now all in memory
--  Execution Time: 9.064 ms

The plan and row counts are identical, but the cold run is 5x slower. Without BUFFERS, you might conclude that the plan is bad when the real issue is caching. The buffer counts also show why this query is expensive at all: 10,037 rows required 6,459 heap pages, because each user's events are scattered across the table (correlation near 0).

VERBOSE: columns and names. Shows each node's output columns, schema-qualified table names, and the query identifier used by pg_stat_statements:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT u.name, count(*) FROM users AS u JOIN events AS e ON e.user_id = u.id
WHERE u.id < 10 GROUP BY u.name;
--  HashAggregate
--    Output: u.name, count(*)
--    Group Key: u.name
--    ->  Nested Loop
--          Output: u.name
--          ->  Index Scan using users_pkey on public.users u
--                Output: u.id, u.name, u.city, u.country, u.plan, u.signed_up
--                Index Cond: (u.id < 10)
--          ->  Index Only Scan using events_user_id_idx on public.events e
--                Output: e.user_id
--                Index Cond: (e.user_id = u.id)
--  Query Identifier: -8114277754045215791

All options

Option	Requires ANALYZE?	Adds
ANALYZE		Runs the statement; actual times, rows, loops
BUFFERS	No (planning buffers only)	Page hits, reads, dirtied, written, temp I/O. On by default with ANALYZE since 18
VERBOSE	No	Output columns, qualified names, query identifier, per-worker details
COSTS	No	Estimated costs and rows (default on); COSTS OFF gives stable output for docs and tests
TIMING	Yes	Per-node times (default on with ANALYZE)
SUMMARY	No	Planning and execution time lines
SETTINGS	No	Planner-relevant settings that differ from defaults
WAL	Yes	WAL records, full-page images, and bytes generated
MEMORY (17+)	No	Memory used by the planner
SERIALIZE (17+)	Yes	Time and bytes to convert result rows to output format, including de-TOASTing
GENERIC_PLAN (16+)	Not allowed with it	Plans a query containing $1, $2 without values, as a generic plan
FORMAT	No	TEXT (default), JSON, YAML, XML

SETTINGS and GENERIC_PLAN in practice

SET random_page_cost = 1.1;
SET work_mem = '64MB';
EXPLAIN (SETTINGS, COSTS OFF) SELECT * FROM events WHERE user_id = 42;
--  Index Scan using events_user_id_idx on events
--    Index Cond: (user_id = 42)
--  Settings: random_page_cost = '1.1', work_mem = '64MB'   -- explains why another server plans differently

EXPLAIN (GENERIC_PLAN, COSTS OFF)
SELECT * FROM events WHERE user_id = $1 AND created_at > $2;   -- query text straight from a log
--  Bitmap Heap Scan on events
--    Recheck Cond: (user_id = $1)
--    Filter: (created_at > $2)
--    ->  Bitmap Index Scan on events_user_id_idx
--          Index Cond: (user_id = $1)

SERIALIZE: hidden costs of large results. Execution time does not include converting rows to text and de-TOASTing large values for sending. SERIALIZE measures it:

EXPLAIN (ANALYZE, SERIALIZE, COSTS OFF) SELECT payload FROM events WHERE user_id < 2000;
--  ...
--  Serialization: time=10.492 ms  output=487kB  format=text
--  Execution Time: 59.479 ms

JSON for tools

EXPLAIN (FORMAT JSON) SELECT * FROM events WHERE user_id = 42;
-- [ { "Plan": { "Node Type": "Bitmap Heap Scan", "Relation Name": "events",
--               "Startup Cost": 4.51, "Total Cost": 47.58, "Plan Rows": 11, ...
--               "Plans": [ { "Node Type": "Bitmap Index Scan", "Index Name": "events_user_id_idx", ... } ] } } ]

Plan visualizers accept JSON and text. JSON is also convenient for scripts that, for example, fail a CI test when a query stops using an index.

Key takeaways

• BUFFERS separates cache hits from reads and reveals spills to temporary files; a plan that is slow only when cold is a caching problem, not a planning problem.
• VERBOSE shows output columns, qualified names, and the query identifier.
• SETTINGS reveals non-default planner settings; GENERIC_PLAN explains parameterized query text from logs.
• FORMAT JSON is the input format for visualizers and automated checks; COSTS OFF gives stable output.

🧪 Practice

1. (Beginner) Run EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events twice and compare shared hit and read. Why might the second run still show reads?
2. (Intermediate) Copy a parameterized query from pg_stat_statements and explain it with GENERIC_PLAN. Then run EXPLAIN ANALYZE with real values substituted and compare the plans.
3. (Interview) "A query takes 2 seconds in the application but EXPLAIN ANALYZE reports 300 ms execution time. Where could the rest of the time go?" Hint: Consider serialization and de-TOASTing (SERIALIZE), network transfer of a large result, client-side processing, and planning time on each call.

Sequential vs. Index Scans

The most basic decision in any plan is how to read each table. Intuition says "an index is always faster", but it is not: an index is a shortcut only when you need a small part of the table. For a large part, jumping back and forth through the index costs more than reading the whole table in order.

Analogy. To find one topic in a book, use the index at the back. To read half the chapters, it is faster to read the book from start to finish than to look up every page through the index and flip back and forth.

The scan types

Node	How it works	Best for
Seq Scan	Reads every page of the table in physical order, filtering rows	Large fractions of a table; small tables
Index Scan	Walks the index for matching entries, fetching each row from the heap in index order	Few rows; returning rows already sorted for ORDER BY or LIMIT
Index Only Scan	Returns values from the index without visiting the heap, when pages are all-visible (Index-Only Scans)	Queries needing only indexed columns
Bitmap Index Scan + Bitmap Heap Scan	First collects all matching row locations from the index into a bitmap sorted by page, then reads each needed heap page once, in physical order	Medium fractions; combining several indexes with BitmapAnd/BitmapOr
Tid Scan / Tid Range Scan	Reads rows by ctid directly	WHERE ctid = ..., batch processing by page ranges

The crossover in practice. The same WHERE on user_id, with increasingly wide ranges (parallelism off to keep plans simple):

SET max_parallel_workers_per_gather = 0;

EXPLAIN SELECT * FROM events WHERE user_id BETWEEN 1 AND 100;     -- ~0.1 % of rows
--  Bitmap Heap Scan on events  (cost=18.77..3009.48 rows=1009 width=57)
--    ->  Bitmap Index Scan on events_user_id_idx  (cost=0.00..18.52 rows=1009 width=0)

EXPLAIN SELECT * FROM events WHERE user_id BETWEEN 1 AND 30000;   -- ~30 %
--  Bitmap Heap Scan on events  (cost=4480.44..19486.91 rows=302831 width=57)

EXPLAIN SELECT * FROM events WHERE user_id BETWEEN 1 AND 50000;   -- ~50 %
--  Seq Scan on events  (cost=0.00..25464.00 rows=502348 width=57)
--    Filter: ((user_id >= 1) AND (user_id <= 50000))

EXPLAIN SELECT user_id FROM events WHERE user_id BETWEEN 1 AND 30000;   -- only the indexed column
--  Index Only Scan using events_user_id_idx on events  (cost=0.42..7433.04 rows=302831 width=4)

EXPLAIN SELECT * FROM events WHERE id BETWEEN 1 AND 100000;       -- 10 %, but correlation = 1
--  Index Scan using events_pkey on events  (cost=0.42..4190.79 rows=100918 width=57)

RESET max_parallel_workers_per_gather;

Notice the last two plans. An index-only scan stays cheap for 30 % of rows because it never touches the heap. A plain index scan is chosen for 10 % of rows on id, because rows with consecutive ids sit on consecutive pages, so the heap fetches are effectively sequential. On user_id, whose rows are scattered, the same fraction already needs a bitmap scan.

 cost
   ^           index scan (scattered rows)
   |          /
   |         /        bitmap heap scan
   |        /     ___/-------------------
   |       /  ___/
   |------/--/----------------------------------------  seq scan (flat: always reads everything)
   |     / /
   |    //
   |   /    index-only scan (all-visible pages)
   |__/___________________________________________________
   0%                fraction of rows matched              100%

Why bitmap scans exist. A plain index scan on scattered rows visits heap pages in random order, and may visit the same page many times. A bitmap scan sorts the row locations by page first, so each page is read once, in order. The cost is that rows come out in physical order, not index order, so a bitmap scan cannot satisfy an ORDER BY. If the bitmap grows larger than work_mem, it becomes lossy: it remembers only which pages contain matches (Heap Blocks: exact=... lossy=...), and every row on those pages must be rechecked against Recheck Cond.

Combining indexes. A bitmap scan can merge results from several indexes:

EXPLAIN SELECT * FROM events WHERE user_id = 42 OR created_at < '2025-01-02';
--  Bitmap Heap Scan on events  (cost=64.36..6579.85 rows=2938 width=57)
--    Recheck Cond: ((user_id = 42) OR (created_at < '2025-01-02 00:00:00+00'::timestamp with time zone))
--    ->  BitmapOr  (cost=64.36..64.36 rows=2938 width=0)
--          ->  Bitmap Index Scan on events_user_id_idx  (cost=0.00..4.51 rows=11 width=0)
--          ->  Bitmap Index Scan on events_created_at_idx  (cost=0.00..58.38 rows=2928 width=0)

Why an index is not used. When you expect an index scan and see a sequential scan, check in this order:

1. The condition does not match the index: a function on the column (lower(email)), a type mismatch, a leading wildcard (LIKE '%x'), or the wrong leading column of a multicolumn index (Column Order in Composite Indexes).
2. The estimate says many rows match, so the sequential scan really is cheaper. Check whether the estimate is right.
3. The table is small: a few pages are cheaper to read entirely than through an index.
4. Cost parameters make random I/O look too expensive (random_page_cost).

Key takeaways

• Sequential scans win for large fractions of a table; index scans for small ones; bitmap scans in between and to combine indexes.
• The crossover point depends on correlation: scattered rows make index scans expensive much sooner.
• Index-only scans avoid the heap and stay cheap for large fractions.
• An unused index means either the condition cannot use it, or the planner estimates (correctly or not) that it is not cheaper.

🧪 Practice

1. (Beginner) Find the smallest range user_id BETWEEN 1 AND n for which the planner switches to a sequential scan, with parallelism disabled.
2. (Intermediate) Repeat the search with random_page_cost = 1.1 and explain why the crossover moves. Then set work_mem = '64kB' and run EXPLAIN ANALYZE for user_id BETWEEN 1 AND 30000 to observe lossy heap blocks.
3. (Interview) "Your query has WHERE status = 'active' and an index on status, but PostgreSQL does a sequential scan. A colleague suggests SET enable_seqscan = off in the application. What do you say?" Hint: What fraction of rows is 'active', and what would forcing the index cost? Consider a partial index for the rare values instead.

Nested Loop, Hash, and Merge Joins

PostgreSQL joins two inputs at a time using one of three algorithms. Each one is fastest in different circumstances, and recognizing them, along with what they need, is essential for reading any multi-table plan.

Nested loop. For each row of the outer input, scan the inner input for matches. With an index on the inner join column, each inner scan is a quick lookup.

 for each row o in outer:
     for each row i in inner where i.key = o.key:     <- index lookup, re-run for every o
         emit (o, i)

EXPLAIN SELECT u.name, e.kind FROM users AS u JOIN events AS e ON e.user_id = u.id WHERE u.id = 42;
--  Nested Loop  (cost=4.80..56.00 rows=11 width=15)
--    ->  Index Scan using users_pkey on users u  (cost=0.29..8.31 rows=1 width=14)          -- outer: 1 row
--          Index Cond: (id = 42)
--    ->  Bitmap Heap Scan on events e  (cost=4.51..47.58 rows=11 width=9)                  -- inner: run once per outer row
--          Recheck Cond: (user_id = 42)
--          ->  Bitmap Index Scan on events_user_id_idx  (cost=0.00..4.51 rows=11 width=0)

A nested loop is the only method that works for any join condition, including <, BETWEEN, or function calls. Without a usable index, it compares every pair of rows, and the planner may add a Materialize node to cache the inner side (next topic).

Hash join. Read the smaller input (inner) entirely and build a hash table on the join key. Then stream the larger input (outer) and probe the hash table for each row.

 build:  for each row i in inner:  hash_table[i.key].append(i)          <- blocking, uses memory
 probe:  for each row o in outer:  for i in hash_table[o.key]: emit (o, i)

EXPLAIN SELECT u.id, u.name, count(e.id)
FROM users AS u JOIN events AS e ON e.user_id = u.id
GROUP BY u.id;
--  HashAggregate  (cost=31181.11..32181.11 rows=100000 width=22)
--    Group Key: u.id
--    ->  Hash Join  (cost=3092.00..26181.11 rows=1000000 width=22)
--          Hash Cond: (e.user_id = u.id)
--          ->  Seq Scan on events e  (cost=0.00..20464.00 rows=1000000 width=12)    -- probe side
--          ->  Hash  (cost=1842.00..1842.00 rows=100000 width=14)                   -- build side
--                ->  Seq Scan on users u  (cost=0.00..1842.00 rows=100000 width=14)

The hash table must fit in work_mem x hash_mem_multiplier (4 MB x 2 by default). If it does not, the join splits both inputs into batches written to temporary files, shown as Batches: 8 or more in EXPLAIN ANALYZE. Hash joins need an equality condition.

Merge join. Sort both inputs on the join key (or read them in order from indexes), then walk through them together, like merging two sorted lists.

 outer (sorted): 1 1 2 4 4 5            inner (sorted): 1 2 3 4 6
                 ^                                      ^
 advance whichever side has the smaller key; emit pairs when keys are equal

EXPLAIN SELECT u.name, e.created_at
FROM users AS u JOIN events AS e ON e.user_id = u.id
ORDER BY u.id;
--  Merge Join  (cost=0.87..77593.42 rows=1000000 width=22)
--    Merge Cond: (u.id = e.user_id)
--    ->  Index Scan using users_pkey on users u  (cost=0.29..3449.29 rows=100000 width=14)
--    ->  Index Scan using events_user_id_idx on events e  (cost=0.42..61395.47 rows=1000000 width=12)

Here both inputs come pre-sorted from indexes, and the output is already in u.id order, so the ORDER BY costs nothing extra. Without that, the planner would rather hash-join and sort the result.

Comparison

	Nested loop	Hash join	Merge join
Join conditions	Any	Equality only	Equality (and sortable types)
Best when	Outer side small, inner side indexed	Large unsorted inputs, one fits in memory	Both inputs already sorted, or output must be sorted
Startup cost	Low: first row immediately	High: must build the hash table first	Low if pre-sorted, high if it must sort
Memory	Little	Hash table (work_mem x hash_mem_multiplier)	Little (plus sorts, if needed)
Degrades when	Outer side is much larger than estimated: inner side runs millions of times	Hash table exceeds memory and spills to disk	Inputs must be sorted from scratch
Typical in	OLTP lookups, LIMIT queries	Reports and analytics	Large joins on indexed keys

Join variants. The same three methods also implement LEFT, RIGHT, and FULL joins, and semi-joins (EXISTS, IN) and anti-joins (NOT EXISTS). They appear as Hash Semi Join, Nested Loop Anti Join, Hash Right Join, and so on. The planner may swap the sides of an outer join: for a LEFT JOIN b, a Hash Right Join means it built the hash table from a and probed it with b, because a was the smaller input.

Key takeaways

• Nested loop: per outer row, look up the inner side; ideal for a small outer side with an index on the inner side; works with any condition.
• Hash join: build a hash table on the smaller input, probe with the larger; ideal for large equality joins; spills to disk when memory runs out.
• Merge join: walk two sorted inputs together; ideal when both are already sorted or sorted output is needed.
• A nested loop whose outer row estimate is far too low is the most common cause of a catastrophically slow join.

🧪 Practice

1. (Beginner) Write three queries joining users and events that produce a nested loop, a hash join, and a merge join, without changing any settings.
2. (Intermediate) Run the hash join example with EXPLAIN ANALYZE and SET work_mem = '256kB'. How many batches does the hash join use, and how does execution time change?
3. (Interview) "A join that normally takes 50 ms takes 20 minutes. The plan shows a Nested Loop with rows=1 estimated for the outer side and rows=400000 actual. Explain what happened and how you would fix it." Hint: Compute how many times the inner side ran; then trace the outer side's estimate back to its cause (statistics, correlation, a function) rather than disabling nested loops.

Sort, Aggregate, and Materialize Nodes

Beyond scans and joins, most plans contain nodes that reorder, group, or store rows. These nodes are usually the ones that need memory, and the ones that slow down dramatically when their data does not fit in it.

Sort. Sorts its input for ORDER BY, merge joins, DISTINCT, window functions, or sort-based grouping. EXPLAIN ANALYZE shows which method it used:

Sort Method	Meaning
quicksort Memory: 51kB	Everything fit in work_mem, sorted in memory
top-N heapsort Memory: 26kB	With LIMIT n, only the best n rows are kept: cheap regardless of input size
external merge Disk: 65672kB	Did not fit; sorted runs were written to temporary files and merged

SET max_parallel_workers_per_gather = 0;

EXPLAIN ANALYZE SELECT * FROM events ORDER BY amount, id;              -- work_mem = 4MB (default)
--  Sort  (cost=195322.84..197822.84 rows=1000000 width=57) (actual time=1023.495..1173.172 rows=1000000.00 loops=1)
--    Sort Key: amount, id
--    Sort Method: external merge  Disk: 65672kB                         -- 64 MB spilled to disk
--    Buffers: shared hit=8967 read=1503 dirtied=1, temp read=16410 written=16434
--  Execution Time: 1237.108 ms

SET work_mem = '256MB';
-- same query:
--    Sort Method: quicksort  Memory: 95914kB                            -- fits in memory now
--  Execution Time: 968.382 ms
RESET work_mem;

EXPLAIN ANALYZE SELECT * FROM events ORDER BY amount DESC NULLS LAST LIMIT 10;
--  Limit  (...) (actual time=278.786..278.790 rows=10.00 loops=1)
--    ->  Sort  (...) (actual time=278.784..278.786 rows=10.00 loops=1)
--          Sort Key: amount DESC NULLS LAST
--          Sort Method: top-N heapsort  Memory: 26kB                    -- 10 rows kept, not 1,000,000

Incremental Sort. If the input is already sorted on a prefix of the sort key (for example from an index on created_at for ORDER BY created_at, id), PostgreSQL sorts only within groups of equal prefixes, which needs little memory and returns first rows early.

Aggregate nodes

Node	How it groups	Shows in EXPLAIN ANALYZE
Aggregate	No GROUP BY: one result row
HashAggregate	Hash table with one entry per group; input in any order	Batches, Memory Usage, Disk Usage
GroupAggregate	Input sorted by the group key; emits each group when the key changes
MixedAggregate	Several grouping sets at once (ROLLUP, CUBE)
Partial / Finalize	Parallel aggregation in two steps (Parallel Query Plans)

EXPLAIN ANALYZE SELECT user_id, count(*) FROM events GROUP BY user_id;
--  GroupAggregate  (cost=0.42..25472.11 rows=93169 width=12) (actual ... rows=99999.00 loops=1)
--    Group Key: user_id
--    ->  Index Only Scan using events_user_id_idx on events  (...)    -- index provides sorted input

EXPLAIN ANALYZE SELECT user_id, kind, count(*) FROM events GROUP BY user_id, kind;
--  HashAggregate  (cost=27964.00..28964.00 rows=100000 width=17) (actual ... rows=232860.00 loops=1)
--    Group Key: user_id, kind
--    Batches: 5  Memory Usage: 9017kB  Disk Usage: 15136kB            -- more groups than expected: spilled
--    ->  Seq Scan on events  (...)

The second plan expected 100,000 groups but found 232,860, so its hash table outgrew work_mem x hash_mem_multiplier (8 MB) and spilled to disk in 5 batches. Extended ndistinct statistics on (user_id, kind) would fix the estimate; more work_mem would avoid the spill.

Materialize. Stores its child's output in memory (spilling to disk if needed) so that it can be read again cheaply. It appears on the inner side of nested loops whose inner side would otherwise be recomputed for every outer row:

EXPLAIN ANALYZE
SELECT a.id, b.id
FROM events AS a JOIN events AS b
  ON b.created_at BETWEEN a.created_at AND a.created_at + interval '1 minute'   -- no equality: nested loop only
WHERE a.id <= 100 AND b.id <= 1000;
--  Nested Loop  (...) (actual ... rows=300.00 loops=1)
--    Join Filter: ((b.created_at >= a.created_at) AND (b.created_at <= (a.created_at + '00:01:00'::interval)))
--    Rows Removed by Join Filter: 99700
--    ->  Index Scan using events_pkey on events b  (...) (actual ... rows=1000.00 loops=1)
--    ->  Materialize  (...) (actual ... rows=100.00 loops=1000)        -- read 1,000 times from memory
--          Storage: Memory  Maximum Storage: 20kB
--          ->  Index Scan using events_pkey on events a  (...) (actual ... rows=100.00 loops=1)   -- ran once

Memoize. A cache in front of a parameterized inner side of a nested loop, keyed on the parameter. When many outer rows share the same key, only the first lookup for each key actually runs:

EXPLAIN ANALYZE
SELECT e.id, newest.name
FROM events AS e
CROSS JOIN LATERAL (SELECT u.name FROM users AS u
                    WHERE u.country = e.country
                    ORDER BY u.signed_up DESC LIMIT 1) AS newest     -- newest user in the same country
WHERE e.id <= 5000;
--  Nested Loop  (...) (actual time=15.366..119.504 rows=5000.00 loops=1)
--    ->  Index Scan using events_pkey on events e  (...) (actual ... rows=5000.00 loops=1)
--    ->  Memoize  (...) (actual ... rows=1.00 loops=5000)
--          Cache Key: e.country
--          Hits: 4992  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 1kB
--          ->  Subquery Scan on newest  (...) (actual time=14.450..14.451 rows=1.00 loops=8)  -- 8, not 5,000

RESET max_parallel_workers_per_gather;

Without Memoize, the subquery's sequential scan of users would have run 5,000 times instead of 8.

Key takeaways

• Sort Method tells you whether a sort fit in memory (quicksort), used a bounded heap for LIMIT (top-N heapsort), or spilled to disk (external merge).
• HashAggregate needs memory per group; GroupAggregate needs sorted input. Underestimated group counts cause spills.
• Materialize caches a whole inner input for repeated reads; Memoize caches inner results per parameter value.
• Disk spills (Disk:, temp read/written, Batches > 1) point to work_mem or to a row-count misestimate.

🧪 Practice

1. (Beginner) Find the smallest work_mem for which SELECT * FROM users ORDER BY name sorts in memory.
2. (Intermediate) Create CREATE STATISTICS (ndistinct) ON user_id, kind FROM events, run ANALYZE, and re-run the GROUP BY user_id, kind query. Did the estimate change? Did the plan change? What else would avoid the spill?
3. (Interview) "A query's EXPLAIN ANALYZE shows Sort Method: external merge Disk: 2GB. The team wants to raise work_mem globally to 4 GB. What are the risks and alternatives?" Hint: work_mem is per operation per process; multiply by connections and nodes. Consider setting it per role or transaction, or an index that provides the order.

Estimated vs. Actual Row Discrepancies

Nearly every bad plan can be traced to a node where the planner expected one number of rows and got a very different one. Learning to find that node quickly is the single most useful skill in plan analysis, because it turns "the query is slow" into a specific, fixable cause.

Comparing correctly. Estimated rows and actual rows are both per loop. To compare totals, multiply each by loops. In parallel plans, the per-loop values are averages across processes:

 ->  Index Only Scan using events_user_id_idx on events e
       (cost=0.42..3.42 rows=11 width=4) (actual time=0.009..0.010 rows=10.09 loops=945)

 estimated rows per loop: 11           x 945 loops ~ 10,400
 actual rows per loop:    10.09        x 945 loops ~  9,535      -> estimate is fine

A node with rows=1 estimated and rows=1.00 loops=500000 actual is not a misestimate per lookup, but if the planner expected loops=10, the problem is in the node feeding those loops.

A procedure for any slow plan

1. Run EXPLAIN (ANALYZE, BUFFERS).
2. Find where the time goes: the node with the largest self time (EXPLAIN ANALYZE and Real Timings).
3. Starting from the leaves, compare estimated and actual rows for each node. Note the lowest node where they differ by more than about 10x.
4. Ask whether that misestimate changed a decision above it: the join method, join order, or whether an aggregate spilled.
5. Diagnose the cause of that estimate (When Estimates Go Wrong) and fix it there.

Example: a misestimate that causes a spill. From the previous topic:

 HashAggregate  (cost=27964.00..28964.00 rows=100000 ...) (actual ... rows=232860.00 loops=1)
   Group Key: user_id, kind
   Batches: 5  Memory Usage: 9017kB  Disk Usage: 15136kB

The planner multiplies the distinct counts of user_id (~92,000) and kind (4), and then caps the result at 10 % of the input rows when grouping by several columns, giving 100,000. The real number of groups is 232,860. The spill is a direct consequence of planning memory for 100,000 groups.

Example: a misestimate that changes the plan. The classic disaster shape:

 Nested Loop  (rows=12) (actual rows=380512.00 loops=1)
   ->  Seq Scan on orders o  (rows=3) (actual rows=95128.00 loops=1)       <-- the problem starts here
         Filter: ((status = 'pending') AND (region = 'EU'))
   ->  Index Scan using order_items_order_id_idx on order_items i
         (rows=4) (actual rows=4.00 loops=95128)                         <-- each lookup is fine

The inner index scan is fast per lookup, but it ran 95,128 times instead of 3. A hash join would have read order_items once. The cause is the filter estimate on the outer side, probably correlated columns (status, region), and the fix is extended statistics, not disabling nested loops.

Rows removed. Rows Removed by Filter, Rows Removed by Join Filter, and Rows Removed by Index Recheck show work that was done and thrown away:

 Parallel Seq Scan on events  (...) (actual ... rows=14971.00 loops=3)
   Filter: (events.kind = 'purchase'::text)
   Rows Removed by Filter: 318362           -- per loop: 3 x 318,362 = 955,086 rows read for nothing

Many rows removed by a filter on a column with good selectivity suggests a missing index. Many rows removed by a join filter suggests a missing equality condition or index on the join.

How large is too large? Small differences are normal; statistics are a sample. As a rough guide:

Ratio of actual to estimated	Typical impact
Within 2x	Harmless
10x	Can change join methods and memory decisions; investigate if the plan is slow
100x or more	Almost always the cause of a bad plan when one exists

Misestimates also compound: if each of three joined filters is 10x too optimistic, the join result can be off by 1,000x.

Key takeaways

• Compare estimated and actual rows per loop, and account for loops when reasoning about totals.
• Find the lowest node with a large discrepancy; everything above it was planned on wrong numbers.
• A large underestimate on the outer side of a nested loop is the classic catastrophic plan.
• Rows Removed by Filter reveals wasted reads and missing indexes.

🧪 Practice

1. (Beginner) Run EXPLAIN ANALYZE SELECT * FROM events WHERE user_id < 200 AND country = 'JP' and compute the ratio between actual and estimated rows on each node.
2. (Intermediate) Drop the users_city_country statistics, then run EXPLAIN ANALYZE on a join of users (filtered by city = 'Berlin' AND country = 'DE') with events. Locate the lowest misestimated node and explain whether it changed the join method.
3. (Interview) "Walk me through how you analyze a 40-node execution plan for a query that takes 30 seconds." Hint: Describe a systematic method: total versus self time, buffers, the lowest large estimate error, and checking whether that error caused the expensive decision. Mention tools that highlight these automatically.

Parallel Query Plans

A single backend process uses one CPU core. For a query that scans a 50 GB table, the rest of the server's cores sit idle. Parallel query lets one query use several processes: a leader (the backend for your connection) and workers started for the duration of the query, each processing part of the data.

Analogy. Counting votes. One person counting a million ballots takes hours; four people each counting a quarter and then adding up their totals take a quarter of the time, plus a little time for coordination.

Plan shape. Parallel plans have a Gather or Gather Merge node. Everything below it runs in each process; everything above runs only in the leader:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS OFF)
SELECT country, count(*), avg(amount) FROM events WHERE kind = 'purchase' GROUP BY country;
--  Finalize GroupAggregate  (cost=16813.10..16815.60 rows=8 width=43) (actual ... rows=8.00 loops=1)
--    Group Key: events.country
--    ->  Gather Merge  (cost=16813.10..16815.31 rows=19 width=43) (actual ... rows=24.00 loops=1)
--          Workers Planned: 2
--          Workers Launched: 2                               -- could be fewer if no workers were free
--          ->  Sort  (...) (actual ... rows=8.00 loops=3)                     -- leader + 2 workers
--                Sort Key: events.country
--                ->  Partial HashAggregate  (...) (actual ... rows=8.00 loops=3)
--                      Group Key: events.country
--                      ->  Parallel Seq Scan on public.events  (cost=0.00..15672.33 rows=18736 width=9)
--                                                              (actual ... rows=14971.00 loops=3)
--                            Filter: (events.kind = 'purchase'::text)
--                            Rows Removed by Filter: 318362
--                            Worker 0:  actual time=0.010..62.374 rows=12323.00 loops=1
--                            Worker 1:  actual time=0.014..63.595 rows=15463.00 loops=1
--  Execution Time: 78.298 ms

               leader only:   Finalize GroupAggregate   <- combines 3 partial results per country
                                       |
                               Gather Merge             <- collects sorted streams from all processes
              ---------------------------------------------------------------
 each process:   Sort -> Partial HashAggregate -> Parallel Seq Scan (a share of the pages)
                 (leader)          (worker 0)          (worker 1)

Reading the numbers. loops=3 means three processes ran the node (leader plus two workers), and rows=14971.00 is the average per process, so the scan returned about 44,900 rows in total. VERBOSE shows each worker's share; the leader's share is the rest (about 17,100 here), since the leader also scans when it is not busy collecting results (parallel_leader_participation). Estimated rows for parallel nodes are also per process: 18736 is the total divided by 2.4 (two workers plus a partial share for the leader).

Parallel-aware nodes

Node	How work is divided
Parallel Seq Scan	Processes take blocks of pages from a shared counter
Parallel Index Scan, Parallel Index Only Scan	Processes take leaf pages of a B-tree
Parallel Bitmap Heap Scan	One process builds the bitmap; all share the heap reading
Parallel Hash Join (Parallel Hash)	All processes build one shared hash table together
Partial Aggregate / Finalize Aggregate	Each process aggregates its rows; the leader combines the partial states
Parallel Append	Processes are spread across the children, for example partitions

When the planner uses parallelism. Every parallel plan pays parallel_setup_cost (1,000) to start workers and parallel_tuple_cost (0.1) per row sent through Gather. It is therefore chosen for large scans that return relatively few rows, such as aggregates and selective filters on big tables. The table must be at least min_parallel_table_scan_size (8 MB) for a parallel sequential scan to be considered. Settings that control how many workers are used are covered in Parallelism Settings.

When parallelism is not possible

• The query writes data (INSERT, UPDATE, DELETE, MERGE). CREATE TABLE ... AS SELECT and the SELECT part of some statements can still run in parallel.
• The query calls a function marked PARALLEL UNSAFE (the default for user-defined functions). Declare functions PARALLEL SAFE when they qualify (Function Cost and Parallel Safety).
• The query runs inside a cursor (DECLARE CURSOR) or a PL/pgSQL FOR loop over a query.
• Serializable isolation is in use (before PostgreSQL 12).

Workers Planned vs. Launched. Workers come from a shared pool (max_parallel_workers). If the pool is exhausted by other queries, a query gets fewer workers than planned, or none, and runs correspondingly slower. Seeing Workers Launched lower than Workers Planned under load is a sign that the pool is too small or too many queries run in parallel at once.

Key takeaways

• Parallel plans contain Gather or Gather Merge; nodes below run in the leader and in each worker.
• loops counts processes; rows and estimates are per process, and VERBOSE shows each worker's share.
• Aggregates are split into Partial (per process) and Finalize (leader) steps.
• Parallelism is skipped for writes, cursors, parallel-unsafe functions, and small tables; under load, fewer workers may launch than planned.

🧪 Practice

1. (Beginner) Run EXPLAIN ANALYZE SELECT count(*) FROM events and identify the leader-only nodes and the per-process nodes.
2. (Intermediate) Create a PL/pgSQL function is_big(numeric) RETURNS boolean without a parallel label and use it in WHERE is_big(amount). Compare the plan with the same function declared PARALLEL SAFE.
3. (Interview) "A dashboard query runs in 2 seconds when tested alone but in 6 seconds during business hours, with identical plans. What could explain it?" Hint: Compare Workers Planned with Workers Launched under load, and think about the shared worker pool and CPU contention.

<a id="74-performance-tuning"></a>

7.4 Performance Tuning

Once you can read plans, you can change what produces them. This subchapter covers the server settings with the largest effect on performance (memory, cost parameters, parallelism, and JIT), then the application side: connection pooling, reducing round trips, rewriting slow queries, and caching results. The order is deliberate: configuration sets a sensible baseline once, while most real speed-ups come from how the application talks to the database.

Memory Parameters work_mem and shared_buffers

Memory is where PostgreSQL performance is won or lost: reading a page from memory takes about 100 nanoseconds, from an SSD about 100 microseconds, a thousand times longer. PostgreSQL does not size its memory automatically; the defaults are deliberately small so that it starts on any machine. A handful of parameters decide how memory is used.

The two kinds of memory

 +------------------------------- server RAM ------------------------------------+
 |                                                                               |
 |  shared memory (allocated at startup, shared by all processes)                |
 |  +-------------------------------------------+                                |
 |  | shared_buffers: cache of 8 KB data pages  |   WAL buffers, lock table ...  |
 |  +-------------------------------------------+                                |
 |                                                                               |
 |  private memory (per backend, allocated as needed)                            |
 |  [backend 1: work_mem x each sort/hash node]  [backend 2 ...]  [backend N]    |
 |  [maintenance_work_mem for VACUUM, CREATE INDEX]                              |
 |                                                                               |
 |  operating system page cache (everything left over: caches files as well)    |
 +-------------------------------------------------------------------------------+

shared_buffers: the database's own cache. Every page PostgreSQL reads or writes goes through this pool. Pages that are used often stay in it; pages that are not are evicted. Because PostgreSQL reads files through the operating system, a page evicted from shared buffers is often still in the OS page cache, so data is effectively cached twice (double buffering). That is why shared buffers should not take most of RAM.

Parameter	Default	Typical starting point	Notes
shared_buffers	128 MB	25 % of RAM (rarely more than 40 %)	Requires a restart. Very large values on big machines benefit from huge pages (huge_pages)
work_mem	4 MB	16 to 64 MB for OLTP; more for analytics, per role	Per sort or hash operation, per process. Not a per-connection limit
hash_mem_multiplier	2.0	Leave, or raise for hash-heavy reports	Hash tables may use work_mem x hash_mem_multiplier
maintenance_work_mem	64 MB	512 MB to 2 GB	VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY; few run at once
autovacuum_work_mem	-1 (use maintenance_work_mem)	Leave or cap	Each autovacuum worker uses it
temp_buffers	8 MB	Raise for heavy temporary-table use	Per session cache for temporary tables

Seeing what is cached. The pg_buffercache extension shows the contents of shared buffers:

CREATE EXTENSION pg_buffercache;

SELECT c.relname,
       count(*)                          AS buffers,
       pg_size_pretty(count(*) * 8192)   AS cached
FROM pg_buffercache AS b
JOIN pg_class AS c
  ON b.relfilenode = pg_relation_filenode(c.oid)
 AND b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 5;
--       relname       | buffers | cached
-- --------------------+---------+---------
--  events             |   10470 | 82 MB         -- the whole table fits in 128 MB of shared buffers
--  events_user_id_idx |    1133 | 9064 kB
--  users              |     843 | 6744 kB

The database-wide cache hit ratio is a quick health check. For an OLTP workload, it should be above 99 %:

SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database WHERE datname = current_database();
--  datname | hit_pct
-- ---------+---------
--  shop    |   99.64

work_mem: memory per operation. Each Sort, Hash, and HashAggregate node in each process may use up to work_mem (hash nodes: times hash_mem_multiplier) before spilling to temporary files (Sort, Aggregate, and Materialize Nodes). The danger is multiplication:

 worst case ~ max_connections x nodes per query x work_mem x (1 + parallel workers)
            = 200 connections x 4 nodes x 64 MB x 1   = 51 GB    <- more than many servers have

Real usage is far below the worst case, because most queries do not sort large data at the same time. But a global work_mem of 1 GB "to make reports faster" can make the server run out of memory when many reports run at once. Set it high only where it is needed:

ALTER ROLE reporting SET work_mem = '256MB';      -- every session of this role
SET LOCAL work_mem = '1GB';                       -- one transaction, e.g. a nightly batch step

Finding spills. Log every temporary file above a size so that you can see which queries need more memory:

ALTER SYSTEM SET log_temp_files = '10MB';         -- log temp files larger than 10 MB
SELECT pg_reload_conf();
-- LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 67248128
-- STATEMENT:  SELECT * FROM events ORDER BY amount, id;

pg_stat_database.temp_files and temp_bytes count spills per database.

Key takeaways

• shared_buffers is PostgreSQL's page cache; start at about 25 % of RAM, because the OS page cache also caches the same files.
• work_mem applies per sort or hash operation, per process; raise it per role or per transaction rather than globally.
• maintenance_work_mem speeds up VACUUM and index builds, and can be generous because few run at once.
• Watch the cache hit ratio, log_temp_files, and temp_bytes to see whether memory is too small.

🧪 Practice

1. (Beginner) Check the current values of shared_buffers, work_mem, and maintenance_work_mem with SHOW, and find in pg_settings which of them need a restart to change (context column).
2. (Intermediate) Run SELECT * FROM events ORDER BY amount, id with work_mem set to 4 MB, 32 MB, and 256 MB. Record the sort method, disk usage, and time for each.
3. (Interview) "A server with 64 GB RAM, max_connections = 500, and work_mem = 256MB crashes with the out-of-memory killer during month-end reporting. What do you change?" Hint: Do the worst-case multiplication, then consider per-role settings, fewer connections via a pooler, and limiting concurrent reports.

Effective Cache Size and Random Page Cost

Two planner parameters describe the storage to the planner rather than allocating anything: how expensive random reads are, and how much of the data is probably cached. Their defaults describe a server from around 2005, with spinning disks and little memory. On modern hardware, they make the planner too reluctant to use indexes.

random_page_cost. The cost of reading a page out of order, relative to a sequential page (1.0). The default of 4 assumes a spinning disk where random reads are much slower, already discounted for caching. SSDs and NVMe drives have almost no penalty for random access.

effective_cache_size. The planner's assumption about how much memory is available for caching data, in shared buffers and the OS page cache combined. It allocates nothing. A larger value tells the planner that an index scan which revisits the same pages will likely find them in cache, which makes index scans, especially on the inner side of nested loops, look cheaper.

Parameter	Default	Typical setting
random_page_cost	4.0	1.1 to 1.5 on SSD, NVMe, or when the working set fits in RAM; 4 for spinning disks
seq_page_cost	1.0	Leave at 1.0
effective_cache_size	4 GB	50 % to 75 % of RAM
effective_io_concurrency	16 (18+; 1 before)	200 or more for SSDs: how many I/O requests may be in flight for prefetching (bitmap heap scans; with the asynchronous I/O of PostgreSQL 18, also sequential scans and vacuum)

The effect on a real plan. The join from EXPLAIN ANALYZE and Real Timings built a hash table of 100,000 users to join 641 events, because 641 random lookups in users_pkey looked expensive at random_page_cost = 4:

-- random_page_cost = 4 (default)
--  Sort  ...
--    ->  Hash Join  (cost=3092.43..3125.21 rows=685 width=23) (actual ... rows=641.00 loops=1)
--          ->  Index Scan using events_created_at_idx on events e  (...)
--          ->  Hash  ->  Seq Scan on users u  (... rows=100000 ...)
--  Execution Time: 43.093 ms

SET random_page_cost = 1.1;
EXPLAIN ANALYZE
SELECT u.name, e.kind, e.created_at
FROM events AS e JOIN users AS u ON u.id = e.user_id
WHERE e.created_at >= '2025-12-14'
ORDER BY e.created_at;
--  Nested Loop  (cost=0.72..997.13 rows=600 width=23) (actual time=0.066..5.385 rows=641.00 loops=1)
--    ->  Index Scan using events_created_at_idx on events e  (...) (actual ... rows=641.00 loops=1)
--    ->  Index Scan using users_pkey on users u  (cost=0.29..1.63 rows=1 width=14) (actual ... rows=1.00 loops=641)
--          Index Cond: (id = e.user_id)
--  Execution Time: 5.504 ms                         -- 8x faster; no sort needed (index already ordered)
RESET random_page_cost;

How to tune them safely

1. Set effective_cache_size to roughly 50 to 75 % of RAM. Being off by a factor of two hardly matters.
2. On SSDs, set random_page_cost to 1.1 to 1.5. If different tablespaces use different storage, set it per tablespace: ALTER TABLESPACE archive SET (random_page_cost = 4).
3. Measure before and after with pg_stat_statements (pg_stat_statements) on real traffic, not on one query.
4. Avoid setting random_page_cost below seq_page_cost: random reads are never cheaper than sequential ones, and the planner would then prefer index scans even for whole-table reads.

Key takeaways

• random_page_cost (default 4) models spinning disks; 1.1 to 1.5 suits SSDs and fully cached data.
• effective_cache_size allocates nothing; it tells the planner how likely repeated page reads are to hit cache. Set it to 50 to 75 % of RAM.
• Both mainly shift the balance between index scans and nested loops on one side, and sequential scans and hash joins on the other.
• Both can be set per tablespace (random_page_cost), database, role, or session; validate changes against the whole workload.

🧪 Practice

1. (Beginner) Set effective_cache_size to '128MB' and then '16GB' in your session, and compare EXPLAIN costs for SELECT * FROM events WHERE user_id < 500. Did the plan change?
2. (Intermediate) Find the value of random_page_cost at which the planner switches the join in this topic from hash join to nested loop.
3. (Interview) "After migrating from on-premises spinning disks to cloud SSD storage, many queries got slower even though the hardware is faster. What setting would you check first and why?" Hint: Which parameter still describes the old storage, and which plan shapes does it penalize?

Parallelism Settings

Parallel query (Parallel Query Plans) lets one query use several CPU cores. How many workers a query may use, and how many may run on the whole server, is controlled by a small set of settings that form a hierarchy of limits.

The hierarchy

 max_worker_processes (8)              all background workers: parallel, logical replication, extensions
   +-- max_parallel_workers (8)        of those, how many may serve parallel queries at once
         +-- max_parallel_workers_per_gather (2)      per Gather node of one query
         +-- max_parallel_maintenance_workers (2)     per CREATE INDEX / VACUUM

Setting	Default	Meaning
max_worker_processes	8	Total background worker slots (restart required)
max_parallel_workers	8	Workers available to parallel queries, server-wide
max_parallel_workers_per_gather	2	Workers one Gather may use; 0 disables parallel query
max_parallel_maintenance_workers	2	Workers for parallel index builds and VACUUM of indexes
min_parallel_table_scan_size	8 MB	Smaller tables are never scanned in parallel
min_parallel_index_scan_size	512 kB	Same for indexes
parallel_setup_cost, parallel_tuple_cost	1000, 0.1	Planner costs of starting workers and passing rows
parallel_leader_participation	on	Whether the leader also scans data

How many workers a table gets. The planner adds one worker each time the table size triples beyond min_parallel_table_scan_size: 8 MB gets 1, 24 MB gets 2, 72 MB gets 3, 216 MB gets 4, and so on, always capped by max_parallel_workers_per_gather. Override it per table:

SET max_parallel_workers_per_gather = 8;
EXPLAIN SELECT count(*) FROM events;              -- events is 82 MB
--  Finalize Aggregate  ...
--    ->  Gather  ...  Workers Planned: 3           -- from the size rule, not 8

ALTER TABLE events SET (parallel_workers = 6);
--    ->  Gather  ...  Workers Planned: 6
ALTER TABLE events RESET (parallel_workers);

Diminishing returns. Measured on the events table for a filtered aggregate, with parallel_setup_cost = 0 to force parallel plans:

max_parallel_workers_per_gather	Workers launched	Execution time	Speed-up
0	0	308 ms	1.0x
1	1	223 ms	1.4x
2	2	167 ms	1.8x
4	3 (size cap)	139 ms	2.2x

Each worker helps less than the one before: the leader spends time combining results, and data must be passed between processes. Parallelism also does not make the server faster in total; it lets one query use more of it, at the expense of others.

Choosing settings by workload

Workload	Suggestion
OLTP: many short queries, many connections	Keep max_parallel_workers_per_gather at 2 or lower; parallel workers add overhead to short queries and compete with other connections for cores
Analytics: few large queries	Raise max_parallel_workers_per_gather to 4 to 8, and max_parallel_workers and max_worker_processes to about the number of cores
Mixed	Low default, higher per role: ALTER ROLE reporting SET max_parallel_workers_per_gather = 6
Index builds on large tables	Raise max_parallel_maintenance_workers for the session running CREATE INDEX

Key takeaways

• Worker limits form a hierarchy: max_worker_processes > max_parallel_workers > per-gather and maintenance limits.
• The number of workers grows with the logarithm of table size; ALTER TABLE ... SET (parallel_workers = n) overrides it.
• Speed-up per added worker diminishes, and parallel workers take CPU from other sessions.
• Keep parallelism low for OLTP and raise it per role for analytical work.

🧪 Practice

1. (Beginner) Run EXPLAIN ANALYZE SELECT count(*) FROM events WHERE country = 'JP' with max_parallel_workers_per_gather set to 0, 2, and 4, and record the execution times.
2. (Intermediate) Set max_parallel_workers = 2 and start the same query in three sessions at once (for example with pgbench -c 3 -f query.sql). Check Workers Launched with auto_explain or by running EXPLAIN ANALYZE while the others are busy.
3. (Interview) "Should a 64-core OLTP server set max_parallel_workers_per_gather = 32?" Hint: Consider how many queries run at once, the overhead of starting workers for short queries, and what happens to other sessions when one query takes 33 cores.

JIT Compilation Controls

For each row, the executor evaluates expressions such as amount * 1.21 > 100 by interpreting a small program step by step, and deforms tuples (extracts column values from the stored row format) using generic code. For queries that process many millions of rows, that interpretation overhead adds up. PostgreSQL can instead compile these expressions to native machine code at run time, using LLVM: just-in-time (JIT) compilation.

Analogy. Following a recipe by reading each step from the book versus memorizing it first. Memorizing takes time up front, which only pays off if you will cook the dish many times.

What JIT compiles

Part	Speeds up
Expressions	WHERE conditions, computed columns, aggregate transition steps
Tuple deforming	Extracting columns from heap tuples
Inlining	Copies the bodies of small built-in functions (like int4pl) into the compiled code
Optimization	Runs LLVM optimization passes over the generated code

When it is used. JIT is chosen by the estimated total cost of the plan, in three steps:

Setting	Default	Effect when the plan cost exceeds it
jit	on	Master switch (requires PostgreSQL built with LLVM)
jit_above_cost	100,000	Compile expressions and deforming
jit_inline_above_cost	500,000	Also inline functions (expensive to compile)
jit_optimize_above_cost	500,000	Also run LLVM optimizations (expensive to compile)

Reading JIT in a plan. When used, EXPLAIN ANALYZE adds a section at the end:

EXPLAIN ANALYZE SELECT sum(e.amount), count(*)
FROM events AS e, generate_series(1, 5) AS g
WHERE e.user_id + g > 10;                      -- 5 million rows through the expression
--  Aggregate  (cost=143212.39..143212.40 rows=1 width=40) (actual time=1885.062..1885.065 rows=1.00 loops=1)
--    ...
--  JIT:
--    Functions: 11
--    Options: Inlining false, Optimization false, Expressions true, Deforming true   -- cost < 500,000
--    Timing: Generation 0.709 ms (...), Inlining 0.000 ms, Optimization 0.419 ms, Emission 9.578 ms, Total 10.705 ms
--  Execution Time: 1889.128 ms                -- with jit = off: 2077 ms (JIT saved ~9 %)

When JIT hurts. Compilation time is spent before the first row, whatever the query actually ends up doing. Forcing full JIT (all thresholds 0) on a one-million-row aggregate on events:

 jit = off                                       Execution Time: 589 ms
 jit with inlining and optimization forced       Execution Time: 771 ms
   JIT: Timing: ... Inlining 94.5 ms, Optimization 100.2 ms, Emission 64.8 ms, Total 260.3 ms

The compiled code ran faster, but 260 ms of compilation more than ate the savings. In production, this happens when a misestimate makes a short query look expensive: a query that actually runs in 5 ms spends 200 ms compiling. The symptom is a JIT: section whose Total is a large share of Execution Time.

Practical guidance

Workload	Recommendation
OLTP: short queries	jit = off, or raise jit_above_cost substantially (for example to 1,000,000). Many managed services disable it by default
Analytics on large tables	Keep it on; check JIT: Timing against total time for your heaviest queries
Mixed	jit = off globally, ALTER ROLE analyst SET jit = on

Key takeaways

• JIT compiles expressions and tuple deforming to machine code with LLVM; it helps queries that process many millions of rows.
• It is triggered by estimated plan cost: compilation above jit_above_cost, expensive inlining and optimization above 500,000.
• Compilation can take hundreds of milliseconds, which hurts short queries, especially when their cost is overestimated.
• Disable or raise thresholds for OLTP; check the JIT: timing section in EXPLAIN ANALYZE.

🧪 Practice

1. (Beginner) Run SHOW jit and SELECT pg_jit_available() to check whether your server can use JIT.
2. (Intermediate) Run a query on events with cost above 100,000 with jit on and off three times each. Compute the average and decide whether JIT pays off for it.
3. (Interview) "After a PostgreSQL upgrade, the p99 latency of a simple API endpoint jumped from 20 ms to 300 ms, but only for some requests. EXPLAIN ANALYZE shows a JIT: section. Explain." Hint: Which requests get plans whose estimated cost exceeds the JIT thresholds, and what fixed cost does compilation add?

Connection Pooling with PgBouncer

PostgreSQL starts a separate operating system process for every connection, with its own memory, catalog caches, and prepared statements. That design is robust, but each connection costs several megabytes and some CPU to create, and thousands of mostly idle connections make the whole server slower. A connection pooler sits between applications and the database, keeps a small number of real server connections, and lends them to many clients.

Analogy. A restaurant with 1,000 guests does not hire 1,000 waiters. A few waiters serve many tables, because each table needs a waiter only briefly.

Why too many connections hurt

• Each backend uses memory: a few MB baseline, plus work_mem per operation, plus catalog caches that grow with schema size.
• Opening a connection forks a process and authenticates, often several milliseconds, more with TLS.
• With hundreds of active connections, the server spends time on lock contention and snapshot computation instead of useful work. Throughput usually peaks at a few times the number of CPU cores, then declines.
• max_connections (default 100) caps the total; raising it to thousands does not make the server handle more work.

 without a pooler:                          with PgBouncer (transaction mode):

 app pod 1 (50 conns) --+                   app pod 1 (50 conns) --+
 app pod 2 (50 conns) --+--> PostgreSQL     app pod 2 (50 conns) --+--> PgBouncer --(20 conns)--> PostgreSQL
 ...                    |    (1,000         ...                    |    (1,000 client
 app pod 20 (50 conns) -+     backends)     app pod 20 (50 conns) -+     connections)

Pool modes

Mode	A server connection is assigned to a client for	Compatible with	Efficiency
session	The whole client connection	Everything	Low: idle clients still hold a server connection
transaction	One transaction	Most applications; restrictions below	High: the usual choice
statement	One statement; multi-statement transactions are forbidden	Autocommit-only workloads	Highest

Restrictions in transaction mode. Between transactions, a client may get a different server connection, so anything that lives in the session breaks:

Feature	Problem	Alternative
SET search_path, SET work_mem	Leaks to other clients or is lost	SET LOCAL inside the transaction, or role/database settings
Session advisory locks	Held by the wrong client	Transaction-level pg_advisory_xact_lock
LISTEN	Notifications go to a connection the client no longer has	Dedicated session-mode connection for listeners
Temporary tables across transactions	Disappear or appear in other sessions	Use them within one transaction (ON COMMIT DROP)
WITH HOLD cursors	Live beyond a transaction	Avoid
Prepared statements	Live in one server session	PgBouncer 1.21+ tracks protocol-level prepared statements with max_prepared_statements

A minimal configuration

; /etc/pgbouncer/pgbouncer.ini
[databases]
shop = host=10.0.0.5 port=5432 dbname=shop          ; clients connect to "shop" on PgBouncer

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000                ; client connections PgBouncer accepts
default_pool_size = 20                ; server connections per (database, user) pair
reserve_pool_size = 5                 ; extra connections when clients wait longer than reserve_pool_timeout
max_prepared_statements = 200         ; 1.21+: support protocol-level prepared statements in transaction mode
server_idle_timeout = 600

Applications change only their connection string, to port 6432. PgBouncer has its own admin console for monitoring:

-- psql -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;    -- cl_active, cl_waiting (clients waiting for a connection), sv_active, sv_idle per pool
SHOW STATS;    -- queries, transactions, and wait time per database

A steadily non-zero cl_waiting means the pool is too small, or transactions are held too long.

Sizing the pool. A good starting point is a small multiple of the number of CPU cores, for example cores x 2 plus the number of disks that can work in parallel. A pool of 20 to 50 connections can serve thousands of clients if transactions are short. Application-side pools (HikariCP, SQLAlchemy, node-postgres Pool) are still useful, but with many application instances, their totals multiply; a central pooler bounds the total.

Key takeaways

• Each connection is a process with its own memory; throughput peaks at a modest number of active connections.
• PgBouncer in transaction mode multiplexes many clients over few server connections.
• Transaction mode breaks session state: SET, session advisory locks, LISTEN, and (before 1.21) prepared statements.
• Size the server pool from CPU and disk capacity, not from the number of clients; monitor waiting clients.

🧪 Practice

1. (Beginner) Run SELECT count(*), state FROM pg_stat_activity GROUP BY state on a database you use. How many connections are idle?
2. (Intermediate) Run pgbench -c 200 -j 4 -T 60 -S directly against PostgreSQL and through PgBouncer with default_pool_size = 20 in transaction mode. Compare throughput and latency.
3. (Interview) "A service has 40 Kubernetes pods, each with a connection pool of 25, against a database with max_connections = 500. During a deploy, connections fail. Explain and propose a design." Hint: Count connections during a rolling deploy when old and new pods overlap, and consider a central pooler and smaller per-pod pools.

Batching and Round-Trip Reduction

Every statement an application sends travels to the server and back. On the same machine, a round trip takes tens of microseconds; across a data center, 0.5 to 1 ms; across regions, 50 ms or more. A request handler that runs 200 small queries one after another spends most of its time waiting for the network, not for the database. Reducing the number of round trips is often the largest available speed-up, and requires no database changes at all.

Analogy. Carrying groceries from the car one item at a time versus loading everything into one bag. The walking, not the lifting, takes the time.

Measured: loading 10,000 rows. The following Python script inserts the same 10,000 rows six ways, against PostgreSQL 18 in a local container:

import psycopg

rows = [(i % 100000 + 1, 'view', 'US', None, '2025-06-01 00:00+00') for i in range(10_000)]
sql = "INSERT INTO ev_load VALUES (%s, %s, %s, %s, %s)"

with psycopg.connect("dbname=shop", autocommit=True) as conn:
    # 1. one statement per row, each its own transaction (one commit = one WAL flush per row)
    for r in rows:
        conn.execute(sql, r)

    # 2. one statement per row, single transaction (one round trip per row, one flush)
    with conn.transaction():
        for r in rows:
            conn.execute(sql, r)

    # 3. executemany: psycopg 3 pipelines the statements, sending them without waiting for each reply
    with conn.transaction(), conn.cursor() as cur:
        cur.executemany(sql, rows)

    # 4. multi-row VALUES: 1,000 rows per statement (watch the 65,535 parameter limit)
    with conn.transaction():
        for i in range(0, len(rows), 1000):
            chunk = rows[i:i + 1000]
            placeholders = ",".join(["(%s,%s,%s,%s,%s)"] * len(chunk))
            conn.execute("INSERT INTO ev_load VALUES " + placeholders,
                         [v for r in chunk for v in r])

    # 5. one statement, one parameter per column: arrays unpacked by unnest
    cols = [list(c) for c in zip(*rows)]
    conn.execute("""INSERT INTO ev_load
                    SELECT * FROM unnest(%s::int[], %s::text[], %s::char(2)[],
                                         %s::numeric[], %s::timestamptz[])""", cols)

    # 6. COPY: a streaming bulk-load protocol
    with conn.cursor() as cur, cur.copy("COPY ev_load FROM STDIN") as copy:
        for r in rows:
            copy.write_row(r)

Method	Time for 10,000 rows	Round trips	Commits
1. INSERT per row, autocommit	19,209 ms	10,000	10,000
2. INSERT per row, one transaction	2,330 ms	10,000	1
3. executemany (pipelined)	222 ms	~1 (pipelined)	1
4. Multi-row VALUES, 1,000 rows each	267 ms	10	1
5. INSERT ... SELECT FROM unnest(arrays)	88 ms	1	1
6. COPY FROM STDIN	14 ms	1 (streamed)	1

Method 1 is dominated by 10,000 WAL flushes to disk at commit (Synchronous Commit Trade-offs); method 2 by 10,000 round trips. The rest are 10 to 1,000 times faster than method 1, with the same data and the same database.

The N+1 query problem. The same effect appears when reading. An ORM loads 100 orders, then lazily loads each order's customer with a separate query: 101 round trips.

// N+1: one query for the list, then one per row
const { rows: orders } = await pool.query(
  "SELECT id, customer_id FROM orders WHERE placed_at > now() - interval '1 day'");
for (const o of orders) {
  const { rows } = await pool.query("SELECT name FROM customers WHERE id = $1", [o.customer_id]);
  o.customer = rows[0];                          // 1 round trip per order
}

// One round trip: let the database join
const { rows: withCustomers } = await pool.query(`
  SELECT o.id, c.name AS customer
  FROM orders o JOIN customers c ON c.id = o.customer_id
  WHERE o.placed_at > now() - interval '1 day'`);

// Or two round trips: fetch all needed customers at once with an array parameter
const ids = [...new Set(orders.map(o => o.customer_id))];
const { rows: customers } = await pool.query(
  "SELECT id, name FROM customers WHERE id = ANY($1::bigint[])", [ids]);

In ORMs, the fix is eager loading (select_related/prefetch_related in Django, joinedload/selectinload in SQLAlchemy, include in Prisma, JOIN FETCH in JPA).

More techniques

Technique	Saves
RETURNING to get generated ids and defaults (RETURNING Clause)	A follow-up SELECT after each write
INSERT ... ON CONFLICT instead of select-then-insert (INSERT ON CONFLICT Upserts)	A round trip and a race condition
Data-modifying CTEs that chain several writes in one statement (Data-Modifying CTEs)	Round trips within a transaction
Pipeline mode (libpq 14+, psycopg 3, pgx, JDBC batching)	Waiting for each reply before sending the next statement
A server-side function for a multi-step operation	All intermediate round trips (at the cost of logic in the database)

Key takeaways

• Round-trip latency, not query execution, often dominates request time; count queries per request.
• Group writes in one transaction to avoid a disk flush per row, then batch them: pipelining, multi-row VALUES, unnest arrays, or COPY.
• The N+1 pattern is fixed with a join, an = ANY($1) array query, or ORM eager loading.
• RETURNING, ON CONFLICT, and data-modifying CTEs remove follow-up queries.

🧪 Practice

1. (Beginner) Enable log_min_duration_statement = 0 for your session's role, run one page of an application, and count how many statements it sends.
2. (Intermediate) Reproduce the benchmark table in your environment, then add tc qdisc ... netem delay 1ms (or run the client on another machine) and repeat. Which methods are affected most by latency?
3. (Interview) "An endpoint takes 800 ms, but every individual query in it takes less than 2 ms according to pg_stat_statements. How do you find the problem?" Hint: Multiply query count by round-trip time; use tracing or the statement log grouped by request to count queries, and look for N+1 patterns.

Query Rewriting for Performance

Sometimes the planner cannot find a fast plan because of how a query is written: a condition hides a column inside a function, a subquery forces repeated work, or the query asks for far more data than needed. Rewriting the query into an equivalent form that the planner handles better can make it faster by orders of magnitude, without any configuration change.

1. Replace repeated subqueries with a join on pre-aggregated data. This query shows, for 1,000 events, how many users live in the event's country. The correlated subquery recounts users for every event row:

EXPLAIN ANALYZE
SELECT c.name,
       (SELECT count(*) FROM users AS u WHERE u.country = c.code) AS users_in_country
FROM events AS e JOIN countries AS c ON c.code = e.country      -- countries: 8 rows (code, name)
WHERE e.id <= 1000;
--  Hash Join (actual time=224.241..7887.944 rows=1000.00 loops=1)
--    ...
--    SubPlan 1
--      ->  Aggregate (actual time=7.657..7.657 rows=1.00 loops=1000)        -- ran 1,000 times
--            ->  Seq Scan on users u (actual ... rows=12500.91 loops=1000)  -- 100,000 rows each time
--  Execution Time: 7913.170 ms

-- Rewritten: count once per country, then join
EXPLAIN ANALYZE
SELECT c.name, uc.n AS users_in_country
FROM events AS e
JOIN countries AS c ON c.code = e.country
JOIN (SELECT country, count(*) AS n FROM users GROUP BY country) AS uc ON uc.country = c.code
WHERE e.id <= 1000;
--  Hash Join (actual time=27.160..27.740 rows=1000.00 loops=1)
--    ...  ->  HashAggregate (actual ... rows=8.00 loops=1)                 -- users scanned once
--  Execution Time: 27.837 ms                                              -- 280x faster

The plan revealed the problem: SubPlan with loops=1000.

2. Make conditions sargable. A condition is sargable (Search ARGument ABLE) when it compares a bare indexed column with a value, so the index can be searched. Wrapping the column in a function or arithmetic hides it from the index:

Not sargable	Sargable rewrite
WHERE date(created_at) = '2025-06-01'	WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02'
WHERE extract(year FROM created_at) = 2025	WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
WHERE amount * 1.21 > 100	WHERE amount > 100 / 1.21
WHERE lower(email) = 'ana@example.com'	Keep it, but add an expression index on lower(email) (Expression Indexes)
WHERE user_id::text = '42'	WHERE user_id = 42 (fix the parameter type in the application)
WHERE coalesce(deleted_at, 'infinity') > now()	WHERE deleted_at IS NULL OR deleted_at > now()

3. Split OR across different columns. WHERE a = 1 OR b = 2 can use a BitmapOr of two indexes, but combined with ORDER BY ... LIMIT or joins it often falls back to a sequential scan. A UNION lets each branch use its best index:

-- messages(id, sender_id, recipient_id, sent_at, body), indexed on (sender_id, sent_at)
-- and on (recipient_id, sent_at); this form may scan and sort far more rows than needed
SELECT * FROM messages WHERE sender_id = 42 OR recipient_id = 42 ORDER BY sent_at DESC LIMIT 20;

-- each branch reads at most 20 rows from its own index; UNION removes rows matched by both
(SELECT * FROM messages WHERE sender_id = 42    ORDER BY sent_at DESC LIMIT 20)
UNION
(SELECT * FROM messages WHERE recipient_id = 42 ORDER BY sent_at DESC LIMIT 20)
ORDER BY sent_at DESC LIMIT 20;

4. Prefer NOT EXISTS to NOT IN with subqueries. NOT IN (SELECT ...) returns no rows at all if the subquery produces a single NULL (NULL Semantics and Three-Valued Logic), and because of that, the planner cannot turn it into an anti-join. At best it becomes a hashed subplan; if the subquery's result does not fit in work_mem, the subquery is searched again for every outer row. NOT EXISTS is correct with NULLs and becomes a Hash Anti Join:

SELECT u.id FROM users AS u
WHERE NOT EXISTS (SELECT 1 FROM events AS e WHERE e.user_id = u.id);   -- Hash Anti Join or Merge Anti Join

5. Do not ask for more than you need

Pattern	Better
SELECT * when three columns are used	Name the columns: enables index-only scans, less de-TOASTing and network transfer
SELECT count(*) ... to check whether any row exists	SELECT EXISTS (SELECT 1 ...): stops at the first match
OFFSET 100000 LIMIT 20 for deep pages	Keyset pagination: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20 (Keyset Pagination)
DISTINCT to remove duplicates created by a join	EXISTS (semi-join) instead of the join
Loading rows to compute in the application	Aggregate in SQL and return the result

6. Help the planner with intermediate results. When a multi-step query is misestimated and statistics cannot fix it (When Estimates Go Wrong), materialize the problematic step into a temporary table, ANALYZE it, and continue from it. The planner then sees real row counts.

Verifying a rewrite. Always confirm that the rewrite returns the same result (for example by comparing both with EXCEPT in both directions on test data), and measure with EXPLAIN (ANALYZE, BUFFERS) on realistic data volumes, not on an empty development database.

Key takeaways

• Look for SubPlan nodes with high loops: correlated subqueries can often become a join on pre-aggregated data.
• Keep indexed columns bare in conditions (sargable); move functions and arithmetic to the other side, or index the expression.
• Use UNION for OR across columns, NOT EXISTS instead of NOT IN, EXISTS instead of count(*) > 0, and keyset pagination instead of deep OFFSET.
• Verify rewrites for identical results and measure them on realistic data.

🧪 Practice

1. (Beginner) Rewrite SELECT count(*) FROM events WHERE date_trunc('day', created_at) = '2025-06-01' so that it can use events_created_at_idx, and compare plans.
2. (Intermediate) Write a query that lists users without any 'purchase' event using NOT IN, then with NOT EXISTS. Compare the plans and execution times, then insert an event with a NULL user_id into a copy of the table and compare the results.
3. (Interview) "A query uses SELECT DISTINCT across five joined tables and takes 20 seconds. Where do you start?" Hint: Ask why duplicates appear at all: which join multiplies rows, and could it be a semi-join (EXISTS) instead? Check rows before and after the DISTINCT node.

Caching Strategies

The fastest query is the one that is not run. When the same expensive result is requested again and again, such as a dashboard total, a product listing, or a leaderboard, storing the result and reusing it can remove most of the load. Caching is a trade-off, though: every cache serves data that may be out of date, and invalidating it correctly is famously hard.

Analogy. A shop keeps a printed price list at the counter rather than asking the warehouse system for every customer. It must remember to reprint the list whenever prices change.

Layers of caching

Layer	What is cached	Freshness	Effort
Shared buffers and OS page cache	Data pages	Always current	None (sizing only)
pg_prewarm	Loads chosen tables into cache after a restart	Current	Low
Materialized view	Result of a query, stored as a table	As of the last REFRESH	Low
Summary table maintained by triggers or jobs	Aggregates, updated incrementally	Near real time	Medium
Application cache (in-process, Redis, Memcached)	Query results or rendered objects	Until expiry or invalidation	Medium to high
HTTP or CDN cache	Whole responses	Until expiry	Low to medium

Warming the database cache. After a restart, shared buffers are empty and the first queries read from disk. pg_prewarm loads relations explicitly, and its autoprewarm worker (enabled when pg_prewarm is in shared_preload_libraries) saves the list of cached blocks periodically and reloads them at startup:

CREATE EXTENSION pg_prewarm;
SELECT pg_prewarm('events');                 -- returns the number of blocks loaded
SELECT pg_prewarm('events_user_id_idx');

Materialized views for expensive aggregates. A materialized view stores a query's result (9.4):

-- Live query: about 64 ms, scans all purchases every time
SELECT country, date_trunc('month', created_at) AS month, sum(amount) AS revenue
FROM events WHERE kind = 'purchase' GROUP BY 1, 2;

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT country, date_trunc('month', created_at) AS month,
       sum(amount) AS revenue, count(*) AS purchases
FROM events WHERE kind = 'purchase'
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON monthly_revenue (country, month);     -- required for CONCURRENTLY

SELECT * FROM monthly_revenue WHERE country = 'DE' ORDER BY month;   -- about 0.6 ms

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;   -- ~84 ms; readers are not blocked

Schedule the refresh at the staleness the business accepts (for example every 5 minutes with pg_cron). A refresh recomputes everything, so for very large aggregates, a summary table updated incrementally (only the changed day or month) is cheaper.

Application-level caching. The cache-aside pattern: check the cache, fall back to the database on a miss, and store the result with an expiry:

import json
import redis
import psycopg

cache = redis.Redis()

def monthly_revenue(conn: psycopg.Connection, country: str) -> list:
    key = f"revenue:{country}"
    hit = cache.get(key)
    if hit is not None:
        return json.loads(hit)                          # served without touching PostgreSQL
    rows = conn.execute(
        "SELECT month::date::text, revenue::float FROM monthly_revenue WHERE country = %s ORDER BY month",
        (country,)).fetchall()
    cache.set(key, json.dumps(rows), ex=300)            # expire after 5 minutes: bounded staleness
    return rows

Invalidation strategies

Strategy	How	Trade-off
Time-based expiry (TTL)	Entry expires after N seconds	Simple; data can be up to N seconds stale
Explicit invalidation	Delete the key when the application writes the underlying data	Fresh, but every write path must remember it
Event-driven	Triggers send NOTIFY or logical decoding streams changes; a listener invalidates keys (9.5)	Catches all writers, including other services; more moving parts
Versioned keys	Include a version or updated_at in the key	No deletes needed; old entries expire naturally

When not to cache. Cache results that are expensive and read much more often than they change. Do not cache to hide a query that a missing index would make fast, data that must be exactly current (account balances during a transfer), or results that are almost never requested twice. A cache also adds failure modes: a cold cache after a restart can send a sudden flood of queries to the database (a cache stampede), which per-key locking or early, staggered refresh prevents.

Key takeaways

• PostgreSQL already caches pages; pg_prewarm restores the cache after restarts.
• Materialized views turn expensive aggregates into cheap indexed lookups, at the staleness of the refresh schedule; REFRESH ... CONCURRENTLY requires a unique index.
• Application caches remove database load entirely but need an invalidation strategy: TTL, explicit, event-driven, or versioned keys.
• Fix slow queries before caching them, and plan for cold caches and stampedes.

🧪 Practice

1. (Beginner) Create monthly_revenue, insert a new purchase into events, and check when the view reflects it.
2. (Intermediate) Replace the materialized view with a daily_revenue table maintained by an AFTER INSERT trigger on events (using INSERT ... ON CONFLICT DO UPDATE). Compare the write overhead with pgbench.
3. (Interview) "Design caching for a product page that receives 5,000 requests per second, where prices change a few times a day and stock levels change every few seconds." Hint: Different data has different freshness needs; consider separating the price and stock parts, their TTLs, event-driven invalidation for prices, and protection against stampedes.

<a id="75-monitoring-and-diagnostics"></a>

7.5 Monitoring and Diagnostics

Tuning is guesswork without measurement. PostgreSQL records detailed statistics about statements, sessions, tables, indexes, I/O, locks, and waits, and exposes them as ordinary views you can query with SQL. This subchapter covers which views and modules answer which question: what is slow, what is running right now, what is waiting and on what, and how fast the database is growing.

pg_stat_statements

Logs and EXPLAIN tell you about one query at a time. To decide where to spend tuning effort, you need the opposite view: which queries, in total, consume the most database time. A query that takes 2 ms but runs 5 million times a day matters more than a 30-second report that runs once. The pg_stat_statements extension keeps running totals for every distinct statement the server executes.

Setup. The module must be loaded at server start, then the extension created in a database to get the views:

-- postgresql.conf (restart required):
--   shared_preload_libraries = 'pg_stat_statements'
--   compute_query_id = auto                 -- default; pg_stat_statements turns it on
CREATE EXTENSION pg_stat_statements;          -- in any database you query it from

Normalization. Statements are grouped by a query identifier, a hash of the statement's parse tree with constants removed. WHERE user_id = 42 and WHERE user_id = 4242 count as the same statement and are shown as WHERE user_id = $1. Statements that differ only in whitespace or in constant values are therefore aggregated together, while a different column list or a different IN list length may create a separate entry (PostgreSQL 18 merges IN lists of constants of different lengths).

Finding the top queries. After running a mixed workload with pgbench on the sample tables:

SELECT round(100 * total_exec_time / sum(total_exec_time) OVER ()) AS pct,  -- share of all DB time
       calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows,
       left(query, 50)                     AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 3;
--  pct | calls | mean_ms | stddev_ms |  rows  |                       query
-- -----+-------+---------+-----------+--------+----------------------------------------------------
--   59 |    60 |  110.06 |      3.47 |     60 | SELECT count(*) FROM events WHERE payload->>$1 = $
--   32 | 74140 |    0.05 |      0.03 | 739718 | SELECT kind, created_at FROM events WHERE user_id
--    9 | 74140 |    0.01 |      0.02 |  74140 | SELECT name FROM users WHERE id = $1

The first row is a slow query that runs rarely; the second a fast query that runs very often. Both are worth looking at: the first needs an index on payload->>'device' (or a different query), the second might benefit from a covering index or caching, since it is 32 % of all database time.

Useful columns

Column	Use
calls, total_exec_time, mean_exec_time, min/max/stddev_exec_time	Where time goes; high stddev suggests plan changes or parameter-dependent behavior
rows	Rows returned or affected in total; rows / calls for result size
total_plan_time	Planning time (with pg_stat_statements.track_planning = on)
shared_blks_hit, shared_blks_read, shared_blks_dirtied	I/O per statement; read high relative to hit means cache misses
temp_blks_written	Spills to disk: candidates for more work_mem or better estimates
wal_bytes	Write volume per statement
jit_*_time	Time spent on JIT compilation
queryid, userid, dbid	Join keys; queryid appears in EXPLAIN VERBOSE and logs (%Q in log_line_prefix)

More questions it answers

-- Statements that spill the most to temporary files
SELECT left(query, 60), calls, temp_blks_written
FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 5;

-- Statements with the worst cache hit ratio (high physical reads)
SELECT left(query, 60), shared_blks_read,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 5;

-- Statements returning many rows per call (possible missing LIMIT or N+1 parent)
SELECT left(query, 60), calls, rows / nullif(calls, 0) AS rows_per_call
FROM pg_stat_statements ORDER BY rows_per_call DESC LIMIT 5;

Working with snapshots. Counters accumulate from the last reset (pg_stat_statements_reset(), or stats_reset in pg_stat_statements_info). To see what happened during a specific hour, store snapshots periodically and subtract, or use a monitoring tool that does it (pganalyze, pgwatch, Datadog, or Prometheus with postgres_exporter). Settings worth knowing:

Setting	Default	Note
pg_stat_statements.max	5000	Distinct statements tracked; least-used entries are evicted when full
pg_stat_statements.track	top	all also tracks statements inside functions
pg_stat_statements.track_planning	off	Records planning time; small overhead
pg_stat_statements.track_utility	on	Includes VACUUM, CREATE INDEX, and other non-DML statements

Key takeaways

• pg_stat_statements aggregates execution statistics per normalized statement; load it via shared_preload_libraries.
• Sort by total_exec_time to find where the database spends its time; both frequent fast queries and rare slow ones show up.
• Buffer, temp, and WAL columns explain why a statement is expensive.
• Counters are cumulative since the last reset; compare snapshots to analyze a time window.

🧪 Practice

1. (Beginner) Install pg_stat_statements, run pgbench -S -T 30, and list the five statements with the highest total_exec_time.
2. (Intermediate) Write a query that shows, for each of the top 10 statements, the average number of shared blocks read per call and the percentage of time spent in planning (enable track_planning first).
3. (Interview) "CPU on the database server has doubled since last week's release. How do you find the cause using only the database?" Hint: Compare pg_stat_statements snapshots from before and after (calls and total time per queryid), and look for new statements or ones whose calls or mean_exec_time changed.

Activity and Progress Views

pg_stat_statements summarizes the past. When something is wrong right now, such as the database is slow, connections are piling up, or a migration seems stuck, you need to see what every session is doing at this moment. pg_stat_activity shows one row per server process, and the pg_stat_progress_* views show how far long-running maintenance commands have come.

pg_stat_activity columns you will use most

Column	Meaning
pid	Server process id: use it with pg_cancel_backend and pg_terminate_backend
usename, datname, application_name, client_addr	Who and where
state	active (running a statement), idle (waiting for the client), idle in transaction (inside an open transaction, doing nothing), idle in transaction (aborted)
backend_start, xact_start, query_start, state_change	When the connection, transaction, and current statement began
wait_event_type, wait_event	What the process is waiting for, if anything (Wait Event Analysis)
query	Current statement, or the last one when idle (truncated to track_activity_query_size)
backend_type	client backend, autovacuum worker, parallel worker, walsender, ...
backend_xid, backend_xmin	Transaction ids this session holds; an old backend_xmin prevents vacuum from cleaning up

Everyday queries

-- What is running now, longest first
SELECT pid, usename, application_name AS app, state,
       now() - query_start AS running_for,
       wait_event_type, wait_event,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

-- Connections by state: how many are actually doing work?
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend' GROUP BY state;

-- Long-open transactions: they hold locks and block vacuum
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE xact_start < now() - interval '5 minutes'
ORDER BY xact_start;

Stopping a session

SELECT pg_cancel_backend(12345);      -- cancel the current statement; the session stays connected
SELECT pg_terminate_backend(12345);   -- end the whole session (rolls back its open transaction)

idle in transaction sessions are the most common hidden problem: an application opened a transaction and forgot to commit, holding locks and the vacuum horizon. idle_in_transaction_session_timeout (for example '5min') ends them automatically.

Progress views. Long maintenance operations report their progress:

View	Command
pg_stat_progress_vacuum	VACUUM and autovacuum
pg_stat_progress_analyze	ANALYZE
pg_stat_progress_create_index	CREATE INDEX, REINDEX
pg_stat_progress_cluster	CLUSTER, VACUUM FULL
pg_stat_progress_copy	COPY
pg_stat_progress_basebackup	pg_basebackup

Polling an index build on events from a second session:

SELECT p.pid, p.phase,
       round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS scan_pct,
       p.tuples_done, p.tuples_total
FROM pg_stat_progress_create_index AS p;
--  pid |             phase              | scan_pct | tuples_done | tuples_total
-- -----+--------------------------------+----------+-------------+--------------
--  393 | building index: scanning table |     36.3 |           0 |            0
--  393 | building index: scanning table |     82.2 |           0 |            0   -- 0.7 s later
--  393 | building index: loading tuples in tree |  |      197033 |      1000000   -- next phase

Each command passes through several phases, so the percentage restarts per phase. Still, it answers the question "is it stuck or making progress?" and, with two samples, gives a rough remaining time.

Key takeaways

• pg_stat_activity shows every server process: who, what statement, which state, since when, and what it waits for.
• idle in transaction sessions and old xact_start values point to application bugs that hold locks and block vacuum.
• pg_cancel_backend stops a statement; pg_terminate_backend ends a session.
• pg_stat_progress_* views show phase and progress of vacuum, analyze, index builds, cluster, copy, and base backups.

🧪 Practice

1. (Beginner) Open three psql sessions: one idle, one with BEGIN; and nothing else, and one running SELECT pg_sleep(60). Identify each in pg_stat_activity by its state.
2. (Intermediate) Run VACUUM (VERBOSE) events after updating 20 % of its rows, and poll pg_stat_progress_vacuum every half second. List the phases you observe.
3. (Interview) "The application reports connection errors: too many clients already. What do you look at in the database, in what order?" Hint: Group pg_stat_activity by state, application_name, and client_addr; look for idle and idle in transaction connections, connection leaks, and whether a pooler is in use.

Table and Index Statistics Views

Besides per-statement statistics, PostgreSQL counts activity per table and per index: how often each is scanned, how many rows are inserted, updated, and deleted, how many dead rows wait for vacuum, and how many blocks were read from disk versus found in cache. These counters answer design questions: which tables are read by sequential scans, which indexes are never used, which tables need vacuum tuning.

The main views

View	One row per	Highlights
pg_stat_user_tables	Table	seq_scan, seq_tup_read, idx_scan, n_tup_ins/upd/del, n_tup_hot_upd, n_live_tup, n_dead_tup, last (auto)vacuum and (auto)analyze
pg_stat_user_indexes	Index	idx_scan, idx_tup_read, idx_tup_fetch, last_idx_scan (16+)
pg_statio_user_tables	Table	heap_blks_read/hit, idx_blks_read/hit, TOAST blocks
pg_statio_user_indexes	Index	idx_blks_read/hit
pg_stat_io (16+)	Backend type x object x context	Reads, writes, extends, hits, evictions, fsyncs, and their timings
pg_stat_database	Database	Commits, rollbacks, blocks read/hit, temp files, deadlocks, conflicts

The _user_ views exclude system catalogs; pg_stat_all_tables includes them.

Tables: scans and churn

SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup,
       n_tup_upd, n_tup_hot_upd, last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('events', 'users');
--  relname | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup | n_tup_upd | n_tup_hot_upd | last_autovacuum
-- ---------+----------+--------------+----------+------------+------------+-----------+---------------+-----------------
--  events  |      235 |     88000000 |    75179 |    1000000 |          1 |         1 |             0 |
--  users   |     3021 |    301900001 |    74856 |     100000 |          0 |         0 |             0 |

Things to look for:

• Many sequential scans reading many rows (seq_tup_read / seq_scan close to the table size) on a large table: some frequent query lacks an index. Here, users was scanned 3,021 times, mostly by the correlated subquery from Query Rewriting for Performance.
• High n_dead_tup relative to n_live_tup, or an old last_autovacuum on a busy table: autovacuum is not keeping up (8.5).
• Low n_tup_hot_upd / n_tup_upd on an update-heavy table: updates are not HOT, so every update also writes every index (HOT Updates and Fillfactor).

Indexes: usage

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname IN ('events', 'users')
ORDER BY idx_scan;
--  relname |     indexrelname      | idx_scan | idx_tup_read | idx_tup_fetch |  size
-- ---------+-----------------------+----------+--------------+---------------+---------
--  events  | events_created_at_idx |       15 |         7052 |          7047 | 21 MB
--  events  | events_pkey           |       27 |        39116 |         39104 | 21 MB
--  users   | users_pkey            |    74856 |        75048 |         74791 | 2208 kB
--  events  | events_user_id_idx    |    75137 |      3822512 |           205 | 9080 kB

idx_tup_fetch much lower than idx_tup_read for events_user_id_idx shows that most of its scans were index-only scans, which read index entries without fetching heap rows. Indexes with idx_scan = 0 over a long period are candidates for removal (Identifying Unused Indexes).

I/O: cache hits per table

SELECT relname, heap_blks_read, heap_blks_hit,
       round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS hit_pct
FROM pg_statio_user_tables WHERE relname IN ('events', 'users');
--  relname | heap_blks_read | heap_blks_hit | hit_pct
-- ---------+----------------+---------------+---------
--  events  |          18648 |       2792309 |   99.34
--  users   |            843 |       2721883 |   99.97

pg_stat_io: I/O by who and why. Since PostgreSQL 16, pg_stat_io breaks I/O down by backend type (client backends, autovacuum, checkpointer, background writer) and context (normal, vacuum, bulkread, bulkwrite). A high number of writes by client backend in context normal means queries are writing out dirty buffers themselves, because the background writer and checkpointer are not keeping up:

SELECT backend_type, context, reads, writes, hits, evictions
FROM pg_stat_io
WHERE object = 'relation' AND (reads > 0 OR writes > 0)
ORDER BY reads DESC;

Resetting. Counters accumulate since the last reset or since the statistics were lost (crash, pg_upgrade). Check stats_reset in pg_stat_database before drawing conclusions such as "this index is unused"; pg_stat_reset() resets the current database's counters.

Key takeaways

• pg_stat_user_tables shows scans, row churn, dead rows, and vacuum/analyze times per table.
• pg_stat_user_indexes shows how often each index is used; idx_scan = 0 over a long period means it only costs writes.
• pg_statio_* and pg_stat_io separate cache hits from reads and show who performs I/O.
• Counters are cumulative since stats_reset; interpret them over a known time window.

🧪 Practice

1. (Beginner) List the five tables with the most sequential scans and the average number of rows each scan read.
2. (Intermediate) Update 30 % of events rows, then watch n_dead_tup and last_autovacuum in pg_stat_user_tables until autovacuum processes the table. How long did it take, and why?
3. (Interview) "A table has 12 indexes and writes are slow. How do you decide which indexes to drop?" Hint: Use idx_scan over a long enough window (including month-end jobs), check stats_reset, check replicas separately (their counters are independent), and consider which indexes enforce constraints.

Slow Query Logging

pg_stat_statements shows aggregates, but not individual executions: which parameter values made a query slow, at what time, from which application. The server log can record each statement that exceeds a time threshold, with its duration, parameters, and context. It is the simplest and most widely used way to catch slow queries in production.

The core setting

ALTER SYSTEM SET log_min_duration_statement = '100ms';   -- log every statement taking 100 ms or more
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d app=%a ';
SELECT pg_reload_conf();                                  -- no restart needed

What the log contains

2026-10-05 12:27:34.636 UTC [464] postgres@shop app=psql LOG:  duration: 112.497 ms  statement: SELECT count(*) FROM events WHERE payload->>'device' = 'ios'
2026-10-05 12:27:42.246 UTC [490] postgres@shop app=pgbench LOG:  duration: 121.475 ms  execute <unnamed>: SELECT count(*), pg_sleep(0.12) FROM events WHERE user_id = $1;
2026-10-05 12:27:42.246 UTC [490] postgres@shop app=pgbench DETAIL:  Parameters: $1 = '4242'
2026-10-05 12:27:35.015 UTC [475] postgres@shop app=psql LOG:  duration: 151.056 ms  statement: EXECUTE q(42);
2026-10-05 12:27:35.015 UTC [475] postgres@shop app=psql DETAIL:  prepare: PREPARE q(int) AS SELECT count(*), pg_sleep(0.15) FROM events WHERE user_id = $1;

For prepared statements sent with the extended protocol, the parameter values are logged in DETAIL, so you can reproduce the exact slow execution.

Useful log_line_prefix escapes

Escape	Meaning
%m	Timestamp with milliseconds
%p	Process id (match with pg_stat_activity.pid)
%u, %d	User and database
%a	application_name: set it in every application's connection string
%h	Client host
%x	Transaction id
%Q	Query identifier (matches pg_stat_statements.queryid)
%q	Stop here for non-session processes (keeps background process lines short)

Related logging settings

Setting	Purpose
log_min_duration_sample + log_statement_sample_rate	Log only a fraction of statements above a lower threshold; for example, 10 % of statements above 20 ms, while all above 500 ms are logged
log_transaction_sample_rate	Log all statements of a random fraction of transactions, to see full transaction behavior
log_parameter_max_length	Truncate logged parameter values (privacy and log size)
log_lock_waits	Log when a statement waits for a lock longer than deadlock_timeout (Detecting Lock Contention)
log_temp_files	Log temporary files above a size (sort and hash spills)
log_autovacuum_min_duration	Log autovacuum runs that take longer than a threshold
log_checkpoints	Log each checkpoint with timing and volume (on by default since 15)
log_statement = 'ddl'	Log all schema changes regardless of duration

Choosing the threshold. Too low, and the log volume itself slows the server and fills disks; too high, and you miss problems. A common approach: start at 500 ms to 1 s for OLTP, lower it gradually while watching log volume, and use sampling for the range below. Set it per database or role to target specific workloads, for example ALTER ROLE reporting SET log_min_duration_statement = '10s'.

Analyzing the log. Reading raw logs works for single incidents. For trends, pgBadger parses PostgreSQL logs into an HTML report of the slowest and most frequent queries, lock waits, temp files, checkpoints, and connections per hour. It works best with log_line_prefix containing %m [%p] and with lc_messages = 'C' so messages are in English. Logging in jsonlog format (log_destination = 'jsonlog', PostgreSQL 15+) makes logs easy to ship to log platforms.

Key takeaways

• log_min_duration_statement logs every statement above a threshold, with duration and bound parameters.
• A good log_line_prefix (%m [%p] %u@%d app=%a, plus %Q) makes log lines traceable to sessions, applications, and pg_stat_statements.
• Sampling settings capture a representative share of moderately slow queries without flooding the log.
• Related settings log lock waits, temp files, autovacuum, and checkpoints; pgBadger turns logs into reports.

🧪 Practice

1. (Beginner) Set log_min_duration_statement = 0 in your session (as a superuser), run three queries, and find them in the server log.
2. (Intermediate) Configure logging so that all statements above 1 s and 5 % of statements above 50 ms are logged. Verify with SELECT pg_sleep(...) at different durations.
3. (Interview) "Why not just set log_statement = 'all' in production to capture everything?" Hint: Estimate the log volume at 5,000 statements per second, the I/O and CPU it costs, and the sensitive data (parameters) it would write to disk. Which tools give the same insight more cheaply?

auto_explain Module

A slow query in the log tells you that it was slow, not why. By the time you run EXPLAIN ANALYZE yourself, the data, the cache, or the parameter values may differ, and the problem may not reproduce. The auto_explain module solves this by logging the execution plan of every statement that exceeds a threshold, at the moment it runs.

Enabling it

-- For all sessions: postgresql.conf, then restart
--   shared_preload_libraries = 'pg_stat_statements,auto_explain'

-- Or for one session, without a restart (superuser):
LOAD 'auto_explain';

ALTER SYSTEM SET auto_explain.log_min_duration = '200ms';   -- plans of statements slower than this
ALTER SYSTEM SET auto_explain.log_analyze = on;             -- actual rows and loops, like EXPLAIN ANALYZE
ALTER SYSTEM SET auto_explain.log_buffers = on;             -- buffer counts (needs log_analyze)
ALTER SYSTEM SET auto_explain.log_timing = off;             -- skip per-node timing: much less overhead
SELECT pg_reload_conf();

What it logs

2026-10-05 12:27:53.217 UTC [506] postgres@shop app=psql LOG:  duration: 436.566 ms  plan:
	Query Text: SELECT u.plan, count(*), pg_sleep(0.1) FROM events e JOIN users u ON u.id = e.user_id WHERE e.created_at < '2025-04-01' GROUP BY u.plan
	Finalize GroupAggregate  (cost=13567.90..13568.79 rows=3 width=16) (actual rows=3.00 loops=1)
	  Group Key: u.plan
	  Buffers: shared hit=6698
	  ->  Gather Merge  (cost=13567.90..13568.72 rows=7 width=12) (actual rows=9.00 loops=1)
	        Workers Planned: 2
	        Workers Launched: 2
	        ->  Sort  (...) (actual rows=3.00 loops=3)
	              ->  Partial HashAggregate  (...) (actual rows=3.00 loops=3)
	                    ->  Hash Join  (cost=3092.43..12024.19 rows=108727 width=4) (actual rows=86399.67 loops=3)
	                          Hash Cond: (e.user_id = u.id)
	                          ->  Parallel Index Scan using events_created_at_idx on events e  (...)
	                          ->  Hash  (...) (actual rows=100000.00 loops=3)
	                                ->  Seq Scan on users u  (...)

This is the plan exactly as it ran, with the real row counts, from production, without anyone running anything by hand.

Settings

Setting	Default	Notes
auto_explain.log_min_duration	-1 (off)	Threshold; 0 logs every plan (only for debugging)
auto_explain.log_analyze	off	Collects actual rows for every statement, not only slow ones, because PostgreSQL cannot know in advance which will be slow
auto_explain.log_timing	on	Per-node timing when log_analyze is on; turn it off to limit overhead
auto_explain.log_buffers	off	Buffer usage
auto_explain.log_format	text	json for machine processing
auto_explain.log_nested_statements	off	Also log statements executed inside functions
auto_explain.log_parameter_max_length	-1	Logs bound parameter values (PostgreSQL 16+)
auto_explain.sample_rate	1	Instrument only a fraction of statements, to reduce overhead
auto_explain.log_settings	off	Include non-default planner settings

Overhead. Without log_analyze, auto_explain only prints the plan of slow statements, and the cost is negligible. With log_analyze, every statement is instrumented. Row counting is cheap, but per-node timing (log_timing = on) can add significant overhead on systems with slow clocks. A common production configuration is log_analyze = on, log_timing = off, log_buffers = on, and a threshold of a few hundred milliseconds to a few seconds, possibly with sample_rate below 1 on very busy systems.

Nested statements. Slow statements inside PL/pgSQL functions are invisible in the normal log, which only shows the outer SELECT my_function(). With auto_explain.log_nested_statements = on, each inner statement's plan is logged separately.

Key takeaways

• auto_explain logs the actual execution plan of statements slower than log_min_duration, capturing problems that do not reproduce later.
• log_analyze adds actual row counts; combine it with log_timing = off to keep overhead low.
• Load it via shared_preload_libraries for all sessions, or LOAD it in one session for debugging.
• log_nested_statements exposes slow statements inside functions.

🧪 Practice

1. (Beginner) In a session, LOAD 'auto_explain', set auto_explain.log_min_duration = 0, run two queries, and find their plans in the server log.
2. (Intermediate) Write a PL/pgSQL function that runs a slow query inside it. Call it with log_nested_statements off and on, and compare what appears in the log.
3. (Interview) "A query is slow in production about once an hour, but always fast when you run it manually. How would you catch the slow plan?" Hint: Combine auto_explain (with log_analyze and a threshold), bound parameter logging, and pg_stat_statements stddev; think about generic plans, cache state, and concurrent load at those times.

Wait Event Analysis

A query is slow either because it is doing work (using CPU) or because it is waiting: for a disk read, for a lock held by another session, for a WAL flush, for the client to read results. Wait events tell you which. Each process continuously reports what, if anything, it is waiting for in pg_stat_activity.wait_event_type and wait_event. Sampling these columns over time shows where the whole system spends its waiting time.

Analogy. Profiling a restaurant's slow service. Are the cooks busy (CPU), waiting for ingredients from the storeroom (I/O), waiting for each other to free the stove (locks), or are the plates ready and waiting for a waiter to pick them up (client)?

Wait event types

wait_event_type	Meaning	Common events	Typical cause
NULL (with state = 'active')	Not waiting: running on CPU		CPU-heavy queries, or many active sessions
IO	Waiting for a read, write, or fsync	DataFileRead, WalSync, WalWrite, BufFileRead	Data not in cache, slow storage, commit flushes, temp file spills
Lock	Waiting for a heavyweight lock	relation, transactionid, tuple, advisory	Blocking between sessions (Detecting Lock Contention)
LWLock	Waiting for an internal lightweight lock	BufferMapping, WALWrite, LockManager, SubtransSLRU	Contention on shared structures under high concurrency
BufferPin	Waiting to get exclusive access to a buffer	BufferPin	Vacuum waiting for a long-held cursor or scan
Client	Waiting for the client	ClientRead, ClientWrite	Application is slow to send the next command or to read results
Activity	Background process idling	WalWriterMain, AutovacuumMain	Normal; ignore
IPC	Waiting for another process	ParallelFinish, SyncRep, BgWorkerStartup	Parallel query coordination, synchronous replication
Timeout	Sleeping on purpose	PgSleep, VacuumDelay	pg_sleep, vacuum cost delay
Extension	Waiting inside an extension		Extension-specific

A snapshot

SELECT coalesce(wait_event_type, 'CPU') AS wait_type,
       coalesce(wait_event, 'running')  AS wait_event,
       count(*)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;
--  wait_type |  wait_event   | count
-- -----------+---------------+-------
--  Lock      | relation      |     1
--  Lock      | transactionid |     1
--  Timeout   | PgSleep       |     1

Sampling over time. A single snapshot is noisy. Sampling every second and aggregating gives a profile, much like a CPU profiler:

CREATE TABLE wait_samples (
  sampled_at      timestamptz NOT NULL DEFAULT now(),
  wait_event_type text,
  wait_event      text,
  queryid         bigint,                       -- pg_stat_activity.query_id (14+)
  sessions        int
);

-- run every second, e.g. from a small loop or a monitoring agent
INSERT INTO wait_samples (wait_event_type, wait_event, queryid, sessions)
SELECT coalesce(wait_event_type, 'CPU'), coalesce(wait_event, 'running'), query_id, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend' AND pid <> pg_backend_pid()
GROUP BY 1, 2, 3;

-- profile of the last 15 minutes: average active sessions per wait event
SELECT wait_event_type, wait_event,
       round(sum(sessions)::numeric / count(DISTINCT sampled_at), 2) AS avg_active_sessions
FROM wait_samples
WHERE sampled_at > now() - interval '15 minutes'
GROUP BY 1, 2
ORDER BY 3 DESC;

The result reads as "on average, 3.2 sessions were waiting on IO: DataFileRead, 1.1 were on CPU". Joining queryid with pg_stat_statements shows which statements cause each kind of wait. The pg_wait_sampling extension does this sampling in-server at high frequency, and managed services offer it as "performance insights" or "query insights" dashboards.

Interpreting the profile

Dominant wait	Next step
CPU	Find top statements by time in pg_stat_statements; check plans; check that active sessions do not exceed cores
IO: DataFileRead	Working set does not fit in cache: more memory, better indexes, fewer rows read
IO: WalSync / WalWrite	Many small commits or slow WAL storage: batch writes, faster disk, consider synchronous_commit trade-offs
Lock: *	Find the blockers (Detecting Lock Contention)
LWLock: LockManager	Many sessions locking many partitions or relations at once; fewer partitions per query, connection pooling
Client: ClientRead while idle in transaction	Application holds transactions open while doing other work

Key takeaways

• Every active process is either on CPU (wait_event is NULL) or waiting on a reported wait event.
• Wait types distinguish I/O, heavyweight locks, internal locks, client delays, and inter-process coordination.
• Sample pg_stat_activity repeatedly and aggregate to build a profile of where time goes; join with pg_stat_statements via query_id.
• The dominant wait type points directly at the kind of fix needed.

🧪 Practice

1. (Beginner) Run pgbench -c 8 -T 30 and, during the run, take five snapshots of wait events with the snapshot query. What dominates?
2. (Intermediate) Build the wait_samples sampler with a shell loop calling psql once per second, run a workload with synchronous_commit = on and then off, and compare the share of WalSync waits.
3. (Interview) "Average active sessions on the database jumped from 4 to 40 during an incident, while CPU usage stayed at 30 %. What does that tell you, and what do you check next?" Hint: If they are not on CPU, they are waiting. Which wait types would you expect, and which views show the blockers or the I/O source?

Detecting Lock Contention

PostgreSQL's MVCC design means readers and writers rarely block each other (8.3). But two transactions updating the same row, or a schema change waiting for exclusive access to a busy table, do wait. A single long transaction can then create a chain of waiting sessions that grows within seconds and looks like a complete outage. Finding the session at the head of the chain is the key skill.

Reproducing a lock queue. Three sessions:

-- Session 1 (application_name 'batch_job'): updates a row and keeps the transaction open
BEGIN;
UPDATE users SET plan = 'pro' WHERE id = 42;
SELECT pg_sleep(8);                                 -- stands in for slow application work

-- Session 2 ('api'): wants the same row -> waits for session 1
UPDATE users SET name = 'Ana' WHERE id = 42;

-- Session 3 ('migration'): needs ACCESS EXCLUSIVE on users -> waits for both
ALTER TABLE users ADD COLUMN note text;

Who blocks whom. pg_blocking_pids(pid) returns the processes a given process is waiting for:

SELECT pid, application_name AS app, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       round(extract(epoch FROM now() - xact_start)::numeric, 1) AS xact_secs,
       left(query, 45) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid() AND backend_type = 'client backend'
ORDER BY xact_start;
--  pid |    app    | state  | wait_event_type |  wait_event   | blocked_by | xact_secs |                    query
-- -----+-----------+--------+-----------------+---------------+------------+-----------+---------------------------------------------
--  523 | batch_job | active | Timeout         | PgSleep       | {}         |       3.6 | SELECT pg_sleep(8)
--  536 | api       | active | Lock            | transactionid | {523}      |       2.5 | UPDATE users SET name = 'Ana' WHERE id = 42
--  537 | migration | active | Lock            | relation      | {523,536}  |       2.5 | ALTER TABLE users ADD COLUMN note text

  523 batch_job (holds row lock on users id=42, transaction open)
    ^                 ^
    |                 |
  536 api           537 migration (needs AccessExclusiveLock on users)
    ^                 |
    +-----------------+   537 also waits for 536, which holds RowExclusiveLock on users

Session 523 is the root blocker: it waits for nothing (blocked_by is empty) but others wait for it.

The lock details. pg_locks shows every lock held (granted = true) or requested (granted = false):

SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted
FROM pg_locks AS l JOIN pg_stat_activity AS a USING (pid)
WHERE a.application_name IN ('batch_job', 'api', 'migration')
  AND l.locktype IN ('relation', 'transactionid', 'tuple')
ORDER BY l.pid, l.granted DESC;
--  pid |   locktype    | relation |        mode         | granted
-- -----+---------------+----------+---------------------+---------
--  523 | transactionid |          | ExclusiveLock       | t        -- its own transaction id
--  523 | relation      | users    | RowExclusiveLock    | t        -- taken by UPDATE
--  536 | tuple         | users    | ExclusiveLock       | t        -- first in line for the row
--  536 | transactionid |          | ShareLock           | f        -- waits for 523's transaction to end
--  537 | relation      | users    | AccessExclusiveLock | f        -- ALTER TABLE waits for the table
--  (rows for indexes and own transaction ids omitted)

Row-level conflicts appear as a wait on the other transaction's id (transactionid, ShareLock), because PostgreSQL stores row locks in the row itself, not in the lock table.

The danger of waiting DDL. Lock requests queue in order. While session 537's ACCESS EXCLUSIVE request waits, every new query on users, even a plain SELECT, queues behind it. A migration that waits for one slow transaction can therefore block all traffic to a table. Protect DDL with a lock timeout and retry:

SET lock_timeout = '3s';                          -- give up instead of queueing indefinitely
ALTER TABLE users ADD COLUMN note text;
-- ERROR:  canceling statement due to lock timeout   -> retry later, with backoff

Logging lock waits. With log_lock_waits = on, any wait longer than deadlock_timeout (default 1 s) is logged with the holders and the queue:

LOG:  process 536 still waiting for ShareLock on transaction 10833 after 1000.282 ms
DETAIL:  Process holding the lock: 523. Wait queue: 536.
CONTEXT:  while updating tuple (0,42) in relation "users"
STATEMENT:  UPDATE users SET name = 'Ana' WHERE id = 42
LOG:  process 537 still waiting for AccessExclusiveLock on relation 16398 of database 16384 after 1000.498 ms
DETAIL:  Processes holding the lock: 523, 536. Wait queue: 537.

Resolving an incident

1. Find the root blocker: the session that appears in others' pg_blocking_pids but has none itself.
2. Look at its state, xact_start, application_name, and last query. Often it is idle in transaction.
3. Decide: wait, pg_cancel_backend (stops the current statement; may not release locks if the transaction stays open), or pg_terminate_backend (ends the session and rolls back, releasing all its locks).
4. Fix the cause afterward: short transactions, lock_timeout on DDL, idle_in_transaction_session_timeout, consistent lock ordering to avoid deadlocks (8.4).

Key takeaways

• pg_blocking_pids(pid) gives the blockers of each session; the root blocker waits for nothing but blocks others.
• pg_locks shows granted and requested locks; row conflicts appear as waits on a transactionid.
• A waiting ACCESS EXCLUSIVE request blocks all later queries on the table; always set lock_timeout for DDL.
• log_lock_waits records lock waits longer than deadlock_timeout, with holders and queue, for after-the-fact analysis.

🧪 Practice

1. (Beginner) Reproduce the two-session row conflict (sessions 1 and 2) and identify the blocker with pg_blocking_pids.
2. (Intermediate) Write a recursive query over pg_blocking_pids that prints the full blocking tree with indentation, starting from root blockers.
3. (Interview) "A deploy ran ALTER TABLE orders ADD COLUMN ..., which normally takes milliseconds, and the site went down for two minutes. Explain what happened and how to prevent it." Hint: What did the ALTER wait for, and what happened to every query that arrived while it waited? Consider lock_timeout with retries.

Capacity and Growth Monitoring

Performance problems are often capacity problems in disguise: a table that doubled in size no longer fits in memory, a disk fills up and the server stops accepting writes, connections reach max_connections, or transaction IDs approach wraparound. These develop over weeks and are easy to predict, if someone is measuring. Capacity monitoring tracks a few numbers over time and alerts long before a limit is reached.

What to track

Resource	How to measure	Danger sign
Database size	pg_database_size(datname)	Growth rate predicts disk full
Largest tables and indexes	pg_total_relation_size, pg_relation_size, pg_indexes_size	Indexes larger than the table; unexpected growth (bloat)
Disk free space	Operating system or cloud metrics	Below 20 to 30 %; WAL and temp files need headroom
Connections	count(*) in pg_stat_activity vs. max_connections	Above 80 %
Transaction ID age	age(datfrozenxid), age(relfrozenxid)	Above ~500 million: autovacuum is falling behind (8.5)
WAL generation rate	pg_stat_wal.wal_bytes, pg_current_wal_lsn() differences	Spikes; replication and backup capacity
Replication lag	pg_stat_replication (12.1)	Growing lag; retained WAL filling the disk
Cache hit ratio, temp bytes	pg_stat_database	Hit ratio dropping as data outgrows memory

Sizes

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;
--  datname  |  size
-- -----------+---------
--  shop      | 150 MB
--  template1 | 7750 kB

SELECT c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,     -- heap + indexes + TOAST
       pg_size_pretty(pg_relation_size(c.oid))       AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))        AS indexes
FROM pg_class AS c JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 'p') AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 5;
--      relname     |  total  |    heap    | indexes
-- -----------------+---------+------------+---------
--  events          | 134 MB  | 82 MB      | 52 MB
--  users           | 8984 kB | 6736 kB    | 2208 kB

Connections and transaction ID age

SELECT count(*) AS connections,
       current_setting('max_connections')::int AS max,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct
FROM pg_stat_activity WHERE backend_type = 'client backend';

SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_to_wraparound
FROM pg_database ORDER BY 2 DESC;

Tracking growth over time. Current sizes are only half the picture; the trend tells you when you run out. Record sizes daily:

CREATE TABLE size_history (
  captured_on date   NOT NULL DEFAULT current_date,
  relname     text   NOT NULL,
  total_bytes bigint NOT NULL,
  PRIMARY KEY (captured_on, relname)
);

-- daily job (pg_cron: SELECT cron.schedule('size-history', '0 1 * * *', $$ ... $$))
INSERT INTO size_history (relname, total_bytes)
SELECT c.relname, pg_total_relation_size(c.oid)
FROM pg_class AS c JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 'p') AND n.nspname = 'public'
ON CONFLICT DO NOTHING;

-- growth per table over the last 30 days, and days until the volume is full
SELECT relname,
       pg_size_pretty(max(total_bytes) - min(total_bytes)) AS growth_30d,
       pg_size_pretty((max(total_bytes) - min(total_bytes)) / 30) AS per_day
FROM size_history
WHERE captured_on > current_date - 30
GROUP BY relname
ORDER BY max(total_bytes) - min(total_bytes) DESC;

 days until full  =  free disk space  /  average daily growth (database + WAL archive + backups)
 e.g. 400 GB free / 6 GB per day  ~  66 days  ->  plan storage expansion or data retention now

WAL rate. WAL volume drives replication bandwidth, archive storage, and backup size. Measure it by sampling the WAL position:

SELECT pg_current_wal_lsn();                         -- e.g. 0/15A14E68; sample again an hour later
SELECT pg_size_pretty(pg_wal_lsn_diff('0/1A3B0000', '0/15A14E68')) AS wal_in_interval;
SELECT pg_size_pretty(wal_bytes) AS wal_since_reset, stats_reset FROM pg_stat_wal;

Alerting. A small set of alerts covers most capacity incidents: disk usage above 80 %, connections above 80 % of max_connections, transaction ID age above 1 billion, replication lag above a threshold, and a sudden change in daily growth (often a runaway job or bloat). Tools such as Prometheus with postgres_exporter, pgwatch, or a cloud provider's monitoring collect these metrics and evaluate the alerts.

Key takeaways

• Track database and relation sizes, disk space, connections, transaction ID age, WAL rate, and replication lag over time.
• pg_total_relation_size includes indexes and TOAST; compare heap and index sizes to spot bloat.
• Store size snapshots daily to compute growth rates and predict when disks fill.
• Alert well before limits: disk and connections at 80 %, XID age at 1 billion, unexpected growth spikes.

🧪 Practice

1. (Beginner) List the ten largest relations in your database with heap, index, and total size, and compute each table's index-to-heap ratio.
2. (Intermediate) Create size_history, insert artificial snapshots for 30 days with a growth of 2 GB per day, and write a query that predicts the date a 500 GB disk will be full.
3. (Interview) "Design a monitoring and alerting setup for a production PostgreSQL cluster with one primary and two replicas." Hint: Cover the metrics in this topic, the views they come from, which alerts page someone at night versus create a ticket, and how you would avoid alert fatigue.

<a id="8-transactions-and-concurrency"></a>