The Query Planner | Internals for Interns

In the previous article , we explored how PostgreSQL’s rewriter transforms queries—expanding views, applying security policies, and executing custom rules. By the end of that phase, your query has been fully expanded and secured, ready for execution.

But here’s the million-dollar question: How should PostgreSQL actually execute your query?

Let me show you why this matters. Take this simple query:

SELECT c.first_name, c.last_name, r.rental_date
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE c.active = 1;

PostgreSQL could execute this in dozens of different ways:

Should it scan the entire customer table or use an index on active?
Should it scan rental sequentially or use an index on customer_id?
Should it join using nested loops, hash join, or merge join?
Should it start with customer and find matching rentals, or start with rental and find matching customers?

Each choice can make your query run 10x faster or 100x slower. A naive sequential scan through both tables might take seconds, while a smart index-based nested loop might return instantly. This is where the query planner (also known as the optimizer) comes in—PostgreSQL’s strategic mastermind that figures out the optimal execution strategy.

The planner’s job is fascinating: it must explore potentially millions of possible approaches, estimate their costs, and choose the best strategy—all in milliseconds. It balances I/O costs against CPU costs, considers what data might be cached in memory, and decides whether parallel execution would help. This cost-based optimization represents decades of database research distilled into practical algorithms.

In this article, we’ll explore how PostgreSQL’s planner works its magic: how it estimates costs, uses statistics to understand your data, generates different execution paths, and ultimately selects the fastest approach to answer your query.

Let’s start by understanding the foundation of all planning decisions: cost estimation.

Understanding Costs: How PostgreSQL Measures Work

PostgreSQL doesn’t measure execution time in seconds or milliseconds—it measures it in abstract cost units. These units represent the relative expense of different operations, allowing the planner to compare radically different approaches on equal footing.

The key insight is that execution time correlates with computational work. Reading a page from disk costs more than processing a row in memory. Random disk access costs more than sequential access. The planner’s cost model captures these relationships in a unified framework.

So what are these cost units actually made of? Let’s look at the specific parameters that drive every planning decision.

The Building Blocks of Cost

Let’s break down the main cost parameters PostgreSQL uses. The most important categories are I/O costs and CPU costs.

I/O costs are about reading data from disk:

seq_page_cost (default: 1.0) is the baseline—the cost of reading one page sequentially. Everything else is measured relative to this.

random_page_cost (default: 4.0) is the cost of reading a page randomly. Why 4x higher? Because on traditional hard drives, random access really is that much more expensive—the disk head has to physically move around. On SSDs, random access is nearly as fast as sequential, so you’d set this closer to 1.1 instead.

This ratio is huge for planning decisions. High ratio? PostgreSQL prefers sequential scans. Low ratio? Index scans become way more attractive.

CPU costs are much simpler because CPU is fast:

cpu_tuple_cost (default: 0.01) is the cost to process one row.

cpu_index_tuple_cost (default: 0.005) is the cost to process one index entry. It’s cheaper because index entries are smaller.

cpu_operator_cost (default: 0.0025) is the cost to execute one operator or function call.

Notice CPU costs are significantly lower than I/O costs. But remember—these costs are per row. When you’re processing millions of rows, CPU costs add up quickly and can have a significant impact on the total cost.

Startup vs. Total Cost

PostgreSQL calculates two costs for each operation:

Startup Cost: The work required before producing the first result row. For a sequential scan, startup is near zero—PostgreSQL can immediately start returning rows. For a sort operation, startup includes sorting all the data before returning anything.

Total Cost: Startup plus the work to produce all remaining rows.

Let’s see a concrete example using the pagila database :

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM film WHERE rental_duration = 5;

-- QUERY PLAN
-- Seq Scan on film  (cost=0.00..76.50 rows=191 width=390) (actual time=0.013..0.252 rows=191 loops=1)
--   Filter: (rental_duration = 5)
--   Rows Removed by Filter: 809
--   Buffers: shared hit=64
-- Planning Time: 0.063 ms
-- Execution Time: 0.274 ms

This plan shows cost=0.00..76.50:

Startup cost: 0.00 (can start returning rows immediately)
Total cost: 76.50 (reading all pages + processing all rows + applying the filter)

Notice the Buffers: shared hit=64 line in the output? That’s PostgreSQL telling us it accessed 64 pages from the shared buffer cache to complete this query. Now we can see exactly how the planner estimated that 76.50 cost:

