pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Roman Fail <rfail@posportal.com>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.1 New install, large queries are slow
Date: Wed, 15 Jan 2003 19:40:04 -0800 (PST)
Message-ID: <20030115192815.T98147-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BFD@pos_pdc.posportal.com>


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








view thread (66+ messages)  latest in thread

Message-ID: <20030115192815.T98147-100000@megazone23.bigpanda.com>
Permalink:  ../20030115192815.T98147-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20030115192815.T98147-100000@megazone23.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sszabo@megazone23.bigpanda.com, rfail@posportal.com, josh@agliodbs.com
  Subject: Re: 7.3.1 New install, large queries are slow
  In-Reply-To: <20030115192815.T98147-100000@megazone23.bigpanda.com>
  References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BFD@pos_pdc.posportal.com> <20030115192815.T98147-100000@megazone23.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox