[BUGS] GIN index not working for integer[] if there is more then one columnin table
| От | Grzegorz Grabek |
|---|---|
| Тема | [BUGS] GIN index not working for integer[] if there is more then one columnin table |
| Дата | |
| Msg-id | CAMNzsHB1-z_Ykkxr5hYUF5337wkwE3MubSaYARAEB+9ZZzrxWw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: [BUGS] GIN index not working for integer[] if there is more then one column in table
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-bugs |
Hi,
WORKS 2 - single integer column with operator <@
I created GIN index on my table as :
CREATE INDEX my_table_my_column_idx
ON my_table
USING GIN((array[mycolumn]);
Column is integer type.
It works pefectly fine when i use "array[my_column]=array[50]" for example. But when i use diffrent operators @> <@ && it doesnt use index. I tried it on diffrent volums of data from 100 record to 100M records and it never worked.
Most strange thing that same data works fine when i change type of column from integer to bigint.
Few examples when it works, and when doesn't.
DOESN'T WORK - integer with another column with operator <@
Column is integer type.
It works pefectly fine when i use "array[my_column]=array[50]" for example. But when i use diffrent operators @> <@ && it doesnt use index. I tried it on diffrent volums of data from 100 record to 100M records and it never worked.
Most strange thing that same data works fine when i change type of column from integer to bigint.
Few examples when it works, and when doesn't.
DOESN'T WORK - integer with another column with operator <@
drop table if exists tmp_test;
create table tmp_test as
select generate_series(1,10000) a, 'bbb'::text pole;
create index tmp_test_a_idx
on tmp_test using btree(a);
create index tmp_test_a_arridx
on tmp_test using gin((array[a]));
EXPLAIN
select * from tmp_test where array[a] <@ (select array_agg(a.a) a from (select generate_series(80,85) a) a);
Seq Scan on tmp_test (cost=10000000017.52..10000000187.52 rows=50 width=36)
Filter: (ARRAY[a] <@ $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=17.51..17.52 rows=1 width=32)
-> Result (cost=0.00..5.01 rows=1000 width=4)
WORKS 1 - bigint with another column with operator <@
drop table if exists tmp_test;
create table tmp_test as
select generate_series(1,10000)::BIGINT a, 'bbb'::text pole;
create index tmp_test_a_idx
on tmp_test using btree(a);
create index tmp_test_a_arridx
on tmp_test using gin((array[a]));
EXPLAIN
select * from tmp_test where array[a] <@ (select array_agg(a.a) a from (select generate_series(80,85)::BIGINT a) a);
Bitmap Heap Scan on tmp_test (cost=29.91..86.77 rows=50 width=40)
Recheck Cond: (ARRAY[a] <@ $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=17.51..17.52 rows=1 width=32)
-> Result (cost=0.00..5.01 rows=1000 width=8)
-> Bitmap Index Scan on tmp_test_a_arridx (cost=0.00..12.38 rows=50 width=0)
Index Cond: (ARRAY[a] <@ $0)
drop table if exists tmp_test;
create table tmp_test as
select generate_series(1,10000) a;
create index tmp_test_a_idx
on tmp_test using btree(a);
create index tmp_test_a_arridx
on tmp_test using gin((array[a]));
EXPLAIN
select * from tmp_test where array[a] <@ (select array_agg(a.a) a from (select generate_series(80,85) a) a);
Bitmap Heap Scan on tmp_test (cost=187.82..357.82 rows=50 width=4)
Filter: (ARRAY[a] <@ $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=17.51..17.52 rows=1 width=32)
-> Result (cost=0.00..5.01 rows=1000 width=4)
-> Bitmap Index Scan on tmp_test_a_idx (cost=0.00..170.29 rows=10000 width=0)
WORKS 3 - integer with another column with operator =
drop table if exists tmp_test;
create table tmp_test as
select generate_series(1,100) a, 'bbb'::text pole;
create index tmp_test_a_idx
on tmp_test using btree(a);
create index tmp_test_a_arridx
on tmp_test using gin((array[a]));
EXPLAIN
select * from tmp_test where array[a] = (select array_agg(a.a) a from (select generate_series(80,80) a) a);
Bitmap Heap Scan on tmp_test (cost=25.53..29.54 rows=1 width=36)
Recheck Cond: (ARRAY[a] = $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=17.51..17.52 rows=1 width=32)
-> Result (cost=0.00..5.01 rows=1000 width=4)
-> Bitmap Index Scan on tmp_test_a_arridx (cost=0.00..8.01 rows=1 width=0)
Index Cond: (ARRAY[a] = $0)
Best regards,
Grzegorz Grabek
Grzegorz Grabek
В списке pgsql-bugs по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: [BUGS] GIN index not working for integer[] if there is more then one column in table