Re: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Sat, Nov 12, 2016 at 5:33 PM, otar shavadze <oshavadze(at)gmail(dot)com> wrote:

> Tried
>
> OPERATOR(pg_catalog.@>)
>
>
> as Tom mentioned, but still, don't get fast performance when value does
> not existed in any array.
>

Did you build the correct index?

>
> Also "played" with many different ways, gin, gist indexes (gin with and
> without *gin__int_ops*) but, always, there was some situation, where
> search in array was slow.
>

Yes. There will always be some situation when the array search is slow.
Is that situation one that a specific person cares about? Hard to tell,
since you have not given us any additional useful information.

I don't know exactly, may be I am wrong, but what I understood after
> several day "trying", is that, I never will use arrays, with tables more
> than 500 000-1000 000 rows, because then searching in this array is
> somehow problematic.
>
> I rebuild my structure and added another table (instead of using array)
> and then used join's instead of searching in array.
>
> That's works perfectly, joining works fast as hell, even for several
> millions rows in each table.
>

"Properly" normalizing your data is a wonderful thing, no doubt about it,
if you are prepared to deal with the consequences of doing so. But not
everyone has that luxury. Which is why there is more than one way of doing
things.

Cheers,

Jeff