Postgres Pro
Downloads
Home   >   mailing lists

Re: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists - Mailing list pgsql-general

From otar shavadze
Subject Re: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists
Date
Msg-id CAG-jOyAPQYMviwungC8P_3j4Jgt_wExoK=piOmUPDDHv==9Khg@mail.gmail.com
Whole thread Raw
In response to Re: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
Tree view
Tried  

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

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.

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.

On Fri, Nov 11, 2016 at 12:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.janes@gmail.com> writes:
> On Thu, Nov 10, 2016 at 7:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you are using that contrib module, and it's capturing this operator
>> reference, that would probably explain the bad estimate.  You could
>> drop the extension if you're not depending on its other features, or you
>> could explicitly qualify the operator name ("operator(pg_catalog.@>)"),
>> or you could upgrade to 9.6 (don't forget to do ALTER EXTENSION ... UPDATE
>> afterwards).

> Isn't the operator determined at index build time?  If he doesn't want to
> update to 9.6, I think he would need to rebuild the index, removing
> the "gin__int_ops" specification.

The operator in the query isn't.  But yes, if he's using an index that's
built on the extension's opclass, he'd need to rebuild that too in order
to still use the index with the core @> operator.

                        regards, tom lane

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: pg_dumpall: could not connect to database "template1": FATAL:
Next
From: Aaron Lewis
Date:
Subject: Why is this query not using GIN index?