Received: from heidegger.catalyst.net.nz (203-96-145-84.adsl.paradise.net.nz [203.96.145.84]) by postgresql.org (Postfix) with ESMTP id B112D4758F9 for ; Fri, 8 Mar 2002 06:36:01 -0500 (EST) Received: from 127.0.0.1 (ident=unknown) by heidegger.catalyst.net.nz with esmtp (MasqMail 0.1.16) id 16jIez-1gF-00; Sat, 09 Mar 2002 00:35:41 +1300 Subject: Re: Bad Query?? Extremely slow response From: Andrew McMillan To: Patrick Hatcher Cc: pgsql-novice@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 09 Mar 2002 00:35:41 +1300 Message-Id: <1015587341.618.21.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Archive-Number: 200203/89 X-Sequence-Number: 3216 On Fri, 2002-03-08 at 10:46, Patrick Hatcher wrote: > HEELLLLPPPPPPP. I have this query which ran less than 20 seconds on my > 500mhz MS SQL 2000 server with 192 megs ram. When I try to run this on my > Postgres box which has dual 750mhz with 500 mg ram, it takes 3+ mins. If I > run from PgAdminII, the app freezes. If I use a WHERE clause, data comes > back extremely fast. To help you, we probably need to know: 1) You have done a vacuum analyze on these tables. 2) What the structure of the tables is (indexes, etc). 3) The output of "SELECT versio()" 4) If version is 7.2 then EXPLAIN ANALYZE ... is a better choice. Off the top of my head it looks odd that PostgreSQL has chosen to do two index scans and a merge join, rather than doing sequential scans, since you are going for the full table. Unfortunately nothing presents itself off the top of my head, but someone else on this list may have some ideas. More information will hopefully help. Regards, Andrew. > > > SELECT c.itemnumber, c.mbmcolorcode, c.mbmsizedesc, c.mbmrange, c.upc, > c.isavailable, c.totaloh, sum(c.mcoh) AS mcoh, c.backorder, c.oo, c.cost, > c.retail, c.feddept, c.description, c.mbmdiv, c.mbmdept, c.mbmclass, sum > (((c.mcoh + c.oo) - c.backorder)) AS totalavailable, c.pending_picks, > c.pending_putaways, c.transfer_suspense, c.reserved, c.oo_lt_30_days, > c.oo_30_60_days, c.oo_gt_60_days, c.last_receipt, sum((c.totaloh - > (((c.pending_picks + c.transfer_suspense) + c.reserved) + c.backorder))) AS > avail, s.pid > FROM (cheshire_data c LEFT JOIN sku_non_inh s ON (((c.upc = s.upc) AND > (c.itemnumber = s.itemnumber)))) > GROUP BY c.itemnumber, c.mbmcolorcode, c.mbmsizedesc, c.mbmrange, c.upc, > c.cost, c.retail, c.feddept, c.description, c.mbmdiv, c.mbmdept, > c.mbmclass, c.totaloh, c.backorder, c.oo, c.isavailable, c.pending_picks, > c.pending_putaways, c.transfer_suspense, c.reserved, c.oo_lt_30_days, > c.oo_30_60_days, c.oo_gt_60_days, c.last_receipt, s.pid; > > SCAN>>> > Aggregate (cost=117164.97..130210.52 rows=18636 width=189) > -> Group (cost=117164.97..128812.78 rows=186365 width=189) > -> Sort (cost=117164.97..117164.97 rows=186365 width=189) > -> Merge Join (cost=0.00..55710.79 rows=186365 width=189) > -> Index Scan using xie2cheshire_dataitem on > cheshire_data c (cost=0.00..8003.01 rows=186365 width=161) > -> Index Scan using xie2sku_non_inhitm on sku_non_inh > s (cost=0.00..5774.53 rows=190048 width=28) > > > Any suggestions would be greatly appreciated > TIA > > Patrick Hatcher > Macys.Com > Legacy Integration Developer > 415-932-0610 office -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?