Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 83AC8476226 for ; Wed, 15 Jan 2003 22:43:33 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 242C447667D for ; Wed, 15 Jan 2003 22:40:00 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 8E30FD60D; Wed, 15 Jan 2003 19:40:04 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 83EA95C02; Wed, 15 Jan 2003 19:40:04 -0800 (PST) Date: Wed, 15 Jan 2003 19:40:04 -0800 (PST) From: Stephan Szabo To: Roman Fail Cc: , Subject: Re: 7.3.1 New install, large queries are slow In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BFD@pos_pdc.posportal.com> Message-ID: <20030115192815.T98147-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/128 X-Sequence-Number: 775 > 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 > AND b.batchdate > '2002-12-15' > AND m.merchid = '701252267' > ORDER BY b.batchdate DESC > LIMIT 50 Well, you might get a little help by replace the from with something like: FROM transheader t, batchheader b, merchants m, cardtype c, batchdetail d 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 and adding AND t.tranheaderid=b.tranheaderid AND m.merchantid=b.merchantid AND d.batchid=b.batchid AND c.cardtypeid=d.cardtypeid to the WHERE conditions. That should at least allow it to do some small reordering of the joins. I don't think that alone is going to do much, since most of the time seems to be on the scan of d. What does vacuum verbose batchdetail give you (it'll give an idea of pages anyway)