BUG #17079: btree_gin and type coersion combination doesn't work - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17079
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 13.3
Operating system:   Linux
Description:

Hi,

There are simple case when type coercion work with common btree index but
doesn't work with btree_gin.
(reason for testing btree_gin was fact that the btree_gin index could
provide over 10x size reduction for some column with medium to low
cardinality which provides huge savings for archive tables):

test=# create table test as select id::bigint from
generate_series(1,1000000) as g(id);
SELECT 1000000
test=# create index test_id_btree on test using btree(id);
CREATE INDEX
test=# analyze test;
ANALYZE
test=# explain analyze select * from test where id=10000;
                                                       QUERY PLAN
                                            

-------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_id_btree on test  (cost=0.42..8.44 rows=1
width=8) (actual time=0.101..0.103 rows=1 loops=1)
   Index Cond: (id = 10000)
   Heap Fetches: 1
 Planning Time: 0.322 ms
 Execution Time: 0.140 ms
(5 rows)

-- so far all good

test=# drop index test_id_btree;
DROP INDEX
test=# create index test_id_btree on test using gin(id);
CREATE INDEX
test=# analyze test;
ANALYZE

-- manual type coercion work
test=# explain analyze select * from test where id=10000::bigint;
                                                      QUERY PLAN
                                          

-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=20.01..24.02 rows=1 width=8) (actual
time=0.229..0.232 rows=1 loops=1)
   Recheck Cond: (id = '10000'::bigint)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on test_id_btree  (cost=0.00..20.01 rows=1 width=0)
(actual time=0.157..0.158 rows=1 loops=1)
         Index Cond: (id = '10000'::bigint)
 Planning Time: 0.258 ms
 Execution Time: 0.359 ms
(7 rows)

--bigint input work as well
test=# explain analyze select * from test where id=10000000000;
                                                      QUERY PLAN
                                          

-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=20.01..24.02 rows=1 width=8) (actual
time=0.028..0.030 rows=0 loops=1)
   Recheck Cond: (id = '10000000000'::bigint)
   ->  Bitmap Index Scan on test_id_btree  (cost=0.00..20.01 rows=1 width=0)
(actual time=0.023..0.024 rows=0 loops=1)
         Index Cond: (id = '10000000000'::bigint)
 Planning Time: 0.127 ms
 Execution Time: 0.091 ms

--surprise index isn't used
test=# explain analyze select * from test where id=10000;
                                                    QUERY PLAN
                                     
------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..10633.43 rows=1 width=8) (actual time=1.835..55.939
rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..9633.33 rows=1 width=8)
(actual time=32.163..49.174 rows=0 loops=3)
         Filter: (id = 10000)
         Rows Removed by Filter: 333333
 Planning Time: 0.194 ms
 Execution Time: 55.955 ms

Is it expected behavior or bug?

Kind Regards,
Maxim