COUNT(*) of Table Attempts to use trgm gin Index

[yugabyte-ci]

Jira Link: DB-3486

[jasonyb]

A small repro is

create table t (id serial, string text);
create extension pg_trgm;
create index on t using gin (string gin_trgm_ops);
insert into t (string) select 'a' from generate_series(1, 50000);
analyze t;
explain select count(*) from t;

YB around commit 94c72fa:

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate  (cost=4555.00..4555.01 rows=1 width=8)
   ->  Index Only Scan using t_string_idx on t  (cost=3600.00..4430.00 rows=50000 width=0)
(2 rows)

PG 15.1:

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Aggregate  (cost=12.16..12.17 rows=1 width=8)
   ->  Index Only Scan using i on t  (cost=0.13..12.16 rows=2 width=0)
(2 rows)

For context, ybgin supports amgettuple but not amgetbitmap, whereas gin (upstream PG) supports the opposite. That's the difference. Otherwise, the codepaths each take appear the same:

get_index_paths

• build_index_paths
  • this returns an IndexOnlyScan IndexPath
• "However, some of the indexes might support only bitmap scans"
• if (index->amhasgettuple)
  • this is where code diverges, but it is expected behavior

The issue is that using the index results in error due to not-supported scan type:

select count(*) from t;

ERROR: unsupported ybgin index scan
Detail: ybgin index method does not support non-default search mode: everything.

This could be considered duplicate of issue #9960 and item "feat: support non-default search modes" of issue #7850.