X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 18767566A8 for ; Thu, 3 Mar 2005 04:30:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09739-06 for ; Thu, 3 Mar 2005 04:30:25 +0000 (GMT) Received: from mail.coretech.co.nz (coretech.co.nz [202.36.204.41]) by svr1.postgresql.org (Postfix) with ESMTP id 73024566A0 for ; Thu, 3 Mar 2005 04:30:23 +0000 (GMT) Received: (qmail 20376 invoked from network); 3 Mar 2005 04:28:47 -0000 Received: from 218-101-13-54.paradise.net.nz (HELO ?192.168.1.11?) (218.101.13.54) by 0 with SMTP; 3 Mar 2005 04:28:47 -0000 Message-ID: <422692D8.2030002@coretech.co.nz> Date: Thu, 03 Mar 2005 17:30:16 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.8b) Gecko/20050301 MIME-Version: 1.0 To: Ken Egervari Cc: pgsql-performance@postgresql.org Subject: Re: Help with tuning this query References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> In-Reply-To: <001001c51ef4$3849eeb0$6a01a8c0@KEN> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/45 X-Sequence-Number: 10750 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 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 > 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).