GIN indexes on an = ANY(array) clause

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...).

Responses

Browse pgsql-hackers by date

  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()