Hi,
>However, since in my application the number of values in the array can vary, I'm wondering if using ANY has the benefit of causing the driver/DB to cache a smaller number of prepared statement.
Right you are. In case you use Array, the whole thing is passed as a single bind, and it results in a single prepared statement.
Naive use of (?, ?, ?, ?, ?) would result in multiple prepared statements that would consume memory at both client and server sides.
1) Note: sometimes you might want to use (?, ?, ?) kind of syntax (e.g. to support DBs that cannot process arrays), then you might want to use "power of two" placeholders, and fill the excessive ones with null value or with a repetition of the last value.
For instance, if you want to pass 5 values 1,2,3,4,5 you'd better pass it as
(1,2,3,4,5,5,5,5) (of course, use ?,?,?,?, ?,?,?,? )
I do not advice you to use that extensively, however it is a nice to know trick.
2) As "array-based-SQL" is the same statement, you will get pretty much the same execution plan no matter how many values you put there.
I find that a plus since I do not want my application to flip to some nasty "table seq scan" as the number of values flips from 21 to 22 or whatever.
The execution plan might vary in case you use variable number of "?".
3) Of course, if you plan to move lots of data, then COPY and/or insert batch (+ reWriteBatchedInserts=true) might be your friends there.
4) Note: array support is limited, so currently you might fail to pass "array of composite type".
Vladimir
