>
>
> Hi, All.
>
> I ran tpc-h on a hawq cluster. The cluster consists of 1 master node and 3
> segment nodes, and the same with HDFS cluster. Meanwhile, I build a gpdb
> cluster on same nodes.
>
> Each node(exactly each vm) has the same configurations -- 4GB memory, 1
> vcpu, and sharing host network. I used dbgen to generate 3GB data, then
> loaded into hawq and gpdb.
>
> I ran Query 4 in tpc-h. gpdb finished it in a few seconds, but hawq got
> stuck for tens of minutes and more.
>
> Query 4 is as follows:
>
> *select*
> * o_orderpriority,*
> * count(*) as order_count*
> *from*
> * orders*
> *where*
> * o_orderdate >= date '1996-03-01'*
> * and o_orderdate < date '1996-03-01' + interval '3 month'*
> * and exists (*
> * select*
> * **
> * from*
> * lineitem*
> * where*
> * l_orderkey = o_orderkey*
> * and l_commitdate < l_receiptdate*
> * )*
> *group by*
> * o_orderpriority*
> *order by^M*
> * o_orderpriority*
> *LIMIT 1;*
>
> Moreover, I retrieved both of query plans. I found hawq produced a huge
> cost plan while gpdb did a small one. The obvious difference between them
> is that hawq adopted
> nest loop join but gpdb adopted hash join. I checked that the parameter
> 'enable_nestloop' is off by default.
>
> The gpdb plan is as follows:
>
> QUERY PLAN
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> Limit (cost=407633.93..407633.96 rows=1 width=72)
> -> Gather Motion 12:1 (slice3; segments: 12)
> (cost=407633.93..407633.96 rows=1 width=72)
> Merge Key: partial_aggregation.o_orderpriority
> -> Limit (cost=407633.93..407633.94 rows=1 width=72)
> -> Sort (cost=407633.93..407633.94 rows=1 width=72)
> Sort Key (Limit): partial_aggregation.o_orderpriority
> -> HashAggregate (cost=407633.91..407633.92 rows=1
> width=72)
> Group By: orders.o_orderpriority
> -> Redistribute Motion 12:12 (slice2;
> segments: 12) (cost=407633.88..407633.90 rows=1 width=72)
> Hash Key: orders.o_orderpriority
> -> HashAggregate
> (cost=407633.88..407633.88 rows=1 width=72)
> Group By: orders.o_orderpriority
> -> HashAggregate
> (cost=404041.00..406436.25 rows=19961 width=74)
> Group By:
> orders.ctid::bigint, orders.gp_segment_id
> -> Hash Join
> (cost=89634.53..402843.37 rows=19961 width=26)
> Hash Cond:
> lineitem.l_orderkey = orders.o_orderkey
> -> Append-only
> Columnar Scan on lineitem (cost=0.00..295217.60 rows=499906 width=4)
> Filter:
> l_commitdate < l_receiptdate
> -> Hash
> (cost=87388.56..87388.56 rows=14974 width=34)
> -> Redistribute
> Motion 12:12 (slice1; segments: 12) (cost=0.00..87388.56 rows=14974
> width=34)
> Hash Key:
> orders.o_orderkey
> ->
> Append-only Columnar Scan on orders (cost=0.00..83795.00 rows=14974
> width=34)
>
> Filter: o_orderdate >= '1996-03-01'::date AND o_orderdate < '1996-06-01
> 00:00:00'::timestamp without time zone
> (23 rows)
>
> The hawq plan is as follows:
>
> QUERY PLAN
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------------------
> Limit (cost=424555364732.86..424555364732.88 rows=1 width=72)
> -> Gather Motion 18:1 (slice4; segments: 18)
> (cost=424555364732.86..424555364732.88 rows=1 width=72)
> Merge Key: partial_aggregation.o_orderpriority
> -> Limit (cost=424555364732.86..424555364732.86 rows=1
> width=72)
> -> Sort (cost=424555364732.86..424555364732.86 rows=1
> width=72)
> Sort Key (Limit): partial_aggregation.o_orderpriority
> -> HashAggregate (cost=424555364732.84..424555364732.85
> rows=1 width=72)
> Group By: orders.o_orderpriority
> -> Redistribute Motion 18:18 (slice3;
> segments: 18) (cost=424555364732.80..424555364732.82 rows=1 width=72)
> Hash Key: orders.o_orderpriority
> -> HashAggregate
> (cost=424555364732.80..424555364732.80 rows=1 width=72)
> Group By: orders.o_orderpriority
> -> HashAggregate
> (cost=424555361238.53..424555363568.04 rows=12942 width=74)
> Group By:
> orders.ctid::bigint, orders.gp_segment_id
> -> Result
> (cost=122726.35..424555360073.77 rows=12942 width=26)
> -> Redistribute Motion
> 18:18 (slice2; segments: 18) (cost=122726.35..424555360073.77
> rows=12942 width=26)
> Hash Key:
> orders.ctid
> -> Nested Loop
> (cost=122726.35..424555355414.73 rows=12942 width=26)
> Join
> Filter: lineitem.l_orderkey = orders.o_orderkey
> -> Parquet
> table Scan on lineitem (cost=0.00..318758.60 rows=333271 width=4)
>
> Filter: l_commitdate < l_receiptdate
> ->
> Materialize (cost=122726.35..154180.79 rows=174747 width=34)
> ->
> Broadcast Motion 18:18 (slice1; segments: 18) (cost=0.00..119580.91
> rows=174747 width=34)
>
> -> Parquet table Scan on orders (cost=0.00..86379.00 rows=9709 width=34)
>
> Filter: o_orderdate >= '1996-03-01'::date AND o_orderdate <
> '1996-06-01 00:00:00'::timestamp without time zone
> Settings: default_hash_table_bucket_number=18
> (26 rows)
>
> Please suggest explanations why this happens. Thank you!
>
>
