I am testing different queries and I'm curious how db decides to use a raster map scan and index scan.
create customers_email_idx index for customers (email varchar_pattern_ops);
As you can see, there is a customer table (dellstore example) and I am adding an index column to the email address.
The first request is here:
select * from customers where email is "ITQ%@dell.com"; → query with indexing
Explain the analysis request 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
Another request:
select * from customers where email is "IT%@dell.com"; → request with bitmap heap scan
Explain the analysis request 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 are Bitmap and Index Scan used here?
..