Postgres Pro
Downloads
Home   >   mailing lists

[BUGS] GIN index not working for integer[] if there is more then one columnin table - Mailing list pgsql-bugs

From Grzegorz Grabek
Subject [BUGS] GIN index not working for integer[] if there is more then one columnin table
Date
Msg-id CAMNzsHB1-z_Ykkxr5hYUF5337wkwE3MubSaYARAEB+9ZZzrxWw@mail.gmail.com
Whole thread Raw
Responses Re: [BUGS] GIN index not working for integer[] if there is more then one column in table  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Tree view
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

pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [BUGS] BUG #14737: Wrong PL/pgSQL behaviour
Next
From: Tom Lane
Date:
Subject: Re: [BUGS] GIN index not working for integer[] if there is more then one column in table