pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBad 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