Planner not choosing GIN index

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Planner not choosing GIN index

Corey Huinker
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?
Reply | Threaded
Open this post in threaded view
|

Re: Planner not choosing GIN index

Flo Rance
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 <[hidden email]> 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?
Reply | Threaded
Open this post in threaded view
|

Re: Planner not choosing GIN index

Corey Huinker
On Wed, Mar 13, 2019 at 5:11 AM Flo Rance <[hidden email]> 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 <[hidden email]> 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.

 
Reply | Threaded
Open this post in threaded view
|

Re: Planner not choosing GIN index

Flo Rance
Yep, honestly this is far beyond my knowledge.

On Wed, Mar 13, 2019 at 2:56 PM Corey Huinker <[hidden email]> wrote:
On Wed, Mar 13, 2019 at 5:11 AM Flo Rance <[hidden email]> 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 <[hidden email]> 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.