Rahul Dhawan

Posted on Jul 23

What Really Happens When PostgreSQL Runs Your Query?

#database #performance #backend #postgres

I recently had one of those exciting engineering moments where something I had used for months finally clicked.

While investigating query performance on a project, I started looking beyond the SQL and into what PostgreSQL was doing behind the scenes. Using execution plans helped me understand why schema design, indexes, and even the columns in a SELECT statement can significantly affect performance.

This article shares the practical mental model I learned—without going too deeply into PostgreSQL internals.

What happens when PostgreSQL receives a query?

When PostgreSQL receives SQL, it does not immediately start reading rows.

It first:

Parses the SQL and validates its syntax.
Rewrites the query when views or rules are involved.
Plans possible execution strategies.
Estimates the cost of each strategy.
Executes the plan it believes will be cheapest.
Returns the result.

A simplified view looks like this:

PostgreSQL uses table statistics, estimated row counts, available indexes, and expected I/O costs to choose a plan.

An index existing does not guarantee that PostgreSQL will use it. The planner may decide that another approach is cheaper.

Tables are stored as pages

At the SQL level, we think of a table as a collection of rows. Internally, PostgreSQL stores rows—also called tuples—inside fixed-size blocks called pages.

A page is typically 8 KB.

When PostgreSQL retrieves data, it works with these pages. A page may already be available in memory, or it may need to be read from storage.

This is one reason row width matters.

Narrow rows allow more records to fit on a page. Wider rows may require PostgreSQL to process more pages for the same number of records.

So how does PostgreSQL find a record?

Consider the following table:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Now consider this query:

SELECT id, name, email
FROM users
WHERE status = 'active';

PostgreSQL has several ways to find matching records. Three important ones are:

Sequential scan
Index scan
Bitmap scan

Sequential scan

A sequential scan, shown as Seq Scan in an execution plan, reads the table page by page and checks visible rows against the filter.

Conceptually, PostgreSQL:

Loads a table page.
Inspects its tuples.
Applies transaction visibility rules.
Evaluates the WHERE condition.
Returns matching rows.
Continues through the table.

A simplified plan may look like this:

Seq Scan on users
  Filter: (status = 'active')
  Rows Removed by Filter: 800000

A sequential scan is not automatically bad. It can be efficient when:

The table is small.
The query returns a large percentage of the table.
The filter is not selective.
No useful index exists.
Reading the table sequentially is cheaper than performing many separate lookups.

If almost every user is active, scanning the table may be cheaper than using an index to fetch nearly every row.

Index scan

An index is a separate data structure that helps PostgreSQL locate records without scanning the entire table.

PostgreSQL uses B-tree indexes by default:

CREATE INDEX idx_users_email
ON users (email);

A simplified B-tree index looks like this:

For this query:

SELECT id, name, email
FROM users
WHERE email = 'alice@example.com';

PostgreSQL can:

Search the index for the email.
Find the corresponding tuple location.
Fetch the relevant table page.
Check whether the row is visible.
Return the requested columns.

                      B-tree index
                           |
             search for alice@example.com
                           |
                           v
                  tuple location: (42, 7)
                           |
                           v
                    Table page 42
                           |
                           v
                       Row 7

An index scan is most useful when the query returns a small, selective portion of the table.

Index-only scans

If an index contains every value required by a query, PostgreSQL may use an Index Only Scan.

CREATE INDEX idx_users_email_name
ON users (email, name);

This index could support:

SELECT email, name
FROM users
WHERE email = 'alice@example.com';

PostgreSQL may still need to check visibility information from the table, so an index-only scan does not always mean that the table is completely ignored.

Bitmap scan

A bitmap scan is useful when PostgreSQL expects more than a few matches, but not enough to justify scanning the entire table.

It normally appears as two operations:

Bitmap Heap Scan on users
  -> Bitmap Index Scan on idx_users_status

PostgreSQL first uses the index to collect matching tuple locations. It then groups those locations by table page before fetching the records.

This can reduce scattered access by allowing PostgreSQL to fetch each required page and retrieve multiple matching rows from it.

Scan type	Usually useful when	Main trade-off
Sequential scan	A large part of the table is needed	Inspects many rows
Index scan	A small number of rows is needed	May perform random page access
Bitmap scan	A moderate number of rows is needed	Must build and process a bitmap
Index-only scan	Required values are covered by an index	May still require visibility checks

These are guidelines, not fixed rules. PostgreSQL chooses the plan with the lowest estimated cost.

Why schema design matters

Schema design affects more than data organization. It can influence:

Row width
The number of pages required
Cache efficiency
Query memory usage
Network traffic
Serialization and deserialization work

Consider a table containing several large fields:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    total NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    customer_notes TEXT,
    internal_notes TEXT,
    provider_response JSONB,
    metadata JSONB
);

An order-list endpoint probably does not need every field.

Instead of:

SELECT *
FROM orders
WHERE customer_id = 42;

Request only the columns the application needs:

SELECT id, status, total, created_at
FROM orders
WHERE customer_id = 42;

Selecting fewer columns can reduce:

Query output width
Network transfer
Application memory usage
Serialization work
Retrieval of unnecessary large values

It may also make an index-only scan possible when the required columns are covered by an index.

PostgreSQL has optimizations for null and large values, including variable-length storage and TOAST. Adding a column therefore does not always increase every row by its full declared size.

Still, columns—especially large text and JSON fields—should be added deliberately.

Indexes are powerful, but not free

