How to detect raster scan and scan raster images?

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?

..

+5
1

? , .

, , . , . ( , , )

, . , , "IT%", , , "ITQ%" (, , )

+6

All Articles