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?
Flo Rance (tourance@gmail.com) was nice enough to show that yes, this is expected behavior.
Which leaves the questions: - is the transformation I made is algebraically correct in a general case? - if so, could we have the planner do that automatically when in the presence of a matching GIN index?
This seems like it might tie in with the enforcement of foreign keys within an array thread (which I can't presently find...).