Re: 7.3.1 New install, large queries are slow
От | Roman Fail |
---|---|
Тема | Re: 7.3.1 New install, large queries are slow |
Дата | |
Msg-id | 9B1C77393DED0D4B9DAA1AA1742942DA0E4C11@pos_pdc.posportal.com обсуждение исходный текст |
Ответ на | 7.3.1 New install, large queries are slow ("Roman Fail" <rfail@posportal.com>) |
Список | 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
В списке pgsql-performance по дате отправления: