Angelo.Hannes Angelo.Hannes - 1 month ago 16
Java Question

Performance issue with jooq generated query against postgres

I have performance problems with an jooq generated query that sould be equal to an normal string query. The jooq query looks like this:

return ctx.select(AT_TRAIL.POINT)
.from(AT_TRAIL)
.where(AT_TRAIL.ID.le(
ctx.select(AT_TRAIL.ID)
.from(
ctx.select(AT_TRAIL.ID, AT_TRAIL.POINT, field(
"point <-> ( " +
" select point " +
" from at_shelter " +
" where id = ( " +
" select at_shelter " +
" from at_last_shelter " +
" ) "+
")"
).as("dist"))
.from(AT_TRAIL)
.orderBy(field("dist").asc())
.limit(1)
)
))
.orderBy(AT_TRAIL.ID.asc())
.fetch()
.map(r -> {
PGpoint point = r.get(AT_TRAIL.POINT, PGpoint.class);
return ImmutableMap.of("lat", point.x, "lng", point.y);
});


And my plain string query looks like this

return ctx.fetch(
" select point " +
" from at_trail " +
" where id <= ( " +
" select id " +
" from ( " +
" select id, point, point <-> ( " +
" select point " +
" from at_shelter " +
" where id = ( " +
" select at_shelter " +
" from at_last_shelter " +
" ) " +
" ) as dist " +
" from at_trail " +
" order by dist asc " +
" limit 1 " +
" ) t " +
" ) " +
"order by id asc"
)
.map(r -> {
PGpoint point = r.get(AT_TRAIL.POINT, PGpoint.class);
return ImmutableMap.of("lat", point.x, "lng", point.y);
});


I compared the jooq generated query, with the other one. They differ in the table alias. jooq generates an
as "alias_108340908"
, while I only use
t
. And jooq fully quallifies column names and tables like
"public"."at_trail"."point"
. Otherwise the two queries are identical. However the query generated with jooq needs up to 30 seconds to complete, while the other needs only some milli seconds.
What is causing the performance issue? The qualification? And how do I disable it/speed up the query?

Answer

Your jOOQ query is wrong (assuming that your plain SQL query is right). Consider this:

return ctx.select(AT_TRAIL.POINT)
        .from(AT_TRAIL)
        .where(AT_TRAIL.ID.le(
                ctx.select(AT_TRAIL.ID) // This is the outer query's ID, not the local ID
                .from(...)
        ))
        .orderBy(AT_TRAIL.ID.asc())
        .fetch()

What you meant to write is this:

return ctx.select(AT_TRAIL.POINT)
        .from(AT_TRAIL)
        .where(AT_TRAIL.ID.le(
                ctx.select(field("id", AT_TRAIL.ID.getDataType())) // Better
                .from(...)
        ))
        .orderBy(AT_TRAIL.ID.asc())
        .fetch()

Now, of course you could simplify your original query instead, to make this easier. E.g. this seems to do the same thing:

Step 1: Remove one nested query by moving dist from SELECT to ORDER BY:

select point 
from at_trail 
where id <= ( 
  select id
  from at_trail 
  order by point <-> ( 
    select point 
    from at_shelter  
    where id = ( 
      select at_shelter 
      from at_last_shelter 
    ) 
  ) asc  
  limit 1 
) 
order by id asc

Step 2: Translate back to jOOQ

The above query would read as such:

ctx.select(AT_TRAIL.POINT)
   .from(AT_TRAIL)
   .where(AT_TRAIL.ID.le(
      select(AT_TRAIL.ID) // Now, no scoping problem anymore
     .from(AT_TRAIL)
     .orderBy(field("{0} <-> {1}", // jOOQ doesn't support this op, resorting to plain SQL
        AT_TRAIL.POINT,
        select(AT_SHELTER.POINT)
       .from(AT_SHELTER)
       .where(AT_SHELTER.ID.eq(
          select(AT_LAST_SHELTER.AT_SHELTER)
         .from(AT_LAST_SHELTER)
       ))
     ).asc())
     .limit(1)
   ))
   .orderBy(AT_TRAIL.ID.asc())
   .fetch();

Depending on what you're doing (I read this as finding the trail up until the closest point to the last shelter), this might be even more optimisable, but for the sake of this question, I think this is already pretty good.

Comments