Re: Why is GIN index slowing down my query?

Поиск
Список
Период
Сортировка
От Marc Mamin
Тема Re: Why is GIN index slowing down my query?
Дата
Msg-id B6F6FD62F2624C4C9916AC0175D56D8828B5A07A@jenmbs01.ad.intershop.net
обсуждение исходный текст
Ответ на Re: Why is GIN index slowing down my query?  (Marc Mamin <M.Mamin@intershop.de>)
Список pgsql-performance
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].
>
>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.

I wonder if in such cases, the Bitmap Index Scan could discard entries that would result in a table scan
and use them only in the recheck part:

explain
 select * from talent
 where skills @> array[1]

 Seq Scan on talent  (cost=0.00..21846.16 rows=1048573 width=37)
   Filter: (skills @> '{1}'::integer[])


В списке pgsql-performance по дате отправления:

Предыдущее
От: Marc Mamin
Дата:
Сообщение: Re: Why is GIN index slowing down my query?
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: working around JSONB's lack of stats?