Skip to main content
Databases · lesson 3 of 6

Intermediate

2

Advanced

4

Also part of Databases

12
Open the Databases page →
Databases advanced Lesson 3 of 6

Indexing Across Engines: One B-Tree Idea, Measured Twice

The same customer_id lookup, on the same shape of 100k+ row table, measured before and after an index in both PostgreSQL and MySQL — two engines, two real query planners, the same order-of-magnitude win.

By SkillByExample · Updated

databases indexing postgresql mysql explain performance

The same idea — index the column you filter on — measured on two different engines against comparably-sized real data, not asserted once and assumed to generalize.

PostgreSQL: sequential scan vs. indexed lookup

psql -U postgres -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;"
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8033.20 rows=2757 width=12) (actual time=6.711..38.444 rows=9 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on orders  (cost=0.00..6757.50 rows=1622 width=12) (actual time=4.747..26.573 rows=4 loops=2)
         Filter: (customer_id = 12345)
         Rows Removed by Filter: 249996
 Planning Time: 0.474 ms
 Execution Time: 38.508 ms
(8 rows)

Parallel Seq Scan — every one of the 500,000 rows gets checked against the filter, split across a parallel worker. Execution Time: 38.508 ms for a query that only needed 9 rows.

psql -U postgres -c "CREATE INDEX idx_orders_customer ON orders(customer_id);"
psql -U postgres -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;"
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.51..46.54 rows=11 width=12) (actual time=0.126..0.176 rows=9 loops=1)
   Recheck Cond: (customer_id = 12345)
   Heap Blocks: exact=9
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..4.50 rows=11 width=0) (actual time=0.087..0.087 rows=9 loops=1)
         Index Cond: (customer_id = 12345)
 Planning Time: 0.825 ms
 Execution Time: 0.298 ms
(7 rows)

38.508 ms → 0.298 ms — a measured ~129x improvement on the same 500,000-row table, same query, same 9-row result. The index turned “check every row” into “look up exactly which rows qualify, then fetch just those.”

MySQL: the same shape of query, the same shape of result

mysql -uroot -p dbcompare -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;"
-> Filter: (orders.customer_id = 12345)  (cost=10082 rows=10026) (actual time=4.34..28.8 rows=2 loops=1)
    -> Table scan on orders  (cost=10082 rows=100256) (actual time=1.28..22.4 rows=100000 loops=1)

Table scan — MySQL’s name for the same idea as Postgres’s Seq Scan — checking all 100,000 rows, taking 28.8 ms.

mysql -uroot -p dbcompare -e "CREATE INDEX idx_orders_customer ON orders(customer_id);"
mysql -uroot -p dbcompare -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;"
-> Index lookup on orders using idx_orders_customer (customer_id=12345)  (cost=0.7 rows=2) (actual time=0.0174..0.0186 rows=2 loops=1)

28.8 ms → 0.0186 ms — a different engine, a different query planner, a different plan vocabulary (Index lookup instead of Bitmap Index Scan), and the same order-of-magnitude story: an unindexed equality filter on a 100k+-row table costs milliseconds; an indexed one costs microseconds.

What transfers, and what doesn’t

The B-tree idea itself transfers directly: index a column you filter on by equality, and the engine can jump straight to the matching rows instead of checking every one. What doesn’t transfer is the plan’s vocabulary and the exact mechanics — Postgres’s Bitmap Heap Scan step (collecting matching row locations, then fetching them) has no exact MySQL equivalent in this plan. For the real depth on either engine’s specific optimizer — composite indexes, covering indexes, when the optimizer chooses not to use an available index — see this site’s own postgresql/indexing-strategy.md and mysql/indexing-strategy.md, each with real measured examples of those deeper cases on one engine.

Practice

