I Added an Index to PostgreSQL. My Query Went from 75 ms to 0.3 ms

Aryan Jain

4 min read

·

Oct 19, 2026

--

1

Listen

Share

An experiment with a million-row table, EXPLAIN ANALYZE, and a B-tree index.

Indexes are one of the first things people recommend when a SQL query is slow.

But I wanted to understand what actually changes when an index is added.

Does PostgreSQL use it automatically? How does the execution plan change? And how much faster can a simple lookup become?

I decided to test this with a small PostgreSQL experiment.

1. Setting up the experiment

I created a synthetic e-commerce orders table containing one million rows.

The table had five columns:

• order_id
• customer_id
• order_date
• amount
• status

The data was generated using PostgreSQL’s generate_series() function.

There were 10,000 customer IDs, with exactly 100 orders per customer.

I already had a primary key on order_id. For the experiment, I wanted to investigate an additional index on customer_id.

The query I tested was:

SELECT order_id, order_date, amount, status
FROM index_lab_orders
WHERE customer_id = 4242;

This query retrieves 100 orders from a table containing one million rows.

In other words, it selects only 0.01% of the table.

2. Measuring performance before the index

Before creating the new index, I ran:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, order_date, amount, status
FROM index_lab_orders
WHERE customer_id = 4242;

The plan showed a Parallel Seq Scan.

PostgreSQL was scanning the table in parallel and applying the customer ID filter. The plan reported approximately 7,875 shared-buffer hits.

I recorded five execution times:

Run Execution time 74.725 ms, 74.591 ms, 198.638 ms, 88.418 ms, 74.105 ms

The median was 74.725 ms.

One execution was much slower than the others. This was a reminder that individual query timings can vary, even when the SQL remains unchanged.

3. Creating the index

I created a B-tree index on customer_id:

CREATE INDEX idx_orders_customer_id
ON index_lab_orders (customer_id);

ANALYZE index_lab_orders;

Then I ran the same query again.

Initially, PostgreSQL continued choosing a sequential scan. That was not the result I expected.

Instead of assuming the index had failed, I investigated the execution plan. As a diagnostic, I temporarily discouraged sequential scans and confirmed that PostgreSQL could produce an index-based plan.

I then collected a new set of measurements under normal planner settings.

4. What changed?

The new plan showed:

Bitmap Index Scan → Bitmap Heap Scan

The bitmap index scan identified the matching customer entries. The bitmap heap scan then retrieved the corresponding rows from the table.

The five recorded execution times were:

Run Execution time 0.405 ms , 0.341 ms, 0.315 ms, 0.152 ms 50.202 ms

The median was 0.315 ms.

Here’s the comparison:

Press enter or click to view image in full size

The observed median was approximately 237 times lower after adding the index.

The indexed plan accessed far fewer shared-buffer pages. Instead of scanning and filtering the table, PostgreSQL could use the index to locate the relevant customer rows.

5. Does this mean indexes always make queries faster?

No.

This experiment demonstrates a particular case: a lookup that retrieves a very small fraction of a table.

If a query retrieves most of the table, a sequential scan can be more efficient than repeatedly following index entries to fetch rows.

Indexes also have costs:

• They consume disk space.
• They add work to inserts, updates, and deletes.
• They need maintenance.
• They may not help queries that filter on unrelated columns.

PostgreSQL’s query planner considers these trade-offs when selecting an execution plan.

The important lesson is not that every index creates a 237× speedup.

It is that an index can substantially reduce the work required for a selective lookup — and that execution plans and measurements are needed to establish what happened.

6. Limitations of my experiment

This was a small, synthetic benchmark, not a production workload.

The customer distribution was uniform, and I tested only one query pattern. I also did not rigorously control cache state or system activity between runs. The original measurements varied, and five executions per condition are not enough to characterize performance under all circumstances.

The result should therefore be interpreted as an observed difference in this environment, not a general performance guarantee.

A stronger follow-up would test several customer selectivities, repeat the benchmark under controlled conditions, and measure the index’s storage and write costs.

Conclusion

This experiment gave me a more practical understanding of PostgreSQL indexes.

The index did not simply make the same plan faster. PostgreSQL changed how it located the matching rows, and the resulting plan accessed substantially fewer buffer pages.

It also taught me to investigate the query planner instead of assuming that creating an index automatically means it will be used.

The SQL scripts and recorded execution plans are available here:

GitHub: https://github.com/aryan802/postgresql-index-performance-lab

I plan to extend the experiment by testing how query selectivity affects PostgreSQL’s choice between sequential and index-based scans.