From: Ken Egervari <ken@upfactor.com>
To: Ken Egervari <ken@upfactor.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with tuning this query (more musings)
Date: Wed, 2 Mar 2005 21:51:55 -0500
Message-ID: <001001c51f9b$f606cd40$6a01a8c0@KEN> (raw)
References: <001001c51ef4$3849eeb0$6a01a8c0@KEN>
<1109787227.31084.12.camel@localhost.localdomain>
<000901c51f55$aa4b7400$6a01a8c0@KEN>
<1109789394.31084.20.camel@localhost.localdomain>
<000601c51f63$63c8bb10$6a01a8c0@KEN>
<13490.1109795370@sss.pgh.pa.us>
<000801c51f67$c3abdcc0$6a01a8c0@KEN>
<14444.1109802588@sss.pgh.pa.us>
<001a01c51f8f$32bba5b0$6a01a8c0@KEN>
<000401c51f9b$219ebbd0$6a01a8c0@KEN>
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ken@upfactor.com
Subject: Re: Help with tuning this query (more musings)
In-Reply-To: <001001c51f9b$f606cd40$6a01a8c0@KEN>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox