AlexK987 <alex.cue.987@gmail.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]. I'm not sure whether it'd be practical to
teach GIN about negative proofs, ie noticing that rows containing "0"
could be eliminated based on the index contents. But in any case it
does not know that today.
Another problem, at least with the default statistics target, is that
the entries for "0" and "1" swamp everything else so that the planner
doesn't know that eg "15" is really rare.
You'd be much better off if you could refactor the data representation so
that whatever you mean by "0" and "1" is stored separately from whatever
you mean by the other entries, ie, don't keep both extremely common and
extremely rare entries in the same array.
Also ... perhaps I'm reading too much into the name you chose for the
column, but I'm finding it hard to imagine why you'd care about the
performance of this query as opposed to "where skills @> array[1, 15]".
That is, wouldn't you typically be searching for people with at least
certain specified skills, rather than at most certain specified skills?
Another thing that maybe is a question for -hackers is why we consider
arraycontained to be an indexable operator at all. As this example
demonstrates, the current GIN infrastructure isn't really capable
of coping efficiently, at least not in the general case. It might be
all right in specific cases, but the planner doesn't have the smarts
to tell which is which.
regards, tom lane