Postgres Professional
   

Re: Help with tuning this query

From: Mark Kirkwood
Subject: Re: Help with tuning this query
Date: ,
Msg-id: 422692D8.2030002@coretech.co.nz
(view: Whole thread, Raw)
In response to: Help with tuning this query  ("Ken Egervari")
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, ) 
Ken Egervari wrote:
> I've tried to use Dan Tow's tuning method and created all the right indexes from his diagraming method, but the query
stillperforms quite slow both inside the application and just inside pgadmin III.  Can anyone be kind enough to help me
tuneit so that it performs better in postgres?  I don't think it's using the right indexes, or maybe postgres needs
specialtreatment. 
>
> I've converted the below query to SQL from a Hibernate query, so the syntax is probably not perfect but it's
semanticsare exactly the same.  I've done so by looking at the source code, but I can't run it to get the exact SQL
sinceI 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
>

You might be able to coerce the planner to drive off person by
rearranging the join orders, plus a few other bits... hopefully I have
not brutalized the query to the point where it does not work :-)  :

select p.id, s*, ss.*
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
     inner join shipment s on s.carrier_code_id = cc.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 between current_date - 31 and current_date
order by cs.date desc


I have added the 'p.id' in the select list in the hope that that might
encourage the planner to take seriously the idea of getting the person
row(?) first. In addition I made 9 a number and closed the inequality
(just in case it helps a bit).

pgsql-performance by date:

Previous:
From: "Ken Egervari"
Date:
Subject: Re: Help with tuning this query (with explain analyze finally)
Next:
From: Jeremiah Jahn
Date:
Subject: name search query speed