text: \o query_mem_col.txt
  set current_schema=vector_engine;
  set explain_perf_mode=summary;
  set query_dop=0;
  
  -- Test on hashjoin
  set enable_hashjoin=on;
  set enable_mergejoin=off;
  set enable_nestloop=off;
  
  -- $ID$
  -- TPC-H/TPC-R Pricing Summary Report Query (Q1)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_returnflag,
  	l_linestatus,
  	sum(l_quantity) as sum_qty,
  	sum(l_extendedprice) as sum_base_price,
  	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) as sum_charge, --add /1000
  	avg(l_quantity) as avg_qty,
  	avg(l_extendedprice) as avg_price,
  	avg(l_discount) as avg_disc,
  	count(*) as count_order
  from
  	lineitem
  where
  	l_shipdate <= date '1998-12-01' - interval '3 day'
  group by
  	l_returnflag,
  	l_linestatus
  order by
  	l_returnflag,
  	l_linestatus;
  
  -- $ID$
  -- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	s_acctbal,
  	s_name,
  	n_name,
  	p_partkey,
  	p_mfgr,
  	s_address,
  	s_phone,
  	s_comment
  from
  	part,
  	supplier,
  	partsupp,
  	nation,
  	region,
  	(
  		select
  			ps_partkey as temp_ps_partkey, min(ps_supplycost) as temp_min_ps_supplycost
  		from
  			partsupp,
  			supplier,
  			nation,
  			region
  		where
  			s_suppkey = ps_suppkey
  			and s_nationkey = n_nationkey
  			and n_regionkey = r_regionkey
  			and r_name = 'EUROPE'
  	    group by ps_partkey
  	) as temp
  where
      p_partkey = temp_ps_partkey
  	and p_partkey = ps_partkey
  	and s_suppkey = ps_suppkey
  	and p_size = 15
  	and p_type like '%BRASS'
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'EUROPE'
  	and ps_supplycost = temp_min_ps_supplycost
  order by
  	s_acctbal desc,
  	n_name,
  	s_name,
  	p_partkey
  limit 100
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Shipping Priority Query (Q3)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_orderkey,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	o_orderdate,
  	o_shippriority
  from
  	customer,
  	orders,
  	lineitem
  where
  	c_mktsegment = 'BUILDING'
  	and c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate < '1995-03-15'::date
  	and l_shipdate > '1995-03-15'::date
  group by
  	l_orderkey,
  	o_orderdate,
  	o_shippriority
  order by
  	revenue desc,
  	o_orderdate
  limit 10
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Order Priority Checking Query (Q4)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	o_orderpriority,
  	count(*) as order_count
  from
  	orders
  where
  	o_orderdate >= '1993-07-01'::date
  	and o_orderdate < '1993-07-01'::date + interval '3 month'
  	and exists (
  		select
  			*
  		from
  			lineitem
  		where
  			l_orderkey = o_orderkey
  			and l_commitdate < l_receiptdate
  	)
  group by
  	o_orderpriority
  order by
  	o_orderpriority;
  
  -- $ID$
  -- TPC-H/TPC-R Local Supplier Volume Query (Q5)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	n_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue
  from
  	customer,
  	orders,
  	lineitem,
  	supplier,
  	nation,
  	region
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and l_suppkey = s_suppkey
  	and c_nationkey = s_nationkey
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'ASIA'
  	and o_orderdate >= '1994-01-01'::date
  	and o_orderdate < '1994-01-01'::date + interval '1 year'
  group by
  	n_name
  order by
  	revenue desc;
  
  -- $ID$
  -- TPC-H/TPC-R Forecasting Revenue Change Query (Q6)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	sum(l_extendedprice * l_discount) as revenue
  from
  	lineitem
  where
  	l_shipdate >= '1994-01-01'::date 
  	and l_shipdate < '1994-01-01'::date + interval '1 year'
  	and l_discount between 0.06 - 0.01 and 0.06 + 0.01
  	and l_quantity < 24;
  
  -- $ID$
  -- TPC-H/TPC-R Volume Shipping Query (Q7)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	supp_nation,
  	cust_nation,
  	l_year,
  	sum(volume) as revenue
  from
  	(
  		select
  			n1.n_name as supp_nation,
  			n2.n_name as cust_nation,
  			extract(year from l_shipdate) as l_year,
  			l_extendedprice * (1 - l_discount) as volume
  		from
  			supplier,
  			lineitem,
  			orders,
  			customer,
  			nation n1,
  			nation n2
  		where
  			s_suppkey = l_suppkey
  			and o_orderkey = l_orderkey
  			and c_custkey = o_custkey
  			and s_nationkey = n1.n_nationkey
  			and c_nationkey = n2.n_nationkey
  			and (
  				(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
  				or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
  			)
  			and l_shipdate between date '1995-01-01' and date '1996-12-31'
  	) as shipping
  group by
  	supp_nation,
  	cust_nation,
  	l_year
  order by
  	supp_nation,
  	cust_nation,
  	l_year;
  
  -- $ID$
  -- TPC-H/TPC-R National Market Share Query (Q8)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	o_year,
  	sum(case
  		when nation = 'BRAZIL' then volume
  		else 0
  	end) / sum(volume) as mkt_share
  from
  	(
  		select
  			extract(year from o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) as volume,
  			n2.n_name as nation
  		from
  			part,
  			supplier,
  			lineitem,
  			orders,
  			customer,
  			nation n1,
  			nation n2,
  			region
  		where
  			p_partkey = l_partkey
  			and s_suppkey = l_suppkey
  			and l_orderkey = o_orderkey
  			and o_custkey = c_custkey
  			and c_nationkey = n1.n_nationkey
  			and n1.n_regionkey = r_regionkey
  			and r_name = 'AMERICA'
  			and s_nationkey = n2.n_nationkey
  			and o_orderdate between date '1995-01-01' and date '1996-12-31'
  			and p_type = 'ECONOMY ANODIZED STEEL'
  	) as all_nations
  group by
  	o_year
  order by
  	o_year;
  
  -- $ID$
  -- TPC-H/TPC-R Product Type Profit Measure Query (Q9)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	nation,
  	o_year,
  	sum(amount) as sum_profit
  from
  	(
  		select
  			n_name as nation,
  			extract(year from o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
  		from
  			part,
  			supplier,
  			lineitem,
  			partsupp,
  			orders,
  			nation
  		where
  			s_suppkey = l_suppkey
  			and ps_suppkey = l_suppkey
  			and ps_partkey = l_partkey
  			and p_partkey = l_partkey
  			and o_orderkey = l_orderkey
  			and s_nationkey = n_nationkey
  			and p_name like '%green%'
  	) as profit
  group by
  	nation,
  	o_year
  order by
  	nation,
  	o_year desc;
  
  -- $ID$
  -- TPC-H/TPC-R Returned Item Reporting Query (Q10)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	c_custkey,
  	c_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	c_acctbal,
  	n_name,
  	c_address,
  	c_phone,
  	c_comment
  from
  	customer,
  	orders,
  	lineitem,
  	nation
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate >= date '1993-10-01'
  	and o_orderdate < date '1993-10-01' + interval '3 month'
  	and l_returnflag = 'R'
  	and c_nationkey = n_nationkey
  group by
  	c_custkey,
  	c_name,
  	c_acctbal,
  	c_phone,
  	n_name,
  	c_address,
  	c_comment
  order by
  	revenue desc
  limit 20
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Important Stock Identification Query (Q11)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	ps_partkey,
  	sum(ps_supplycost * ps_availqty) as value
  from
  	partsupp,
  	supplier,
  	nation
  where
  	ps_suppkey = s_suppkey
  	and s_nationkey = n_nationkey
  	and n_name = 'GERMANY'
  group by
  	ps_partkey having
  		sum(ps_supplycost * ps_availqty) > (
  			select
  				sum(ps_supplycost * ps_availqty) * 0.0001/100
  			from
  				partsupp,
  				supplier,
  				nation
  			where
  				ps_suppkey = s_suppkey
  				and s_nationkey = n_nationkey
  				and n_name = 'GERMANY'
  		)
  order by
  	value desc;
  
  -- $ID$
  -- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_shipmode,
  	sum(case
  		when o_orderpriority = '1-URGENT'
  			or o_orderpriority = '2-HIGH'
  			then 1
  		else 0
  	end) as high_line_count,
  	sum(case
  		when o_orderpriority <> '1-URGENT'
  			and o_orderpriority <> '2-HIGH'
  			then 1
  		else 0
  	end) as low_line_count
  from
  	orders,
  	lineitem
  where
  	o_orderkey = l_orderkey
  	and l_shipmode in ('MAIL', 'SHIP')
  	and l_commitdate < l_receiptdate
  	and l_shipdate < l_commitdate
  	and l_receiptdate >= date '1994-01-01'
  	and l_receiptdate < date '1994-01-01' + interval '1 year'
  group by
  	l_shipmode
  order by
  	l_shipmode;
  
  -- $ID$
  -- TPC-H/TPC-R Customer Distribution Query (Q13)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	c_count,
  	count(*) as custdist
  from
  	(
  		select
  			c_custkey,
  			count(*)
  		from
  			customer left outer join orders on
  				c_custkey = o_custkey
  				and o_comment not like '%special%request%'
  		group by
  			c_custkey
  	) as c_orders (c_custkey, c_count)
  group by
  	c_count
  order by
  	custdist desc,
  	c_count desc;
  
  -- $ID$
  -- TPC-H/TPC-R Promotion Effect Query (Q14)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	100 * sum(case
  		when p_type like 'PROMO%'
  			then l_extendedprice * (1 - l_discount)/10
  		else 0
  	end) / sum(l_extendedprice * (1 - l_discount)/1000) as promo_revenue --add /1000
  from
  	lineitem,
  	part
  where
  	l_partkey = p_partkey
  	and l_shipdate >= date '1995-09-01'
  	and l_shipdate < date '1995-09-01' + interval '1 month';
  
  -- $ID$
  -- TPC-H/TPC-R Top Supplier Query (Q15)
  -- Functional Query Definition
  -- Approved February 1998
  explain
  with revenue (supplier_no, total_revenue) as --change view to cte
  (
  	select
  		l_suppkey,
  		sum(l_extendedprice * (1 - l_discount))
  	from
  		lineitem
  	where
  		l_shipdate >= date '1996-01-01'
  		and l_shipdate < date '1996-01-01' + interval '3 month'
  	group by
  		l_suppkey
  )
  select
  	s_suppkey,
  	s_name,
  	s_address,
  	s_phone,
  	total_revenue
  from
  	supplier,
  	revenue
  where
  	s_suppkey = supplier_no
  	and total_revenue = (
  		select
  			max(total_revenue)
  		from
  			revenue
  	)
  order by
  	s_suppkey;
  
  -- $ID$
  -- TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	p_brand,
  	p_type,
  	p_size,
  	count(ps_suppkey) as supplier_cnt --remove distinct
  from
  	partsupp,
  	part
  where
  	p_partkey = ps_partkey
  	and p_brand <> 'Brand#45'
  	and p_type not like 'MEDIUM POLISHED%'
  	and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
  	and ps_suppkey not in (
  		select
  			s_suppkey
  		from
  			supplier
  		where
  			s_comment like '%Customer%Complaints%'
  	)
  group by
  	p_brand,
  	p_type,
  	p_size
  order by
  	supplier_cnt desc,
  	p_brand,
  	p_type,
  	p_size
  limit 100
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Small-Quantity-Order Revenue Query (Q17)
  -- Functional Query Definition
  -- Approved February 1998
  -- Query modified: a subquery is moved from WHERE clause to FROM clause. 
  explain select
  	sum(l_extendedprice) / 7.0 as avg_yearly
  from
  	lineitem,
  	part,
  	(select 
  	    l_partkey as temp_l_partkey, 0.2 * avg(l_quantity) as temp_avg
  	 from
  		lineitem
  	 group by l_partkey
  	) as temp
  where
  	p_partkey = l_partkey
  	and p_brand = 'Brand#23'
  	and p_container = 'MED BOX'
  	and l_quantity < temp_avg
  	and p_partkey = temp_l_partkey;
  
  -- $ID$
  -- TPC-H/TPC-R Large Volume Customer Query (Q18)
  -- Function Query Definition
  -- Approved February 1998
  explain select
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice,
  	sum(l_quantity)
  from
  	customer,
  	orders,
  	lineitem
  where
  	o_orderkey in (
  		select
  			l_orderkey
  		from
  			lineitem
  		group by
  			l_orderkey having
  				sum(l_quantity) > 300
  	)
  	and c_custkey = o_custkey
  	and o_orderkey = l_orderkey
  group by
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice
  order by
  	o_totalprice desc,
  	o_orderdate
  limit 100;
  
  -- $ID$
  -- TPC-H/TPC-R Discounted Revenue Query (Q19)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	sum(l_extendedprice* (1 - l_discount)) as revenue
  from
  	lineitem,
  	part
  where
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#12'
  		and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
  		and l_quantity >= 1 and l_quantity <= 1 + 10
  		and p_size between 1 and 5
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#23'
  		and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
  		and l_quantity >= 10 and l_quantity <= 10 + 10
  		and p_size between 1 and 10
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#34'
  		and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
  		and l_quantity >= 20 and l_quantity <= 20 + 10
  		and p_size between 1 and 15
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	);
  
  -- $ID$
  -- TPC-H/TPC-R Potential Part Promotion Query (Q20)
  -- Function Query Definition
  -- Approved February 1998
  -- The query has been changed!
  explain select
  	s_name,
  	s_address
  from
  	supplier,
  	nation
  where
  	s_suppkey in (
  		select
  			ps_suppkey
  		from
  			partsupp,
  			(
  				select
  					l_partkey as temp_l_partkey, l_suppkey as temp_l_suppkey, 0.5 * sum(l_quantity) as temp_l_quantity
  				from
  					lineitem
  				where
  					l_shipdate >= date '1994-01-01'
  					and l_shipdate < date '1994-01-01' + interval '1 year'
  				group by l_partkey, l_suppkey
  			) as temp
  		where
  		    temp_l_partkey = ps_partkey
  			and temp_l_suppkey = ps_suppkey
  			and ps_partkey in (
  				select
  					p_partkey
  				from
  					part
  				where
  					p_name like 'forest%'
  			)
  			and ps_availqty > temp_l_quantity
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'CANADA'
  order by
  	s_name;
  
  -- $ID$
  -- TPC-H/TPC-R Suppliers Who Kept Orders Waiting Query (Q21)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	s_name,
  	count(*) as numwait
  from
  	supplier,
  	lineitem l1,
  	orders,
  	nation
  where
  	s_suppkey = l1.l_suppkey
  	and o_orderkey = l1.l_orderkey
  	and o_orderstatus = 'F'
  	and l1.l_receiptdate > l1.l_commitdate
  	and exists (
  		select
  			*
  		from
  			lineitem l2
  		where
  			l2.l_orderkey = l1.l_orderkey
  			and l2.l_suppkey <> l1.l_suppkey
  	)
  	and not exists (
  		select
  			*
  		from
  			lineitem l3
  		where
  			l3.l_orderkey = l1.l_orderkey
  			and l3.l_suppkey <> l1.l_suppkey
  			and l3.l_receiptdate > l3.l_commitdate
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'SAUDI ARABIA'
  group by
  	s_name
  order by
  	numwait desc,
  	s_name
  limit 100;
  
  -- $ID$
  -- TPC-H/TPC-R Global Sales Opportunity Query (Q22)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	cntrycode,
  	count(*) as numcust,
  	sum(c_acctbal) as totacctbal
  from
  	(
  		select
  			substring(c_phone from 1 for 2) as cntrycode,
  			c_acctbal
  		from
  			customer
  		where
  			substring(c_phone from 1 for 2) in
  				('13', '31', '23', '29', '30', '18', '17')
  			and c_acctbal > (
  				select
  					avg(c_acctbal)
  				from
  					customer
  				where
  					c_acctbal > 0.00
  					and substring(c_phone from 1 for 2) in
  						('13', '31', '23', '29', '30', '18', '17')
  			)
  			and not exists (
  				select
  					*
  				from
  					orders
  				where
  					o_custkey = c_custkey
  			)
  	) as custsale
  group by
  	cntrycode
  order by
  	cntrycode;
  
  -- Test on append
  explain select
          s_name,
          s_address
  from
          supplier,
          (
          (select
          nation,
          o_year,
          sum(amount) as sum_profit
  from
          (
                  select
                          n_nationkey as nation,
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
                  from
                          part,
                          supplier,
                          lineitem,
                          partsupp,
                          orders,
                          nation
                  where
                          s_suppkey = l_suppkey
                          and ps_suppkey = l_suppkey
                          and ps_partkey = l_partkey
                          and p_partkey = l_partkey
                          and o_orderkey = l_orderkey
                          and s_nationkey = n_nationkey
                          and p_name like '%green%'
          ) as profit
  group by
          nation,
          o_year
  union all
  select
          nation,
          o_year,
          sum(amount) as sum_profit
  from
          (
                  select
                          n_nationkey as nation,
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
                  from
                          part,
                          supplier,
                          lineitem,
                          partsupp,
                          orders,
                          nation
                  where
                          s_suppkey = l_suppkey
                          and ps_suppkey = l_suppkey
                          and ps_partkey = l_partkey
                          and p_partkey = l_partkey
                          and o_orderkey = l_orderkey
                          and s_nationkey = n_nationkey
                          and p_name like '%green%'
          ) as profit
  group by
          nation,
          o_year
  order by
          nation,
          o_year desc)
  union
  (select
          nation,
          o_year,
          sum(amount) as sum_profit
  from
          (
                  select
                          n_nationkey as nation,
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
                  from
                          part,
                          supplier,
                          lineitem,
                          partsupp,
                          orders,
                          nation
                  where
                          s_suppkey = l_suppkey
                          and ps_suppkey = l_suppkey
                          and ps_partkey = l_partkey
                          and p_partkey = l_partkey
                          and o_orderkey = l_orderkey
                          and s_nationkey = n_nationkey
                          and p_name like '%green%'
          ) as profit
  group by
          nation,
          o_year
  order by
          nation,
          o_year desc)) nation
  where
          s_suppkey in (
                  select
                          ps_suppkey
                  from
                          partsupp,
                          (
                                  select
                                          l_partkey as temp_l_partkey, l_suppkey as temp_l_suppkey, 0.5 * sum(l_quantity) as temp_l_quantity
                                  from
                                          lineitem
                                  where
                                          l_shipdate >= date '1994-01-01'
                                          and l_shipdate < date '1994-01-01' + interval '1 year'
                                  group by l_partkey, l_suppkey
                          ) as temp
                  where
                      temp_l_partkey = ps_partkey
                          and temp_l_suppkey = ps_suppkey
                          and ps_partkey in (
                                  select
                                          p_partkey
                                  from
                                          part
                                  where
                                          p_name like 'forest%'
                          )
                          and ps_availqty > temp_l_quantity
          )
          and s_nationkey = nation
  order by
          s_name;
  
  -- Test on multi gather
  explain select
          s_name,
          count(*) as numwait
  from
          supplier,
          lineitem l1,
          orders,
          nation
  where
          s_suppkey = l1.l_suppkey
          and o_orderkey = l1.l_orderkey
          and o_orderstatus = 'F'
          and l1.l_receiptdate > l1.l_commitdate
          and exists (
                  select
                          *
                  from
                          lineitem l2
                  where
                          l2.l_orderkey = l1.l_orderkey
                          and l2.l_suppkey <> l1.l_suppkey
          )
          and not exists (
                  select
                          *
                  from
                          lineitem l3
                  where
                          l3.l_orderkey = l1.l_orderkey
                          and l3.l_suppkey <> l1.l_suppkey
                          and l3.l_receiptdate > l3.l_commitdate
          )
          and s_nationkey = n_nationkey
          and n_name = 'SAUDI ARABIA'
  group by
          s_name
  order by
          numwait desc,
          s_name
  limit 
  (select
          count(*) as numwait
  from
          supplier,
          lineitem l1,
          orders,
          nation
  where
          s_suppkey = l1.l_suppkey
          and o_orderkey = l1.l_orderkey
          and o_orderstatus = 'F'
          and l1.l_receiptdate > l1.l_commitdate
          and exists (
                  select
                          *
                  from
                          lineitem l2
                  where
                          l2.l_orderkey = l1.l_orderkey
                          and l2.l_suppkey <> l1.l_suppkey
          )
          and not exists (
                  select
                          *
                  from
                          lineitem l3
                  where
                          l3.l_orderkey = l1.l_orderkey
                          and l3.l_suppkey <> l1.l_suppkey
                          and l3.l_receiptdate > l3.l_commitdate
          )
          and s_nationkey = n_nationkey
          and n_name = 'SAUDI ARABIA');
  
  -- Test on mergejoin
  set enable_hashjoin=off;
  set enable_mergejoin=on;
  set enable_nestloop=off;
  
  -- $ID$
  -- TPC-H/TPC-R Pricing Summary Report Query (Q1)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_returnflag,
  	l_linestatus,
  	sum(l_quantity) as sum_qty,
  	sum(l_extendedprice) as sum_base_price,
  	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) as sum_charge, --add /1000
  	avg(l_quantity) as avg_qty,
  	avg(l_extendedprice) as avg_price,
  	avg(l_discount) as avg_disc,
  	count(*) as count_order
  from
  	lineitem
  where
  	l_shipdate <= date '1998-12-01' - interval '3 day'
  group by
  	l_returnflag,
  	l_linestatus
  order by
  	l_returnflag,
  	l_linestatus;
  
  -- $ID$
  -- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	s_acctbal,
  	s_name,
  	n_name,
  	p_partkey,
  	p_mfgr,
  	s_address,
  	s_phone,
  	s_comment
  from
  	part,
  	supplier,
  	partsupp,
  	nation,
  	region,
  	(
  		select
  			ps_partkey as temp_ps_partkey, min(ps_supplycost) as temp_min_ps_supplycost
  		from
  			partsupp,
  			supplier,
  			nation,
  			region
  		where
  			s_suppkey = ps_suppkey
  			and s_nationkey = n_nationkey
  			and n_regionkey = r_regionkey
  			and r_name = 'EUROPE'
  	    group by ps_partkey
  	) as temp
  where
      p_partkey = temp_ps_partkey
  	and p_partkey = ps_partkey
  	and s_suppkey = ps_suppkey
  	and p_size = 15
  	and p_type like '%BRASS'
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'EUROPE'
  	and ps_supplycost = temp_min_ps_supplycost
  order by
  	s_acctbal desc,
  	n_name,
  	s_name,
  	p_partkey
  limit 100
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Shipping Priority Query (Q3)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_orderkey,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	o_orderdate,
  	o_shippriority
  from
  	customer,
  	orders,
  	lineitem
  where
  	c_mktsegment = 'BUILDING'
  	and c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate < '1995-03-15'::date
  	and l_shipdate > '1995-03-15'::date
  group by
  	l_orderkey,
  	o_orderdate,
  	o_shippriority
  order by
  	revenue desc,
  	o_orderdate
  limit 10
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Order Priority Checking Query (Q4)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	o_orderpriority,
  	count(*) as order_count
  from
  	orders
  where
  	o_orderdate >= '1993-07-01'::date
  	and o_orderdate < '1993-07-01'::date + interval '3 month'
  	and exists (
  		select
  			*
  		from
  			lineitem
  		where
  			l_orderkey = o_orderkey
  			and l_commitdate < l_receiptdate
  	)
  group by
  	o_orderpriority
  order by
  	o_orderpriority;
  
  -- $ID$
  -- TPC-H/TPC-R Local Supplier Volume Query (Q5)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	n_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue
  from
  	customer,
  	orders,
  	lineitem,
  	supplier,
  	nation,
  	region
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and l_suppkey = s_suppkey
  	and c_nationkey = s_nationkey
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'ASIA'
  	and o_orderdate >= '1994-01-01'::date
  	and o_orderdate < '1994-01-01'::date + interval '1 year'
  group by
  	n_name
  order by
  	revenue desc;
  
  -- $ID$
  -- TPC-H/TPC-R Forecasting Revenue Change Query (Q6)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	sum(l_extendedprice * l_discount) as revenue
  from
  	lineitem
  where
  	l_shipdate >= '1994-01-01'::date 
  	and l_shipdate < '1994-01-01'::date + interval '1 year'
  	and l_discount between 0.06 - 0.01 and 0.06 + 0.01
  	and l_quantity < 24;
  
  -- $ID$
  -- TPC-H/TPC-R Volume Shipping Query (Q7)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	supp_nation,
  	cust_nation,
  	l_year,
  	sum(volume) as revenue
  from
  	(
  		select
  			n1.n_name as supp_nation,
  			n2.n_name as cust_nation,
  			extract(year from l_shipdate) as l_year,
  			l_extendedprice * (1 - l_discount) as volume
  		from
  			supplier,
  			lineitem,
  			orders,
  			customer,
  			nation n1,
  			nation n2
  		where
  			s_suppkey = l_suppkey
  			and o_orderkey = l_orderkey
  			and c_custkey = o_custkey
  			and s_nationkey = n1.n_nationkey
  			and c_nationkey = n2.n_nationkey
  			and (
  				(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
  				or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
  			)
  			and l_shipdate between date '1995-01-01' and date '1996-12-31'
  	) as shipping
  group by
  	supp_nation,
  	cust_nation,
  	l_year
  order by
  	supp_nation,
  	cust_nation,
  	l_year;
  
  -- $ID$
  -- TPC-H/TPC-R National Market Share Query (Q8)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	o_year,
  	sum(case
  		when nation = 'BRAZIL' then volume
  		else 0
  	end) / sum(volume) as mkt_share
  from
  	(
  		select
  			extract(year from o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) as volume,
  			n2.n_name as nation
  		from
  			part,
  			supplier,
  			lineitem,
  			orders,
  			customer,
  			nation n1,
  			nation n2,
  			region
  		where
  			p_partkey = l_partkey
  			and s_suppkey = l_suppkey
  			and l_orderkey = o_orderkey
  			and o_custkey = c_custkey
  			and c_nationkey = n1.n_nationkey
  			and n1.n_regionkey = r_regionkey
  			and r_name = 'AMERICA'
  			and s_nationkey = n2.n_nationkey
  			and o_orderdate between date '1995-01-01' and date '1996-12-31'
  			and p_type = 'ECONOMY ANODIZED STEEL'
  	) as all_nations
  group by
  	o_year
  order by
  	o_year;
  
  -- $ID$
  -- TPC-H/TPC-R Product Type Profit Measure Query (Q9)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	nation,
  	o_year,
  	sum(amount) as sum_profit
  from
  	(
  		select
  			n_name as nation,
  			extract(year from o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
  		from
  			part,
  			supplier,
  			lineitem,
  			partsupp,
  			orders,
  			nation
  		where
  			s_suppkey = l_suppkey
  			and ps_suppkey = l_suppkey
  			and ps_partkey = l_partkey
  			and p_partkey = l_partkey
  			and o_orderkey = l_orderkey
  			and s_nationkey = n_nationkey
  			and p_name like '%green%'
  	) as profit
  group by
  	nation,
  	o_year
  order by
  	nation,
  	o_year desc;
  
  -- $ID$
  -- TPC-H/TPC-R Returned Item Reporting Query (Q10)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	c_custkey,
  	c_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	c_acctbal,
  	n_name,
  	c_address,
  	c_phone,
  	c_comment
  from
  	customer,
  	orders,
  	lineitem,
  	nation
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate >= date '1993-10-01'
  	and o_orderdate < date '1993-10-01' + interval '3 month'
  	and l_returnflag = 'R'
  	and c_nationkey = n_nationkey
  group by
  	c_custkey,
  	c_name,
  	c_acctbal,
  	c_phone,
  	n_name,
  	c_address,
  	c_comment
  order by
  	revenue desc
  limit 20
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Important Stock Identification Query (Q11)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	ps_partkey,
  	sum(ps_supplycost * ps_availqty) as value
  from
  	partsupp,
  	supplier,
  	nation
  where
  	ps_suppkey = s_suppkey
  	and s_nationkey = n_nationkey
  	and n_name = 'GERMANY'
  group by
  	ps_partkey having
  		sum(ps_supplycost * ps_availqty) > (
  			select
  				sum(ps_supplycost * ps_availqty) * 0.0001/100
  			from
  				partsupp,
  				supplier,
  				nation
  			where
  				ps_suppkey = s_suppkey
  				and s_nationkey = n_nationkey
  				and n_name = 'GERMANY'
  		)
  order by
  	value desc;
  
  -- $ID$
  -- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_shipmode,
  	sum(case
  		when o_orderpriority = '1-URGENT'
  			or o_orderpriority = '2-HIGH'
  			then 1
  		else 0
  	end) as high_line_count,
  	sum(case
  		when o_orderpriority <> '1-URGENT'
  			and o_orderpriority <> '2-HIGH'
  			then 1
  		else 0
  	end) as low_line_count
  from
  	orders,
  	lineitem
  where
  	o_orderkey = l_orderkey
  	and l_shipmode in ('MAIL', 'SHIP')
  	and l_commitdate < l_receiptdate
  	and l_shipdate < l_commitdate
  	and l_receiptdate >= date '1994-01-01'
  	and l_receiptdate < date '1994-01-01' + interval '1 year'
  group by
  	l_shipmode
  order by
  	l_shipmode;
  
  -- $ID$
  -- TPC-H/TPC-R Customer Distribution Query (Q13)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	c_count,
  	count(*) as custdist
  from
  	(
  		select
  			c_custkey,
  			count(*)
  		from
  			customer left outer join orders on
  				c_custkey = o_custkey
  				and o_comment not like '%special%request%'
  		group by
  			c_custkey
  	) as c_orders (c_custkey, c_count)
  group by
  	c_count
  order by
  	custdist desc,
  	c_count desc;
  
  -- $ID$
  -- TPC-H/TPC-R Promotion Effect Query (Q14)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	100 * sum(case
  		when p_type like 'PROMO%'
  			then l_extendedprice * (1 - l_discount)/10
  		else 0
  	end) / sum(l_extendedprice * (1 - l_discount)/1000) as promo_revenue --add /1000
  from
  	lineitem,
  	part
  where
  	l_partkey = p_partkey
  	and l_shipdate >= date '1995-09-01'
  	and l_shipdate < date '1995-09-01' + interval '1 month';
  
  -- $ID$
  -- TPC-H/TPC-R Top Supplier Query (Q15)
  -- Functional Query Definition
  -- Approved February 1998
  explain
  with revenue (supplier_no, total_revenue) as --change view to cte
  (
  	select
  		l_suppkey,
  		sum(l_extendedprice * (1 - l_discount))
  	from
  		lineitem
  	where
  		l_shipdate >= date '1996-01-01'
  		and l_shipdate < date '1996-01-01' + interval '3 month'
  	group by
  		l_suppkey
  )
  select
  	s_suppkey,
  	s_name,
  	s_address,
  	s_phone,
  	total_revenue
  from
  	supplier,
  	revenue
  where
  	s_suppkey = supplier_no
  	and total_revenue = (
  		select
  			max(total_revenue)
  		from
  			revenue
  	)
  order by
  	s_suppkey;
  
  -- $ID$
  -- TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	p_brand,
  	p_type,
  	p_size,
  	count(ps_suppkey) as supplier_cnt --remove distinct
  from
  	partsupp,
  	part
  where
  	p_partkey = ps_partkey
  	and p_brand <> 'Brand#45'
  	and p_type not like 'MEDIUM POLISHED%'
  	and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
  	and ps_suppkey not in (
  		select
  			s_suppkey
  		from
  			supplier
  		where
  			s_comment like '%Customer%Complaints%'
  	)
  group by
  	p_brand,
  	p_type,
  	p_size
  order by
  	supplier_cnt desc,
  	p_brand,
  	p_type,
  	p_size
  limit 100
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Small-Quantity-Order Revenue Query (Q17)
  -- Functional Query Definition
  -- Approved February 1998
  -- Query modified: a subquery is moved from WHERE clause to FROM clause. 
  explain select
  	sum(l_extendedprice) / 7.0 as avg_yearly
  from
  	lineitem,
  	part,
  	(select 
  	    l_partkey as temp_l_partkey, 0.2 * avg(l_quantity) as temp_avg
  	 from
  		lineitem
  	 group by l_partkey
  	) as temp
  where
  	p_partkey = l_partkey
  	and p_brand = 'Brand#23'
  	and p_container = 'MED BOX'
  	and l_quantity < temp_avg
  	and p_partkey = temp_l_partkey;
  
  -- $ID$
  -- TPC-H/TPC-R Large Volume Customer Query (Q18)
  -- Function Query Definition
  -- Approved February 1998
  explain select
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice,
  	sum(l_quantity)
  from
  	customer,
  	orders,
  	lineitem
  where
  	o_orderkey in (
  		select
  			l_orderkey
  		from
  			lineitem
  		group by
  			l_orderkey having
  				sum(l_quantity) > 300
  	)
  	and c_custkey = o_custkey
  	and o_orderkey = l_orderkey
  group by
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice
  order by
  	o_totalprice desc,
  	o_orderdate
  limit 100;
  
  -- $ID$
  -- TPC-H/TPC-R Discounted Revenue Query (Q19)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	sum(l_extendedprice* (1 - l_discount)) as revenue
  from
  	lineitem,
  	part
  where
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#12'
  		and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
  		and l_quantity >= 1 and l_quantity <= 1 + 10
  		and p_size between 1 and 5
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#23'
  		and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
  		and l_quantity >= 10 and l_quantity <= 10 + 10
  		and p_size between 1 and 10
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#34'
  		and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
  		and l_quantity >= 20 and l_quantity <= 20 + 10
  		and p_size between 1 and 15
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	);
  
  -- $ID$
  -- TPC-H/TPC-R Potential Part Promotion Query (Q20)
  -- Function Query Definition
  -- Approved February 1998
  -- The query has been changed!
  explain select
  	s_name,
  	s_address
  from
  	supplier,
  	nation
  where
  	s_suppkey in (
  		select
  			ps_suppkey
  		from
  			partsupp,
  			(
  				select
  					l_partkey as temp_l_partkey, l_suppkey as temp_l_suppkey, 0.5 * sum(l_quantity) as temp_l_quantity
  				from
  					lineitem
  				where
  					l_shipdate >= date '1994-01-01'
  					and l_shipdate < date '1994-01-01' + interval '1 year'
  				group by l_partkey, l_suppkey
  			) as temp
  		where
  		    temp_l_partkey = ps_partkey
  			and temp_l_suppkey = ps_suppkey
  			and ps_partkey in (
  				select
  					p_partkey
  				from
  					part
  				where
  					p_name like 'forest%'
  			)
  			and ps_availqty > temp_l_quantity
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'CANADA'
  order by
  	s_name;
  
  -- $ID$
  -- TPC-H/TPC-R Suppliers Who Kept Orders Waiting Query (Q21)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	s_name,
  	count(*) as numwait
  from
  	supplier,
  	lineitem l1,
  	orders,
  	nation
  where
  	s_suppkey = l1.l_suppkey
  	and o_orderkey = l1.l_orderkey
  	and o_orderstatus = 'F'
  	and l1.l_receiptdate > l1.l_commitdate
  	and exists (
  		select
  			*
  		from
  			lineitem l2
  		where
  			l2.l_orderkey = l1.l_orderkey
  			and l2.l_suppkey <> l1.l_suppkey
  	)
  	and not exists (
  		select
  			*
  		from
  			lineitem l3
  		where
  			l3.l_orderkey = l1.l_orderkey
  			and l3.l_suppkey <> l1.l_suppkey
  			and l3.l_receiptdate > l3.l_commitdate
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'SAUDI ARABIA'
  group by
  	s_name
  order by
  	numwait desc,
  	s_name
  limit 100;
  
  -- $ID$
  -- TPC-H/TPC-R Global Sales Opportunity Query (Q22)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	cntrycode,
  	count(*) as numcust,
  	sum(c_acctbal) as totacctbal
  from
  	(
  		select
  			substring(c_phone from 1 for 2) as cntrycode,
  			c_acctbal
  		from
  			customer
  		where
  			substring(c_phone from 1 for 2) in
  				('13', '31', '23', '29', '30', '18', '17')
  			and c_acctbal > (
  				select
  					avg(c_acctbal)
  				from
  					customer
  				where
  					c_acctbal > 0.00
  					and substring(c_phone from 1 for 2) in
  						('13', '31', '23', '29', '30', '18', '17')
  			)
  			and not exists (
  				select
  					*
  				from
  					orders
  				where
  					o_custkey = c_custkey
  			)
  	) as custsale
  group by
  	cntrycode
  order by
  	cntrycode;
  
  -- Test on nestloop 
  set enable_hashjoin=off;
  set enable_mergejoin=off;
  set enable_nestloop=on;
  
  -- $ID$
  -- TPC-H/TPC-R Pricing Summary Report Query (Q1)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_returnflag,
  	l_linestatus,
  	sum(l_quantity) as sum_qty,
  	sum(l_extendedprice) as sum_base_price,
  	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) as sum_charge, --add /1000
  	avg(l_quantity) as avg_qty,
  	avg(l_extendedprice) as avg_price,
  	avg(l_discount) as avg_disc,
  	count(*) as count_order
  from
  	lineitem
  where
  	l_shipdate <= date '1998-12-01' - interval '3 day'
  group by
  	l_returnflag,
  	l_linestatus
  order by
  	l_returnflag,
  	l_linestatus;
  
  -- $ID$
  -- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	s_acctbal,
  	s_name,
  	n_name,
  	p_partkey,
  	p_mfgr,
  	s_address,
  	s_phone,
  	s_comment
  from
  	part,
  	supplier,
  	partsupp,
  	nation,
  	region,
  	(
  		select
  			ps_partkey as temp_ps_partkey, min(ps_supplycost) as temp_min_ps_supplycost
  		from
  			partsupp,
  			supplier,
  			nation,
  			region
  		where
  			s_suppkey = ps_suppkey
  			and s_nationkey = n_nationkey
  			and n_regionkey = r_regionkey
  			and r_name = 'EUROPE'
  	    group by ps_partkey
  	) as temp
  where
      p_partkey = temp_ps_partkey
  	and p_partkey = ps_partkey
  	and s_suppkey = ps_suppkey
  	and p_size = 15
  	and p_type like '%BRASS'
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'EUROPE'
  	and ps_supplycost = temp_min_ps_supplycost
  order by
  	s_acctbal desc,
  	n_name,
  	s_name,
  	p_partkey
  limit 100
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Shipping Priority Query (Q3)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_orderkey,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	o_orderdate,
  	o_shippriority
  from
  	customer,
  	orders,
  	lineitem
  where
  	c_mktsegment = 'BUILDING'
  	and c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate < '1995-03-15'::date
  	and l_shipdate > '1995-03-15'::date
  group by
  	l_orderkey,
  	o_orderdate,
  	o_shippriority
  order by
  	revenue desc,
  	o_orderdate
  limit 10
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Order Priority Checking Query (Q4)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	o_orderpriority,
  	count(*) as order_count
  from
  	orders
  where
  	o_orderdate >= '1993-07-01'::date
  	and o_orderdate < '1993-07-01'::date + interval '3 month'
  	and exists (
  		select
  			*
  		from
  			lineitem
  		where
  			l_orderkey = o_orderkey
  			and l_commitdate < l_receiptdate
  	)
  group by
  	o_orderpriority
  order by
  	o_orderpriority;
  
  -- $ID$
  -- TPC-H/TPC-R Local Supplier Volume Query (Q5)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	n_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue
  from
  	customer,
  	orders,
  	lineitem,
  	supplier,
  	nation,
  	region
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and l_suppkey = s_suppkey
  	and c_nationkey = s_nationkey
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'ASIA'
  	and o_orderdate >= '1994-01-01'::date
  	and o_orderdate < '1994-01-01'::date + interval '1 year'
  group by
  	n_name
  order by
  	revenue desc;
  
  -- $ID$
  -- TPC-H/TPC-R Forecasting Revenue Change Query (Q6)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	sum(l_extendedprice * l_discount) as revenue
  from
  	lineitem
  where
  	l_shipdate >= '1994-01-01'::date 
  	and l_shipdate < '1994-01-01'::date + interval '1 year'
  	and l_discount between 0.06 - 0.01 and 0.06 + 0.01
  	and l_quantity < 24;
  
  -- $ID$
  -- TPC-H/TPC-R Volume Shipping Query (Q7)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	supp_nation,
  	cust_nation,
  	l_year,
  	sum(volume) as revenue
  from
  	(
  		select
  			n1.n_name as supp_nation,
  			n2.n_name as cust_nation,
  			extract(year from l_shipdate) as l_year,
  			l_extendedprice * (1 - l_discount) as volume
  		from
  			supplier,
  			lineitem,
  			orders,
  			customer,
  			nation n1,
  			nation n2
  		where
  			s_suppkey = l_suppkey
  			and o_orderkey = l_orderkey
  			and c_custkey = o_custkey
  			and s_nationkey = n1.n_nationkey
  			and c_nationkey = n2.n_nationkey
  			and (
  				(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
  				or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
  			)
  			and l_shipdate between date '1995-01-01' and date '1996-12-31'
  	) as shipping
  group by
  	supp_nation,
  	cust_nation,
  	l_year
  order by
  	supp_nation,
  	cust_nation,
  	l_year;
  
  -- $ID$
  -- TPC-H/TPC-R National Market Share Query (Q8)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	o_year,
  	sum(case
  		when nation = 'BRAZIL' then volume
  		else 0
  	end) / sum(volume) as mkt_share
  from
  	(
  		select
  			extract(year from o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) as volume,
  			n2.n_name as nation
  		from
  			part,
  			supplier,
  			lineitem,
  			orders,
  			customer,
  			nation n1,
  			nation n2,
  			region
  		where
  			p_partkey = l_partkey
  			and s_suppkey = l_suppkey
  			and l_orderkey = o_orderkey
  			and o_custkey = c_custkey
  			and c_nationkey = n1.n_nationkey
  			and n1.n_regionkey = r_regionkey
  			and r_name = 'AMERICA'
  			and s_nationkey = n2.n_nationkey
  			and o_orderdate between date '1995-01-01' and date '1996-12-31'
  			and p_type = 'ECONOMY ANODIZED STEEL'
  	) as all_nations
  group by
  	o_year
  order by
  	o_year;
  
  -- $ID$
  -- TPC-H/TPC-R Product Type Profit Measure Query (Q9)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	nation,
  	o_year,
  	sum(amount) as sum_profit
  from
  	(
  		select
  			n_name as nation,
  			extract(year from o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
  		from
  			part,
  			supplier,
  			lineitem,
  			partsupp,
  			orders,
  			nation
  		where
  			s_suppkey = l_suppkey
  			and ps_suppkey = l_suppkey
  			and ps_partkey = l_partkey
  			and p_partkey = l_partkey
  			and o_orderkey = l_orderkey
  			and s_nationkey = n_nationkey
  			and p_name like '%green%'
  	) as profit
  group by
  	nation,
  	o_year
  order by
  	nation,
  	o_year desc;
  
  -- $ID$
  -- TPC-H/TPC-R Returned Item Reporting Query (Q10)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	c_custkey,
  	c_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	c_acctbal,
  	n_name,
  	c_address,
  	c_phone,
  	c_comment
  from
  	customer,
  	orders,
  	lineitem,
  	nation
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate >= date '1993-10-01'
  	and o_orderdate < date '1993-10-01' + interval '3 month'
  	and l_returnflag = 'R'
  	and c_nationkey = n_nationkey
  group by
  	c_custkey,
  	c_name,
  	c_acctbal,
  	c_phone,
  	n_name,
  	c_address,
  	c_comment
  order by
  	revenue desc
  limit 20
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Important Stock Identification Query (Q11)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	ps_partkey,
  	sum(ps_supplycost * ps_availqty) as value
  from
  	partsupp,
  	supplier,
  	nation
  where
  	ps_suppkey = s_suppkey
  	and s_nationkey = n_nationkey
  	and n_name = 'GERMANY'
  group by
  	ps_partkey having
  		sum(ps_supplycost * ps_availqty) > (
  			select
  				sum(ps_supplycost * ps_availqty) * 0.0001/100
  			from
  				partsupp,
  				supplier,
  				nation
  			where
  				ps_suppkey = s_suppkey
  				and s_nationkey = n_nationkey
  				and n_name = 'GERMANY'
  		)
  order by
  	value desc;
  
  -- $ID$
  -- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	l_shipmode,
  	sum(case
  		when o_orderpriority = '1-URGENT'
  			or o_orderpriority = '2-HIGH'
  			then 1
  		else 0
  	end) as high_line_count,
  	sum(case
  		when o_orderpriority <> '1-URGENT'
  			and o_orderpriority <> '2-HIGH'
  			then 1
  		else 0
  	end) as low_line_count
  from
  	orders,
  	lineitem
  where
  	o_orderkey = l_orderkey
  	and l_shipmode in ('MAIL', 'SHIP')
  	and l_commitdate < l_receiptdate
  	and l_shipdate < l_commitdate
  	and l_receiptdate >= date '1994-01-01'
  	and l_receiptdate < date '1994-01-01' + interval '1 year'
  group by
  	l_shipmode
  order by
  	l_shipmode;
  
  -- $ID$
  -- TPC-H/TPC-R Customer Distribution Query (Q13)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	c_count,
  	count(*) as custdist
  from
  	(
  		select
  			c_custkey,
  			count(*)
  		from
  			customer left outer join orders on
  				c_custkey = o_custkey
  				and o_comment not like '%special%request%'
  		group by
  			c_custkey
  	) as c_orders (c_custkey, c_count)
  group by
  	c_count
  order by
  	custdist desc,
  	c_count desc;
  
  -- $ID$
  -- TPC-H/TPC-R Promotion Effect Query (Q14)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	100 * sum(case
  		when p_type like 'PROMO%'
  			then l_extendedprice * (1 - l_discount)/10
  		else 0
  	end) / sum(l_extendedprice * (1 - l_discount)/1000) as promo_revenue --add /1000
  from
  	lineitem,
  	part
  where
  	l_partkey = p_partkey
  	and l_shipdate >= date '1995-09-01'
  	and l_shipdate < date '1995-09-01' + interval '1 month';
  
  -- $ID$
  -- TPC-H/TPC-R Top Supplier Query (Q15)
  -- Functional Query Definition
  -- Approved February 1998
  explain
  with revenue (supplier_no, total_revenue) as --change view to cte
  (
  	select
  		l_suppkey,
  		sum(l_extendedprice * (1 - l_discount))
  	from
  		lineitem
  	where
  		l_shipdate >= date '1996-01-01'
  		and l_shipdate < date '1996-01-01' + interval '3 month'
  	group by
  		l_suppkey
  )
  select
  	s_suppkey,
  	s_name,
  	s_address,
  	s_phone,
  	total_revenue
  from
  	supplier,
  	revenue
  where
  	s_suppkey = supplier_no
  	and total_revenue = (
  		select
  			max(total_revenue)
  		from
  			revenue
  	)
  order by
  	s_suppkey;
  
  -- $ID$
  -- TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	p_brand,
  	p_type,
  	p_size,
  	count(ps_suppkey) as supplier_cnt --remove distinct
  from
  	partsupp,
  	part
  where
  	p_partkey = ps_partkey
  	and p_brand <> 'Brand#45'
  	and p_type not like 'MEDIUM POLISHED%'
  	and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
  	and ps_suppkey not in (
  		select
  			s_suppkey
  		from
  			supplier
  		where
  			s_comment like '%Customer%Complaints%'
  	)
  group by
  	p_brand,
  	p_type,
  	p_size
  order by
  	supplier_cnt desc,
  	p_brand,
  	p_type,
  	p_size
  limit 100
  ;
  
  -- $ID$
  -- TPC-H/TPC-R Small-Quantity-Order Revenue Query (Q17)
  -- Functional Query Definition
  -- Approved February 1998
  -- Query modified: a subquery is moved from WHERE clause to FROM clause. 
  explain select
  	sum(l_extendedprice) / 7.0 as avg_yearly
  from
  	lineitem,
  	part,
  	(select 
  	    l_partkey as temp_l_partkey, 0.2 * avg(l_quantity) as temp_avg
  	 from
  		lineitem
  	 group by l_partkey
  	) as temp
  where
  	p_partkey = l_partkey
  	and p_brand = 'Brand#23'
  	and p_container = 'MED BOX'
  	and l_quantity < temp_avg
  	and p_partkey = temp_l_partkey;
  
  -- $ID$
  -- TPC-H/TPC-R Large Volume Customer Query (Q18)
  -- Function Query Definition
  -- Approved February 1998
  explain select
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice,
  	sum(l_quantity)
  from
  	customer,
  	orders,
  	lineitem
  where
  	o_orderkey in (
  		select
  			l_orderkey
  		from
  			lineitem
  		group by
  			l_orderkey having
  				sum(l_quantity) > 300
  	)
  	and c_custkey = o_custkey
  	and o_orderkey = l_orderkey
  group by
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice
  order by
  	o_totalprice desc,
  	o_orderdate
  limit 100;
  
  -- $ID$
  -- TPC-H/TPC-R Discounted Revenue Query (Q19)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	sum(l_extendedprice* (1 - l_discount)) as revenue
  from
  	lineitem,
  	part
  where
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#12'
  		and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
  		and l_quantity >= 1 and l_quantity <= 1 + 10
  		and p_size between 1 and 5
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#23'
  		and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
  		and l_quantity >= 10 and l_quantity <= 10 + 10
  		and p_size between 1 and 10
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_partkey = l_partkey
  		and p_brand = 'Brand#34'
  		and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
  		and l_quantity >= 20 and l_quantity <= 20 + 10
  		and p_size between 1 and 15
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	);
  
  -- $ID$
  -- TPC-H/TPC-R Potential Part Promotion Query (Q20)
  -- Function Query Definition
  -- Approved February 1998
  -- The query has been changed!
  explain select
  	s_name,
  	s_address
  from
  	supplier,
  	nation
  where
  	s_suppkey in (
  		select
  			ps_suppkey
  		from
  			partsupp,
  			(
  				select
  					l_partkey as temp_l_partkey, l_suppkey as temp_l_suppkey, 0.5 * sum(l_quantity) as temp_l_quantity
  				from
  					lineitem
  				where
  					l_shipdate >= date '1994-01-01'
  					and l_shipdate < date '1994-01-01' + interval '1 year'
  				group by l_partkey, l_suppkey
  			) as temp
  		where
  		    temp_l_partkey = ps_partkey
  			and temp_l_suppkey = ps_suppkey
  			and ps_partkey in (
  				select
  					p_partkey
  				from
  					part
  				where
  					p_name like 'forest%'
  			)
  			and ps_availqty > temp_l_quantity
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'CANADA'
  order by
  	s_name;
  
  -- $ID$
  -- TPC-H/TPC-R Suppliers Who Kept Orders Waiting Query (Q21)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	s_name,
  	count(*) as numwait
  from
  	supplier,
  	lineitem l1,
  	orders,
  	nation
  where
  	s_suppkey = l1.l_suppkey
  	and o_orderkey = l1.l_orderkey
  	and o_orderstatus = 'F'
  	and l1.l_receiptdate > l1.l_commitdate
  	and exists (
  		select
  			*
  		from
  			lineitem l2
  		where
  			l2.l_orderkey = l1.l_orderkey
  			and l2.l_suppkey <> l1.l_suppkey
  	)
  	and not exists (
  		select
  			*
  		from
  			lineitem l3
  		where
  			l3.l_orderkey = l1.l_orderkey
  			and l3.l_suppkey <> l1.l_suppkey
  			and l3.l_receiptdate > l3.l_commitdate
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'SAUDI ARABIA'
  group by
  	s_name
  order by
  	numwait desc,
  	s_name
  limit 100;
  
  -- $ID$
  -- TPC-H/TPC-R Global Sales Opportunity Query (Q22)
  -- Functional Query Definition
  -- Approved February 1998
  explain select
  	cntrycode,
  	count(*) as numcust,
  	sum(c_acctbal) as totacctbal
  from
  	(
  		select
  			substring(c_phone from 1 for 2) as cntrycode,
  			c_acctbal
  		from
  			customer
  		where
  			substring(c_phone from 1 for 2) in
  				('13', '31', '23', '29', '30', '18', '17')
  			and c_acctbal > (
  				select
  					avg(c_acctbal)
  				from
  					customer
  				where
  					c_acctbal > 0.00
  					and substring(c_phone from 1 for 2) in
  						('13', '31', '23', '29', '30', '18', '17')
  			)
  			and not exists (
  				select
  					*
  				from
  					orders
  				where
  					o_custkey = c_custkey
  			)
  	) as custsale
  group by
  	cntrycode
  order by
  	cntrycode;
  \o
  