> 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)
