GPORCA not support using btree index for prefix queries with `LIKE 'prefix%'`, even if I declare the opclass when creating the index.

[cobolbaby]

trafficstars

Feature Request

Is your feature request related to a problem? Please describe:

CREATE INDEX idx_inframinio_newitcnd_log_info_uut_serial_number2
    ON inframinio.newitcnd_log_info USING btree
    (uut_serial_number COLLATE pg_catalog."default" varchar_pattern_ops ASC NULLS LAST)
;

analyse inframinio.newitcnd_log_info;

explain 
select * from inframinio.newitcnd_log_info
where uut_serial_number like 'BV34PP3349%'
limit 10


/*
"Limit  (cost=0.00..3151.17 rows=1 width=285)"
"  ->  Gather Motion 20:1  (slice1; segments: 20)  (cost=0.00..3151.17 rows=10 width=285)"
"        ->  Limit  (cost=0.00..3151.16 rows=1 width=285)"
"              ->  Sequence  (cost=0.00..3151.16 rows=90274 width=285)"
"                    ->  Partition Selector for newitcnd_log_info (dynamic scan id: 1)  (cost=10.00..100.00 rows=5 width=4)"
"                          Partitions selected: 5 (out of 5)"
"                    ->  Dynamic Seq Scan on newitcnd_log_info (dynamic scan id: 1)  (cost=0.00..3151.16 rows=90274 width=285)"
"                          Filter: ((uut_serial_number)::text ~~ 'BV34PP3349%'::text)"
"Optimizer: Pivotal Optimizer (GPORCA)"
*/

set optimizer = off;

/*
"Limit  (cost=6810.32..6866.35 rows=10 width=291)"
"  ->  Gather Motion 20:1  (slice1; segments: 20)  (cost=6810.32..6866.35 rows=10 width=291)"
"        ->  Limit  (cost=6810.32..6866.15 rows=1 width=291)"
"              ->  Append  (cost=6810.32..155643.04 rows=1334 width=291)"
"                    ->  Bitmap Heap Scan on newitcnd_log_info_1_prt_p50  (cost=6810.32..155556.40 rows=175 width=288)"
"                          Filter: ((uut_serial_number)::text ~~ 'BV34PP3349%'::text)"
"                          ->  Bitmap Index Scan on newitcnd_log_info_1_prt_p50_uut_serial_number_idx1  (cost=0.00..6809.45 rows=17297 width=0)"
"                                Index Cond: (((uut_serial_number)::text ~>=~ 'BV34PP3349'::text) AND ((uut_serial_number)::text ~<~ 'BV34PP334:'::text))"
"                    ->  Bitmap Heap Scan on newitcnd_log_info_1_prt_p55  (cost=13.48..23.49 rows=656 width=293)"
"                          Filter: ((uut_serial_number)::text ~~ 'BV34PP3349%'::text)"
"                          ->  Bitmap Index Scan on newitcnd_log_info_1_prt_p55_uut_serial_number_idx1  (cost=0.00..10.20 rows=1 width=0)"
"                                Index Cond: (((uut_serial_number)::text ~>=~ 'BV34PP3349'::text) AND ((uut_serial_number)::text ~<~ 'BV34PP334:'::text))"
"                    ->  Bitmap Heap Scan on newitcnd_log_info_1_prt_p60  (cost=10.81..20.82 rows=123 width=289)"
"                          Filter: ((uut_serial_number)::text ~~ 'BV34PP3349%'::text)"
"                          ->  Bitmap Index Scan on newitcnd_log_info_1_prt_p60_uut_serial_number_idx1  (cost=0.00..10.20 rows=1 width=0)"
"                                Index Cond: (((uut_serial_number)::text ~>=~ 'BV34PP3349'::text) AND ((uut_serial_number)::text ~<~ 'BV34PP334:'::text))"
"                    ->  Bitmap Heap Scan on newitcnd_log_info_1_prt_pty  (cost=10.78..20.79 rows=117 width=292)"
"                          Filter: ((uut_serial_number)::text ~~ 'BV34PP3349%'::text)"
"                          ->  Bitmap Index Scan on newitcnd_log_info_1_prt_pty_uut_serial_number_idx1  (cost=0.00..10.20 rows=1 width=0)"
"                                Index Cond: (((uut_serial_number)::text ~>=~ 'BV34PP3349'::text) AND ((uut_serial_number)::text ~<~ 'BV34PP334:'::text))"
"                    ->  Bitmap Heap Scan on newitcnd_log_info_1_prt_other  (cost=11.52..21.53 rows=264 width=290)"
"                          Filter: ((uut_serial_number)::text ~~ 'BV34PP3349%'::text)"
"                          ->  Bitmap Index Scan on newitcnd_log_info_1_prt_other_uut_serial_number_idx1  (cost=0.00..10.20 rows=1 width=0)"
"                                Index Cond: (((uut_serial_number)::text ~>=~ 'BV34PP3349'::text) AND ((uut_serial_number)::text ~<~ 'BV34PP334:'::text))"
"Optimizer: Postgres query optimizer"
*/

Describe the feature you'd like to request: