Postgres index type for array column for ANY queries

Question

I have a Postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL / IS NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column)).

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the Postgres docs seem to only mention <, =, <= etc. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

EDIT: Since posting both of those answers have now been updated to no longer suggest that a B-tree index will work.

Create the index, then check the execution plan and you will know if the index is used. — user330315
– user330315, Commented May 15, 2022 at 19:21
Postgres is smart enough that for small databases it uses a full scan so when I do EXPLAIN on my small test database it just tells me it is going to do a full scan. I'd need to populate a large database to determine this so seems easier and more reliable to ask what the best option is. — Peter Gerdes
– Peter Gerdes, Commented May 15, 2022 at 19:52
To help using ANY (array contains) , you'd better : create index <index_name> on <table_name> using GIN (<column> gin__int_ops) To evaluate the usage, it's not hard to prepopulate enough data in the target table for a test. — SeanH
– SeanH, Commented May 15, 2022 at 21:41
I presume that gin__int_ops is only helpful if my arrays are null free arrays of integers (in this case they are actually arrays of strings). — Peter Gerdes
– Peter Gerdes, Commented May 19, 2022 at 21:34

Erwin Brandstetter · Accepted Answer · 2022-05-16 01:59:09Z

5

Here is a detailed explanation why the ANY construct with the indexed column to the right cannot tap into a GIN index (or any index, for that matter):