37 lines
1.4 KiB
MySQL
37 lines
1.4 KiB
MySQL
|
-- correlated attributes
|
||
|
|
|
||
|
|
-- Q1
|
||
|
|
explain analyze select count(*) from lineitem, orders, customer where l_quantity > 15 and l_extendedprice < 15000 and l_orderkey = o_orderkey and o_custkey = c_custkey;
|
||
|
|
|
||
|
|
-- Q2
|
||
|
|
explain analyze select count(*) from lineitem, orders, customer where l_quantity < 6 and l_extendedprice > 9500 and l_orderkey = o_orderkey and o_custkey = c_custkey;
|
||
|
|
|
||
|
|
-- Q3
|
||
|
|
explain analyze select count(*) from lineitem, orders, customer where l_quantity < 6 and l_extendedprice > 9525 and l_orderkey = o_orderkey and o_custkey = c_custkey;
|
||
|
|
|
||
|
|
|
||
|
|
-- select the same tuples but using a precomputed attribute
|
||
|
|
|
||
|
|
-- Q1a
|
||
|
|
explain analyze select count(*) from lineitem2, orders, customer where l_evaluatedpreds = 1 and l_orderkey = o_orderkey and o_custkey = c_custkey;
|
||
|
|
|
||
|
|
-- Q2a
|
||
|
|
explain analyze select count(*) from lineitem3, orders, customer where l_evaluatedpreds = 1 and l_orderkey = o_orderkey and o_custkey = c_custkey;
|
||
|
|
|
||
|
|
-- Q3a
|
||
|
|
explain analyze select count(*) from lineitem4, orders, customer where l_evaluatedpreds = 1 and l_orderkey = o_orderkey and o_custkey = c_custkey;
|
||
|
|
|
||
|
|
|
||
|
|
-- queries on synthesized data
|
||
|
|
set cpu_tuple_cost = 0.001
|
||
|
|
set random_page_cost = 1.2
|
||
|
|
-- otherwise the optimizer will choose a sortmergejoin plan for Q2 which is much
|
||
|
|
-- slower.
|
||
|
|
|
||
|
|
|
||
|
|
-- Q1
|
||
|
|
explain analyze select count(*) from r_million as R, s_million as S where R.a=1 and R.c=122 and R.d = S.d;
|
||
|
|
|
||
|
|
-- Q2
|
||
|
|
explain analyze count(*) from r_million as R, s_million as S where R.a=1 and R.d = S.d;
|