Page read cost: 1.0 × 64 pages = 64.0
Processing cost: 0.01 × 1000 rows = 10.0
Filter evaluation cost: 0.0025 × 1000 rows = 2.5
Total: 76.5

Those 64 pages drove the bulk of our query cost—page I/O is expensive compared to CPU operations.

This is why understanding cost parameters matters—they’re the building blocks of every planning decision.

Want to see the code? The cost calculations are in src/backend/optimizer/path/costsize.c .

With cost fundamentals in place, let’s explore how PostgreSQL gathers the data knowledge needed to make these cost calculations meaningful.

Statistics: Understanding Your Data

Cost formulas are useless without accurate data knowledge. How many rows will match that WHERE clause? How many distinct values exist in that column? What’s the most common value? These questions require statistics.

PostgreSQL’s ANALYZE command samples your tables to build statistical summaries. These statistics guide every planning decision, making the difference between a brilliant plan and a disaster.

What PostgreSQL Tracks

For every column, PostgreSQL maintains several statistical measures:

Row Counts: The total number of live rows in each table. This is the foundation—without knowing table sizes, the planner can’t estimate anything.

Null Fraction: The percentage of NULL values. When you filter WHERE column IS NOT NULL, this statistic tells the planner how selective that filter is.

Most Common Values (MCVs): The most frequently occurring values and their frequencies. PostgreSQL stores up to 100 of these per column.

Here’s why MCVs matter. Consider this table:

-- Most films have rental_duration = 6 (21.2%), others distributed more evenly
SELECT * FROM film WHERE rental_duration = 6;
-- MCV shows 21.2% frequency → likely sequential scan

SELECT * FROM film WHERE rental_duration = 7;
-- MCV shows 19.1% frequency → still moderately selective

The same index, different values, different selectivity—MCVs tell the planner exactly what percentage of rows to expect.

Histograms: For values not captured by MCVs, PostgreSQL creates histograms by dividing the remaining values into groups, where each group contains approximately the same number of rows. This helps the planner estimate how many rows fall within a range:

SELECT * FROM payment WHERE amount BETWEEN 5.00 AND 10.00;

The planner uses the histogram to estimate what fraction of rows fall in that payment amount range, which determines whether an index scan is worthwhile.

Correlation: This measures whether column values correlate with physical storage order. High correlation means consecutive pages contain similar values. Low correlation means values are scattered randomly.

Correlation dramatically affects index scan costs. In the pagila database, rental_date has a correlation of 0.95 (very high)—rentals are inserted chronologically, so consecutive pages contain similar dates. An index scan by date range reads consecutive pages—fast! But customer_id has a correlation of only 0.0025 (essentially random)—the same index scan requires random page access across the entire table—expensive.

Now that we understand what statistics PostgreSQL tracks, let’s see how to actually view them.

Viewing Your Statistics

PostgreSQL stores all this data in system catalogs you can query directly:

-- Basic table statistics
SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'film';

-- Result:
-- schemaname | relname | n_live_tup | n_dead_tup
-- -----------+---------+------------+------------
-- public     | film    |       1000 |          0

-- Column statistics
SELECT tablename, attname, n_distinct, null_frac,
       most_common_vals, most_common_freqs,
       correlation
FROM pg_stats
WHERE tablename = 'film' AND attname = 'rental_duration';

-- Result:
-- tablename | attname         | n_distinct | null_frac | most_common_vals  | most_common_freqs                      | correlation
-- ----------+-----------------+------------+-----------+-------------------+----------------------------------------+-------------
-- film      | rental_duration |          5 |         0 | {6,3,4,5,7}       | {0.212,0.203,0.203,0.191,0.191}        |      0.163

This tells the planner that 21.2% of films have rental_duration = 6, 20.3% have 3, 20.3% have 4, 19.1% have 5, and 19.1% have 7. The correlation of 0.163 (low) means these values are scattered randomly across the table pages—no clustering by rental duration.

Statistics quality directly affects plan quality. Stale statistics lead to bad estimates, which lead to bad plans. This is why ANALYZE matters—run it regularly, especially after significant data changes, or enable autovacuum to handle it automatically.

With these building blocks in place—cost parameters to estimate work and statistics to understand data—the planner is ready to evaluate different execution strategies.

How the Planner Evaluates Query Plans

