Hi,
Why does adding SUM and GROUP BY destroy performance?
details follow.
Thanks, David Link
s1.sql:
SELECT
t.tid, t.title,
COALESCE(s0c100 r100.units, 0) as w0c100r100units ,
(COALESCE(r1c2r 100.units, 0) + COALESCE(y0c2r1 00.units, 0))
as r0c2r100units
FROM
title t
JOIN upc u1 ON t.tid = u1.tid
LEFT OUTER JOIN sale_200331 s0c100r100 ON u1.upc = s0c100r100.upc
AND s0c100r100.week = 200331 AND s0c100r100.chan nel = 100
AND s0c100r100.regi on = 100
LEFT OUTER JOIN rtd r1c2r100 ON u1.upc = r1c2r100.upc
AND r1c2r100.year = 2002 AND r1c2r100.channe l = 2
AND r1c2r100.region = 100
LEFT OUTER JOIN ytd_200331 y0c2r100 ON u1.upc = y0c2r100.upc
AND y0c2r100.week = 200331 AND y0c2r100.channe l = 2
AND y0c2r100.region = 100
LEFT OUTER JOIN media m ON t.media = m.key
LEFT OUTER JOIN screen_format sf ON t.screen_format = sf.key
WHERE
t.distributor != 'CONTROL LABEL'
ORDER BY
t.title ASC
LIMIT 50
;
s2.sql:
SELECT
t.tid, t.title,
SUM(COALESCE(s0 c100r100.units, 0)) as w0c100r100units ,
SUM((COALESCE(r 1c2r100.units, 0) + COALESCE(y0c2r1 00.units, 0)))
as r0c2r100units
FROM
title t
JOIN upc u1 ON t.tid = u1.tid
LEFT OUTER JOIN sale_200331 s0c100r100 ON u1.upc = s0c100r100.upc
AND s0c100r100.week = 200331 AND s0c100r100.chan nel = 100
AND s0c100r100.regi on = 100
LEFT OUTER JOIN rtd r1c2r100 ON u1.upc = r1c2r100.upc
AND r1c2r100.year = 2002 AND r1c2r100.channe l = 2
AND r1c2r100.region = 100
LEFT OUTER JOIN ytd_200331 y0c2r100 ON u1.upc = y0c2r100.upc
AND y0c2r100.week = 200331 AND y0c2r100.channe l = 2
AND y0c2r100.region = 100
LEFT OUTER JOIN media m ON t.media = m.key
LEFT OUTER JOIN screen_format sf ON t.screen_format = sf.key
WHERE
t.distributor != 'CONTROL LABEL'
GROUP BY
t.tid, t.title
ORDER BY
t.title ASC
LIMIT 50
;
Times:
s1.sql takes 0m0.124s
s2.sql takes 1m1.450s
Stats:
title table: 68,000 rows
sale_200331 table: 150,000 rows
ytd_200331 table: 0 rows
rtd table: 650,000 rows
Indexes are in place.
s1 explain plan:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..651 05.51 rows=50 width=132)
-> Nested Loop (cost=0.00..917 26868.54 rows=70445 width=132)
Join Filter: ("outer".screen _format = "inner"."ke y")
-> Nested Loop (cost=0.00..916 51668.74 rows=70445 width=127)
Join Filter: ("outer".med ia = "inner"."ke y")
-> Nested Loop (cost=0.00..915 78053.95 rows=70445
width=122)
-> Nested Loop (cost=0.00..912 36359.89
rows=70445 width=98)
-> Nested Loop (cost=0.00..908 94665.82
rows=70445 width=74)
-> Nested Loop
(cost=0.00..905 39626.76 rows=70445 width=50)
-> Index Scan using
title_title_ind on title t (cost=0.00..193 051.67 rows=68775 width=38)
Filter: (distributor <>
'CONTROL LABEL'::charact er varying)
-> Index Scan using
davids_tid_inde x on upc u1 (cost=0.00..130 9.24 rows=353 width=12)
Index Cond: ("outer".tid =
u1.tid)
-> Index Scan using
sale_200331_upc _wk_chl_reg_ind on sale_200331 s0c100r100
(cost=0.00..5.0 2 rows=1 width=24)
Index Cond: (("outer".upc =
s0c100r100.upc) AND (s0c100r100.wee k = 200331) AND (s0c100r100.cha nnel
= 100) AND (s0c100r100.reg ion = 100))
-> Index Scan using
rtd_upc_year_ch l_reg_ind on rtd r1c2r100 (cost=0.00..4.8 3 rows=1
width=24)
Index Cond: (("outer".upc =
r1c2r100.upc) AND (r1c2r100."year " = 2002) AND (r1c2r100.chann el = 2)
AND (r1c2r100.regio n = 100))
-> Index Scan using ytd_200331_upc_ wkchlreg_ind
on ytd_200331 y0c2r100 (cost=0.00..4.8 3 rows=1 width=24)
Index Cond: (("outer".upc = y0c2r100.upc)
AND (y0c2r100.week = 200331) AND (y0c2r100.chann el = 2) AND
(y0c2r100.regio n = 100))
-> Seq Scan on media m (cost=0.00..1.0 2 rows=2
width=5)
-> Seq Scan on screen_format sf (cost=0.00..1.0 3 rows=3
width=5)
(21 rows)
s2 explain plan:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=403996.99 ..403997.11 rows=50 width=132)
-> Sort (cost=403996.99 ..404014.60 rows=7044 width=132)
Sort Key: t.title
-> Aggregate (cost=402393.74 ..403274.30 rows=7044 width=132)
-> Group (cost=402393.74 ..402922.08 rows=70445
width=132)
-> Sort (cost=402393.74 ..402569.86 rows=70445
width=132)
Sort Key: t.tid, t.title
-> Hash Join (cost=375382.76 ..392011.46
rows=70445 width=132)
Hash Cond: ("outer".screen _format =
"inner"."ke y")
-> Hash Join
(cost=375381.72 ..390997.78 rows=70445 width=127)
Hash Cond: ("outer".med ia =
"inner"."ke y")
-> Merge Join
(cost=375380.70 ..390057.49 rows=70445 width=122)
Merge Cond: ("outer".upc =
"inner".upc )
Join Filter:
(("inner".wee k = 200331) AND ("inner".channe l = 2) AND ("inner".reg ion
= 100))
-> Merge Join
(cost=375380.70 ..382782.40 rows=70445 width=98)
Merge Cond:
("outer".upc = "inner".upc )
Join Filter:
(("inner"."year " = 2002) AND ("inner".channe l = 2) AND ("inner".reg ion
= 100))
-> Sort
(cost=375310.87 ..375486.98 rows=70445 width=74)
Sort Key:
u1.upc
-> Nested
Loop (cost=6348.20.. 367282.53 rows=70445 width=74)
-> Hash
Join (cost=6348.20.. 12243.46 rows=70445 width=50)
Hash Cond: ("outer".tid = "inner".tid )
->
Seq Scan on upc u1 (cost=0.00..279 5.28 rows=70628 width=12)
->
Hash (cost=4114.93.. 4114.93 rows=68775 width=38)
-> Seq Scan on title t (cost=0.00..411 4.93 rows=68775 width=38)
Filter: (distributor <> 'CONTROL LABEL'::charact er varying)
->
Index Scan using sale_200331_upc _wk_chl_reg_ind on sale_200331
s0c100r100 (cost=0.00..5.0 2 rows=1 width=24)
Index Cond: (("outer".upc = s0c100r100.upc) AND (s0c100r100.wee k =
200331) AND (s0c100r100.cha nnel = 100) AND (s0c100r100.reg ion = 100))
-> Sort
(cost=69.83..72 .33 rows=1000 width=24)
Sort Key:
r1c2r100.upc
-> Seq Scan
on rtd r1c2r100 (cost=0.00..20. 00 rows=1000 width=24)
-> Index Scan using
ytd_200331_upc_ wkchlreg_ind on ytd_200331 y0c2r100 (cost=0.00..52. 00
rows=1000 width=24)
-> Hash (cost=1.02..1.0 2
rows=2 width=5)
-> Seq Scan on media m
(cost=0.00..1.0 2 rows=2 width=5)
-> Hash (cost=1.03..1.0 3 rows=3
width=5)
-> Seq Scan on screen_format sf
(cost=0.00..1.0 3 rows=3 width=5)
(36 rows)
_______________ _______________ ____
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend