Re: Bad Query?? Extremely slow response

Thanks:
1) Did the vacuum analyze on both table
2) see below
3) PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
3) Here is the EXPLAIN ANAYLZE
Subquery Scan cheshire_data_v (cost=23778.00..23778.89 rows=1 width=171)
(actual time=33152.83..59799.37 rows=178898 loops=1)
-> Aggregate (cost=23778.00..23778.89 rows=1 width=171) (actual
time=33152.78..54320.62 rows=178898 loops=1)
-> Group (cost=23778.00..23778.46 rows=13 width=171) (actual
time=33152.64..45126.73 rows=178992 loops=1)
-> Sort (cost=23778.00..23778.00 rows=13 width=171) (actual
time=33152.57..33784.99 rows=178992 loops=1)
-> Hash Join (cost=12887.71..23777.75 rows=13
width=171) (actual time=6168.64..19319.10 rows=178992 loops=1)
-> Seq Scan on sku_non_inh s
(cost=0.00..4713.48 rows=190048 width=28) (actual time=0.12..1252.05
rows=190048 loops=1)
-> Hash (cost=6774.65..6774.65 rows=186365
width=143) (actual time=6168.08..6168.08 rows=0 loops=1)
-> Seq Scan on cheshire_data c
(cost=0.00..6774.65 rows=186365 width=143) (actual time=0.12..5334.14
rows=186365 loops=1)
Total runtime: 60097.33 msec

Table structure:

-- Table: cheshire_data
CREATE TABLE "cheshire_data" (
"companyid" int2,
"itemnumber" varchar(15),
"mbmcolorcode" char(2),
"mbmsizedesc" varchar(30),
"mbmrange" char(3),
"upc" int8 NOT NULL,
"isavailable" varchar(1),
"totaloh" int4,
"mcoh" int4,
"backorder" int4,
"oo" int4,
"cost" float8,
"retail" float8,
"feddept" int4,
"description" varchar(30),
"mbmdiv" char(2),
"mbmdept" char(2),
"mbmclass" char(2),
"added_by_app" varchar(8),
"pending_picks" int4,
"pending_putaways" int4,
"transfer_suspense" int4,
"reserved" int4,
"oo_lt_30_days" int4,
"oo_30_60_days" int4,
"oo_gt_60_days" int4,
"last_receipt" varchar(10)
) WITH OIDS;
CREATE INDEX xak1cheshire_dataupc ON cheshire_data USING btree (upc);
CREATE INDEX xie2cheshire_dataitem ON cheshire_data USING btree
(itemnumber);

-- Table: sku_non_inh
CREATE TABLE "sku_non_inh" (
"coid" char(2),
"itemnumber" varchar(15),
"mbmcolorcode" varchar(2),
"mbmsizecode" varchar(3),
"mbmsizerange" varchar(3),
"pid" varchar(20),
"description" varchar(30),
"upc" int8
) WITH OIDS;
CREATE INDEX xie1sku_non_inhupc ON sku_non_inh USING btree (upc);
CREATE INDEX xie2sku_non_inhitm ON sku_non_inh USING btree (itemnumber);

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office

Andrew McMillan
<andrew(at)catalyst(dot)net(dot)nz To: Patrick Hatcher <PHatcher(at)macys(dot)com>
> cc: pgsql-novice(at)postgresql(dot)org
Sent by: Subject: Re: [NOVICE] Bad Query?? Extremely slow response
pgsql-novice-owner(at)post
gresql.org


03/08/2002 04:35 PM

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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly