Re: 7.3.1 New install, large queries are slow

> 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