From: Hannu Krosing <hannu@tm.ee>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Roman Fail <rfail@posportal.com>
Cc: josh@agliodbs.com, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: 7.3.1 New install, large queries are slow
Date: 16 Jan 2003 12:54:58 +0000
Message-ID: <1042721698.2502.109.camel@huli> (raw)
In-Reply-To: <20030115192815.T98147-100000@megazone23.bigpanda.com>
References: <20030115192815.T98147-100000@megazone23.bigpanda.com>
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@tm.ee>
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: hannu@tm.ee, sszabo@megazone23.bigpanda.com, rfail@posportal.com
Subject: Re: 7.3.1 New install, large queries are slow
In-Reply-To: <1042721698.2502.109.camel@huli>
References: <20030115192815.T98147-100000@megazone23.bigpanda.com> <1042721698.2502.109.camel@huli>
* 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