From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | Roman Fail <rfail(at)posportal(dot)com>, josh(at)agliodbs(dot)com, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: 7.3.1 New install, large queries are slow |
Date: | 2003-01-16 12:54:58 |
Message-ID: | 1042721698.2502.109.camel@huli |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 <hannu(at)tm(dot)ee>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2003-01-16 13:17:38 | Re: 7.3.1 New install, large queries are slow |
Previous Message | Ron Johnson | 2003-01-16 12:29:43 | Re: 7.3.1 New install, large queries are slow |