PostgreSQL中ANY运算符能否使用GIN索引查询整数数组？

Great question! Let’s break this down clearly and practically:

Short Answer #

By default, no—your query select * from tree_nodes where :id = any(path) will not trigger a GIN index on the path array column. PostgreSQL’s query planner doesn’t automatically map the = ANY(array) expression to GIN index capabilities, even though the logic is equivalent to a "value exists in array" check.

Why This Happens #

GIN indexes are optimized for array operations that explicitly check membership or containment using operators like @> (contains) or && (overlap). While :id = any(path) does the same thing as verifying path contains :id, the planner doesn’t recognize this equivalence out of the box. Instead, it’ll fall back to a full table scan, where it unnests every array and checks each element individually—this gets slow fast for large datasets.

What You Can Do #

Since you already know the @> operator triggers the GIN index, let’s cover your options:

1. Use the equivalent @> operator (recommended)
   The query select * from tree_nodes where path @> ARRAY[:id]::int[] is logically identical to your = ANY version, but it will reliably use your existing GIN index. For example, with :id = 2, this becomes:

   select * from tree_nodes where path @> ARRAY[2]::int[];
   

   If you thought ANY was faster, that’s likely only true for small datasets where full table scans have negligible overhead. For larger tables, the GIN index will outperform the ANY approach by a huge margin.

2. Rework the query if you insist on ANY syntax
   If you’re set on keeping the = ANY structure, you can rewrite the query to explicitly play nice with the index, though this is redundant since the @> approach works perfectly. For example:

   select tn.*
   from tree_nodes tn
   where exists (
     select 1 from unnest(tn.path) elem where elem = :id
   );
   

   Even this might not trigger the GIN index automatically, so sticking with @> is still the smarter choice.

Verify with Execution Plans #

Always use EXPLAIN ANALYZE to confirm index usage. For your original = ANY query, you’ll see a full table scan:

Seq Scan on tree_nodes (cost=0.00..100.00 rows=10 width=40) (actual time=0.010..0.500 rows=10 loops=1)

For the @> version, you’ll see the GIN index being used:

Index Scan using idx_tree_nodes_path_gin on tree_nodes (cost=0.14..8.16 rows=1 width=40) (actual time=0.005..0.006 rows=1 loops=1)

Final Note #

Don’t prioritize syntax over performance here. The @> operator is the idiomatic, efficient way to check array membership in PostgreSQL when you want to leverage a GIN index, and it’s just as readable once you get used to it.

内容的提问来源于stack exchange，提问作者Bohdan Petrenko