CODEWITHSUNDEEP
javahibernatepostgresqljpajpql
vect
vect

Reputation: 665

What method of joining entities is faster?

Here are two queries retrieving equivalent data:

SELECT DISTINCT packStatus
  FROM PackStatus packStatus JOIN FETCH packStatus.vars, ProcessEntity requestingProcess
    WHERE
      packStatus.status.code='OGVrquestExec'
      AND packStatus.procId=requestingProcess.activityRequestersProcessId
      AND requestingProcess.id='1000323733_GU_OGVProc'

SELECT DISTINCT packStatus
  FROM PackStatus packStatus JOIN FETCH packStatus.vars
    WHERE
      packStatus.status.code='OGVrquestExec'
      AND packStatus.procId=(SELECT requestingProcess.activityRequestersProcessId FROM ProcessEntity requestingProcess WHERE requestingProcess.id='1000323733_GU_OGVProc')

These queries differ in the method how requstingProcess is joined with packStatus. In general, which of these two methods is more preferable in terms of performance? I'm using JPA 1.2 provided by Hibernate 3.3 on Postgres 8.4.

UPD: I've replaced fake queries with real queries from my app. Here is SQL generated by Hibernate for first and second query. Links to query plans: first, second. Query plans look pretty the same. The only difference is what moment data from bpms_process table is aggregated to query result at. But I don't know is it right to generalize these results? Would query plans be almost the same for queries differing only in joining method? Is it possible to get a big difference in query cost by changing joining method?

Upvotes: 0

Views: 70

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324325

Use EXPLAIN ANALYZE and see.

I won't be surprised if they get turned into the same query plan.

See: https://stackoverflow.com/tags/postgresql-performance/info

Upvotes: 2

Related Questions