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 4184F55773 for ; Wed, 2 Mar 2005 08:58:09 +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 79387-05 for ; Wed, 2 Mar 2005 08:58:08 +0000 (GMT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id D75CD554DD for ; Wed, 2 Mar 2005 08:58:07 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 4.42) id 1D6PgF-000CYq-0c; Wed, 02 Mar 2005 08:58:08 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 3E6ED15976; Wed, 2 Mar 2005 08:58:06 +0000 (GMT) Message-ID: <4225801E.8010003@archonet.com> Date: Wed, 02 Mar 2005 08:58:06 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en 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.061 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/25 X-Sequence-Number: 10730 Ken Egervari wrote: > I've tried to use Dan Tow's tuning method Who? What? > 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. Hibernate is a java thing, no? It'd be helpful to have the actual SQL the hibernate class (or whatever) generates. One of the problems with SQL is that you can have multiple ways to get the same results and it's not always possible for the planner to convert from one to the other. Anyway, people will want to see EXPLAIN ANALYSE for the query in question. Obviously, make sure you've vacuumed and analysed the tables in question recently. Oh, and make sure yousay what version of PG you're running. > 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 1. Why are you quoting the 9 when checking against rc.number? 2. The "cs is not null" doesn't appear to be qualified - which table? > 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). 3. If you rewrite the "current_date - 31" as a suitable ago(31) function then you can use an index on cs.date 4. Are you familiar with the configuration setting "join_collapse_limit"? -- Richard Huxton Archonet Ltd