Re: Help with tuning this query (more musings)
От | Ken Egervari |
---|---|
Тема | Re: Help with tuning this query (more musings) |
Дата | |
Msg-id | 001001c51f9b$f606cd40$6a01a8c0@KEN обсуждение исходный текст |
Ответ на | Help with tuning this query ("Ken Egervari" <ken@upfactor.com>) |
Ответы |
Re: Help with tuning this query (more musings)
|
Список | pgsql-performance |
>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
В списке pgsql-performance по дате отправления: