Performance issue with jooq generated query against postgres

Question

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?

Wow, if qualification has this kind of effect in PostgreSQL, that would be devastating. You can prevent that generated alias by calling Select.asTable("t") for instace. The full qualification (schema) can be avoided by specifying Setting.renderSchema for instance. Will it help? Also, what are the generated execution plans (run EXPLAIN SELECT ... in PostgreSQL) — Lukas Eder
– Lukas Eder, Commented Nov 1, 2016 at 12:36
@LukasEder I got the error. In line 4 of the query. I replaced it with ctx.select(field("id", Integer.class)) and it worked. Apparently the qualification is wrong at this point, because it is the id of the temporary table. (I guess) — Angelo.Hannes
– Angelo.Hannes, Commented Nov 1, 2016 at 13:51
Oh yes of course :) So, by fully qualifying, you referenced the ID column from the outer table instead of referencing the local t.ID. This happened to me recently in an ordinary SQL statement. Do you want to answer your own question or shall I write up an answer? — Lukas Eder
– Lukas Eder, Commented Nov 1, 2016 at 14:28
I only got it with your help, so I'll accept your answer, if you write one. Maybe you have an idea to simplify the query. E.g. the second query is just for projection, can it be avoided? — Angelo.Hannes
– Angelo.Hannes, Commented Nov 1, 2016 at 15:44

Lukas Eder · Accepted Answer · 2016-11-01 18:54:01Z

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.

"I read this as finding the trail up until the closest point to the last shelter" - I like it how descriptive SQL is.

Collectives™ on Stack Overflow

Performance issue with jooq generated query against postgres

1 Answer 1

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

Step 2: Translate back to jOOQ

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

Step 2: Translate back to jOOQ

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

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