Help with tuning this query

From: Ken Egervari
Subject: Help with tuning this query
Date: ,
Msg-id: 001001c51ef4$3849eeb0$6a01a8c0@KEN
(view: Whole thread, Raw)
Responses: Re: Help with tuning this query  (Richard Huxton)
Re: Help with tuning this query  (John Arbash Meinel)
Re: Help with tuning this query  (Ragnar Hafstað)
Re: Help with tuning this query  (Mark Kirkwood)
Re: Help with tuning this query  (Josh Berkus)
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've tried to use Dan Tow's tuning method and created all the right indexes from his diagraming method, but the query still performs quite slow both inside the application and just inside pgadmin III.  Can anyone be kind enough to help me tune it so that it performs better in postgres?  I don't think it's using the right indexes, or maybe postgres needs special treatment.
 
I've converted the below query to SQL from a Hibernate query, so the syntax is probably not perfect but it's semantics are exactly the same.  I've done so by looking at the source code, but I can't run it to get the exact SQL since I don't have the database on my home machine.
 
select s.*
from shipment s
    inner join carrier_code cc on s.carrier_code_id = cc.id
    inner join carrier c on cc.carrier_id = c.id
    inner join carrier_to_person ctp on ctp.carrier_id = c.id
    inner join person p on p.id = ctp.person_id
    inner join shipment_status cs on s.current_status_id = cs.id
    inner join release_code rc on cs.release_code_id = rc.id
    left join shipment_status ss on ss.shipment_id = s.id
where
    p.id = :personId and
    s.is_purged = false and
    rc.number = '9' and
    cs is not null and
    cs.date >= current_date - 31
order by cs.date desc
Just assume I have no indexes for the moment because while some of the indexes I made make it work faster, it's still around 250 milliseconds and under heavy load, the query performs very badly (6-7 seconds).
 
For your information:
 
shipment contains 40,000 rows
shipment_status contains 80,000 rows
release_code contains 8 rows
person contains 300 rows
carrier contains 60 rows
carrier_code contains 70 rows
 
The filter ratios are:
 
rc.number = '9' (0.125)
cs.date >= current_date - 31 (.10)
p.id = ? (0.003)
s.is_purged = false (.98)
 
I really hope someone can help since I'm pretty much stuck.
 
Best regards and many thanks,
Ken

pgsql-performance by date:

From: Richard Huxton
Date:
Subject: Re: Help with tuning this query
From: Bricklen Anderson
Date:
Subject: Re: Help with tuning this query