I have to perform a query with a huge IN clause (about 1000 values). For example, in SQL it would look like
WHERE "entity"."page" IN (1, 2, 3, 4, 5,...)
In Postgres it seems to be a lot faster when you use the VALUES function in the IN clause like this:
WHERE "entity"."page" IN (VALUES (1), (2), (3), (4), (5),...)
But I can't figure out how to create such a query in JOOQ. Is that even possible?
Unfortunately I can't do anything about the data model - it's a legacy application.
The VALUES() constructor can't be used as a top level query type yet, in jOOQ, see issue #5871. You can only use it in the FROM clause, via:
.where(ENTITY.PAGE.in(selectFrom(values(row(1), row(2), ...))))
Maybe that produces the desired speedup as well for you? Alternatively, use arrays as shown in this article here. It shows that for an older PG version (9.5), arrays start outperforming IN lists at around size 64 in a simple benchmark. You may find a different number on your environment, but for 1000 values, it should be better indeed.
This question shows how to use an array in jOOQ:
.where(ENTITY.PAGE.eq(any(1, 2, 3)))
I'd generally prefer a single array bind variable over a variable length dynamic SQL string, especially if PostgreSQL ever decides to implement a more sophisticated execution plan cache, in case of which it will be imperative to prefer less dynamic SQL strings.