Postgres in depth · Chapter 51 of 65

When the planner guesses wrong

By the end of this chapter you can read the statistics the planner uses, redo its row estimates by hand, spot a bad estimate in EXPLAIN ANALYZE, and fix it with ANALYZE and CREATE STATISTICS. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Inside Postgres
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

After a bulk import of download events, Arjun's per-student report went from 6 ms to 74 ms. Nothing else changed: same query, same indexes. Priya opens the plan and points at one number: rows=1, where 20,000 rows came back. Chapter 50 showed that the planner picks the cheapest plan by its estimates. When an estimate is wrong, the "cheapest" plan is the wrong plan. This chapter is about where estimates come from, and how they go wrong.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    stats.sql: what ANALYZE knows about event_type

    The Check builds lesson_events exactly as the lesson does (30,000 events, with each lesson's section and course) and analyzes it. Return the statistics Postgres keeps for its event_type column, as the statistics view shows them: the fraction of NULLs, the number of distinct values, the most common values and their frequencies.

    Return four columns: null_frac, n_distinct, most_common_vals and most_common_freqs.

  2. Task 2

    estimate.sql: the planner's arithmetic

    For a condition event_type = 'complete', the planner multiplies the table's row count (reltuples in pg_class) by that value's frequency in the most-common-values list. Return one row: that frequency, reltuples, their product rounded to a whole number, and the real number of complete events.

    Return four columns: frequency, reltuples, estimated_rows and actual_rows.

    Careful: the values and the frequencies are two arrays in step; the values array has a pseudo-type, so cast it through text to a text array.

  3. Task 3

    expression.sql: statistics on an expression

    Sana's support tool finds events by the lower-case form of event_type (the query is in the starter file). Postgres has no statistics on that expression, so it guesses 150 rows where there are 300.

    Give the planner statistics on that expression and refresh them. Afterwards EXPLAIN of that query must estimate within 10% of the real count.

    Careful: declaring statistics collects nothing; they are gathered when the table is analyzed.

  4. Task 4 · interview question

    Interview: dependencies.sql, columns that depend on each other

    "Why does the planner estimate 4 rows when there are 112?" The query (in the starter file) asks for the events of lesson 42 within its own course. Every lesson belongs to one course, so the course condition removes nothing once the lesson condition holds, but the planner multiplies their selectivities as if the columns were independent.

    Teach the planner that lesson_id determines course_id, and refresh the statistics. Afterwards the estimate for that query must be within a factor of 2 of the real count.

    Careful: there are several kinds of extended statistics; pick the one about one column determining another.

What you will learn

  • ANALYZE and pg_stats: the sample, null fractions, distinct counts, most common values, histograms, correlation.
  • How an estimate is made: row count times selectivity.
  • Stale statistics and autovacuum's analyze.
  • The independence assumption, and extended statistics with CREATE STATISTICS.

More in Inside Postgres

Build "When the planner guesses wrong" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.