Rewrite JPA query to improve performence

Viewed 64

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
0 Answers
Related