Re: Bad Query?? Extremely slow response
От | Patrick Hatcher |
---|---|
Тема | Re: Bad Query?? Extremely slow response |
Дата | |
Msg-id | OFF0AD7B35.328B4A58-ON88256B76.0058C5C8@fds.com обсуждение исходный текст |
Список | pgsql-novice |
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@catalyst.net.nz To: Patrick Hatcher <PHatcher@macys.com> > cc: pgsql-novice@postgresql.org Sent by: Subject: Re: [NOVICE] Bad Query?? Extremely slow response pgsql-novice-owner@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@postgresql.org so that your message can get through to the mailing list cleanly
В списке pgsql-novice по дате отправления: