PostgreSQL supports the "ANY" SQL operator which is a really nice alternative to "IN" statements for us Java programmers that rely on JDBC/PreparedStatements. For example, instead of creating a long SQL string with thousands of IDs, we can do something like this:
final PreparedStatement statement = connection.prepareStatement(
"SELECT my_column FROM my_table where search_column = ANY (?)"
);
final Long[] values = getValues();
statement.setArray(1, connection.createArrayOf("bigint", values));
final ResultSet rs = statement.executeQuery();
I recently stumbled across an interesting article which claims to have a 100x improvement in executing "ANY" clauses by changing this:
"WHERE search_column = ANY (ARRAY[15368196, -- 11,000 other keys --)])"
To this:
"WHERE search_column = ANY (VALUES (15368196), -- 11,000 other keys --)"
Questions:
- Which syntax does the PostgreSQL JDBC driver use? I suspect the createArrayOf() method is creating an ARRAY[] vs VALUES() but I don't know how to verify this.
- If, in fact, the createArrayOf() method is creating an ARRAY[], what is the best approach for using the VALUES() instead?
I would prefer not to have to do something like this:
final PreparedStatement statement = connection.prepareStatement(
"SELECT my_column FROM my_table where search_column = ANY (VALUES (?), (?), (?), ...)"
);
A little more context/background for why I'm using "ANY" clauses can be found here: PreparedStatement IN clause alternatives?
Thanks in advance!
