I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3

I turned on logging for the SQL queries to test some code behavior.

So, assume 2 tables, OneToMany relation. In the code, this will have fields on the objects to represent it.

So for instance, class A will have a list of class B instances as a field.

Suppose I want to get those instances of class B, that are related to class A.

I could simply do return aInstance.bInstances

Or, I could write an Ebean function that asks for the ID of A and gets all the instances from the B table where A is present. The result is the same.

I did both, to see what SQL query Ebean would generate. And it does generate a different one.

The first, asking for the instances related to the object, will get Ebean to generate a statement with a left outer join based on the ID of the instance of A.

The second will do a select on the B table.

I'm wondering about the performance of this. On my test setup, which is fairly small, the left outer join is always faster. But I wonder about production databases that are constantly being updated. I did some googling in to the performance of left outer join and it's pretty much all bad things I've found.

So, what's the hidden cost here? Will left outer join works fast at first but mess things up later? Will the simple select statement prove more reliable, if a bit slower?

  • 3
    There is absolutely no problem with a LEFT [OUTER] JOIN, it's a common myth, I guess. Read a bit about the N+1 issue, too: stackoverflow.com/questions/97197/what-is-the-n1-selects-issue – dezso Apr 14 '15 at 9:45
  • 1
    Your question is unclear, because we neither know the actual table definition (\d tbl in psql - most of the rest is just talk, table definitions are the truth of the matter) nor do we know your actual task. What is the information you already have in your client and what exactly do you want to retrieve from the database. The best query depends on the missing information. – Erwin Brandstetter Apr 14 '15 at 21:23
  • @ErwinBrandstetter Can you provide an example of cases where table definitions have a very clear impact the performance of a left outer join vs a select? The task is just: get the data. It's a relational database and the data is set to be related. In a OneToMany relation. So one entry in table A is linked to multiple entries in table B. The task is: get all the relevant entries from table B, for a row in table A. And I'm wondering about the difference in performance of doing this with a select or left outer join. – KdgDev Apr 17 '15 at 12:24
  • 1
    Can you provide the missing information? – Erwin Brandstetter Apr 18 '15 at 21:04
  • @ErwinBrandstetter I don't see how there's any missing information. – KdgDev Apr 20 '15 at 9:55

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way:

SELECT * FROM b WHERE a_id = ?

If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't clarify).

SELECT a.a_id, b.*  -- often you don't need all columns
FROM   a
LEFT   JOIN b USING (a_id)
WHERE  a.some_column = ?;  -- not defined

One integrated query is typically faster than two separate queries. LEFT [OUTER] JOIN or [INNER] JOIN perform about the same here.

| improve this answer | |
  • My understanding is the OP can't control the join type because that is something that Ebean (a framework of some kind? I don't know) generates based on an instruction like return aInstance.bInstances. Nevertheless, I don't think there's a much clearer answer to give at this point. They never elaborated on what exactly the generated left join query looks like. – Andriy M Apr 21 '15 at 8:51
  • Huh, going over old stuff, I find that I wasn't as polite here as I could have been. Sorry about that. – KdgDev Feb 22 '19 at 18:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.