pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Patrick Hatcher <PHatcher@macys.com>
To: pgsql-sql@postgresql.org
Subject: Re: [NOVICE] Bad Query??  Extremely slow response
Date: Thu, 07 Mar 2002 17:28:24 -0800
Message-ID: <web-821043@davinci.ethosmedia.com> (raw)
In-Reply-To: <OF65F0D774.4410E97A-ON88256B75.0075B7E0@fds.com>

Patrick,

This belongs on the SQL list, so I'm copying it there.

> 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)

That's odd.  You must be missing an index somewhere.
- Do you have indexes on itemnumber and upc on both tables?  Are they
 unique where applicable?
- You're showing a huge cost on aggregation.  How many rows are in the
 cheshire_data table?
- Your indication that the same query works fast with a where clause
 may be a sign that you need to increase psotmaster's sort_mem
 parameter.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



view thread (3+ messages)  latest in thread

Message-ID: <web-821043@davinci.ethosmedia.com>
Permalink:  ../web-821043@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-821043@davinci.ethosmedia.com

 · 

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: josh@agliodbs.com, PHatcher@macys.com
  Subject: Re: [NOVICE] Bad Query??  Extremely slow response
  In-Reply-To: <web-821043@davinci.ethosmedia.com>

* 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