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 EE5615571D for ; Thu, 3 Mar 2005 02:52:37 +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 83599-01 for ; Thu, 3 Mar 2005 02:52:29 +0000 (GMT) Received: from fep6.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id EC06D53C92 for ; Thu, 3 Mar 2005 02:52:28 +0000 (GMT) Received: from windsortransportationclub.com (d57-59-156.home.cgocable.net [24.57.59.156]) by fep6.cogeco.net (Postfix) with SMTP id 32F9C8AF for ; Wed, 2 Mar 2005 21:52:32 -0500 (EST) Received: (qmail 31034 invoked from network); 3 Mar 2005 03:23:32 -0000 Received: from d36-80-110.home1.cgocable.net (HELO KEN) (24.36.80.110) by 0 with SMTP; 3 Mar 2005 03:23:32 -0000 Message-ID: <002a01c51f9c$1066d0e0$6a01a8c0@KEN> From: "Ken Egervari" To: 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> Subject: Re: Help with tuning this query (Some musings) Date: Wed, 2 Mar 2005 21:52:39 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.753 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200503/44 X-Sequence-Number: 10749 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. 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.id in ( select s.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 inner join shipment s on s.carrier_code_id = cc.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 *** Musing 1 Also, "s.current_status_id is not null" is an important filter that I forgot to mention. In this example where p.id = 355, it filters out 90% of the rows. In general, that filter ratio is 0.46 though, which is not quite so high. However, this filter gets better over time because more and more users will use a filter that will make this value null. It's still not as strong as person though and probably never will be. But I thought I'd mention it nonetheless. *** Musing 2 I do think that the filter "ss.date>=current_date-31" is slowing this query down. I don't think it's the mention of "current_date" or even that it's dynamic instead of static. I think the range is just too big. For example, if I use: and ss.date between '2005-02-01 00:00:00' and '2005-02-28 23:59:59' The query still results in 250 milliseconds. But if I make the range very small - say Feb 22nd of 2005: and ss.date between '2005-02-22 00:00:00' and '2005-02-22 23:59:59' Now the entire query runs in 47 milliseconds on average. If I can't make this query perform any better, should I change the user interface to select the date instead of showing the last 31 days to benefit from this single-day filter? This causes more clicks to select the day (like from a calendar), but most users probably aren't interested in seeing the entire listing anyway. However, it's a very important requirement that users know that shipment enteries exist in the last 31 days (because they are usually sure-fire problems if they are still in this query after a few days). I guess I'm wondering if tuning the query is futile and I should get the requirements changed, or is there something I can do to really speed it up? Thanks again, Ken