|
|
〇〇 发表于 2012-9-17 18:16 ![]()
机器重起了，只好重测最后3个，花了8小时10分。。。。
(4 行记录)
(100 行记录)
q20执行计划
test_en=# explain select
test_en-# s_name,
test_en-# s_address
test_en-# from
test_en-# supplier,
test_en-# nation
test_en-# where
test_en-# s_suppkey in (
test_en(# select
test_en(# ps_suppkey
test_en(# from
test_en(# partsupp
test_en(# where
test_en(# ps_partkey in (
test_en(# select
test_en(# p_partkey
test_en(# from
test_en(# part
test_en(# where
test_en(# p_name like 'bisque%'
test_en(# )
test_en(# and ps_availqty > (
test_en(# select
test_en(# 0.5 * sum(l_quantity)
test_en(# from
test_en(# lineitem
test_en(# where
test_en(# l_partkey = ps_partkey
test_en(# and l_suppkey = ps_suppkey
test_en(# and l_shipdate >= date '1994-01-01'
test_en(# and l_shipdate < date '1994-01-01' + interval '1' year
test_en(# )
test_en(# )
test_en-# and s_nationkey = n_nationkey
test_en-# and n_name = 'CANADA'
test_en-# order by
test_en-# s_name
test_en-# LIMIT 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
----------------
Limit (cost=23387399.83..23387399.85 rows=10 width=51)
-> Sort (cost=23387399.83..23387409.83 rows=4000 width=51)
Sort Key: supplier.s_name
-> Nested Loop Semi Join (cost=0.00..23387313.39 rows=4000 width=51)
-> Nested Loop (cost=0.00..4467.31 rows=4000 width=55)
Join Filter: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4)
Filter: (n_name = 'CANADA'::bpchar)
-> Seq Scan on supplier (cost=0.00..3216.00 rows=100000 width=59)
-> Nested Loop Semi Join (cost=0.00..6418.47 rows=26 width=4)
-> Index Scan using partsupp_fk1 on partsupp (cost=0.00..6161.58 rows=26 width=8)
Index Cond: (ps_suppkey = supplier.s_suppkey)
Filter: ((ps_availqty)::numeric > (SubPlan 1))
SubPlan 1
-> Aggregate (cost=74.07..74.08 rows=1 width=5)
-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..74.07 rows=1 width=5)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
Filter: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp with
out time zone))
-> Index Scan using part_pkey on part (cost=0.00..9.88 rows=1 width=4)
Index Cond: (p_partkey = partsupp.ps_partkey)
Filter: ((p_name)::text ~~ 'bisque%'::text)
(21 行记录)
修改写法
test_en=# explain select s_name, s_address from supplier, nation where s_suppkey in (select ps_suppkey from
test_en(# partsupp ,(select l_partkey,l_suppkey, sum(l_quantity) l_quantity_SUM from lineitem,part where
test_en(# l_partkey = p_partkey and p_name like 'bisque%' and l_shipdate >= date '1994-01-01' and l_shipdate
test_en(# < date '1994-01-01' + interval '1' year GROUP BY l_partkey,l_suppkey ) b where l_partkey = ps_partkey and
test_en(# l_suppkey = ps_suppkey and ps_availqty > 0.5*l_quantity_SUM ) and s_nationkey = n_nationkey and
test_en-# n_name = 'CANADA' order by s_name limit 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
Limit (cost=2680414.46..2680414.49 rows=10 width=51)
-> Sort (cost=2680414.46..2680419.46 rows=2000 width=51)
Sort Key: supplier.s_name
-> Nested Loop Semi Join (cost=2238699.85..2680371.25 rows=2000 width=51)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Nested Loop (cost=0.00..4467.31 rows=4000 width=55)
Join Filter: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4)
Filter: (n_name = 'CANADA'::bpchar)
-> Seq Scan on supplier (cost=0.00..3216.00 rows=100000 width=59)
-> Materialize (cost=2238699.85..2675603.95 rows=5 width=8)
-> Merge Join (cost=2238699.85..2675603.92 rows=5 width=8)
Merge Cond: (lineitem.l_partkey = partsupp.ps_partkey)
Join Filter: ((partsupp.ps_suppkey = lineitem.l_suppkey) AND ((partsupp.ps_availqty)::numeric > (0.5 * (sum(linei
tem.l_quantity)))))
-> GroupAggregate (cost=2238699.85..2240463.39 rows=88177 width=13)
-> Sort (cost=2238699.85..2238920.30 rows=88177 width=13)
Sort Key: lineitem.l_partkey, lineitem.l_suppkey
-> Hash Join (cost=66313.77..2229948.44 rows=88177 width=13)
Hash Cond: (lineitem.l_partkey = part.p_partkey)
-> Seq Scan on lineitem (cost=0.00..2024331.82 rows=9228072 width=13)
Filter: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::time
stamp without time zone))
-> Hash (cost=66061.26..66061.26 rows=20201 width=4)
-> Seq Scan on part (cost=0.00..66061.26 rows=20201 width=4)
Filter: ((p_name)::text ~~ 'bisque%'::text)
-> Index Scan using partsupp_fk2 on partsupp (cost=0.00..382152.20 rows=7999948 width=12)
(25 行记录)
成本差10倍，都是nested loop |
|
楼主: 〇〇
IP卡
狗仔卡
楼主
显身卡
发表于 2012-9-17 14:34
发表于 2012-9-24 11:21