Columns frequently used for filtering, joining, or sorting are often good index candidates.

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

Composite indexes can support more specific access patterns:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

This may improve:

SELECT id, status, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Benefits of indexes

Indexes can:

Avoid unnecessary table scans.
Improve selective lookups.
Speed up joins.
Support sorting.
Enforce uniqueness.
Enable index-only scans.

Costs of indexes

Indexes also:

Consume storage.
Make inserts more expensive.
Add work to updates and deletes.
Require vacuuming and maintenance.
Can become bloated or redundant.

Indexes trade storage and write performance for faster reads. The goal is not to index every column, but to create a small set of useful indexes based on real query patterns.

A frequently queried column is not necessarily a good index candidate if most rows contain the same value.

Understanding queries with `EXPLAIN`

The most useful tool during my investigation was:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, name, email
FROM users
WHERE email = 'alice@example.com';

Each option provides different information:

EXPLAIN displays the selected execution plan.
ANALYZE executes the query and reports actual measurements.
BUFFERS reports how PostgreSQL accessed data pages.
VERBOSE includes additional details about operations and output.

Be careful: ANALYZE actually executes the statement.

For a modifying query, investigate it inside a transaction and roll it back:

BEGIN;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM users
WHERE status = 'inactive';

ROLLBACK;

Reading an execution plan

A simplified plan might look like this:

Buffers

A plan may include:

Buffers: shared hit=12 read=4

At a high level:

shared hit means the block was already in PostgreSQL’s shared buffers.
shared read means the block had to be loaded into shared buffers.
dirtied means the block was modified in memory.
written means a block was written out.

Buffer information adds useful context because execution times can change depending on caching.

Sequential scan versus index scan

Before adding an index, a selective email lookup might produce:

Seq Scan on users
  Filter: (email = 'target@example.com')
  Rows Removed by Filter: 999999
  Buffers: shared hit=2100 read=6200
Execution Time: 82.400 ms

After creating an index:

CREATE INDEX idx_users_email
ON users (email);

ANALYZE users;

The plan might change to:

Index Scan using idx_users_email on users
  Index Cond: (email = 'target@example.com')
  Buffers: shared hit=4
Execution Time: 0.080 ms

The exact numbers depend on the data, caching, hardware, and configuration. The important difference is the amount of work performed.

The index is not magically making PostgreSQL faster. It is allowing PostgreSQL to do less work.

A practical query-investigation workflow

When investigating a slow query:

Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE).
Check the scan type.
Compare estimated and actual rows.
Inspect buffer usage.
Look for rows removed by filters.
Check how many times each node loops.
Remove unnecessary selected columns.
Review existing indexes.
Test using realistic data.
Make one change and measure again.

Performance optimization should be evidence-driven:

Measure → Change → Measure again

Not:

Guess → Add indexes → Deploy → Hope

Helpful PostgreSQL metadata queries

Describe a table in `psql`

\d public.users

For additional details:

\d+ public.users

These are psql commands and may not work in every database client.

List columns and their types

SELECT
    ordinal_position,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'users'
ORDER BY ordinal_position;

List table indexes

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'users'
ORDER BY indexname;

Check table and index sizes

SELECT
    pg_size_pretty(
        pg_table_size('public.users')
    ) AS table_size,
    pg_size_pretty(
        pg_indexes_size('public.users')
    ) AS index_size,
    pg_size_pretty(
        pg_total_relation_size('public.users')
    ) AS total_size;

Check scan statistics

SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND relname = 'users';

These are cumulative statistics. A high sequential-scan count does not automatically indicate a problem.

Final thoughts

Understanding how PostgreSQL executes a query changed the way I approach database performance.

The main lesson is simple: performance is often about reducing unnecessary work.

That means:

Returning only the required columns
Examining fewer rows
Reading fewer pages
Creating indexes for real access patterns
Keeping planner statistics current
Balancing read improvements against write costs
Measuring changes instead of guessing

Most importantly, use:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

It turns query optimization from guesswork into investigation.

Once you can read an execution plan, PostgreSQL stops feeling like a black box. You can see the decisions it makes—and begin designing systems that work with the database rather than against it.

Hopefully reading through my first post was not an odyssey and if you made it this far then your answer to the below question might be yes ;)

DEV Community

What Really Happens When PostgreSQL Runs Your Query?

What happens when PostgreSQL receives a query?

Tables are stored as pages

So how does PostgreSQL find a record?

Sequential scan

Index scan

Index-only scans

Bitmap scan

Why schema design matters

Indexes are powerful, but not free

Benefits of indexes

Costs of indexes

Understanding queries with `EXPLAIN`

Reading an execution plan

Buffers

Sequential scan versus index scan

A practical query-investigation workflow

Helpful PostgreSQL metadata queries

Describe a table in `psql`

List columns and their types

List table indexes

Check table and index sizes

Check scan statistics

Final thoughts

Top comments (0)

What happens when PostgreSQL receives a query?

Tables are stored as pages

So how does PostgreSQL find a record?

Sequential scan

Index scan

Index-only scans

Bitmap scan

Why schema design matters

Indexes are powerful, but not free

Benefits of indexes

Costs of indexes

Understanding queries with EXPLAIN

Reading an execution plan

Buffers

Sequential scan versus index scan

A practical query-investigation workflow

Helpful PostgreSQL metadata queries

Describe a table in psql

List columns and their types

List table indexes

Check table and index sizes

Check scan statistics

Final thoughts

Understanding queries with `EXPLAIN`

Describe a table in `psql`