Обсуждение: Planner not choosing GIN index
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?
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 asWHERE ARRAY[123456] <@ integer_array_columndid 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?
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,FloOn 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 asWHERE ARRAY[123456] <@ integer_array_columndid 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.
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,FloOn 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 asWHERE ARRAY[123456] <@ integer_array_columndid 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.