Re: 7.3.1 New install, large queries are slow - Mailing list pgsql-performance

> 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
are2000 rows or less.  I think having the 6-disk RAID-10 devoted to /usr/local/pgsql/data helps out a little here.   I
didtry changing the WHERE clauses to radically different values and it was still just as fast.  This is the original
queryI 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=8loops=1)
 
                                            ->  Nested Loop  (cost=0.00..539.32 rows=4 width=106) (actual
time=0.17..1.61rows=26 loops=1)
 
                                                  ->  Nested Loop  (cost=0.00..515.48 rows=5 width=94) (actual
time=0.13..1.01rows=26 loops=1)
 
                                                        ->  Index Scan using merchants_ix_merchid_idx on merchants m
(cost=0.00..5.65rows=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
batchheaderb  (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.08rows=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.81rows=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.03rows=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=0loops=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=0loops=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.01rows=0 loops=8)
 
                    Index Cond: (ck.batchdetailid = "outer".batchdetailid)
Total runtime: 5.89 msec