left join is for eager loading so that I don't have to run a seperate query to fetch the children for each shipment. This really does improve performance because otherwise you'll have to make N+1 queries to the database, and that's just too much overhead.
are you saying that you are actually doing a select s.*,ss.* ... ?
Yes, this is how the SQL should be written. When I manually converted the query, I forgot to include this detail. In hibernate, you don't need to specifiy the ss.* because you are dealing with objects, so you just say shipment. The ss.* is indicated in the "fetch" part of the Hibernate query. That was my mistake.
it might help the planner estimate better the number of cs rows affected. whether this improves performance depends on whether the best plans are sensitive to this.
This sounds like a good idea since cs rows are quite large. shipment and shipment_status are the largest tables in the database and they will grow very large over time.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
