Query Planner & Cost-Based Optimization in PostgreSQL

Every SQL query you write goes through an important decision-making process before PostgreSQL reads a single row. Instead of immediately executing the query, PostgreSQL first evaluates multiple possible execution strategies and chooses the one it believes will be the cheapest. This component is called the Query Planner, and understanding how it works explains many of the “why is PostgreSQL doing that?” moments developers encounter.

What Is the Query Planner?

When I first started exploring PostgreSQL internals, I assumed the database would simply execute whatever query I sent it. In reality, PostgreSQL spends time analyzing the query and generating several possible execution plans before execution even begins.

Consider the following query:

SELECT *
FROM users
WHERE age > 25;

PostgreSQL does not immediately start scanning rows. Instead, it asks a series of questions:

• Should I use a sequential scan?
• Is an index scan cheaper?
• Would a bitmap scan be more efficient?
• Can parallel workers help here?

The planner evaluates these possibilities and picks the one with the lowest estimated cost.

PostgreSQL is not trying to find the fastest-looking plan. It is trying to find the lowest-cost plan according to its internal cost model.

Understanding Query Plans

A query plan is simply a sequence of operations PostgreSQL will execute to retrieve the requested data.

For a simple query, the plan might involve a sequential scan. For more complex queries, the plan can contain joins, sorts, aggregations, index lookups, parallel workers, and other operations.

You can inspect the chosen plan using:

EXPLAIN
SELECT *
FROM users
WHERE age > 25;

Example output:

Seq Scan on users
(cost=0.00..431.00 rows=5000 width=64)

This tells us PostgreSQL decided a sequential scan was the most efficient way to retrieve the data.

The interesting part is the cost value. Understanding that number is key to understanding planner decisions.

What Does Cost Actually Mean?

One of the biggest misconceptions is that PostgreSQL cost represents execution time.

It does not.

Cost is an internal estimation unit used to compare different plans. PostgreSQL assigns costs to various operations and combines them into a total estimated cost for each execution strategy.

For example:

Seq Scan
(cost=0.00..1000.00)

Index Scan
(cost=0.42..120.00)

The planner sees the second plan as cheaper and will usually choose it.

Several factors contribute to cost calculations:

• Reading pages from disk
• Reading pages from memory
• CPU processing
• Row filtering
• Sorting operations
• Join operations

The actual numbers are less important than the comparison between plans. PostgreSQL uses cost to rank alternatives and select the most efficient one.

Where Do These Estimates Come From?

The planner cannot make intelligent decisions without information about the data.

This information comes from statistics maintained by PostgreSQL.

These statistics are collected through:

ANALYZE;

or automatically by autovacuum.

The statistics include information such as:

• Estimated row counts
• Number of distinct values
• Data distribution
• Null value percentages
• Correlation between values

For example, PostgreSQL may know that a table contains ten million rows but only five distinct status values.

That information helps the planner estimate how many rows a query will return and determine whether an index is worth using.

A planner is only as good as its statistics. Outdated statistics often lead to poor execution plans.

Why PostgreSQL Sometimes Ignores Your Index

This is probably one of the most common questions developers ask.

Imagine a table where 95% of users belong to a single country:

SELECT *
FROM users
WHERE country = 'India';

Many developers expect PostgreSQL to use an index because one exists on the country column.

However, the planner may choose a sequential scan instead.

Why?

Because an index scan would still require PostgreSQL to visit most of the table’s rows. In that situation, reading the table sequentially can be cheaper than repeatedly jumping between index pages and table pages.

The planner might estimate:

Sequential Scan Cost = 500

Index Scan Cost = 900

Since the sequential scan has the lower cost, PostgreSQL chooses it.

The planner does not care whether an index exists. It only cares about the estimated cost.

Statistics Drive Planner Decisions

Let’s look at a different example.

Suppose a table contains ten million users and only 500 users have the value:

status = 'SUSPENDED'

For the query:

SELECT *
FROM users
WHERE status = 'SUSPENDED';

The planner knows only a tiny fraction of rows match the condition.

In this case, an index scan becomes highly attractive because PostgreSQL can locate the matching rows directly instead of scanning the entire table.

Accurate statistics allow PostgreSQL to estimate selectivity and choose the appropriate access method.

Without those statistics, the planner is effectively guessing.

Planner vs Executor

A useful mental model is separating PostgreSQL into two stages.

Planner

The planner:

• Parses the query
• Generates alternative plans
• Estimates costs
• Chooses the best plan

Executor

The executor:

• Receives the chosen plan
• Performs scans
• Executes joins
• Applies filters
• Returns results

You can think of the workflow like this:

SQL Query
     ↓
Query Planner
     ↓
Execution Plan
     ↓
Query Executor
     ↓
Results

The planner decides what should happen. The executor makes it happen.

Reading Plans with EXPLAIN

Whenever I encounter a slow query, the first thing I look at is the execution plan.

Using:

EXPLAIN ANALYZE

provides both the planner’s estimates and the actual execution results.

This allows you to compare:

• Estimated rows vs actual rows
• Estimated cost vs real execution behavior
• Planner assumptions vs reality

Large differences often indicate outdated statistics or inaccurate cardinality estimates.

Learning to read execution plans is one of the most valuable PostgreSQL skills because it reveals exactly why the planner made a particular decision.

Final Thoughts

The Query Planner is one of PostgreSQL’s most powerful components. Before executing a query, it evaluates multiple strategies, estimates their costs using table statistics, and selects the plan it believes will be the most efficient.

Understanding this process changes how you think about query optimization. Instead of asking, “Why isn’t PostgreSQL using my index?” you start asking, “Why does PostgreSQL believe this plan is cheaper?”

That shift in perspective is often the difference between blindly tuning queries and genuinely understanding how the database works internally.