I'm attempting to implement keyset pagination for a postgresql database in a Java repository that uses QueryDSL / JPA to generate queries. The usual pattern is using the BooleanBuilder querydsl class to construct the queries then using repository.findAll with that query.
In my case, ideally the queries I'd like to generate would use the row constructor syntax, i.e.
select * from table
where (col1, col2) < (val1, val2)
order by col1 desc col2 desc offset n;
however, as far as I can tell this syntax is not supported by querydsl. To get around that, I converted the above query to its logical equivalent, which is
select * from table
where col1 < val1 or (col1 = val1 and col2 < val2)
order by col1 desc col2 desc offset n;
it turns out that with a joint index on (col1, col2), the row constructor syntax is vastly more performant than its logically equivalent syntax (on a table with 300k records, queries were around 5ms vs 175ms).
I'd prefer to use some kind of query building library to generate my queries, as there lots of other non required parameters that make writing native queries for each case untenable. Anyone know of a way to do that using something like the BooleanBuilder, or am I stuck with the less performant query dsl implementation or several gross native queries?