pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roman Fail <rfail@posportal.com>
To: pgsql-performance@postgresql.org
Subject: Re: 7.3.1 New install, large queries are slow
Date: Mon, 20 Jan 2003 14:33:25 -0800
Message-ID: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.com> (raw)

> Jochem van Dieten wrote:
> Just out of curiosity and for archiving purposes, could you post the new
> EXPLAIN ANALYZE output to the list?

To reiterate, the batchdetail table is 24 million rows, batchheader is 2.7 million, and purc1 is 1 million.  The rest are 2000 rows or less.  I think having the 6-disk RAID-10 devoted to /usr/local/pgsql/data helps out a little here.   I did try changing the WHERE clauses to radically different values and it was still just as fast.  This is the original query I was working with (plus suggested modifications from the list):

EXPLAIN ANALYZE
SELECT ss.batchdate, ss.batchdetailid, ss.bankno, ss.trandate, ss.tranamount,
ss.submitinterchange, ss.authamount, ss.authno, ss.cardtypeid, ss.mcccode,
ss.name AS merchantname, ss.cardtype, ss.merchid,
p1.localtaxamount, p1.productidentifier, dr.avsresponse,
cr.checkoutdate, cr.noshowindicator, ck.checkingacctno,
ck.abaroutingno, ck.checkno
FROM
  (SELECT b.batchdate, d.batchdetailid, t.bankno, d.trandate, d.tranamount,
       d.submitinterchange, d.authamount, d.authno, d.cardtypeid, d.mcccode,
       m.name, c.cardtype, m.merchid
   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
ORDER BY ss.batchdate DESC
LIMIT 50

Limit  (cost=1351.93..1351.93 rows=1 width=261) (actual time=5.34..5.36 rows=8 loops=1)
  ->  Sort  (cost=1351.93..1351.93 rows=1 width=261) (actual time=5.33..5.34 rows=8 loops=1)
        Sort Key: b.batchdate
        ->  Nested Loop  (cost=0.01..1351.92 rows=1 width=261) (actual time=1.61..5.24 rows=8 loops=1)
              ->  Hash Join  (cost=0.01..1346.99 rows=1 width=223) (actual time=1.58..5.06 rows=8 loops=1)
                    Hash Cond: ("outer".batchdetailid = "inner".batchdetailid)
                    ->  Hash Join  (cost=0.00..1346.98 rows=1 width=210) (actual time=1.21..4.58 rows=8 loops=1)
                          Hash Cond: ("outer".batchdetailid = "inner".batchdetailid)
                          ->  Nested Loop  (cost=0.00..1346.97 rows=1 width=201) (actual time=0.82..4.05 rows=8 loops=1)
                                ->  Nested Loop  (cost=0.00..1343.84 rows=1 width=182) (actual time=0.78..3.82 rows=8 loops=1)
                                      Join Filter: ("inner".cardtypeid = "outer".cardtypeid)
                                      ->  Nested Loop  (cost=0.00..1342.62 rows=1 width=172) (actual time=0.74..3.35 rows=8 loops=1)
                                            ->  Nested Loop  (cost=0.00..539.32 rows=4 width=106) (actual time=0.17..1.61 rows=26 loops=1)
                                                  ->  Nested Loop  (cost=0.00..515.48 rows=5 width=94) (actual time=0.13..1.01 rows=26 loops=1)
                                                        ->  Index Scan using merchants_ix_merchid_idx on merchants m  (cost=0.00..5.65 rows=1 width=78) (actual time=0.07..0.08 rows=1 loops=1)
                                                              Index Cond: (merchid = '701252267'::character varying)
                                                        ->  Index Scan using batchheader_ix_merchantid_idx on batchheader b  (cost=0.00..508.56 rows=20 width=16) (actual time=0.04..0.81 rows=26 loops=1)
                                                              Index Cond: ("outer".merchantid = b.merchantid)
                                                              Filter: (batchdate > '2002-12-15'::date)
                                                  ->  Index Scan using tranheader_pkey on tranheader t  (cost=0.00..5.08 rows=1 width=12) (actual time=0.01..0.01 rows=1 loops=26)
                                                        Index Cond: (t.tranheaderid = "outer".tranheaderid)
                                                        Filter: (clientid = 6)
                                            ->  Index Scan using batchdetail_ix_batchid_idx on batchdetail d  (cost=0.00..186.81 rows=2 width=66) (actual time=0.05..0.06 rows=0 loops=26)
                                                  Index Cond: (d.batchid = "outer".batchid)
                                                  Filter: ((tranamount >= 500.0) AND (tranamount <= 700.0))
                                      ->  Seq Scan on cardtype c  (cost=0.00..1.10 rows=10 width=10) (actual time=0.00..0.03 rows=10 loops=8)
                                ->  Index Scan using purc1_ix_batchdetailid_idx on purc1 p1  (cost=0.00..3.12 rows=1 width=19) (actual time=0.01..0.01 rows=0 loops=8)
                                      Index Cond: (p1.batchdetailid = "outer".batchdetailid)
                          ->  Hash  (cost=0.00..0.00 rows=1 width=9) (actual time=0.00..0.00 rows=0 loops=1)
                                ->  Seq Scan on direct dr  (cost=0.00..0.00 rows=1 width=9) (actual time=0.00..0.00 rows=0 loops=1)
                    ->  Hash  (cost=0.00..0.00 rows=1 width=13) (actual time=0.01..0.01 rows=0 loops=1)
                          ->  Seq Scan on carrental cr  (cost=0.00..0.00 rows=1 width=13) (actual time=0.00..0.00 rows=0 loops=1)
              ->  Index Scan using checks_ix_batchdetailid_idx on checks ck  (cost=0.00..4.92 rows=1 width=38) (actual time=0.01..0.01 rows=0 loops=8)
                    Index Cond: (ck.batchdetailid = "outer".batchdetailid)
Total runtime: 5.89 msec




view thread (66+ messages)  latest in thread

Message-ID: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.com>
Permalink:  ../9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.com/
Also on:    postgresql.org/message-id/9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.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: rfail@posportal.com
  Subject: Re: 7.3.1 New install, large queries are slow
  In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.com>
  References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.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