Now that we understand costs and statistics, let’s see how PostgreSQL actually builds and chooses query plans.

The planner works by breaking down your query into pieces and costing each piece separately:

For each table in your query, PostgreSQL evaluates different ways to access it. Should it scan the entire table sequentially? Use an index? Use multiple indexes combined? Each option gets a cost estimate based on the cost parameters and statistics we just learned about.

For joins between tables, PostgreSQL evaluates different join algorithms. Should it use nested loops? Hash join? Merge join? Again, each option gets costed.

The total cost of a query plan is the sum of all these individual costs—every table scan, every join operation, every sort or filter. PostgreSQL generates multiple complete plans this way, calculating the total cost for each one.

The winner is simply the plan with the lowest estimated total cost. That’s the one PostgreSQL will execute.

This bottom-up approach—cost individual operations, combine them into complete plans, pick the cheapest—is how PostgreSQL navigates that enormous space of possibilities. Let’s start by looking at how it evaluates table access options.

Access Paths: Choosing How to Read Tables

Before the planner can figure out how to join multiple tables, it must determine the best way to read each individual table. For every table in your query, PostgreSQL considers several access methods and estimates their costs.

Sequential Scan: The Baseline

The simplest approach is reading the table from start to finish, page by page:

digraph sequential_scan {
    rankdir=LR;
    node [shape=box, style=filled, fillcolor=lightblue];

    subgraph cluster_table {
        label="Table Pages on Disk";
        style=filled;
        fillcolor=lightgray;

        page1 [label="Page 1\nRows 1-100"];
        page2 [label="Page 2\nRows 101-200"];
        page3 [label="Page 3\nRows 201-300"];
        page4 [label="Page 4\nRows 301-400"];

        page1 -> page2 -> page3 -> page4 [style=bold, color=red, label="Sequential Read"];
    }

    scanner [shape=ellipse, fillcolor=yellow, label="Scanner"];
    scanner -> page1 [label="Start"];
}

PostgreSQL always considers sequential scan as an option. It’s predictable, and the cost is straightforward:

Cost = (seq_page_cost × pages) + (cpu_tuple_cost × rows) + filter_costs

Sequential scan is often optimal for:

Small tables (reading everything is fast anyway)
Queries that need most rows (why use an index if you’re reading 80% of the table?)
Tables without useful indexes

EXPLAIN SELECT * FROM film WHERE length > 100;

-- QUERY PLAN
-- Seq Scan on film  (cost=0.00..76.50 rows=609 width=390)
--   Filter: (length > 100)

The planner chose sequential scan because this filter matches 609 out of 1000 rows (61%). Reading the entire table sequentially is faster than random index access for all those matches.

Implementation: nodeSeqscan.c

Index Scan: Precise and Targeted

When you need only a few rows, indexes provide surgical precision. An Index Scan uses an index (typically a B-tree) to find exact row locations, then fetches those rows from the heap pages:

digraph index_scan {
    rankdir=TB;
    node [shape=box, style=filled, fillcolor=lightblue];

    subgraph cluster_index {
        label="B-Tree Index";
        style=filled;
        fillcolor=lightgreen;

        root [label="Root\n10|20|30"];
        leaf1 [label="Leaf\n5|7|9\nTID1|TID2|TID3"];
        leaf2 [label="Leaf\n12|15|18\nTID4|TID5|TID6"];
        leaf3 [label="Leaf\n25|27|29\nTID7|TID8|TID9"];

        root -> leaf1;
        root -> leaf2;
        root -> leaf3;
    }

    subgraph cluster_heap {
        label="Table Heap";
        style=filled;
        fillcolor=lightgray;

        page1 [label="Page 1\nFull Rows"];
        page2 [label="Page 2\nFull Rows"];
        page3 [label="Page 3\nFull Rows"];
    }

    query [shape=ellipse, fillcolor=yellow, label="Query: id = 7"];

    query -> root [label="1. Index Lookup"];
    root -> leaf1 [label="2. Traverse"];
    leaf1 -> page1 [label="3. Heap Fetch\n(TID2)", style=dashed, color=red];
}

The two-step process—index traversal plus heap fetch—is efficient when fetching few rows:

EXPLAIN SELECT * FROM customer WHERE customer_id = 42;

-- QUERY PLAN
-- Index Scan using customer_pkey on customer  (cost=0.28..8.29 rows=1 width=74)
--   Index Cond: (customer_id = 42)

