Received: from fddlnint05.fds.com (fddlnint05.fds.com [208.15.91.52]) by postgresql.org (Postfix) with ESMTP id A755647587E for ; Thu, 7 Mar 2002 16:46:23 -0500 (EST) Subject: Bad Query?? Extremely slow response To: pgsql-novice@postgresql.org X-Mailer: Lotus Notes Release 5.0.6 December 14, 2000 Message-ID: From: "Patrick Hatcher" Date: Thu, 7 Mar 2002 13:46:15 -0800 X-MIMETrack: Serialize by Router on FDDLNINT05/FSG/SVR/FDD(Release 5.0.4 |June 8, 2000) at 03/07/2002 04:45:46 PM MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Archive-Number: 200203/82 X-Sequence-Number: 3209 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. 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