(moving this over from pgsql-performance) 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 (toura...@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...).