Notice the low cost (8.29) compared to sequential scan. The planner knows this query returns exactly one row from 599 total customers, making index scan dramatically faster.

The catch: if an index scan returns many rows scattered across the table, those heap fetches become expensive random I/O. That’s when sequential scan wins despite having an index available.

But there’s a way to avoid those expensive heap fetches entirely. Index-only scans are a special optimization: if the index contains all the columns you need and the rows haven’t been recently modified, PostgreSQL can skip the heap entirely. For example, SELECT customer_id FROM rental WHERE customer_id = 123 can be satisfied entirely from the index—you’ll see Heap Fetches: 0 in the plan. This is dramatically faster since the index is much smaller than the full table.

Implementation: nodeIndexscan.c , nodeIndexonlyscan.c

Bitmap Scan: Efficient for Moderate Selectivity

Sometimes you need to fetch a moderate number of rows—too many for a simple index scan to be efficient (all those random heap fetches), but too few to warrant a full sequential scan. Or you have multiple conditions, each with its own index. This is where Bitmap Scan shines.

Bitmap scans work with either a single index or multiple indexes combined. The key advantage: they eliminate random I/O by converting scattered index lookups into an organized, sequential heap scan.

digraph bitmap_scan {
    rankdir=TB;
    node [shape=box, style=filled, fillcolor=lightblue];

    subgraph cluster_indexes {
        label="Indexes";
        style=filled;
        fillcolor=lightgreen;

        idx1 [label="rental_duration Index\n'5'\n→ 191 rows"];
        idx2 [label="rating Index\n'PG'\n→ 194 rows"];
    }

    subgraph cluster_bitmap {
        label="Bitmap Operations";
        style=filled;
        fillcolor=lightyellow;

        bitmap1 [label="Bitmap 1\n[1,0,1,0,0,0,1,0,1]"];
        bitmap2 [label="Bitmap 2\n[1,1,0,0,1,0,0,0,1]"];
        combined [label="Combined Bitmap\n[1,0,0,0,0,0,0,0,1]\n(AND operation)"];
    }

    subgraph cluster_heap {
        label="Table Heap";
        style=filled;
        fillcolor=lightgray;

        page1 [label="Page 1\n✓ Read", fillcolor=lightcoral];
        page2 [label="Page 2\nSkip"];
        page3 [label="Page 3\nSkip"];
        page4 [label="Page 4\nSkip"];
        page5 [label="Page 5\nSkip"];
        page6 [label="Page 6\nSkip"];
        page7 [label="Page 7\nSkip"];
        page8 [label="Page 8\nSkip"];
        page9 [label="Page 9\n✓ Read", fillcolor=lightcoral];
    }

    idx1 -> bitmap1;
    idx2 -> bitmap2;
    bitmap1 -> combined;
    bitmap2 -> combined;
    combined -> page1 [label="Guided Scan"];
    combined -> page9;
}

Bitmap scan works in two phases:

Build Phase: Scan indexes and build an in-memory bitmap marking which table pages contain matches
Heap Scan: Read only the marked pages sequentially

Let’s see this in action:

EXPLAIN SELECT * FROM film
WHERE rental_duration = 5 AND rating = 'PG';

-- QUERY PLAN
-- Bitmap Heap Scan on film  (cost=11.46..69.45 rows=37 width=390)
--   Recheck Cond: ((rental_duration = 5) AND (rating = 'PG'::mpaa_rating))
--   ->  BitmapAnd  (cost=11.46..11.46 rows=37 width=0)
--         ->  Bitmap Index Scan on idx_film_rental_duration  (cost=0.00..5.58 rows=191 width=0)
--               Index Cond: (rental_duration = 5)
--         ->  Bitmap Index Scan on idx_film_rating  (cost=0.00..5.61 rows=194 width=0)
--               Index Cond: (rating = 'PG'::mpaa_rating)

The planner combines both indexes with BitmapAnd—one scan finds 191 films with rental_duration = 5, another finds 194 films rated PG. PostgreSQL intersects these bitmaps to identify the 37 films matching both conditions, then scans only the relevant heap pages. This is far more efficient than using one index and filtering, or scanning the entire 1000-film table.

Implementation: nodeBitmapHeapscan.c , nodeBitmapIndexscan.c

