pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad Query??  Extremely slow response
3+ messages / 3 participants
[nested] [flat]

* Bad Query??  Extremely slow response
@ 2002-03-07 21:46  Patrick Hatcher <PHatcher@macys.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Patrick Hatcher @ 2002-03-07 21:46 UTC (permalink / raw)
  To: pgsql-novice@postgresql.org

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [NOVICE] Bad Query??  Extremely slow response
@ 2002-03-08 01:28  Josh Berkus <josh@agliodbs.com>
  parent: Patrick Hatcher <PHatcher@macys.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2002-03-08 01:28 UTC (permalink / raw)
  To: Patrick Hatcher <PHatcher@macys.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Bad Query??  Extremely slow response
@ 2002-03-08 11:35  Andrew McMillan <andrew@catalyst.net.nz>
  parent: Patrick Hatcher <PHatcher@macys.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Andrew McMillan @ 2002-03-08 11:35 UTC (permalink / raw)
  To: Patrick Hatcher <PHatcher@macys.com>; +Cc: pgsql-novice@postgresql.org

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?




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-03-08 11:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-07 21:46 Bad Query??  Extremely slow response Patrick Hatcher <PHatcher@macys.com>
2002-03-08 01:28 ` Josh Berkus <josh@agliodbs.com>
2002-03-08 11:35 ` Andrew McMillan <andrew@catalyst.net.nz>

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