Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 782E1475F6A for ; Thu, 16 Jan 2003 12:16:26 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id ACEBB475BD7 for ; Thu, 16 Jan 2003 12:16:25 -0500 (EST) Received: from [63.195.55.98] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2) with HTTP id 2316163; Thu, 16 Jan 2003 09:16:33 -0800 From: "Josh Berkus" Subject: Re: 7.3.1 New install, large queries are slow To: Tom Lane , "Roman Fail" Cc: "Josh Berkus" , sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.4.0.2 Date: Thu, 16 Jan 2003 09:16:33 -0800 Message-ID: In-Reply-To: <14736.1042730017@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/159 X-Sequence-Number: 806 Roman, Tom: > No no no ... this is even worse than before. Your big tables are > batchdetail (d) and purc1 (p1). What you've got to do is arrange the > computation so that those are trimmed to just the interesting records > as > soon as possible. When joining disproportionally large tables, I've also had some success with the following method: SELECT b.batchdate, d.batchdetailid, t.bankno, d.trandate, d.tranamount, d.submitinterchange, d.authamount, d.authno, d.cardtypeid, d.mcccode, m.name AS merchantname, c.cardtype, m.merchid, p1.localtaxamount, p1.productidentifier, dr.avsresponse, cr.checkoutdate, cr.noshowindicator, ck.checkingacctno, ck.abaroutingno, ck.checkno FROM tranheader t JOIN batchheader b ON (t.tranheaderid = b.tranheaderid AND b.batchdate > '2002-12-15') JOIN merchants m ON (m.merchantid = b.merchantid AND mmerchid = '701252267') JOIN batchdetail d ON (d.batchid = b.batchid AND d.tranamount BETWEEN 500 and 700) JOIN cardtype c ON d.cardtypeid = c.cardtypeid LEFT JOIN purc1 p1 ON p1.batchdetailid = d.batchdetailid LEFT JOIN direct dr ON dr.batchdetailid = d.batchdetailid LEFT JOIN carrental cr ON cr.batchdetailid = d.batchdetailid LEFT JOIN checks ck ON ck.batchdetailid = d.batchdetailid WHERE t.clientid = 6 AND d.tranamount BETWEEN 500.0 AND 700.0 AND b.batchdate > '2002-12-15' AND m.merchid = '701252267' ORDER BY b.batchdate DESC LIMIT 50 This could be re-arranged some, but I think you get the idea ... I've been able, in some queries, to get the planner to use a better and faster join strategy by repeating my WHERE conditions in the JOIN criteria. -Josh