From: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | GIN indexes on an = ANY(array) clause |
Date: | 2019-03-13 14:06:04 |
Message-ID: | CADkLM=ez70=KuNgZpPExWczFDK9dqE4woRP+QOJXrD_OrH+Q9A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
(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 (tourance(at)gmail(dot)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...).
From | Date | Subject | |
---|---|---|---|
Next Message | John Naylor | 2019-03-13 14:12:03 | Re: WIP: Avoid creation of the free space map for small tables |
Previous Message | Robert Haas | 2019-03-13 13:48:26 | Re: performance issue in remove_from_unowned_list() |