Re: Why is GIN index slowing down my query?

AlexK987 <alex(dot)cue(dot)987(at)gmail(dot)com> writes:
>> 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]
>
>Well, that's pretty much going to suck given that data distribution.
>Since "1" is a member of every last entry, the GIN scan will end up
>examining every entry, and then rejecting all of them as not being
>true subsets of [1,15].

This is equivalent and fast:

explain analyze
WITH rare AS (
select * from talent
where skills @> array[15])
select * from rare
where skills @> array[1]
-- (with changed operator)

You might variate your query according to an additional table that keeps the occurrence count of all skills.
Not really pretty though.

regards,

Marc Mamin