Re: Slow "not in array" operation

Wow! Thank you very much Jeff!! I am really grateful.

Thanks to the btree (instead of gin) the query now takes about 500ms
instead of 70s.

Il Mer 13 Nov 2019, 13:18 Jeff Janes <jeff(dot)janes(at)gmail(dot)com> ha scritto:

> On Wed, Nov 13, 2019 at 6:56 AM Marco Colli <collimarco91(at)gmail(dot)com>
> wrote:
>
>> > the answer is that is because it is a GIN index. Make the same index
>> only as btree, and you should get good performance as it can filter the
>> tags within a given project without visiting the table.
>>
>> Currently I have this GIN index:
>> "index_subscriptions_on_project_id_and_tags" gin (project_id, tags)
>> WHERE trashed_at IS NULL
>>
>>
> Multicolumn GIN indexes are nearly worthless IMO when one column is a
> scalar. You can use this index, but it won't be better than one just on
> "GIN (tags) trashed_at IS NULL". An N-column GIN index is mostly the same
> thing as N single column GIN indexes.
>
>
>> It uses the btree_gin extension and works perfectly for tag search,
>> except for the "NOT" operator. I don't understand why it doesn't use the
>> GIN index also for the "NOT" operator.
>>
>
> Because it can't. Tom already did a good job of describing that. Can you
> describe what steps you think an index should take to jump to the specific
> rows which fail to exist in an inverted index?
>
>
> The problem is that I cannot create the same index with BTree, because PG
>> doesn't support BTree on array :(
>>
>
> Sure it does. It can't jump to specific parts of the index based on the
> array containment operators, but it can use them for in-index filtering
> (but only if you can do an index-only scan). And really, that is probably
> all you need to get > 100x improvement.
>
> Are you getting an error when you try to build it? If so, what is the
> error?
>
> Cheers,
>
> Jeff
>
>>