[Solved] You want to optimize a query that shows this EXPLAIN output:Bitmap Heap Scan on orders (cost=4.00..50.00 rows=1000 width=80) Bitmap Index Scan on idx_orders_customer_id (cost=0.00..4.... | PostgreSQL

PostgreSQL - Performance Tuning

You want to optimize a query that shows this EXPLAIN output:

Bitmap Heap Scan on orders  (cost=4.00..50.00 rows=1000 width=80)
  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.00 rows=1000 width=0)

What does this plan tell you about how the query accesses data?

AIt performs a nested loop join using the index

BIt scans the entire table sequentially without using indexes

CIt sorts the rows before applying the index

DIt uses an index to find matching rows, then fetches them from the table

Step-by-Step Solution

Solution:

Step 1: Understand Bitmap Index Scan
Bitmap Index Scan finds matching row locations using the index.
Step 2: Understand Bitmap Heap Scan
Bitmap Heap Scan fetches the actual rows from the table using those locations.
Final Answer:
It uses an index to find matching rows, then fetches them from the table -> Option D
Quick Check:
Bitmap scans = index lookup + table fetch [OK]

Quick Trick: Bitmap scans combine index search with table fetch [OK]

Common Mistakes:

Master "Performance Tuning" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

You want to optimize a query that shows this EXPLAIN output: