How a PostgreSQL Index Turned a 231 ms Query into 0.8 ms

What Is a Database Index?

A database index helps PostgreSQL find rows without scanning the entire table.

Think of a table as a large book:

• Rows are the information inside the book.

• Table pages are the physical pages containing that information.

• An index tells PostgreSQL which pages contain the required rows.

An index generally stores:

Indexed value → location of the row in the table

Indexed value → location of the row in the table

Indexed value → location of the row in the table

For example:

customer_id 50000 → table page 120, row 4
customer_id 50000 → table page 850, row 2

customer_id 50000 → table page 120, row 4
customer_id 50000 → table page 850, row 2

customer_id 50000 → table page 120, row 4
customer_id 50000 → table page 850, row 2

PostgreSQL can use these locations to visit only the required table pages.

An index helps PostgreSQL jump directly to relevant table pages instead of scanning every page.

Practical Indexing Test in pgAdmin 4

Step 1: Create the Orders Table

Open pgAdmin 4, select your database, and open the Query Tool.

Run:

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Here:

• order_id is generated automatically.

• customer_id identifies the customer.

• order_date stores the order date.

• order_status stores values such as Pending or Completed.

• total_amount stores the order value.

Step 2: Insert One Million Test Records

INSERT INTO orders (
    customer_id,
    order_date,
    order_status,
    total_amount
)
SELECT
    FLOOR(RANDOM() * 100000 + 1)::INTEGER,
    CURRENT_DATE - FLOOR(RANDOM() * 730)::INTEGER,
    CASE FLOOR(RANDOM() * 4)::INTEGER
        WHEN 0 THEN 'Pending'
        WHEN 1 THEN 'Processing'
        WHEN 2 THEN 'Completed'
        ELSE 'Cancelled'
    END,
    ROUND((RANDOM() * 1000 + 10)::NUMERIC, 2)
FROM generate_series(1, 1000000);

INSERT INTO orders (
    customer_id,
    order_date,
    order_status,
    total_amount
)
SELECT
    FLOOR(RANDOM() * 100000 + 1)::INTEGER,
    CURRENT_DATE - FLOOR(RANDOM() * 730)::INTEGER,
    CASE FLOOR(RANDOM() * 4)::INTEGER
        WHEN 0 THEN 'Pending'
        WHEN 1 THEN 'Processing'
        WHEN 2 THEN 'Completed'
        ELSE 'Cancelled'
    END,
    ROUND((RANDOM() * 1000 + 10)::NUMERIC, 2)
FROM generate_series(1, 1000000);

INSERT INTO orders (
    customer_id,
    order_date,
    order_status,
    total_amount
)
SELECT
    FLOOR(RANDOM() * 100000 + 1)::INTEGER,
    CURRENT_DATE - FLOOR(RANDOM() * 730)::INTEGER,
    CASE FLOOR(RANDOM() * 4)::INTEGER
        WHEN 0 THEN 'Pending'
        WHEN 1 THEN 'Processing'
        WHEN 2 THEN 'Completed'
        ELSE 'Cancelled'
    END,
    ROUND((RANDOM() * 1000 + 10)::NUMERIC, 2)
FROM generate_series(1, 1000000);

This creates one million sample orders with:

• Customer IDs between 1 and 100,000

• Dates from approximately the last two years

• Four possible order statuses

• Random order amounts

Update PostgreSQL’s statistics:

ANALYZE orders;

ANALYZE orders;

ANALYZE orders;

PostgreSQL uses these statistics to estimate how many rows a query will return and choose an execution plan.

Confirm the row count:

SELECT COUNT(*)
FROM orders;

SELECT COUNT(*)
FROM orders;

SELECT COUNT(*)
FROM orders;

Expected result:

1000000

1000000

1000000

Sample records from the orders table in PostgreSQL, displayed in pgAdmin.

Step 3: Test the Query Without an Index

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;

Before creating the index, PostgreSQL used:

Parallel Seq Scan on orders

Parallel Seq Scan on orders

Parallel Seq Scan on orders

A sequential scan means PostgreSQL reads table pages and checks the rows one by one.

In my test, PostgreSQL:

• Returned only 8 rows

• Examined almost one million rows

• Accessed 8,104 table pages

• Took 231.730 milliseconds

The execution plan showed:

Rows Removed by Filter: 333331
loops=3

Rows Removed by Filter: 333331
loops=3

Rows Removed by Filter: 333331
loops=3

Three processes participated in the parallel scan, so approximately:

333,331 × 3 ≈ 999,993 rejected rows

333,331 × 3 ≈ 999,993 rejected rows

333,331 × 3 ≈ 999,993 rejected rows

PostgreSQL scanned almost the entire table to return only eight records.

Without an index, PostgreSQL performed a Parallel Sequential Scan and examined almost the entire table.

What Are Table Pages?

PostgreSQL stores table data in fixed-size blocks called pages.

A PostgreSQL page is normally:

8 KB

8 KB

8 KB

Each page can contain several rows.

Your one-million-row table was stored across approximately 8,104 pages. Without an index, PostgreSQL had to inspect all those pages because it did not know where the matching customer records were located.

Simplified:

Page 1    → Check all rows
Page 2    → Check all rows
Page 3    → Check all rows
...
Page 8104 → Check all rows

