From: | Grzegorz Grabek <grzegorz(dot)grabek(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | GIN index not working for integer[] if there is more then one column in table |
Date: | 2017-07-07 17:19:42 |
Message-ID: | CAMNzsHB1-z_Ykkxr5hYUF5337wkwE3MubSaYARAEB+9ZZzrxWw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
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 <@*
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)
*WORKS 2 - single integer column **with operator <@*
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-07-07 17:38:39 | Re: GIN index not working for integer[] if there is more then one column in table |
Previous Message | David G. Johnston | 2017-07-07 16:28:03 | Re: BUG #14737: Wrong PL/pgSQL behaviour |