Are arrays optimized in jOOQ & PostgreSQL?

问题:

I have a large list of identifiers which I would like to add to the WHERE clause like this:

identifier IN (..., ..., ..., ...)

However, that is pretty slow, because it has to bind each value individually. Remember, the list is pretty long (almost 1000 values). In such case, it is better to use:

identifier = ANY({..., ..., ..., ...})

Now, we are only binding the array, just once.

I tried doing that in jOOQ:

Integer[] values = {..., ..., ..., ...}
DSL.any(DSL.array(values))

The following SQL is generated:

"identifier" = any (array[?, ?, ?, ...])
TRACE | 2017-08-24 10:02:08,914 | JooqLogger.java | 187 | Binding variable 1       : ...
TRACE | 2017-08-24 10:02:08,947 | JooqLogger.java | 187 | Binding variable 2       : ...
TRACE | 2017-08-24 10:02:08,958 | JooqLogger.java | 187 | Binding variable 3       : ...
...

So, this makes me conclude that we are still binding each value separately? Is there a way to optimize this?

回答1:

Replace:

DSL.any(DSL.array(values));

With:

DSL.any(values);

To create a single bind variable.

Alternatively, you could create that bind variable explicitly:

Field<Integer[]> array = DSL.val(values);
DSL.any(array);