Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9CD794761AF for ; Thu, 16 Jan 2003 10:13:41 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id ABC7D475CEE for ; Thu, 16 Jan 2003 10:13:40 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0GFDc5u014737; Thu, 16 Jan 2003 10:13:38 -0500 (EST) To: "Roman Fail" Cc: "Josh Berkus" , sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org Subject: Re: 7.3.1 New install, large queries are slow In-reply-to: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C02@pos_pdc.posportal.com> References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C02@pos_pdc.posportal.com> Comments: In-reply-to "Roman Fail" message dated "Thu, 16 Jan 2003 01:03:43 -0800" Date: Thu, 16 Jan 2003 10:13:37 -0500 Message-ID: <14736.1042730017@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/147 X-Sequence-Number: 794 "Roman Fail" 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