[YSQL] Bitmap Scan choosing table-level storage filters instead of index-level filters

[timothy-e]

Jira Link: DB-16434

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);

analyze movies;
set yb_enable_base_scans_cost_model = on;

This query uses a Storage Index Filter, so only 253 rows need to be looked up from the main table.

/*+ BitmapScan(movies) */ explain (analyze, dist, summary off, timing off)
select * from movies where director like 'ab%d';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 YB Bitmap Table Scan on movies  (cost=389.44..462.15 rows=100 width=30) (actual rows=253 loops=1)
   Storage Table Read Requests: 1
   Storage Table Rows Scanned: 253
   ->  Bitmap Index Scan on movies_director_idx  (cost=0.00..109.79 rows=1 width=0) (actual rows=253 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 Rows Scanned: 3869
(8 rows)

Adding another condition makes the index filter move to the table:

/*+ BitmapScan(movies) */ explain (analyze, dist, summary off, timing off)
select * from movies where director like 'ab%d' or rating > 9.9;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 YB Bitmap Table Scan on movies  (cost=857.76..1884.53 rows=200 width=30) (actual rows=287 loops=1)
   Storage Filter: ((director ~~ 'ab%d'::text) OR (rating > '9.9'::double precision))
   Storage Table Read Requests: 4
   Storage Table Rows Scanned: 3903
   ->  BitmapOr  (cost=265.92..265.92 rows=1332 width=0) (actual rows=3903 loops=1)
         ->  Bitmap Index Scan on movies_director_idx  (cost=0.00..208.85 rows=1232 width=0) (actual rows=3869 loops=1)
               Index Cond: ((director >= 'ab'::text) AND (director < 'ac'::text))
               Storage Index Read Requests: 4
               Storage Index Rows Scanned: 3869
         ->  Bitmap Index Scan on movies_rating_idx  (cost=0.00..56.97 rows=100 width=0) (actual rows=34 loops=1)
               Index Cond: (rating > '9.9'::double precision)
               Storage Index Read Requests: 1
               Storage Index Rows Scanned: 34
(13 rows)

Even though the bitmap index scan on movies_director_idx is costed at 208.85 without the filter instead of 109.79 with the filter and the number of rows returned from that scan is estimated to be much higher.

The planner should keep the (director ~~ 'ab%d'::text) filter in the bitmap index scan, do reduce the number of rows seeked from the main table by ~3650.

Issue Type

kind/bug

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

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