Skip site navigation (1) Skip section navigation (2)

Bad Query?? Extremely slow response

From: "Patrick Hatcher" <PHatcher(at)macys(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Bad Query?? Extremely slow response
Date: 2002-03-07 21:46:15
Message-ID: OF65F0D774.4410E97A-ON88256B75.0075B7E0@fds.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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




Responses

pgsql-novice by date

Next:From: John TaylorDate: 2002-03-07 22:13:50
Subject: Re: starting the postmaster
Previous:From: BrianDate: 2002-03-07 20:08:29
Subject: starting the postmaster

pgsql-sql by date

Next:From: Charles HauserDate: 2002-03-07 21:53:35
Subject: ALTER CONSTRAINT FOREIGN KEY
Previous:From: Paul OgdenDate: 2002-03-07 21:01:29
Subject: Re: How to grant a privilege on all tables or views or both of a database to someone?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group