1. Run the unindexed query again after the index exists, on a column that has no index, to confirm the planner still scans. 
psql -U postgres -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE amount = 500;"
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..7429.27 rows=498 width=12) (actual time=0.341..24.415 rows=480 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on orders  (cost=0.00..6379.47 rows=293 width=12) (actual time=0.059..19.050 rows=240 loops=2)
         Filter: (amount = 500)
         Rows Removed by Filter: 249760
 Planning Time: 0.656 ms
 Execution Time: 24.483 ms
(8 rows)

Adding an index on customer_id did nothing for a query filtering on amount — the index only speeds up queries on the column it actually covers, still scanning here at a similar cost to the original unindexed customer_id query.

2. Confirm the indexed customer_id query stays fast when it needs to return more rows, not just the same 9. 
psql -U postgres -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id BETWEEN 12000 AND 12100;"
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=18.41..1967.18 rows=974 width=12) (actual time=1.178..7.922 rows=959 loops=1)
   Recheck Cond: ((customer_id >= 12000) AND (customer_id <= 12100))
   Heap Blocks: exact=814
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..18.16 rows=974 width=0) (actual time=0.687..0.688 rows=959 loops=1)
         Index Cond: ((customer_id >= 12000) AND (customer_id <= 12100))
 Planning Time: 9.558 ms
 Execution Time: 8.541 ms
(7 rows)

Still a Bitmap Index Scan, and still far faster than the original 38.508ms unindexed scan even though this query returns ~960 rows instead of 9 — the index serves a range condition on the same column just as well as a single equality match, and the cost scales with the number of matching rows fetched from the heap, not with the size of the whole table.

3. Check how many rows the index actually covers with a count, to confirm it isn't silently partial. 
psql -U postgres -c "SELECT COUNT(*) FROM orders;"
 count
--------
 500000
(1 row)

Matches the original insert count — the index was built against the full table, not a subset, so the earlier speedup measurement wasn’t an artifact of the index only covering some rows.

Next: ACID vs. BASE, measured — a real lost update Redis won’t stop and Postgres will.

Frequently Asked Questions

Why measure the same kind of query on two different engines instead of just picking one?
Because the underlying idea — a B-tree index turning a full scan into a direct lookup — is the same idea in both engines, and seeing two independent query planners produce the same order-of-magnitude improvement is stronger evidence that the effect is real and general, not an artifact of one engine's specific optimizer.
Why did PostgreSQL's EXPLAIN ANALYZE output look so different from MySQL's?
Each engine has its own plan format and its own terminology for the same underlying operation — Postgres calls an unindexed scan a Seq Scan (with Parallel Seq Scan when it splits the work across workers), MySQL calls the equivalent a Table scan. Once indexed, Postgres shows a Bitmap Index Scan feeding a Bitmap Heap Scan; MySQL shows a flatter Index lookup. Different vocabulary, same underlying tradeoff — this lesson's postgresql and mysql tracks each cover their own engine's plan format in much more depth.
Is a 100x+ speedup from one index typical?
It's typical specifically for this shape of query — an equality lookup on a column with no existing index, against a table too large to fit comfortably in the query's working set. The gain shrinks as the table gets smaller (a full scan of 100 rows is already fast) and can vanish or reverse for queries that need to read most of the table anyway, where an index adds lookup overhead instead of saving work.
Where do I go to learn one engine's indexing behavior in real depth?
This site's own postgresql/indexing-strategy.md and mysql/indexing-strategy.md lessons — both go far deeper into one engine's optimizer than a comparison lesson like this one can, including composite indexes, covering indexes, and cases where the optimizer chooses not to use an available index. This lesson's job is showing that the core idea transfers across engines, not replacing that per-engine depth.
Does adding an index always help, with no downside?
No — every index has to be maintained on every INSERT, UPDATE, and DELETE that touches its column, which is a real, permanent write cost in exchange for the read speedup measured here. The right call depends on the actual read/write ratio for that table, which is exactly the kind of workload-specific judgment the earlier choosing-a-database lesson's scoring framework is built to make explicit instead of guessing.