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

Re: Bad Query?? Extremely slow response

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Patrick Hatcher <PHatcher(at)macys(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Bad Query?? Extremely slow response
Date: 2002-03-08 11:35:41
Message-ID: 1015587341.618.21.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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?


In response to

pgsql-novice by date

Next:From: Bo LorentsenDate: 2002-03-08 12:23:49
Subject: Grand group rights ?
Previous:From: Masse JacquesDate: 2002-03-08 11:34:31
Subject: Re: Good tutorial wanted...

pgsql-sql by date

Next:From: rjb26Date: 2002-03-08 11:38:29
Subject: Help reqd: Copying char to date
Previous:From: rolf.ostvikDate: 2002-03-08 09:40:47
Subject: Re: PL/pgSQL Syntax Problem

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