Re: Help with tuning this query (more musings)

From: Ken Egervari
Subject: Re: Help with tuning this query (more musings)
Date: ,
Msg-id: 001001c51f9b$f606cd40$6a01a8c0@KEN
(view: Whole thread, Raw)
In response to: Help with tuning this query  ("Ken Egervari")
Responses: Re: Help with tuning this query (more musings)  (John A Meinel)
List: pgsql-performance

Tree view

Help with tuning this query  ("Ken Egervari", )
 Re: Help with tuning this query  (Richard Huxton, )
  Re: Help with tuning this query  (Bricklen Anderson, )
   Re: Help with tuning this query  (Bruce Momjian, )
 Re: Help with tuning this query  (John Arbash Meinel, )
 Re: Help with tuning this query  ("Ken Egervari", )
  Re: Help with tuning this query  (John Arbash Meinel, )
 Re: Help with tuning this query  (Ragnar Hafstað, )
 Re: Help with tuning this query  ("Ken Egervari", )
  Re: Help with tuning this query  (Ragnar Hafstað, )
 Re: Help with tuning this query  ("Ken Egervari", )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Tom Lane, )
   Re: Help with tuning this query (with explain analyze finally)  (John A Meinel, )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Tom Lane, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
 Re: Help with tuning this query (more musings)  ("Ken Egervari", )
  Re: Help with tuning this query (more musings)  (John A Meinel, )
 Re: Help with tuning this query (Some musings)  ("Ken Egervari", )
 Re: Help with tuning this query  (Mark Kirkwood, )
 Re: Help with tuning this query  (Josh Berkus, )
 Re: Help with tuning this query (more musings)  ("Ken Egervari", )
  Re: Help with tuning this query (more musings)  (Richard Huxton, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Richard Huxton, )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken", )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
   Re: Help with tuning this query (with explain analyze finally)  (John A Meinel, )

>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 by date:

From: Richard Huxton
Date:
Subject: Re: Help with tuning this query (more musings)
From: "Ken Egervari"
Date:
Subject: Re: Help with tuning this query (with explain analyze finally)