CODEWITHSUNDEEP
javaarrayspostgresqljooq
Alex Dvoretsky
Alex Dvoretsky

Reputation: 948

How to make JOOQ to use arrays in the IN clause

I want JOOQ to generate ... in (?) and bind list as an array parameter (Postgres). My code looks like 

.where(
   Tables.TABLE.FIELD.in(idsList)
)
  1. How can I do this?
  2. Why it's not done by default since it's more efficient that generation (and parsing by PG) of the string in (?, ?, ?, ?, ...)

Upvotes: 5

Views: 4992

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

How can I do this?

You can use the DSL.any(T[]) operator, e.g.

TABLE.FIELD.eq(any(1, 2, 3))

This will bind only one array variable to the JDBC statement

Why it's not done by default since it's more efficient that generation (and parsing by PG) of the string

It might be worth thinking about a flag in Settings. I've registered a feature request for this: https://github.com/jOOQ/jOOQ/issues/6029

In general, jOOQ allows users to write exactly the SQL they want to send to the database server, so the automatic "optimisation" and rewriting of SQL might appear quite unexpected to some users. This is just a general rule of thumb in jOOQ. It's always worth thinking about optimisations and making them opt-in through Settings.

Caution about premature optimisation!

However, it's always important to actually measure these things. While there is certainly a bit less of a parsing and SQL generation overhead with your suggested approach, beware that an array's cardinality may be much harder to estimate correctly than a hard-wired IN list. Using an array for small lists can have negative effects on your execution plan. So, the few microseconds you're saving on the parser side will weigh against the few milliseconds (?) at the execution side!

I've benchmarked this in the following blog post: https://blog.jooq.org/sql-in-predicate-with-in-list-or-with-array-which-is-faster/

The IN list seems to consistently outperform the array version (in my specific benchmark case) until a length of around 50

Upvotes: 7

Related Questions