I have 2 entities: parent (id integer, ...) and child(id integer, parent_id integer, ...)
I have JPA Specification that build query in method
toPredicate(Root<Child> root, CriteriaQuery<?> query, CriteriaBuilder cb). This query looks like
select ch.*
from child ch
inner join parent pr on ch.parent_id = pr.id
where ch.field1 = ?
and ch.field2 = ?
and pr.field1 = ?
... other simple conditions
order by field
limit 50 offset 750000
This query works 18 seconds, index doesn't work. But I run sql-query like
select ch.*
from (select *
from child
where ch.field1 = ?
and ch.field2 = ?
and ...) as ch
inner join (select *
from parent
where pr.field1 = ?
and ...) pr on ch.parent_id = pr.id
where
... other simple conditions
order by field
limit 50 offset 750000
This query works 3 sec.
Question:
Can I realize the second query in method toPredicate(...) ?
Environment
- PostgreSQL 9.6
- Spring Data JPA 1.11.11.RELEASE
- Spring 4.3.16.RELEASE
- Hibernate 5.2.2.Final