Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 313494766DE for ; Thu, 16 Jan 2003 06:05:11 -0500 (EST) Received: from localhost.localdomain (sein.itera.ee [194.126.109.126]) by postgresql.org (Postfix) with ESMTP id E0997476A10 for ; Thu, 16 Jan 2003 06:03:22 -0500 (EST) Received: (from hannu@localhost) by localhost.localdomain (8.11.6/8.11.6) id h0GCsxq02689; Thu, 16 Jan 2003 12:54:59 GMT X-Authentication-Warning: localhost.localdomain: hannu set sender to hannu@tm.ee using -f Subject: Re: 7.3.1 New install, large queries are slow From: Hannu Krosing To: Stephan Szabo Cc: Roman Fail , josh@agliodbs.com, "pgsql-performance@postgresql.org" In-Reply-To: <20030115192815.T98147-100000@megazone23.bigpanda.com> References: <20030115192815.T98147-100000@megazone23.bigpanda.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: Message-Id: <1042721698.2502.109.camel@huli> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 16 Jan 2003 12:54:58 +0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/138 X-Sequence-Number: 785 On Thu, 2003-01-16 at 03:40, Stephan Szabo wrote: > > So here's the query, and another EXPLAIN ANALYZE to go with it > > (executed after all setting changes). The same result columns and > > JOINS are performed all day with variations on the WHERE clause; other > > possible search columns are the ones that are indexed (see below). > > The 4 tables that use LEFT JOIN only sometimes have matching records, > > hence the OUTER join. > > > > EXPLAIN ANALYZE > > 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 > > INNER JOIN batchheader b ON t.tranheaderid = b.tranheaderid > > INNER JOIN merchants m ON m.merchantid = b.merchantid > > INNER JOIN batchdetail d ON d.batchid = b.batchid > > INNER 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 How much of data in d has tranamount BETWEEN 500.0 AND 700.0 ? Do you have an index on d.tranamount ? > > AND b.batchdate > '2002-12-15' again - how much of b.batchdate > '2002-12-15' ? is there an index > > AND m.merchid = '701252267' ditto > > ORDER BY b.batchdate DESC > > LIMIT 50 these two together make me think that perhaps b.batchdate between '2003-12-12' and '2002-12-15' could be better at making the optimiser see that reverse index scan on b.batchdate would be the way to go. > Well, you might get a little help by replace the from with -- Hannu Krosing