pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@catalyst.net.nz>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: Bad Query??  Extremely slow response
Date: 09 Mar 2002 00:35:41 +1300
Message-ID: <1015587341.618.21.camel@kant.mcmillan.net.nz> (raw)
In-Reply-To: <OF65F0D774.4410E97A-ON88256B75.0075B7E0@fds.com>
References: <OF65F0D774.4410E97A-ON88256B75.0075B7E0@fds.com>

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?




view thread (3+ messages)

Message-ID: <1015587341.618.21.camel@kant.mcmillan.net.nz>
Permalink:  ../1015587341.618.21.camel@kant.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1015587341.618.21.camel@kant.mcmillan.net.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: andrew@catalyst.net.nz, PHatcher@macys.com, pgsql-novice@postgresql.org
  Subject: Re: Bad Query??  Extremely slow response
  In-Reply-To: <1015587341.618.21.camel@kant.mcmillan.net.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox