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