select s.* from shipment s inner join carrier_code cc on s.carrier_code_id = cc.id inner join carrier c on cc.carrier_id = c.id inner join carrier_to_person ctp on ctp.carrier_id = c.id inner join person p on p.id = ctp.person_id inner join shipment_status cs on s.current_status_id = cs.id inner join release_code rc on cs.release_code_id = rc.id left join shipment_status ss on ss.shipment_id = s.id where p.id = :personId and s.is_purged = false and rc.number = '9' and cs is not null and cs.date >= current_date - 31 order by cs.date desc ... shipment contains 40,000 rows shipment_status contains 80,000 rows
I may be missing something, but it looks like the second join on shipment_status (the left join) is not adding anything to your results, except more work. ss is not used for output, nor in the where clause, so what is its purpose ?
It does look strange doesn't it? I would think the same thing if it were the first time I looked at it. But rest assured, it's done by design. A shipment relates to many shipment_status rows, but only 1 is the current shipment_status for the shipment. The first does queries on the current status only and doesn't analyze the rest of the related items. The second 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. Since I need all the shipment_status children along with the shipment for the domain logic to work on them, I have to load them all.
On average, a shipment will have 2 shipment_status rows. So if the query selects 100 shipments, the query returns 200 rows. Hibernate is intelligent enough to map the shipment_status children to the appropriate shipment automatically.
if cs.date has an upper limit, it might be helpful to change the condition to a BETWEEN
Well, I could create an upper limit. It would be the current date. Would adding in this redundant condition improve performance? I've clustered the shipment table so that the dates are together, which has improved performance. I'm not sure adding in this implicit condition will speed up anything, but I will definately try it.
in any case, i would think you might need an index on shipment(carrier_code_id) shipment(current_status_id) shipment_status(id)
Unfortunately, I have indexes on all three (Postgres implicitly creates indexes for unique keys). Here are the other 2 that are already created:
CREATE INDEX shipment_carrier_code_id_idx ON shipment USING btree (carrier_code_id);
CREATE INDEX shipment_current_status_id_idx ON shipment USING btree (current_status_id);
So I guess we've been thinking the same thing. Don't get me wrong. These indexes speed up the query from 1.6 seconds to 250 milliseconds. I just need to be around 30 milliseconds.
Another idea that had occured to me was trying to force postgres to driver on the person table because that filter ratio is so great compared to everything else, but I do remember looking at the explain days ago and it was one of the last tables being filtered/joined. Is there anyway to force postgres to pick person? The reason I ask is because this would really reduce the number of rows it pulls out from the shipment table.
Thanks for comments. I'll try making that date explicit and change the query to use between to see if that does anything.
Regards and many thanks,
Ken
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
