How Your Query Actually Runs

A SQL query tells PostgreSQL what answer you want, not the route it must take. Between your text and the grid, PostgreSQL parses, rewrites, plans, and executes the statement — and the planning step is where the optimizer earns its name.

This lesson opens that room. You will not become a performance expert by memorizing one magic plan shape; you become useful by understanding why the same query can deserve different paths as the data changes.

From text to work: parse → rewrite → plan → execute

The journey from 1.4 had three big beats: parse, plan, execute. Here is the fuller version PostgreSQL uses:

• Parse — read the SQL text, check grammar, resolve names and types, and turn the statement into a tree the engine can reason about.
• Rewrite — expand query rules, most visibly views from 11.1. If you query a view, PostgreSQL substitutes the view's saved query before planning.
• Plan — choose a concrete strategy: which table to read first, whether to use an index, how to join, where to sort, and how rows should flow.
• Execute — run that chosen plan and send the result rows back to the client.

The planner is the optimizer. It is not changing your answer; it is choosing the work needed to produce that answer.

The planner is cost-based

PostgreSQL's planner does not use folk rules like "always use an index" or "joins are slow." It assigns estimated costs to possible plans. Those costs are internal units, not milliseconds, built from statistics: table size, distinct values, common values, value ranges, and how selective a condition looks.

Selectivity means "what fraction of rows should survive this condition?" In our performance table, one customer is tiny, while a broad customer range is most of the table:

SELECT count(*) AS total_orders,
       count(*) FILTER (WHERE customer_id = 42) AS customer_42,
       count(*) FILTER (WHERE customer_id <= 900) AS first_900,
       round(100.0 * count(*) FILTER (WHERE customer_id <= 900) / count(*), 1) AS pct_first_900
FROM big_orders;

 total_orders | customer_42 | first_900 | pct_first_900
--------------+-------------+-----------+---------------
       100000 |         100 |     90000 |          90.0
(1 row)

PostgreSQL keeps statistics so it can make educated guesses before reading every row. Here is one tiny slice of those stats: after ANALYZE, PostgreSQL knows customer_id has 1,000 distinct values in this seed.

SELECT attname, n_distinct
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'big_orders'
  AND attname = 'customer_id';

   attname   | n_distinct
-------------+------------
 customer_id |       1000
(1 row)

That statistic is not the plan. It is one ingredient in the planner's guess about how many rows will flow through a plan node.

Sequential scan vs index scan: neither is always right

The table has an index on customer_id. A narrow predicate uses it:

EXPLAIN (COSTS OFF)
SELECT id, customer_id, amount
FROM big_orders
WHERE customer_id = 42;

                     QUERY PLAN
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on big_orders_customer_idx
         Index Cond: (customer_id = 42)
(4 rows)

Do not worry yet about every line of that tree; 16.3 teaches the reading technique. For now, notice the important shape: PostgreSQL used the customer index because the condition keeps only 100 rows out of 100,000.

Now keep 90% of the table through the same indexed column:

EXPLAIN (COSTS OFF)
SELECT id, customer_id, amount
FROM big_orders
WHERE customer_id <= 900;

           QUERY PLAN
--------------------------------
 Seq Scan on big_orders
   Filter: (customer_id <= 900)
(2 rows)

That is not PostgreSQL "forgetting" the index. It is the optimizer deciding that walking the table once is cheaper than using the index to find almost every row and then visiting the table pages anyway.

This is the beginner performance lesson worth keeping: sequential scan vs index scan — neither is always right. A sequential scan over a small table, or a query that keeps most rows, can be the honest plan. An index-assisted scan shines when it lets the engine skip most of the table.

Why fast today can be slow next month

Plans depend on data shape. If customer 42 has 100 orders today and a huge share of the table next month, the same SQL may cross from "use the index" to "read the table." The query did not become morally worse; the data distribution changed.

Statistics can drift too. PostgreSQL refreshes them with ANALYZE and usually keeps them fresh through autovacuum, but after a bulk load or a major cleanup, stale stats can make the planner guess from yesterday's table. Bad guesses lead to bad plan choices: the engine may prepare for a handful of rows and receive a flood, or expect a flood and miss a narrow index opportunity.

The browser lab can show real plan structure, but it is not a production benchmark. Read plans for shape, row estimates, and whether the chosen route matches the amount of data flowing through it. Leave milliseconds for the real system, measured with the real workload.

Next lesson opens the index itself: what a B-tree gives the optimizer to work with, and why column order can decide whether an index is useful at all.