pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 




view thread (4+ messages)  latest in thread

Message-ID: <001001c51f9b$f606cd40$6a01a8c0@KEN>
Permalink:  ../001001c51f9b$f606cd40$6a01a8c0@KEN/
Also on:    postgresql.org/message-id/001001c51f9b$f606cd40$6a01a8c0@KEN

 · 

reply

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