[YSQL] Cost Bitmap Index Scan remote filters

[timothy-e]

Jira Link: DB-11598

Description

create table movies(name text, release_date date, genre text, rating float, director text);

insert into movies select 
    substr(md5(random()::text), 1, 5), 
    current_date - (random() * interval '40 years'), 
    case when random() < 0.5 then 'comedy' else 'action' end, 
    random() * random() * 10, 
    substr(md5(random()::text), 1, 4) from generate_series(1, 1000000);

create index on movies(rating asc);
create index on movies(director asc);

/*+ BitmapScan(movies) */ explain (analyze, dist, summary off)
select * from movies where director like 'ab%d' or rating > 9.9;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 YB Bitmap Table Scan on movies  (cost=6905.00..7209.00 rows=10000 width=108) (actual time=7.232..19.476 rows=304 loops=1)
   Storage Filter: ((director ~~ 'ab%d'::text) OR (rating > '9.9'::double precision))
   Storage Table Read Requests: 4
   Storage Table Read Execution Time: 15.486 ms
   Storage Table Rows Scanned: 3925
   ->  BitmapOr  (cost=6905.00..6905.00 rows=20000 width=0) (actual time=2.633..2.633 rows=3925 loops=1)
         ->  Bitmap Index Scan on movies_director_idx1  (cost=0.00..3450.00 rows=10000 width=0) (actual time=2.370..2.370 rows=3870 loops=1)
               Index Cond: ((director >= 'ab'::text) AND (director < 'ac'::text))
               Storage Index Read Requests: 1
               Storage Index Read Execution Time: 1.894 ms
               Storage Index Rows Scanned: 3870
         ->  Bitmap Index Scan on movies_rating_idx  (cost=0.00..3450.00 rows=10000 width=0) (actual time=0.261..0.261 rows=55 loops=1)
               Index Cond: (rating > '9.9'::double precision)
               Storage Index Read Requests: 1
               Storage Index Read Execution Time: 0.207 ms
               Storage Index Rows Scanned: 55
(16 rows)

/*+ BitmapScan(movies) */ explain (analyze, dist, summary off)
select * from movies where director like 'ab%d';
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 YB Bitmap Table Scan on movies  (cost=3377.50..3506.50 rows=10000 width=108) (actual time=3.665..3.717 rows=249 loops=1)
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 1.233 ms
   Storage Table Rows Scanned: 249
   ->  Bitmap Index Scan on movies_director_idx1  (cost=0.00..3375.00 rows=10000 width=0) (actual time=2.331..2.331 rows=249 loops=1)
         Index Cond: ((director >= 'ab'::text) AND (director < 'ac'::text))
         Storage Index Filter: (director ~~ 'ab%d'::text)
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 2.257 ms
         Storage Index Rows Scanned: 3870
(10 rows)

The second query has a storage index filter on the bitmap index scan, so it is doing slightly more work. However, the cost is lower and the estimated number of rows is the same.

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.