I'm testing different queries and I'm curious about how db decide using Bitmap Heap Scan and Index Scan.
create index customers_email_idx on customers(email varchar_pattern_ops);
As you can see there is a customers table (dellstore example) and I add an index to email column.
First query is here:
select * from customers where email like 'ITQ%@dell.com'; -> query with Index Scan
Explain analyze query is here:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using customers_email_idx on customers (cost=0.00..8.27 rows=2 width=268) (actual time=0.046..0.046 rows=0 loops=1)
Index Cond: (((email)::text ~>=~ 'ITQ'::text) AND ((email)::text ~<~ 'ITR'::text))
Filter: ((email)::text ~~ 'ITQ%@dell.com
'::text)
Total runtime: 0.113 ms
Other query is here:
select * from customers where email like 'IT%@dell.com'; -> query with Bitmap Heap Scan
Explain analyze query is here:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customers (cost=4.54..106.77 rows=2 width=268) (actual time=0.206..0.206 rows=0 loops=1)
Filter: ((email)::text ~~ 'IT%@dell.com
'::text)
-> Bitmap Index Scan on customers_email_idx (cost=0.00..4.54 rows=29 width=0) (actual time=0.084..0.084 rows=28 loops=1)
Index Cond: (((email)::text ~>=~ 'IT'::text) AND ((email)::text ~<~ 'IU'::text))
Total runtime: 0.273 ms
Can you explain this example why Bitmap and Index Scan is used here?
Thank you..