1

Support Array binding via ANY( ? ) ... for better hit ratio on query plans

Postgres has array binding via ANY. This can be used instead of IN clauses with many bind parameters.

 

e.g.  select id, name from customers where id = ANY(?) -- bind array of customer ids

This has the benefit when compared to IN depending on how the database matches SQL to query plans.

 

For example, with the 3 sql queries below:

select id, name from customers where id IN (?,?,?,?)

select id, name from customers where id IN (?,?)

select id, name from customers where id IN (?,?,?)

... can result in 3 different query plans.  If instead there is array binding via ANY then the above 3 queries can be written as the exact same SQL and hence can all use and share the same query plan. 

The benefit depends on how well the query planner can match SQL statements with different numbers of IN bind parameters to the same query plan (in which case the benefit this then only on client side prepared statement cache). 

 

Cheers, Rob.

1 comment

Please sign in to leave a comment.