I took John's advice and tried to work with sub-selects. I tried this variation, which actually seems like it would make a difference conceptually since it drives on the person table quickly. But to my surprise, the query runs at about 375 milliseconds. I think it's because it's going over that shipment table multiple times, which is where the results are coming from.

I also made a version that runs over shipment a single time, but it's exactly 250 milliseconds. I guess the planner does the exact same thing.

select s.*, ss.*

from shipment s
inner join shipment_status ss on s.current_status_id=ss.id
inner join release_code rc on ss.release_code_id=rc.id
left outer join driver d on s.driver_id=d.id
left outer join carrier_code cc on s.carrier_code_id=cc.id
where s.carrier_code_id in (
 select cc.id
 from person p
  inner join carrier_to_person ctp on p.id=ctp.person_id
  inner join carrier c on ctp.carrier_id=c.id
  inner join carrier_code cc on cc.carrier_id = c.id
 where p.id = 355
and s.current_status_id is not null
and s.is_purged=false
and(rc.number='9' )
and(ss.date>=current_date-31 )

order by ss.date desc

---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to