pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17079: btree_gin and type coersion combination doesn't work
2+ messages / 2 participants
[nested] [flat]
* BUG #17079: btree_gin and type coersion combination doesn't work
@ 2021-07-01 17:39 PG Bug reporting form <noreply@postgresql.org>
2021-07-01 18:43 ` Re: BUG #17079: btree_gin and type coersion combination doesn't work Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2021-07-01 17:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: maxim.boguk@gmail.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17079: btree_gin and type coersion combination doesn't work
2021-07-01 17:39 BUG #17079: btree_gin and type coersion combination doesn't work PG Bug reporting form <noreply@postgresql.org>
@ 2021-07-01 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2021-07-01 18:43 UTC (permalink / raw)
To: maxim.boguk@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> There are simple case when type coercion work with common btree index but
> doesn't work with btree_gin.
btree_gin doesn't bother to cover any cross-type operators.
That could possibly be fixed, but I think it'd be a pain
since the applicable cross-type operators would vary for
each data type btree_gin covers.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-07-01 18:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-01 17:39 BUG #17079: btree_gin and type coersion combination doesn't work PG Bug reporting form <noreply@postgresql.org>
2021-07-01 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox