Query Optimization Basics

Objectives

By the end of this lesson, you should be able to:

• Compare an execution plan before and after adding an index
• Recognize an index-based scan versus a sequential scan in a real plan
• Explain why an index actually gets used once a table is large enough

💡 Why this matters: The previous lesson’s employees table was too small (9 rows) for PostgreSQL to bother with the index. This lesson uses a table large enough to see the payoff for real, the same comparison, but at a scale where it actually matters.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using a big_orders table with 20,000 rows, built specifically to demonstrate this.

Setup

CREATE TABLE big_orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

-- 20,000 rows inserted, customer_id values spread across 500 distinct customers

SELECT COUNT(*) FROM big_orders;

 count
-------
 20000

Before an Index

EXPLAIN SELECT * FROM big_orders WHERE customer_id = 250;

 Seq Scan on big_orders  (cost=0.00..265.65 rows=51 width=82)
   Filter: (customer_id = 250)

Without an index on customer_id, PostgreSQL has no choice but a sequential scan, reading through all 20,000 rows to find the ones matching customer_id = 250.

After an Index

CREATE INDEX idx_big_orders_customer_id ON big_orders(customer_id);

EXPLAIN SELECT * FROM big_orders WHERE customer_id = 250;

 Bitmap Heap Scan on big_orders  (cost=4.60..92.22 rows=40 width=23)
   Recheck Cond: (customer_id = 250)
   ->  Bitmap Index Scan on idx_big_orders_customer_id  (cost=0.00..4.59 rows=40 width=0)
         Index Cond: (customer_id = 250)

The plan changed entirely. Bitmap Index Scan on idx_big_orders_customer_id means PostgreSQL used the new index to jump directly to matching rows, instead of reading every row in the table, Bitmap Heap Scan then fetches the actual row data for exactly those matches. (PostgreSQL sometimes uses a plain Index Scan instead of this two-step bitmap version, depending on how many rows are expected to match, both count as “using the index” rather than a full sequential scan.) The estimated cost dropped from 265.65 to 92.22, a real, measurable improvement the planner itself recognizes.

Confirming with EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM big_orders WHERE customer_id = 250;

 Bitmap Heap Scan on big_orders  (cost=4.60..92.22 rows=40 width=23) (actual time=0.091..0.184 rows=40.00 loops=1)
   Recheck Cond: (customer_id = 250)
   Heap Blocks: exact=40
   ->  Bitmap Index Scan on idx_big_orders_customer_id  (cost=0.00..4.59 rows=40 width=0) (actual time=0.065..0.067 rows=40.00 loops=1)
         Index Cond: (customer_id = 250)
 Planning Time: 0.069 ms
 Execution Time: 0.349 ms

The actual time figures confirm the plan genuinely executed using the index, finding all 40 matching rows (out of 20,000) without scanning the rest of the table.

The Basic Optimization Workflow

This is the core loop for diagnosing a slow query: run EXPLAIN (or EXPLAIN ANALYZE) to see the current plan, look for a Seq Scan on a large table where a specific condition is being filtered, consider adding an index on the filtered column, then re-run EXPLAIN to confirm the plan actually changed to use it, and improved the estimated cost. Not every slow query is fixed by an index, but this is the first, most common thing worth checking.

Try It

1. Run EXPLAIN on SELECT * FROM big_orders WHERE status = 'shipped'; and note the estimated cost (with status having only 4 distinct values, roughly 5,000 of the 20,000 rows match).
2. Create an index on status, re-run the same EXPLAIN. In this lesson’s own verification, the plan switched to a Bitmap Index Scan here too, but the cost only dropped from 388.00 to 259.54, a much smaller improvement than customer_id’s drop from 265.65 to 92.22.
3. Explain, in your own words, why matching roughly a quarter of the table (status = 'shipped') benefits less from an index than matching roughly 0.2% of the table (customer_id = 250).
4. Explain, in your own words, why customer_id (500 distinct values, each matching a small slice of the table) benefits more clearly from an index than a column with only a handful of distinct values might.

Recap

• Comparing EXPLAIN output before and after adding an index is the direct way to confirm whether the index actually helps a specific query.
• A Bitmap Index Scan or plain Index Scan in the plan means the index is being used, instead of a full Seq Scan.
• Indexing pays off most clearly on larger tables and more selective columns (many distinct values, each matching a small fraction of rows), the basic loop is: spot a Seq Scan on a large filtered table, add an index, confirm with EXPLAIN that the plan and cost actually improved.

Next lesson: this module’s exercises, creating views and indexes and reading real execution plans for yourself.