Now that we understand table access methods, let’s explore how PostgreSQL joins multiple tables together.

Join Algorithms: Bringing Tables Together

Joining tables is where query execution gets expensive. The planner must choose both which join algorithm to use and what order to join tables. Let’s start with the algorithms.

Nested Loop Join: Simple and Effective

The Nested Loop is conceptually simple—for each row in the outer table, scan the inner table for matches:

FOR each_row IN outer_table (customer):
    FOR each_row IN inner_table (rental):
        IF rows_match_join_condition:
            RETURN combined_row

This looks expensive—and it would be if we scanned the entire inner table for each outer row. But here’s the trick: if the inner table has an index on the join key, PostgreSQL does an efficient index lookup instead:

EXPLAIN SELECT *
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE c.customer_id = 1;

-- QUERY PLAN
-- Nested Loop  (cost=0.28..359.16 rows=32 width=114)
--   ->  Index Scan using customer_pkey on customer c  (cost=0.28..8.29 rows=1 width=74)
--         Index Cond: (customer_id = 1)
--   ->  Seq Scan on rental r  (cost=0.00..350.55 rows=32 width=40)
--         Filter: (customer_id = 1)

The planner finds the single customer (1 out of 599) via index instantly, then scans the rental table for that customer’s 32 rentals out of 16,044 total. Even though rental doesn’t use an index here, the nested loop is still efficient because the outer table returns only one row.

Nested loops excel when:

The outer table is small
The inner table has an index on the join key
The join is highly selective (few matches per outer row)

Implementation: nodeNestloop.c

Hash Join: Speed Through Hashing

When joining large tables without useful indexes, Hash Join dominates. It works in two phases—build a hash table, then probe it:

-- Build phase: create hash table from smaller table
hash_table = {}
FOR each_row IN smaller_table (customer):
    hash_key = HASH(row.customer_id)
    hash_table[hash_key] = row

-- Probe phase: stream through larger table and probe hash table
FOR each_row IN larger_table (rental):
    hash_key = HASH(row.customer_id)
    IF hash_key EXISTS IN hash_table:
        RETURN combined_row(hash_table[hash_key], row)

Hash join is blazingly fast when the hash table fits in memory:

EXPLAIN SELECT *
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id;

-- QUERY PLAN
-- Hash Join  (cost=22.48..375.33 rows=16044 width=114)
--   Hash Cond: (r.customer_id = c.customer_id)
--   ->  Seq Scan on rental r  (cost=0.00..310.44 rows=16044 width=40)
--   ->  Hash  (cost=14.99..14.99 rows=599 width=74)
--         ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=74)

The planner scans the smaller customer table (599 rows) to build an in-memory hash table, then streams through all 16,044 rental rows probing for matches. Each probe is O(1)—instant hash lookup. This returns all 16,044 rental records with their customer information.

The catch: the hash table must fit in work_mem. If it doesn’t, PostgreSQL spills to disk, dramatically slowing the join. This is why work_mem tuning matters for analytical queries.

Hash joins shine for:

Equi-joins (using = operator)
Large tables without useful indexes
Situations with sufficient memory

Implementation: nodeHashjoin.c

Merge Join: Elegant Merging of Sorted Data

When both inputs are already sorted by the join key, Merge Join elegantly merges them like combining two sorted decks of cards:

-- Both inputs must be sorted by join key
left_row = FIRST(left_table)   -- sorted rental table
right_row = FIRST(right_table)  -- sorted payment table

WHILE left_row AND right_row:
    IF left_row.rental_id = right_row.rental_id:
        RETURN combined_row(left_row, right_row)
        ADVANCE both pointers
    ELSE IF left_row.rental_id < right_row.rental_id:
        ADVANCE left_row
    ELSE:
        ADVANCE right_row

The algorithm advances through both sorted inputs simultaneously, comparing keys and emitting matches:

EXPLAIN SELECT *
FROM rental r
JOIN payment p ON r.rental_id = p.rental_id;

-- QUERY PLAN
-- Merge Join  (cost=1483.07..2341.89 rows=16049 width=70)
--   Merge Cond: (r.rental_id = p.rental_id)
--   ->  Index Scan using rental_pkey on rental r  (cost=0.29..578.29 rows=16044 width=40)
--   ->  Sort  (cost=1482.77..1522.90 rows=16049 width=30)
--         Sort Key: p.rental_id
--         ->  Append  (cost=0.00..361.74 rows=16049 width=30)
--               ->  Seq Scan on payment_p2022_01 p_1  (cost=0.00..13.23 rows=723 width=30)
--               ->  Seq Scan on payment_p2022_02 p_2  (cost=0.00..42.01 rows=2401 width=30)
--               ... (additional payment partitions)