Page 1    → Check all rows
Page 2    → Check all rows
Page 3    → Check all rows
...
Page 8104 → Check all rows

Page 1    → Check all rows
Page 2    → Check all rows
Page 3    → Check all rows
...
Page 8104 → Check all rows

Even though the pages were already available in memory, PostgreSQL still had to process their rows.

Step 4: Create an Index

Create an index on the column used in the WHERE condition:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Refresh the statistics:

ANALYZE orders;

ANALYZE orders;

ANALYZE orders;

You can confirm the index exists:

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Expected index:

idx_orders_customer_id

idx_orders_customer_id

idx_orders_customer_id

Step 5: Run the Same Query Again

Run the same query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;

This time, PostgreSQL used:

Bitmap Index Scan
Bitmap Heap Scan

Bitmap Index Scan
Bitmap Heap Scan

Bitmap Index Scan
Bitmap Heap Scan

The process was:

1. Search the index for customer_id = 50000.

2. Identify the locations of the matching rows.

3. Visit only the relevant table pages.

4. Retrieve the complete order records.

The plan showed:

Heap Blocks: exact=8

Heap Blocks: exact=8

Heap Blocks: exact=8

PostgreSQL accessed only eight table pages instead of scanning all 8,104 pages.

The query completed in:

0.825 ms

0.825 ms

0.825 ms

With the index, PostgreSQL located the matching rows through a Bitmap Index Scan and accessed only eight table pages.

Performance Comparison

The exact timing may vary across computers and with repeated executions due to memory caching, hardware, and PostgreSQL configuration.

However, the important improvement is clear: the index prevented PostgreSQL from scanning thousands of unnecessary table pages.

Why Did PostgreSQL Use a Bitmap Scan?

You may expect PostgreSQL to show a normal Index Scan, but it selected:

Bitmap Index Scan
Bitmap Heap Scan

Bitmap Index Scan
Bitmap Heap Scan

Bitmap Index Scan
Bitmap Heap Scan

The bitmap index scan first locates all matching row locations. PostgreSQL then groups the required table-page visits and retrieves the full rows efficiently.

This is still successful index usage.

The index was created only on customer_id, but the query used:

SELECT

SELECT

SELECT

PostgreSQL therefore needed to visit the main table to retrieve the remaining columns:

• order_id

• order_date

• order_status

• total_amount

When Should You Create an Index?

Indexes are most useful when:

• A large table is frequently filtered using WHERE.

• A column is regularly used in a JOIN.

• Queries frequently use ORDER BY.

• The query returns only a small percentage of the table.

• The indexed column contains many different values.

• The same query is executed repeatedly.

Common examples include:

WHERE customer_id = 50000

WHERE customer_id = 50000

WHERE customer_id = 50000

JOIN customers
ON orders.customer_id = customers.customer_id

JOIN customers
ON orders.customer_id = customers.customer_id

JOIN customers
ON orders.customer_id = customers.customer_id

ORDER BY order_date DESC

ORDER BY order_date DESC

ORDER BY order_date DESC

For a query that filters by customer and sorts by date, a composite index may be useful:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date DESC);

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date DESC);

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date DESC);

This can support:

SELECT *
FROM orders
WHERE customer_id = 50000
ORDER BY order_date DESC;

SELECT *
FROM orders
WHERE customer_id = 50000
ORDER BY order_date DESC;

SELECT *
FROM orders
WHERE customer_id = 50000
ORDER BY order_date DESC;

When Might an Index Not Help?

An index may provide little benefit when:

• The table is very small.

• The query returns a large part of the table.

• The indexed column has very few distinct values.

• The table receives heavy insert, update, and delete activity.

For example, order_status has only four possible values:

Pending
Processing
Completed
Cancelled

Pending
Processing
Completed
Cancelled

Pending
Processing
Completed
Cancelled

If one status matches 25% of the table, PostgreSQL may decide that a sequential scan is more efficient.

Indexes also have a cost. Every index:

• Uses additional storage

• Must be updated during inserts

• Must be updated during updates

• Must be maintained during deletes

The goal is not to index every column. The goal is to create indexes that support frequently executed and important queries.

How to Check Whether an Index Helps

Use:

EXPLAIN

EXPLAIN

EXPLAIN

to view PostgreSQL’s estimated plan.

Use:

EXPLAIN ANALYZE

EXPLAIN ANALYZE

EXPLAIN ANALYZE

to execute the query and display actual results.

Use:

EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)

to include information about the table and index pages accessed.

Compare:

• Sequential Scan versus Index Scan

• Execution time

• Buffer usage

• Rows removed by filter

• Estimated rows versus actual rows

• Sort operations

Do not judge an index using execution time alone. Cached data may make later query runs faster.

Conclusion

Without an index, PostgreSQL may need to inspect an entire table to find a few rows.

In this experiment, the unindexed query:

• Scanned almost one million rows

• Accessed 8,104 table pages

• Took 231.730 milliseconds

After indexing customer_id, PostgreSQL:

• Located the required rows through the index

• Accessed only eight table pages

• Completed the query in 0.825 milliseconds

Indexes are most effective on large tables when frequently executed queries return a small number of rows.

The best approach is simple:

Identify an important query, inspect its execution plan, create an appropriate index, and measure the result.