Will GIN index be used with sql ANY operator in PostgreSql

Question

I'm wondering if PostgreSql will use GIN index for ANY operator with an integer array. Lets say I have a table tree_nodes which contains id with type int and path with type int[]. Simple example:

Will GIN index be used when I wrote a select select * from tree_nodes where :id = any(path) and, for example :id = 2.

I know, that in case of @> it will, but I believe, that in my case ANY operator will be more effective than @>

Check the execution plan

user330315
– user330315

2018-05-14 09:36:23 +00:00
Commented May 14, 2018 at 9:36 — user330315
– user330315, Commented May 14, 2018 at 9:36

Laurenz Albe · Accepted Answer · 2018-05-14 10:11:07Z

1

A GIN index cannot be used with =ANY.

What you can do is to use the <@ operator with a one-element array:

SELECT * FROM tree_nodes
WHERE ARRAY[$1] <@ path;

Here the GIN index can be used.

answered May 14, 2018 at 10:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bohdan Petrenko Over a year ago

Thank you. Really, there is no need to use ANY here

Collectives™ on Stack Overflow

Will GIN index be used with sql ANY operator in PostgreSql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related