The rental table (16,044 rows) comes pre-sorted from its primary key index. The payment table (16,049 rows across 7 partitions) needs sorting first, but once sorted, the merge join needs only one pass through each—efficiently matching all rental records with their payments.

Merge joins excel when:

Both inputs are already sorted (from indexes or previous operations)
Joining very large tables where hash join would spill to disk
Non-equi joins (like >, <) where hash join can’t be used

Implementation: nodeMergejoin.c

Join Order: Solving the Combinatorial Explosion

Here’s where planning gets really interesting. The number of possible join orders grows factorially with the number of tables:

3 tables: 12 possible orders
4 tables: 120 possible orders
5 tables: 1,680 possible orders
8 tables: 17 million possible orders
10 tables: 17.6 billion possible orders
12 tables: 28 trillion possible orders

You can see the problem—evaluating every possibility would take ages, even for moderate-sized queries.

PostgreSQL handles this (src/backend/optimizer/path/allpaths.c ) with two different strategies based on query complexity:

For queries with up to 12 tables (configurable via geqo_threshold), PostgreSQL uses dynamic programming (src/backend/optimizer/path/joinrels.c ) with aggressive optimization. It doesn’t explore all possibilities—that would still take forever. Instead, it uses smart techniques:

Caching: Remembers the cost of subplans so it doesn’t recalculate them
Pruning: Throws away entire branches of possibilities that can’t possibly be optimal

This dramatically reduces the search space. But even with these tricks, the approach has limits—beyond 12 tables, there are simply too many possibilities to explore, even with caching and pruning.

For queries with 12+ tables, PostgreSQL switches to the Genetic Query Optimizer (GEQO) (src/backend/optimizer/geqo/ )—a completely different approach inspired by natural selection. Instead of trying to be smart about exploring possibilities, GEQO “evolves” good plans:

Population: Create random join orders as “individuals”
Fitness: Evaluate cost (lower cost = higher fitness)
Selection: Fitter individuals more likely to reproduce
Crossover: Combine two parent plans to create children
Mutation: Random changes to explore new possibilities
Evolution: Repeat for many generations until high-quality plans dominate

GEQO finds excellent plans in milliseconds for complex queries that would take hours or days with exhaustive search. When your query joins 15 tables with quadrillions of possible orders, “pretty good” beats “perfect but never finishes.”

The details of how GEQO works are fascinating and deserve their own deep dive—we’ll explore the genetic algorithm mechanics in a future article.

Wrapping Up

So that’s PostgreSQL’s query planner—the engine that figures out how to actually execute your queries efficiently.

We started with the basics: cost parameters that measure work in abstract units. I/O costs like seq_page_cost and random_page_cost capture how expensive disk access is, while CPU costs are tiny in comparison. These parameters let PostgreSQL compare completely different strategies on equal footing.

Then we looked at statistics—the data knowledge that makes cost estimates meaningful. Most common values, histograms, correlation statistics—all collected by ANALYZE (or autovacuum) to tell the planner what your data actually looks like. Without good statistics, even perfect cost formulas produce terrible plans.

We explored how the planner evaluates query plans by breaking queries into pieces—costing each table access method, costing each join algorithm, then adding it all up. The plan with the lowest total cost wins.

For accessing individual tables, PostgreSQL considers sequential scans (read everything), index scans (targeted lookups), index-only scans (no heap access), and bitmap scans (combining indexes efficiently). Each has its sweet spot based on how many rows you need.

For joining tables, it’s all about the data characteristics. Nested loops when one side is tiny and you have indexes. Hash joins when joining large tables without indexes. Merge joins when your data comes pre-sorted. The planner picks the cheapest option based on table sizes and available indexes.

And for join order, we explored how the order matters enormously and how PostgreSQL uses different approaches depending on how many tables you’re joining—from exhaustive optimization for simple queries to evolutionary algorithms for complex ones.

With the plan ready, PostgreSQL knows exactly how to execute your query. Next up: the executor that brings these plans to life and actually produces your results.