Hi,
The postgres JDBC driver supports binding SQL arrays - created via java.sql.Connection.createArrayOf() - to prepared statements that use WHERE column = ANY(?).
I have been wondering if using WHERE pk = ANY(?) is more efficient than using WHERE pk IN (?, ?, …) for batch queries, since ANY(?) can be used with arrays of arbitrary lengths, and therefore requires a single prepared statement, whereas IN(?, ?, …) can potentially result in multiple prepared statements.
I’m aware that Hibernate tries to mitigate the problem of multiple prepared statements via BatchFetchStyle.PADDED. Still, it seems to me that using ANY(?) would be beneficial.
Any thoughts? Can Hibernate already take advantage of ANY(?) with some of the most recent postgres dialects? If not, would the Hibernate maintainers accept an enhancement merge request for using ANY(?) - and in that case, do they have any suggestions as to how to best implement that enhancement?
Thank you in advance,
Alessandro