Why is GIN index slowing down my query?

I've created a GIN index on an INT[] column, but it slows down the selects.
Here is my table:

create table talent(person_id INT NOT NULL,
skills INT[] NOT NULL);

insert into talent(person_id, skills)
select generate_series, array[0, 1] || generate_series
from generate_series(3, 1048575);

create index talent_skills on talent using gin(skills);

analyze talent;

Here is my select:


explain analyze
select * from talent
where skills <@ array[1, 15]

"Bitmap Heap Scan on talent  (cost=52.00..56.01 rows=1 width=37) (actual
time=590.022..590.022 rows=0 loops=1)"
"  Recheck Cond: (skills <@ '{1,15}'::integer[])"
"  Rows Removed by Index Recheck: 1048573"
"  Heap Blocks: exact=8739"
"  ->  Bitmap Index Scan on talent_skills  (cost=0.00..52.00 rows=1 width=0)
(actual time=207.661..207.661 rows=1048573 loops=1)"
"        Index Cond: (skills <@ '{1,15}'::integer[])"
"Planning time: 1.310 ms"
"Execution time: 590.078 ms"


If I drop my GIN index, my select is faster:


drop index talent_skills

explain analyze
select * from talent
where skills <@ array[1, 15]

"Seq Scan on talent  (cost=0.00..21846.16 rows=1 width=37) (actual
time=347.442..347.442 rows=0 loops=1)"
"  Filter: (skills <@ '{1,15}'::integer[])"
"  Rows Removed by Filter: 1048573"
"Planning time: 0.130 ms"
"Execution time: 347.470 ms"

Am I missing something?




--
View this message in context: http://postgresql.nabble.com/Why-is-GIN-index-slowing-down-my-query-tp5836319.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.