CODEWITHSUNDEEP
arrayspostgresqlindexing
Bohdan Petrenko
Bohdan Petrenko

Reputation: 1155

Will GIN index be used with sql ANY operator in PostgreSql

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:

enter image description here

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 @>

Upvotes: 0

Views: 963

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

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.

Upvotes: 1

Related Questions