pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roman Fail <rfail@posportal.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: sszabo@megazone23.bigpanda.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.1 New install, large queries are slow
Date: Thu, 16 Jan 2003 10:13:37 -0500
Message-ID: <14736.1042730017@sss.pgh.pa.us> (raw)
In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C02@pos_pdc.posportal.com>
References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C02@pos_pdc.posportal.com>

"Roman Fail" <rfail@posportal.com> writes:
> SELECT ...
> FROM tranheader 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)
> WHERE t.tranheaderid=b.tranheaderid
> AND m.merchantid=b.merchantid
> AND d.batchid=b.batchid
> AND c.cardtypeid=d.cardtypeid
> AND t.clientid = 6
> AND d.tranamount BETWEEN 500.0 AND 700.0
> AND b.batchdate > '2002-12-15'
> AND m.merchid = '701252267'

No no no ... this is even worse than before.  Your big tables are
batchdetail (d) and purc1 (p1).  What you've got to do is arrange the
computation so that those are trimmed to just the interesting records as
soon as possible.  The constraint on d.tranamount helps, but after that
you proceed to join d to p1 *first*, before any of the other constraints
can be applied.  That's a huge join that you then proceed to throw away
most of, as shown by the row counts in the EXPLAIN output.

Note the parentheses I added above to show how the system interprets
your FROM clause.  Since dr,cr,ck are contributing nothing to
elimination of records, you really want them joined last, not first.

What would probably work better is

SELECT ...
FROM
  (SELECT ...
   FROM tranheader t, batchheader b, merchants m, cardtype c, batchdetail d
   WHERE t.tranheaderid=b.tranheaderid
   AND m.merchantid=b.merchantid
   AND d.batchid=b.batchid
   AND c.cardtypeid=d.cardtypeid
   AND t.clientid = 6
   AND d.tranamount BETWEEN 500.0 AND 700.0
   AND b.batchdate > '2002-12-15'
   AND m.merchid = '701252267') ss
  LEFT JOIN purc1 p1 on p1.batchdetailid=ss.batchdetailid
  LEFT JOIN direct dr ON dr.batchdetailid = ss.batchdetailid
  LEFT JOIN carrental cr ON cr.batchdetailid = ss.batchdetailid
  LEFT JOIN checks ck ON ck.batchdetailid = ss.batchdetailid

which lets the system get the useful restrictions applied before it has
to finish expanding out the star query.  Since cardtype isn't
contributing any restrictions, you might think about moving it into the
LEFT JOIN series too (although I think the planner will choose to join
it last in the subselect, anyway).

			regards, tom lane



view thread (66+ messages)  latest in thread

Message-ID: <14736.1042730017@sss.pgh.pa.us>
Permalink:  ../14736.1042730017@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/14736.1042730017@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, rfail@posportal.com, josh@agliodbs.com, sszabo@megazone23.bigpanda.com
  Subject: Re: 7.3.1 New install, large queries are slow
  In-Reply-To: <14736.1042730017@sss.pgh.pa.us>
  References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C02@pos_pdc.posportal.com> <14736.1042730017@sss.pgh.pa.us>

* 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