Re: Planner not choosing GIN index - Mailing list pgsql-performance

From Flo Rance
Subject Re: Planner not choosing GIN index
Date
Msg-id CAHogYcVz-PnuWJHnqGd5xoCGCqYBn+FY7G0f_v1zniz=jA3zVg@mail.gmail.com
Whole thread Raw
In response to Re: Planner not choosing GIN index  (Corey Huinker <corey.huinker@gmail.com>)
List pgsql-performance
Yep, honestly this is far beyond my knowledge.

On Wed, Mar 13, 2019 at 2:56 PM Corey Huinker <corey.huinker@gmail.com> wrote:
On Wed, Mar 13, 2019 at 5:11 AM Flo Rance <trourance@gmail.com> wrote:
It is an expected behavior. You can see the list of array operators with which a GIN index can be used in the doc:


And a very good and detailed explanation about any operator here:


Regards,
Flo

On Wed, Mar 13, 2019 at 2:44 AM Corey Huinker <corey.huinker@gmail.com> wrote:
A client had an issue with a where that had a where clause something like this:

WHERE 123456 = ANY(integer_array_column)

I was surprised that this didn't use the pre-existing GIN index on integer_array_column, whereas recoding as

WHERE ARRAY[123456] <@ integer_array_column

did cause the GIN index to be used. Is this a known/expected behavior? If so, is there any logical reason why we couldn't have the planner pick up on that?

Thanks. I'll bring the question of why-cant-we over to the hackers list.

 

pgsql-performance by date:

Previous
From: Corey Huinker
Date:
Subject: Re: Planner not choosing GIN index
Next
From: Adrien NAYRAT
Date:
Subject: Re: ERROR: found xmin from before relfrozenxid