http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/1bbd667f/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test index f4a8e73..334f7b2 100644 --- a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test +++ b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test @@ -36,118 +36,117 @@ limit 100 | group by: dt.d_year, item.i_brand, item.i_brand_id | 04:HASH JOIN [INNER JOIN] -| hash predicates: store_sales.ss_sold_date_sk = dt.d_date_sk -| runtime filters: RF000 <- dt.d_date_sk -| -|--00:SCAN HDFS [tpcds.date_dim dt] -| partitions=1/1 files=1 size=9.84MB -| predicates: dt.d_moy = 12, (dt.d_date_sk BETWEEN 2451149 AND 2451179 OR dt.d_date_sk BETWEEN 2451514 AND 2451544 OR dt.d_date_sk BETWEEN 2451880 AND 2451910 OR dt.d_date_sk BETWEEN 2452245 AND 2452275 OR dt.d_date_sk BETWEEN 2452610 AND 2452640) -| -03:HASH JOIN [INNER JOIN] -| hash predicates: store_sales.ss_item_sk = item.i_item_sk -| runtime filters: RF001 <- item.i_item_sk +| hash predicates: dt.d_date_sk = store_sales.ss_sold_date_sk +| runtime filters: RF000 <- store_sales.ss_sold_date_sk | -|--02:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: item.i_manufact_id = 436 +|--03:HASH JOIN [INNER JOIN] +| | hash predicates: store_sales.ss_item_sk = item.i_item_sk +| | runtime filters: RF001 <- item.i_item_sk +| | +| |--02:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: item.i_manufact_id = 436 +| | +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=10/120 files=10 size=3.53MB +| runtime filters: RF001 -> store_sales.ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=10/120 files=10 size=3.53MB - runtime filters: RF000 -> store_sales.ss_sold_date_sk, RF001 -> store_sales.ss_item_sk +00:SCAN HDFS [tpcds.date_dim dt] + partitions=1/1 files=1 size=9.84MB + predicates: dt.d_moy = 12, (dt.d_date_sk BETWEEN 2451149 AND 2451179 OR dt.d_date_sk BETWEEN 2451514 AND 2451544 OR dt.d_date_sk BETWEEN 2451880 AND 2451910 OR dt.d_date_sk BETWEEN 2452245 AND 2452275 OR dt.d_date_sk BETWEEN 2452610 AND 2452640) + runtime filters: RF000 -> dt.d_date_sk ---- DISTRIBUTEDPLAN -12:MERGING-EXCHANGE [UNPARTITIONED] +11:MERGING-EXCHANGE [UNPARTITIONED] | order by: dt.d_year ASC, sum(ss_ext_sales_price) DESC, item.i_brand_id ASC | limit: 100 | 06:TOP-N [LIMIT=100] | order by: dt.d_year ASC, sum(ss_ext_sales_price) DESC, item.i_brand_id ASC | -11:AGGREGATE [FINALIZE] +10:AGGREGATE [FINALIZE] | output: sum:merge(ss_ext_sales_price) | group by: dt.d_year, item.i_brand, item.i_brand_id | -10:EXCHANGE [HASH(dt.d_year,item.i_brand,item.i_brand_id)] +09:EXCHANGE [HASH(dt.d_year,item.i_brand,item.i_brand_id)] | 05:AGGREGATE [STREAMING] | output: sum(ss_ext_sales_price) | group by: dt.d_year, item.i_brand, item.i_brand_id | -04:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: store_sales.ss_sold_date_sk = dt.d_date_sk -| runtime filters: RF000 <- dt.d_date_sk +04:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: dt.d_date_sk = store_sales.ss_sold_date_sk +| runtime filters: RF000 <- store_sales.ss_sold_date_sk | -|--09:EXCHANGE [HASH(dt.d_date_sk)] +|--08:EXCHANGE [BROADCAST] | | -| 00:SCAN HDFS [tpcds.date_dim dt] -| partitions=1/1 files=1 size=9.84MB -| predicates: dt.d_moy = 12, (dt.d_date_sk BETWEEN 2451149 AND 2451179 OR dt.d_date_sk BETWEEN 2451514 AND 2451544 OR dt.d_date_sk BETWEEN 2451880 AND 2451910 OR dt.d_date_sk BETWEEN 2452245 AND 2452275 OR dt.d_date_sk BETWEEN 2452610 AND 2452640) -| -08:EXCHANGE [HASH(store_sales.ss_sold_date_sk)] -| -03:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: store_sales.ss_item_sk = item.i_item_sk -| runtime filters: RF001 <- item.i_item_sk -| -|--07:EXCHANGE [BROADCAST] +| 03:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: store_sales.ss_item_sk = item.i_item_sk +| | runtime filters: RF001 <- item.i_item_sk | | -| 02:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: item.i_manufact_id = 436 +| |--07:EXCHANGE [BROADCAST] +| | | +| | 02:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: item.i_manufact_id = 436 +| | +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=10/120 files=10 size=3.53MB +| runtime filters: RF001 -> store_sales.ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=10/120 files=10 size=3.53MB - runtime filters: RF000 -> store_sales.ss_sold_date_sk, RF001 -> store_sales.ss_item_sk +00:SCAN HDFS [tpcds.date_dim dt] + partitions=1/1 files=1 size=9.84MB + predicates: dt.d_moy = 12, (dt.d_date_sk BETWEEN 2451149 AND 2451179 OR dt.d_date_sk BETWEEN 2451514 AND 2451544 OR dt.d_date_sk BETWEEN 2451880 AND 2451910 OR dt.d_date_sk BETWEEN 2452245 AND 2452275 OR dt.d_date_sk BETWEEN 2452610 AND 2452640) + runtime filters: RF000 -> dt.d_date_sk ---- PARALLELPLANS -12:MERGING-EXCHANGE [UNPARTITIONED] +11:MERGING-EXCHANGE [UNPARTITIONED] | order by: dt.d_year ASC, sum(ss_ext_sales_price) DESC, item.i_brand_id ASC | limit: 100 | 06:TOP-N [LIMIT=100] | order by: dt.d_year ASC, sum(ss_ext_sales_price) DESC, item.i_brand_id ASC | -11:AGGREGATE [FINALIZE] +10:AGGREGATE [FINALIZE] | output: sum:merge(ss_ext_sales_price) | group by: dt.d_year, item.i_brand, item.i_brand_id | -10:EXCHANGE [HASH(dt.d_year,item.i_brand,item.i_brand_id)] +09:EXCHANGE [HASH(dt.d_year,item.i_brand,item.i_brand_id)] | 05:AGGREGATE [STREAMING] | output: sum(ss_ext_sales_price) | group by: dt.d_year, item.i_brand, item.i_brand_id | -04:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: store_sales.ss_sold_date_sk = dt.d_date_sk -| runtime filters: RF000 <- dt.d_date_sk +04:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: dt.d_date_sk = store_sales.ss_sold_date_sk +| runtime filters: RF000 <- store_sales.ss_sold_date_sk | |--JOIN BUILD | | join-table-id=00 plan-id=01 cohort-id=01 -| | build expressions: dt.d_date_sk +| | build expressions: store_sales.ss_sold_date_sk | | -| 09:EXCHANGE [HASH(dt.d_date_sk)] +| 08:EXCHANGE [BROADCAST] | | -| 00:SCAN HDFS [tpcds.date_dim dt] -| partitions=1/1 files=1 size=9.84MB -| predicates: dt.d_moy = 12, (dt.d_date_sk BETWEEN 2451149 AND 2451179 OR dt.d_date_sk BETWEEN 2451514 AND 2451544 OR dt.d_date_sk BETWEEN 2451880 AND 2451910 OR dt.d_date_sk BETWEEN 2452245 AND 2452275 OR dt.d_date_sk BETWEEN 2452610 AND 2452640) -| -08:EXCHANGE [HASH(store_sales.ss_sold_date_sk)] -| -03:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: store_sales.ss_item_sk = item.i_item_sk -| runtime filters: RF001 <- item.i_item_sk -| -|--JOIN BUILD -| | join-table-id=01 plan-id=02 cohort-id=01 -| | build expressions: item.i_item_sk +| 03:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: store_sales.ss_item_sk = item.i_item_sk +| | runtime filters: RF001 <- item.i_item_sk | | -| 07:EXCHANGE [BROADCAST] +| |--JOIN BUILD +| | | join-table-id=01 plan-id=02 cohort-id=02 +| | | build expressions: item.i_item_sk +| | | +| | 07:EXCHANGE [BROADCAST] +| | | +| | 02:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: item.i_manufact_id = 436 | | -| 02:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: item.i_manufact_id = 436 +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=10/120 files=10 size=3.53MB +| runtime filters: RF001 -> store_sales.ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=10/120 files=10 size=3.53MB - runtime filters: RF000 -> store_sales.ss_sold_date_sk, RF001 -> store_sales.ss_item_sk +00:SCAN HDFS [tpcds.date_dim dt] + partitions=1/1 files=1 size=9.84MB + predicates: dt.d_moy = 12, (dt.d_date_sk BETWEEN 2451149 AND 2451179 OR dt.d_date_sk BETWEEN 2451514 AND 2451544 OR dt.d_date_sk BETWEEN 2451880 AND 2451910 OR dt.d_date_sk BETWEEN 2452245 AND 2452275 OR dt.d_date_sk BETWEEN 2452610 AND 2452640) + runtime filters: RF000 -> dt.d_date_sk ==== # TPCDS-Q7 select @@ -1715,60 +1714,61 @@ limit 100 | order by: c_last_name ASC, c_first_name ASC, ca_city ASC, bought_city ASC, ss_ticket_number ASC | 13:HASH JOIN [INNER JOIN] -| hash predicates: customer.c_current_addr_sk = current_addr.ca_address_sk +| hash predicates: current_addr.ca_address_sk = customer.c_current_addr_sk | other predicates: current_addr.ca_city != ca_city -| runtime filters: RF000 <- current_addr.ca_address_sk -| -|--11:SCAN HDFS [tpcds.customer_address current_addr] -| partitions=1/1 files=1 size=5.25MB -| -12:HASH JOIN [INNER JOIN] -| hash predicates: c_customer_sk = ss_customer_sk -| runtime filters: RF001 <- ss_customer_sk +| runtime filters: RF000 <- customer.c_current_addr_sk | -|--09:AGGREGATE [FINALIZE] -| | output: sum(ss_coupon_amt), sum(ss_net_profit) -| | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city -| | -| 08:HASH JOIN [INNER JOIN] -| | hash predicates: store_sales.ss_store_sk = store.s_store_sk -| | runtime filters: RF002 <- store.s_store_sk -| | -| |--02:SCAN HDFS [tpcds.store] -| | partitions=1/1 files=1 size=3.08KB -| | predicates: store.s_city IN ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville') -| | -| 07:HASH JOIN [INNER JOIN] -| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk -| | runtime filters: RF003 <- household_demographics.hd_demo_sk +|--12:HASH JOIN [INNER JOIN] +| | hash predicates: c_customer_sk = ss_customer_sk +| | runtime filters: RF001 <- ss_customer_sk | | -| |--03:SCAN HDFS [tpcds.household_demographics] -| | partitions=1/1 files=1 size=148.10KB -| | predicates: (household_demographics.hd_dep_count = 5 OR household_demographics.hd_vehicle_count = 3) -| | -| 06:HASH JOIN [INNER JOIN] -| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk -| | runtime filters: RF004 <- date_dim.d_date_sk -| | -| |--01:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: date_dim.d_year IN (1999, 1999 + 1, 1999 + 2), date_dim.d_dow IN (6, 0), tpcds.date_dim.d_date_sk IN (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217, 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259, 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301, 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343, 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385, 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427, 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469, 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511, 2451517, 2 451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553, 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595, 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637, 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679, 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721, 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763, 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805, 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847, 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889, 2451895, 2451896, 2451902, 2451903, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931, 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973, 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015, 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057, 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099, 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141, 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183, 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225, 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267, 2452273, 2452274) -| | -| 05:HASH JOIN [INNER JOIN] -| | hash predicates: customer_address.ca_address_sk = store_sales.ss_addr_sk -| | runtime filters: RF005 <- store_sales.ss_addr_sk -| | -| |--00:SCAN HDFS [tpcds.store_sales] -| | partitions=20/120 files=20 size=3.33MB -| | runtime filters: RF002 -> store_sales.ss_store_sk, RF003 -> store_sales.ss_hdemo_sk, RF004 -> store_sales.ss_sold_date_sk +| |--09:AGGREGATE [FINALIZE] +| | | output: sum(ss_coupon_amt), sum(ss_net_profit) +| | | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city +| | | +| | 08:HASH JOIN [INNER JOIN] +| | | hash predicates: store_sales.ss_store_sk = store.s_store_sk +| | | runtime filters: RF002 <- store.s_store_sk +| | | +| | |--02:SCAN HDFS [tpcds.store] +| | | partitions=1/1 files=1 size=3.08KB +| | | predicates: store.s_city IN ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville') +| | | +| | 07:HASH JOIN [INNER JOIN] +| | | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk +| | | runtime filters: RF003 <- household_demographics.hd_demo_sk +| | | +| | |--03:SCAN HDFS [tpcds.household_demographics] +| | | partitions=1/1 files=1 size=148.10KB +| | | predicates: (household_demographics.hd_dep_count = 5 OR household_demographics.hd_vehicle_count = 3) +| | | +| | 06:HASH JOIN [INNER JOIN] +| | | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk +| | | runtime filters: RF004 <- date_dim.d_date_sk +| | | +| | |--01:SCAN HDFS [tpcds.date_dim] +| | | partitions=1/1 files=1 size=9.84MB +| | | predicates: date_dim.d_year IN (1999, 1999 + 1, 1999 + 2), date_dim.d_dow IN (6, 0), tpcds.date_dim.d_date_sk IN (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217, 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259, 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301, 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343, 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385, 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427, 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469, 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511, 2451517 , 2451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553, 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595, 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637, 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679, 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721, 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763, 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805, 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847, 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889, 2451895, 2451896, 2451902, 24519 03, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931, 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973, 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015, 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057, 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099, 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141, 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183, 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225, 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267, 2452273, 2452274) +| | | +| | 05:HASH JOIN [INNER JOIN] +| | | hash predicates: customer_address.ca_address_sk = store_sales.ss_addr_sk +| | | runtime filters: RF005 <- store_sales.ss_addr_sk +| | | +| | |--00:SCAN HDFS [tpcds.store_sales] +| | | partitions=20/120 files=20 size=3.33MB +| | | runtime filters: RF002 -> store_sales.ss_store_sk, RF003 -> store_sales.ss_hdemo_sk, RF004 -> store_sales.ss_sold_date_sk +| | | +| | 04:SCAN HDFS [tpcds.customer_address] +| | partitions=1/1 files=1 size=5.25MB +| | runtime filters: RF005 -> customer_address.ca_address_sk | | -| 04:SCAN HDFS [tpcds.customer_address] -| partitions=1/1 files=1 size=5.25MB -| runtime filters: RF005 -> customer_address.ca_address_sk +| 10:SCAN HDFS [tpcds.customer] +| partitions=1/1 files=1 size=12.60MB +| runtime filters: RF001 -> c_customer_sk | -10:SCAN HDFS [tpcds.customer] - partitions=1/1 files=1 size=12.60MB - runtime filters: RF000 -> customer.c_current_addr_sk, RF001 -> c_customer_sk +11:SCAN HDFS [tpcds.customer_address current_addr] + partitions=1/1 files=1 size=5.25MB + runtime filters: RF000 -> current_addr.ca_address_sk ---- DISTRIBUTEDPLAN 23:MERGING-EXCHANGE [UNPARTITIONED] | order by: c_last_name ASC, c_first_name ASC, ca_city ASC, bought_city ASC, ss_ticket_number ASC @@ -1778,78 +1778,79 @@ limit 100 | order by: c_last_name ASC, c_first_name ASC, ca_city ASC, bought_city ASC, ss_ticket_number ASC | 13:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: customer.c_current_addr_sk = current_addr.ca_address_sk +| hash predicates: current_addr.ca_address_sk = customer.c_current_addr_sk | other predicates: current_addr.ca_city != ca_city -| runtime filters: RF000 <- current_addr.ca_address_sk +| runtime filters: RF000 <- customer.c_current_addr_sk | |--22:EXCHANGE [BROADCAST] | | -| 11:SCAN HDFS [tpcds.customer_address current_addr] -| partitions=1/1 files=1 size=5.25MB -| -12:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: c_customer_sk = ss_customer_sk -| runtime filters: RF001 <- ss_customer_sk -| -|--21:EXCHANGE [BROADCAST] -| | -| 20:AGGREGATE [FINALIZE] -| | output: sum:merge(ss_coupon_amt), sum:merge(ss_net_profit) -| | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city -| | -| 19:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city)] -| | -| 09:AGGREGATE [STREAMING] -| | output: sum(ss_coupon_amt), sum(ss_net_profit) -| | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city -| | -| 08:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: store_sales.ss_store_sk = store.s_store_sk -| | runtime filters: RF002 <- store.s_store_sk +| 12:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: c_customer_sk = ss_customer_sk +| | runtime filters: RF001 <- ss_customer_sk | | -| |--18:EXCHANGE [BROADCAST] +| |--21:EXCHANGE [BROADCAST] | | | -| | 02:SCAN HDFS [tpcds.store] -| | partitions=1/1 files=1 size=3.08KB -| | predicates: store.s_city IN ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville') -| | -| 07:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk -| | runtime filters: RF003 <- household_demographics.hd_demo_sk -| | -| |--17:EXCHANGE [BROADCAST] +| | 20:AGGREGATE [FINALIZE] +| | | output: sum:merge(ss_coupon_amt), sum:merge(ss_net_profit) +| | | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city | | | -| | 03:SCAN HDFS [tpcds.household_demographics] -| | partitions=1/1 files=1 size=148.10KB -| | predicates: (household_demographics.hd_dep_count = 5 OR household_demographics.hd_vehicle_count = 3) -| | -| 06:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk -| | runtime filters: RF004 <- date_dim.d_date_sk -| | -| |--16:EXCHANGE [BROADCAST] +| | 19:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city)] | | | -| | 01:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: date_dim.d_year IN (1999, 1999 + 1, 1999 + 2), date_dim.d_dow IN (6, 0), tpcds.date_dim.d_date_sk IN (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217, 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259, 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301, 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343, 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385, 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427, 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469, 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511, 2451517, 2 451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553, 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595, 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637, 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679, 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721, 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763, 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805, 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847, 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889, 2451895, 2451896, 2451902, 2451903, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931, 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973, 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015, 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057, 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099, 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141, 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183, 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225, 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267, 2452273, 2452274) -| | -| 05:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: customer_address.ca_address_sk = store_sales.ss_addr_sk -| | runtime filters: RF005 <- store_sales.ss_addr_sk -| | -| |--15:EXCHANGE [BROADCAST] +| | 09:AGGREGATE [STREAMING] +| | | output: sum(ss_coupon_amt), sum(ss_net_profit) +| | | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city | | | -| | 00:SCAN HDFS [tpcds.store_sales] -| | partitions=20/120 files=20 size=3.33MB -| | runtime filters: RF002 -> store_sales.ss_store_sk, RF003 -> store_sales.ss_hdemo_sk, RF004 -> store_sales.ss_sold_date_sk +| | 08:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: store_sales.ss_store_sk = store.s_store_sk +| | | runtime filters: RF002 <- store.s_store_sk +| | | +| | |--18:EXCHANGE [BROADCAST] +| | | | +| | | 02:SCAN HDFS [tpcds.store] +| | | partitions=1/1 files=1 size=3.08KB +| | | predicates: store.s_city IN ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville') +| | | +| | 07:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk +| | | runtime filters: RF003 <- household_demographics.hd_demo_sk +| | | +| | |--17:EXCHANGE [BROADCAST] +| | | | +| | | 03:SCAN HDFS [tpcds.household_demographics] +| | | partitions=1/1 files=1 size=148.10KB +| | | predicates: (household_demographics.hd_dep_count = 5 OR household_demographics.hd_vehicle_count = 3) +| | | +| | 06:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk +| | | runtime filters: RF004 <- date_dim.d_date_sk +| | | +| | |--16:EXCHANGE [BROADCAST] +| | | | +| | | 01:SCAN HDFS [tpcds.date_dim] +| | | partitions=1/1 files=1 size=9.84MB +| | | predicates: date_dim.d_year IN (1999, 1999 + 1, 1999 + 2), date_dim.d_dow IN (6, 0), tpcds.date_dim.d_date_sk IN (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217, 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259, 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301, 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343, 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385, 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427, 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469, 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511, 2451517 , 2451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553, 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595, 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637, 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679, 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721, 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763, 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805, 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847, 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889, 2451895, 2451896, 2451902, 24519 03, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931, 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973, 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015, 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057, 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099, 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141, 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183, 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225, 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267, 2452273, 2452274) +| | | +| | 05:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: customer_address.ca_address_sk = store_sales.ss_addr_sk +| | | runtime filters: RF005 <- store_sales.ss_addr_sk +| | | +| | |--15:EXCHANGE [BROADCAST] +| | | | +| | | 00:SCAN HDFS [tpcds.store_sales] +| | | partitions=20/120 files=20 size=3.33MB +| | | runtime filters: RF002 -> store_sales.ss_store_sk, RF003 -> store_sales.ss_hdemo_sk, RF004 -> store_sales.ss_sold_date_sk +| | | +| | 04:SCAN HDFS [tpcds.customer_address] +| | partitions=1/1 files=1 size=5.25MB +| | runtime filters: RF005 -> customer_address.ca_address_sk | | -| 04:SCAN HDFS [tpcds.customer_address] -| partitions=1/1 files=1 size=5.25MB -| runtime filters: RF005 -> customer_address.ca_address_sk +| 10:SCAN HDFS [tpcds.customer] +| partitions=1/1 files=1 size=12.60MB +| runtime filters: RF001 -> c_customer_sk | -10:SCAN HDFS [tpcds.customer] - partitions=1/1 files=1 size=12.60MB - runtime filters: RF000 -> customer.c_current_addr_sk, RF001 -> c_customer_sk +11:SCAN HDFS [tpcds.customer_address current_addr] + partitions=1/1 files=1 size=5.25MB + runtime filters: RF000 -> current_addr.ca_address_sk ---- PARALLELPLANS 23:MERGING-EXCHANGE [UNPARTITIONED] | order by: c_last_name ASC, c_first_name ASC, ca_city ASC, bought_city ASC, ss_ticket_number ASC @@ -1859,102 +1860,103 @@ limit 100 | order by: c_last_name ASC, c_first_name ASC, ca_city ASC, bought_city ASC, ss_ticket_number ASC | 13:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: customer.c_current_addr_sk = current_addr.ca_address_sk +| hash predicates: current_addr.ca_address_sk = customer.c_current_addr_sk | other predicates: current_addr.ca_city != ca_city -| runtime filters: RF000 <- current_addr.ca_address_sk +| runtime filters: RF000 <- customer.c_current_addr_sk | |--JOIN BUILD | | join-table-id=00 plan-id=01 cohort-id=01 -| | build expressions: current_addr.ca_address_sk +| | build expressions: customer.c_current_addr_sk | | | 22:EXCHANGE [BROADCAST] | | -| 11:SCAN HDFS [tpcds.customer_address current_addr] -| partitions=1/1 files=1 size=5.25MB -| -12:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: c_customer_sk = ss_customer_sk -| runtime filters: RF001 <- ss_customer_sk -| -|--JOIN BUILD -| | join-table-id=01 plan-id=02 cohort-id=01 -| | build expressions: ss_customer_sk -| | -| 21:EXCHANGE [BROADCAST] -| | -| 20:AGGREGATE [FINALIZE] -| | output: sum:merge(ss_coupon_amt), sum:merge(ss_net_profit) -| | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city -| | -| 19:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city)] -| | -| 09:AGGREGATE [STREAMING] -| | output: sum(ss_coupon_amt), sum(ss_net_profit) -| | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city -| | -| 08:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: store_sales.ss_store_sk = store.s_store_sk -| | runtime filters: RF002 <- store.s_store_sk +| 12:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: c_customer_sk = ss_customer_sk +| | runtime filters: RF001 <- ss_customer_sk | | | |--JOIN BUILD -| | | join-table-id=02 plan-id=03 cohort-id=02 -| | | build expressions: store.s_store_sk +| | | join-table-id=01 plan-id=02 cohort-id=02 +| | | build expressions: ss_customer_sk | | | -| | 18:EXCHANGE [BROADCAST] +| | 21:EXCHANGE [BROADCAST] | | | -| | 02:SCAN HDFS [tpcds.store] -| | partitions=1/1 files=1 size=3.08KB -| | predicates: store.s_city IN ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville') -| | -| 07:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk -| | runtime filters: RF003 <- household_demographics.hd_demo_sk -| | -| |--JOIN BUILD -| | | join-table-id=03 plan-id=04 cohort-id=02 -| | | build expressions: household_demographics.hd_demo_sk +| | 20:AGGREGATE [FINALIZE] +| | | output: sum:merge(ss_coupon_amt), sum:merge(ss_net_profit) +| | | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city | | | -| | 17:EXCHANGE [BROADCAST] +| | 19:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city)] | | | -| | 03:SCAN HDFS [tpcds.household_demographics] -| | partitions=1/1 files=1 size=148.10KB -| | predicates: (household_demographics.hd_dep_count = 5 OR household_demographics.hd_vehicle_count = 3) -| | -| 06:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk -| | runtime filters: RF004 <- date_dim.d_date_sk -| | -| |--JOIN BUILD -| | | join-table-id=04 plan-id=05 cohort-id=02 -| | | build expressions: date_dim.d_date_sk +| | 09:AGGREGATE [STREAMING] +| | | output: sum(ss_coupon_amt), sum(ss_net_profit) +| | | group by: ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city | | | -| | 16:EXCHANGE [BROADCAST] +| | 08:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: store_sales.ss_store_sk = store.s_store_sk +| | | runtime filters: RF002 <- store.s_store_sk | | | -| | 01:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: date_dim.d_year IN (1999, 1999 + 1, 1999 + 2), date_dim.d_dow IN (6, 0), tpcds.date_dim.d_date_sk IN (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217, 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259, 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301, 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343, 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385, 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427, 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469, 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511, 2451517, 2 451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553, 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595, 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637, 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679, 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721, 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763, 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805, 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847, 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889, 2451895, 2451896, 2451902, 2451903, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931, 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973, 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015, 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057, 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099, 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141, 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183, 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225, 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267, 2452273, 2452274) -| | -| 05:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: customer_address.ca_address_sk = store_sales.ss_addr_sk -| | runtime filters: RF005 <- store_sales.ss_addr_sk -| | -| |--JOIN BUILD -| | | join-table-id=05 plan-id=06 cohort-id=02 -| | | build expressions: store_sales.ss_addr_sk +| | |--JOIN BUILD +| | | | join-table-id=02 plan-id=03 cohort-id=03 +| | | | build expressions: store.s_store_sk +| | | | +| | | 18:EXCHANGE [BROADCAST] +| | | | +| | | 02:SCAN HDFS [tpcds.store] +| | | partitions=1/1 files=1 size=3.08KB +| | | predicates: store.s_city IN ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville') | | | -| | 15:EXCHANGE [BROADCAST] +| | 07:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk +| | | runtime filters: RF003 <- household_demographics.hd_demo_sk | | | -| | 00:SCAN HDFS [tpcds.store_sales] -| | partitions=20/120 files=20 size=3.33MB -| | runtime filters: RF002 -> store_sales.ss_store_sk, RF003 -> store_sales.ss_hdemo_sk, RF004 -> store_sales.ss_sold_date_sk +| | |--JOIN BUILD +| | | | join-table-id=03 plan-id=04 cohort-id=03 +| | | | build expressions: household_demographics.hd_demo_sk +| | | | +| | | 17:EXCHANGE [BROADCAST] +| | | | +| | | 03:SCAN HDFS [tpcds.household_demographics] +| | | partitions=1/1 files=1 size=148.10KB +| | | predicates: (household_demographics.hd_dep_count = 5 OR household_demographics.hd_vehicle_count = 3) +| | | +| | 06:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk +| | | runtime filters: RF004 <- date_dim.d_date_sk +| | | +| | |--JOIN BUILD +| | | | join-table-id=04 plan-id=05 cohort-id=03 +| | | | build expressions: date_dim.d_date_sk +| | | | +| | | 16:EXCHANGE [BROADCAST] +| | | | +| | | 01:SCAN HDFS [tpcds.date_dim] +| | | partitions=1/1 files=1 size=9.84MB +| | | predicates: date_dim.d_year IN (1999, 1999 + 1, 1999 + 2), date_dim.d_dow IN (6, 0), tpcds.date_dim.d_date_sk IN (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217, 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259, 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301, 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343, 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385, 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427, 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469, 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511, 2451517 , 2451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553, 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595, 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637, 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679, 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721, 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763, 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805, 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847, 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889, 2451895, 2451896, 2451902, 24519 03, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931, 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973, 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015, 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057, 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099, 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141, 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183, 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225, 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267, 2452273, 2452274) +| | | +| | 05:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: customer_address.ca_address_sk = store_sales.ss_addr_sk +| | | runtime filters: RF005 <- store_sales.ss_addr_sk +| | | +| | |--JOIN BUILD +| | | | join-table-id=05 plan-id=06 cohort-id=03 +| | | | build expressions: store_sales.ss_addr_sk +| | | | +| | | 15:EXCHANGE [BROADCAST] +| | | | +| | | 00:SCAN HDFS [tpcds.store_sales] +| | | partitions=20/120 files=20 size=3.33MB +| | | runtime filters: RF002 -> store_sales.ss_store_sk, RF003 -> store_sales.ss_hdemo_sk, RF004 -> store_sales.ss_sold_date_sk +| | | +| | 04:SCAN HDFS [tpcds.customer_address] +| | partitions=1/1 files=1 size=5.25MB +| | runtime filters: RF005 -> customer_address.ca_address_sk | | -| 04:SCAN HDFS [tpcds.customer_address] -| partitions=1/1 files=1 size=5.25MB -| runtime filters: RF005 -> customer_address.ca_address_sk +| 10:SCAN HDFS [tpcds.customer] +| partitions=1/1 files=1 size=12.60MB +| runtime filters: RF001 -> c_customer_sk | -10:SCAN HDFS [tpcds.customer] - partitions=1/1 files=1 size=12.60MB - runtime filters: RF000 -> customer.c_current_addr_sk, RF001 -> c_customer_sk +11:SCAN HDFS [tpcds.customer_address current_addr] + partitions=1/1 files=1 size=5.25MB + runtime filters: RF000 -> current_addr.ca_address_sk ==== # TPCDS-Q52 select @@ -2146,37 +2148,38 @@ limit 100 | partitions=1/1 files=1 size=3.08KB | 05:HASH JOIN [INNER JOIN] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF001 <- d_date_sk -| -|--02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) -| -04:HASH JOIN [INNER JOIN] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF002 <- i_item_sk +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF001 <- ss_sold_date_sk | -|--00:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +|--04:HASH JOIN [INNER JOIN] +| | hash predicates: ss_item_sk = i_item_sk +| | runtime filters: RF002 <- i_item_sk +| | +| |--00:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| | +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=120/120 files=120 size=21.31MB +| runtime filters: RF000 -> ss_store_sk, RF002 -> ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=120/120 files=120 size=21.31MB - runtime filters: RF000 -> ss_store_sk, RF001 -> ss_sold_date_sk, RF002 -> ss_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) + runtime filters: RF001 -> d_date_sk ---- DISTRIBUTEDPLAN -15:MERGING-EXCHANGE [UNPARTITIONED] +14:MERGING-EXCHANGE [UNPARTITIONED] | order by: sum_sales ASC, i_manufact_id ASC | limit: 100 | 08:TOP-N [LIMIT=100] | order by: sum_sales ASC, i_manufact_id ASC | -14:AGGREGATE [FINALIZE] +13:AGGREGATE [FINALIZE] | output: sum:merge(ss_sales_price) | group by: i_manufact_id, d_qoy | -13:EXCHANGE [HASH(i_manufact_id,d_qoy)] +12:EXCHANGE [HASH(i_manufact_id,d_qoy)] | 07:AGGREGATE [STREAMING] | output: sum(ss_sales_price) @@ -2186,49 +2189,48 @@ limit 100 | hash predicates: ss_store_sk = s_store_sk | runtime filters: RF000 <- s_store_sk | -|--12:EXCHANGE [BROADCAST] +|--11:EXCHANGE [BROADCAST] | | | 03:SCAN HDFS [tpcds.store] | partitions=1/1 files=1 size=3.08KB | -05:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF001 <- d_date_sk +05:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF001 <- ss_sold_date_sk | -|--11:EXCHANGE [HASH(d_date_sk)] +|--10:EXCHANGE [BROADCAST] | | -| 02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) -| -10:EXCHANGE [HASH(ss_sold_date_sk)] -| -04:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF002 <- i_item_sk -| -|--09:EXCHANGE [BROADCAST] +| 04:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: ss_item_sk = i_item_sk +| | runtime filters: RF002 <- i_item_sk | | -| 00:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| |--09:EXCHANGE [BROADCAST] +| | | +| | 00:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| | +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=120/120 files=120 size=21.31MB +| runtime filters: RF000 -> ss_store_sk, RF002 -> ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=120/120 files=120 size=21.31MB - runtime filters: RF000 -> ss_store_sk, RF001 -> ss_sold_date_sk, RF002 -> ss_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) + runtime filters: RF001 -> d_date_sk ---- PARALLELPLANS -15:MERGING-EXCHANGE [UNPARTITIONED] +14:MERGING-EXCHANGE [UNPARTITIONED] | order by: sum_sales ASC, i_manufact_id ASC | limit: 100 | 08:TOP-N [LIMIT=100] | order by: sum_sales ASC, i_manufact_id ASC | -14:AGGREGATE [FINALIZE] +13:AGGREGATE [FINALIZE] | output: sum:merge(ss_sales_price) | group by: i_manufact_id, d_qoy | -13:EXCHANGE [HASH(i_manufact_id,d_qoy)] +12:EXCHANGE [HASH(i_manufact_id,d_qoy)] | 07:AGGREGATE [STREAMING] | output: sum(ss_sales_price) @@ -2242,44 +2244,43 @@ limit 100 | | join-table-id=00 plan-id=01 cohort-id=01 | | build expressions: s_store_sk | | -| 12:EXCHANGE [BROADCAST] +| 11:EXCHANGE [BROADCAST] | | | 03:SCAN HDFS [tpcds.store] | partitions=1/1 files=1 size=3.08KB | -05:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF001 <- d_date_sk +05:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF001 <- ss_sold_date_sk | |--JOIN BUILD | | join-table-id=01 plan-id=02 cohort-id=01 -| | build expressions: d_date_sk +| | build expressions: ss_sold_date_sk | | -| 11:EXCHANGE [HASH(d_date_sk)] +| 10:EXCHANGE [BROADCAST] | | -| 02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) -| -10:EXCHANGE [HASH(ss_sold_date_sk)] -| -04:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF002 <- i_item_sk -| -|--JOIN BUILD -| | join-table-id=02 plan-id=03 cohort-id=01 -| | build expressions: i_item_sk +| 04:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: ss_item_sk = i_item_sk +| | runtime filters: RF002 <- i_item_sk | | -| 09:EXCHANGE [BROADCAST] +| |--JOIN BUILD +| | | join-table-id=02 plan-id=03 cohort-id=02 +| | | build expressions: i_item_sk +| | | +| | 09:EXCHANGE [BROADCAST] +| | | +| | 00:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) | | -| 00:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=120/120 files=120 size=21.31MB +| runtime filters: RF000 -> ss_store_sk, RF002 -> ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=120/120 files=120 size=21.31MB - runtime filters: RF000 -> ss_store_sk, RF001 -> ss_sold_date_sk, RF002 -> ss_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) + runtime filters: RF001 -> d_date_sk ==== # TPCDS-Q55 select @@ -2879,26 +2880,27 @@ limit 100 | partitions=1/1 files=1 size=3.08KB | 05:HASH JOIN [INNER JOIN] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF001 <- d_date_sk +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF001 <- ss_sold_date_sk | -|--02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2452275, d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) -| -04:HASH JOIN [INNER JOIN] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF002 <- i_item_sk -| -|--00:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'refernece', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +|--04:HASH JOIN [INNER JOIN] +| | hash predicates: ss_item_sk = i_item_sk +| | runtime filters: RF002 <- i_item_sk +| | +| |--00:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'refernece', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| | +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=24/120 files=24 size=4.32MB +| runtime filters: RF000 -> ss_store_sk, RF002 -> ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=24/120 files=24 size=4.32MB - runtime filters: RF000 -> ss_store_sk, RF001 -> ss_sold_date_sk, RF002 -> ss_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2452275, d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) + runtime filters: RF001 -> d_date_sk ---- DISTRIBUTEDPLAN -18:MERGING-EXCHANGE [UNPARTITIONED] +17:MERGING-EXCHANGE [UNPARTITIONED] | order by: i_manager_id ASC, avg_monthly_sales ASC, sum_sales ASC | limit: 100 | @@ -2915,11 +2917,11 @@ limit 100 08:SORT | order by: i_manager_id ASC NULLS FIRST | -17:AGGREGATE [FINALIZE] +16:AGGREGATE [FINALIZE] | output: sum:merge(ss_sales_price) | group by: i_manager_id, d_moy | -16:EXCHANGE [HASH(i_manager_id)] +15:EXCHANGE [HASH(i_manager_id)] | 07:AGGREGATE [STREAMING] | output: sum(ss_sales_price) @@ -2929,38 +2931,37 @@ limit 100 | hash predicates: ss_store_sk = s_store_sk | runtime filters: RF000 <- s_store_sk | -|--15:EXCHANGE [BROADCAST] +|--14:EXCHANGE [BROADCAST] | | | 03:SCAN HDFS [tpcds.store] | partitions=1/1 files=1 size=3.08KB | -05:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF001 <- d_date_sk +05:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF001 <- ss_sold_date_sk | -|--14:EXCHANGE [HASH(d_date_sk)] +|--13:EXCHANGE [BROADCAST] | | -| 02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2452275, d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) -| -13:EXCHANGE [HASH(ss_sold_date_sk)] -| -04:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF002 <- i_item_sk -| -|--12:EXCHANGE [BROADCAST] +| 04:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: ss_item_sk = i_item_sk +| | runtime filters: RF002 <- i_item_sk | | -| 00:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'refernece', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| |--12:EXCHANGE [BROADCAST] +| | | +| | 00:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'refernece', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| | +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=24/120 files=24 size=4.32MB +| runtime filters: RF000 -> ss_store_sk, RF002 -> ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=24/120 files=24 size=4.32MB - runtime filters: RF000 -> ss_store_sk, RF001 -> ss_sold_date_sk, RF002 -> ss_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2452275, d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) + runtime filters: RF001 -> d_date_sk ---- PARALLELPLANS -18:MERGING-EXCHANGE [UNPARTITIONED] +17:MERGING-EXCHANGE [UNPARTITIONED] | order by: i_manager_id ASC, avg_monthly_sales ASC, sum_sales ASC | limit: 100 | @@ -2977,11 +2978,11 @@ limit 100 08:SORT | order by: i_manager_id ASC NULLS FIRST | -17:AGGREGATE [FINALIZE] +16:AGGREGATE [FINALIZE] | output: sum:merge(ss_sales_price) | group by: i_manager_id, d_moy | -16:EXCHANGE [HASH(i_manager_id)] +15:EXCHANGE [HASH(i_manager_id)] | 07:AGGREGATE [STREAMING] | output: sum(ss_sales_price) @@ -2995,44 +2996,43 @@ limit 100 | | join-table-id=00 plan-id=01 cohort-id=01 | | build expressions: s_store_sk | | -| 15:EXCHANGE [BROADCAST] +| 14:EXCHANGE [BROADCAST] | | | 03:SCAN HDFS [tpcds.store] | partitions=1/1 files=1 size=3.08KB | -05:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF001 <- d_date_sk +05:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF001 <- ss_sold_date_sk | |--JOIN BUILD | | join-table-id=01 plan-id=02 cohort-id=01 -| | build expressions: d_date_sk +| | build expressions: ss_sold_date_sk | | -| 14:EXCHANGE [HASH(d_date_sk)] +| 13:EXCHANGE [BROADCAST] | | -| 02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2452275, d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) -| -13:EXCHANGE [HASH(ss_sold_date_sk)] -| -04:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF002 <- i_item_sk -| -|--JOIN BUILD -| | join-table-id=02 plan-id=03 cohort-id=01 -| | build expressions: i_item_sk +| 04:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: ss_item_sk = i_item_sk +| | runtime filters: RF002 <- i_item_sk | | -| 12:EXCHANGE [BROADCAST] +| |--JOIN BUILD +| | | join-table-id=02 plan-id=03 cohort-id=02 +| | | build expressions: i_item_sk +| | | +| | 12:EXCHANGE [BROADCAST] +| | | +| | 00:SCAN HDFS [tpcds.item] +| | partitions=1/1 files=1 size=4.82MB +| | predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'refernece', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) | | -| 00:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| predicates: ((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'refernece', 'self-help') AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR (i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) +| 01:SCAN HDFS [tpcds.store_sales] +| partitions=24/120 files=24 size=4.32MB +| runtime filters: RF000 -> ss_store_sk, RF002 -> ss_item_sk | -01:SCAN HDFS [tpcds.store_sales] - partitions=24/120 files=24 size=4.32MB - runtime filters: RF000 -> ss_store_sk, RF001 -> ss_sold_date_sk, RF002 -> ss_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2452275, d_month_seq IN (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11) + runtime filters: RF001 -> d_date_sk ==== # TPCDS-Q65 select @@ -3094,267 +3094,265 @@ limit 100 | order by: s_store_name ASC, i_item_desc ASC | 13:HASH JOIN [INNER JOIN] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF000 <- i_item_sk -| -|--01:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| -12:HASH JOIN [INNER JOIN] -| hash predicates: ss_store_sk = s_store_sk -| runtime filters: RF001 <- s_store_sk -| -|--00:SCAN HDFS [tpcds.store] -| partitions=1/1 files=1 size=3.08KB -| -11:HASH JOIN [INNER JOIN] -| hash predicates: ss_store_sk = ss_store_sk -| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue) -| runtime filters: RF002 <- ss_store_sk +| hash predicates: i_item_sk = ss_item_sk +| runtime filters: RF000 <- ss_item_sk | -|--06:AGGREGATE [FINALIZE] -| | output: avg(sum(ss_sales_price)) -| | group by: ss_store_sk -| | -| 05:AGGREGATE [FINALIZE] -| | output: sum(ss_sales_price) -| | group by: ss_store_sk, ss_item_sk +|--12:HASH JOIN [INNER JOIN] +| | hash predicates: s_store_sk = ss_store_sk +| | runtime filters: RF001 <- ss_store_sk | | -| 04:HASH JOIN [INNER JOIN] -| | hash predicates: ss_sold_date_sk = d_date_sk -| | runtime filters: RF004 <- d_date_sk -| | -| |--03:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| |--11:HASH JOIN [INNER JOIN] +| | | hash predicates: ss_store_sk = ss_store_sk +| | | other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue) +| | | runtime filters: RF002 <- ss_store_sk +| | | +| | |--06:AGGREGATE [FINALIZE] +| | | | output: avg(sum(ss_sales_price)) +| | | | group by: ss_store_sk +| | | | +| | | 05:AGGREGATE [FINALIZE] +| | | | output: sum(ss_sales_price) +| | | | group by: ss_store_sk, ss_item_sk +| | | | +| | | 04:HASH JOIN [INNER JOIN] +| | | | hash predicates: ss_sold_date_sk = d_date_sk +| | | | runtime filters: RF004 <- d_date_sk +| | | | +| | | |--03:SCAN HDFS [tpcds.date_dim] +| | | | partitions=1/1 files=1 size=9.84MB +| | | | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| | | | +| | | 02:SCAN HDFS [tpcds.store_sales] +| | | partitions=120/120 files=120 size=21.31MB +| | | runtime filters: RF004 -> ss_sold_date_sk +| | | +| | 10:AGGREGATE [FINALIZE] +| | | output: sum(ss_sales_price) +| | | group by: ss_store_sk, ss_item_sk +| | | +| | 09:HASH JOIN [INNER JOIN] +| | | hash predicates: ss_sold_date_sk = d_date_sk +| | | runtime filters: RF003 <- d_date_sk +| | | +| | |--08:SCAN HDFS [tpcds.date_dim] +| | | partitions=1/1 files=1 size=9.84MB +| | | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| | | +| | 07:SCAN HDFS [tpcds.store_sales] +| | partitions=120/120 files=120 size=21.31MB +| | runtime filters: RF002 -> tpcds.store_sales.ss_store_sk, RF003 -> ss_sold_date_sk | | -| 02:SCAN HDFS [tpcds.store_sales] -| partitions=120/120 files=120 size=21.31MB -| runtime filters: RF001 -> tpcds.store_sales.ss_store_sk, RF004 -> ss_sold_date_sk -| -10:AGGREGATE [FINALIZE] -| output: sum(ss_sales_price) -| group by: ss_store_sk, ss_item_sk -| -09:HASH JOIN [INNER JOIN] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF003 <- d_date_sk -| -|--08:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| 00:SCAN HDFS [tpcds.store] +| partitions=1/1 files=1 size=3.08KB +| runtime filters: RF001 -> s_store_sk | -07:SCAN HDFS [tpcds.store_sales] - partitions=120/120 files=120 size=21.31MB - runtime filters: RF000 -> tpcds.store_sales.ss_item_sk, RF001 -> tpcds.store_sales.ss_store_sk, RF002 -> tpcds.store_sales.ss_store_sk, RF003 -> ss_sold_date_sk +01:SCAN HDFS [tpcds.item] + partitions=1/1 files=1 size=4.82MB + runtime filters: RF000 -> i_item_sk ---- DISTRIBUTEDPLAN -28:MERGING-EXCHANGE [UNPARTITIONED] +26:MERGING-EXCHANGE [UNPARTITIONED] | order by: s_store_name ASC, i_item_desc ASC | limit: 100 | 14:TOP-N [LIMIT=100] | order by: s_store_name ASC, i_item_desc ASC | -13:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF000 <- i_item_sk -| -|--27:EXCHANGE [HASH(i_item_sk)] -| | -| 01:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| -26:EXCHANGE [HASH(ss_item_sk)] -| -12:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_store_sk = s_store_sk -| runtime filters: RF001 <- s_store_sk -| -|--25:EXCHANGE [HASH(s_store_sk)] -| | -| 00:SCAN HDFS [tpcds.store] -| partitions=1/1 files=1 size=3.08KB -| -24:EXCHANGE [HASH(ss_store_sk)] -| -11:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_store_sk = ss_store_sk -| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue) -| runtime filters: RF002 <- ss_store_sk +13:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: i_item_sk = ss_item_sk +| runtime filters: RF000 <- ss_item_sk | -|--23:EXCHANGE [BROADCAST] -| | -| 22:AGGREGATE [FINALIZE] -| | output: avg:merge(revenue) -| | group by: ss_store_sk -| | -| 21:EXCHANGE [HASH(ss_store_sk)] +|--25:EXCHANGE [BROADCAST] | | -| 06:AGGREGATE [STREAMING] -| | output: avg(sum(ss_sales_price)) -| | group by: ss_store_sk -| | -| 20:AGGREGATE [FINALIZE] -| | output: sum:merge(ss_sales_price) -| | group by: ss_store_sk, ss_item_sk -| | -| 19:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] -| | -| 05:AGGREGATE [STREAMING] -| | output: sum(ss_sales_price) -| | group by: ss_store_sk, ss_item_sk -| | -| 04:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: ss_sold_date_sk = d_date_sk -| | runtime filters: RF004 <- d_date_sk +| 12:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: s_store_sk = ss_store_sk +| | runtime filters: RF001 <- ss_store_sk | | -| |--18:EXCHANGE [BROADCAST] +| |--24:EXCHANGE [BROADCAST] | | | -| | 03:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 -| | -| 02:SCAN HDFS [tpcds.store_sales] -| partitions=120/120 files=120 size=21.31MB -| runtime filters: RF001 -> tpcds.store_sales.ss_store_sk, RF004 -> ss_sold_date_sk -| -17:AGGREGATE [FINALIZE] -| output: sum:merge(ss_sales_price) -| group by: ss_store_sk, ss_item_sk -| -16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] -| -10:AGGREGATE [STREAMING] -| output: sum(ss_sales_price) -| group by: ss_store_sk, ss_item_sk -| -09:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF003 <- d_date_sk -| -|--15:EXCHANGE [BROADCAST] +| | 11:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: ss_store_sk = ss_store_sk +| | | other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue) +| | | runtime filters: RF002 <- ss_store_sk +| | | +| | |--23:EXCHANGE [BROADCAST] +| | | | +| | | 22:AGGREGATE [FINALIZE] +| | | | output: avg:merge(revenue) +| | | | group by: ss_store_sk +| | | | +| | | 21:EXCHANGE [HASH(ss_store_sk)] +| | | | +| | | 06:AGGREGATE [STREAMING] +| | | | output: avg(sum(ss_sales_price)) +| | | | group by: ss_store_sk +| | | | +| | | 20:AGGREGATE [FINALIZE] +| | | | output: sum:merge(ss_sales_price) +| | | | group by: ss_store_sk, ss_item_sk +| | | | +| | | 19:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] +| | | | +| | | 05:AGGREGATE [STREAMING] +| | | | output: sum(ss_sales_price) +| | | | group by: ss_store_sk, ss_item_sk +| | | | +| | | 04:HASH JOIN [INNER JOIN, BROADCAST] +| | | | hash predicates: ss_sold_date_sk = d_date_sk +| | | | runtime filters: RF004 <- d_date_sk +| | | | +| | | |--18:EXCHANGE [BROADCAST] +| | | | | +| | | | 03:SCAN HDFS [tpcds.date_dim] +| | | | partitions=1/1 files=1 size=9.84MB +| | | | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| | | | +| | | 02:SCAN HDFS [tpcds.store_sales] +| | | partitions=120/120 files=120 size=21.31MB +| | | runtime filters: RF004 -> ss_sold_date_sk +| | | +| | 17:AGGREGATE [FINALIZE] +| | | output: sum:merge(ss_sales_price) +| | | group by: ss_store_sk, ss_item_sk +| | | +| | 16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] +| | | +| | 10:AGGREGATE [STREAMING] +| | | output: sum(ss_sales_price) +| | | group by: ss_store_sk, ss_item_sk +| | | +| | 09:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: ss_sold_date_sk = d_date_sk +| | | runtime filters: RF003 <- d_date_sk +| | | +| | |--15:EXCHANGE [BROADCAST] +| | | | +| | | 08:SCAN HDFS [tpcds.date_dim] +| | | partitions=1/1 files=1 size=9.84MB +| | | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| | | +| | 07:SCAN HDFS [tpcds.store_sales] +| | partitions=120/120 files=120 size=21.31MB +| | runtime filters: RF002 -> tpcds.store_sales.ss_store_sk, RF003 -> ss_sold_date_sk | | -| 08:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| 00:SCAN HDFS [tpcds.store] +| partitions=1/1 files=1 size=3.08KB +| runtime filters: RF001 -> s_store_sk | -07:SCAN HDFS [tpcds.store_sales] - partitions=120/120 files=120 size=21.31MB - runtime filters: RF000 -> tpcds.store_sales.ss_item_sk, RF001 -> tpcds.store_sales.ss_store_sk, RF002 -> tpcds.store_sales.ss_store_sk, RF003 -> ss_sold_date_sk +01:SCAN HDFS [tpcds.item] + partitions=1/1 files=1 size=4.82MB + runtime filters: RF000 -> i_item_sk ---- PARALLELPLANS -28:MERGING-EXCHANGE [UNPARTITIONED] +26:MERGING-EXCHANGE [UNPARTITIONED] | order by: s_store_name ASC, i_item_desc ASC | limit: 100 | 14:TOP-N [LIMIT=100] | order by: s_store_name ASC, i_item_desc ASC | -13:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_item_sk = i_item_sk -| runtime filters: RF000 <- i_item_sk +13:HASH JOIN [INNER JOIN, BROADCAST] +| hash predicates: i_item_sk = ss_item_sk +| runtime filters: RF000 <- ss_item_sk | |--JOIN BUILD | | join-table-id=00 plan-id=01 cohort-id=01 -| | build expressions: i_item_sk -| | -| 27:EXCHANGE [HASH(i_item_sk)] -| | -| 01:SCAN HDFS [tpcds.item] -| partitions=1/1 files=1 size=4.82MB -| -26:EXCHANGE [HASH(ss_item_sk)] -| -12:HASH JOIN [INNER JOIN, PARTITIONED] -| hash predicates: ss_store_sk = s_store_sk -| runtime filters: RF001 <- s_store_sk -| -|--JOIN BUILD -| | join-table-id=01 plan-id=02 cohort-id=01 -| | build expressions: s_store_sk -| | -| 25:EXCHANGE [HASH(s_store_sk)] -| | -| 00:SCAN HDFS [tpcds.store] -| partitions=1/1 files=1 size=3.08KB -| -24:EXCHANGE [HASH(ss_store_sk)] -| -11:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_store_sk = ss_store_sk -| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue) -| runtime filters: RF002 <- ss_store_sk -| -|--JOIN BUILD -| | join-table-id=02 plan-id=03 cohort-id=01 -| | build expressions: ss_store_sk -| | -| 23:EXCHANGE [BROADCAST] -| | -| 22:AGGREGATE [FINALIZE] -| | output: avg:merge(revenue) -| | group by: ss_store_sk -| | -| 21:EXCHANGE [HASH(ss_store_sk)] -| | -| 06:AGGREGATE [STREAMING] -| | output: avg(sum(ss_sales_price)) -| | group by: ss_store_sk -| | -| 20:AGGREGATE [FINALIZE] -| | output: sum:merge(ss_sales_price) -| | group by: ss_store_sk, ss_item_sk +| | build expressions: ss_item_sk | | -| 19:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] +| 25:EXCHANGE [BROADCAST] | | -| 05:AGGREGATE [STREAMING] -| | output: sum(ss_sales_price) -| | group by: ss_store_sk, ss_item_sk -| | -| 04:HASH JOIN [INNER JOIN, BROADCAST] -| | hash predicates: ss_sold_date_sk = d_date_sk -| | runtime filters: RF004 <- d_date_sk +| 12:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: s_store_sk = ss_store_sk +| | runtime filters: RF001 <- ss_store_sk | | | |--JOIN BUILD -| | | join-table-id=03 plan-id=04 cohort-id=02 -| | | build expressions: d_date_sk +| | | join-table-id=01 plan-id=02 cohort-id=02 +| | | build expressions: ss_store_sk | | | -| | 18:EXCHANGE [BROADCAST] +| | 24:EXCHANGE [BROADCAST] | | | -| | 03:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 -| | -| 02:SCAN HDFS [tpcds.store_sales] -| partitions=120/120 files=120 size=21.31MB -| runtime filters: RF001 -> tpcds.store_sales.ss_store_sk, RF004 -> ss_sold_date_sk -| -17:AGGREGATE [FINALIZE] -| output: sum:merge(ss_sales_price) -| group by: ss_store_sk, ss_item_sk -| -16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] -| -10:AGGREGATE [STREAMING] -| output: sum(ss_sales_price) -| group by: ss_store_sk, ss_item_sk -| -09:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF003 <- d_date_sk -| -|--JOIN BUILD -| | join-table-id=04 plan-id=05 cohort-id=01 -| | build expressions: d_date_sk -| | -| 15:EXCHANGE [BROADCAST] +| | 11:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: ss_store_sk = ss_store_sk +| | | other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue) +| | | runtime filters: RF002 <- ss_store_sk +| | | +| | |--JOIN BUILD +| | | | join-table-id=02 plan-id=03 cohort-id=03 +| | | | build expressions: ss_store_sk +| | | | +| | | 23:EXCHANGE [BROADCAST] +| | | | +| | | 22:AGGREGATE [FINALIZE] +| | | | output: avg:merge(revenue) +| | | | group by: ss_store_sk +| | | | +| | | 21:EXCHANGE [HASH(ss_store_sk)] +| | | | +| | | 06:AGGREGATE [STREAMING] +| | | | output: avg(sum(ss_sales_price)) +| | | | group by: ss_store_sk +| | | | +| | | 20:AGGREGATE [FINALIZE] +| | | | output: sum:merge(ss_sales_price) +| | | | group by: ss_store_sk, ss_item_sk +| | | | +| | | 19:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] +| | | | +| | | 05:AGGREGATE [STREAMING] +| | | | output: sum(ss_sales_price) +| | | | group by: ss_store_sk, ss_item_sk +| | | | +| | | 04:HASH JOIN [INNER JOIN, BROADCAST] +| | | | hash predicates: ss_sold_date_sk = d_date_sk +| | | | runtime filters: RF004 <- d_date_sk +| | | | +| | | |--JOIN BUILD +| | | | | join-table-id=03 plan-id=04 cohort-id=04 +| | | | | build expressions: d_date_sk +| | | | | +| | | | 18:EXCHANGE [BROADCAST] +| | | | | +| | | | 03:SCAN HDFS [tpcds.date_dim] +| | | | partitions=1/1 files=1 size=9.84MB +| | | | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| | | | +| | | 02:SCAN HDFS [tpcds.store_sales] +| | | partitions=120/120 files=120 size=21.31MB +| | | runtime filters: RF004 -> ss_sold_date_sk +| | | +| | 17:AGGREGATE [FINALIZE] +| | | output: sum:merge(ss_sales_price) +| | | group by: ss_store_sk, ss_item_sk +| | | +| | 16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)] +| | | +| | 10:AGGREGATE [STREAMING] +| | | output: sum(ss_sales_price) +| | | group by: ss_store_sk, ss_item_sk +| | | +| | 09:HASH JOIN [INNER JOIN, BROADCAST] +| | | hash predicates: ss_sold_date_sk = d_date_sk +| | | runtime filters: RF003 <- d_date_sk +| | | +| | |--JOIN BUILD +| | | | join-table-id=04 plan-id=05 cohort-id=03 +| | | | build expressions: d_date_sk +| | | | +| | | 15:EXCHANGE [BROADCAST] +| | | | +| | | 08:SCAN HDFS [tpcds.date_dim] +| | | partitions=1/1 files=1 size=9.84MB +| | | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| | | +| | 07:SCAN HDFS [tpcds.store_sales] +| | partitions=120/120 files=120 size=21.31MB +| | runtime filters: RF002 -> tpcds.store_sales.ss_store_sk, RF003 -> ss_sold_date_sk | | -| 08:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11 +| 00:SCAN HDFS [tpcds.store] +| partitions=1/1 files=1 size=3.08KB +| runtime filters: RF001 -> s_store_sk | -07:SCAN HDFS [tpcds.store_sales] - partitions=120/120 files=120 size=21.31MB - runtime filters: RF000 -> tpcds.store_sales.ss_item_sk, RF001 -> tpcds.store_sales.ss_store_sk, RF002 -> tpcds.store_sales.ss_store_sk, RF003 -> ss_sold_date_sk +01:SCAN HDFS [tpcds.item] + partitions=1/1 files=1 size=4.82MB + runtime filters: RF000 -> i_item_sk ==== # TPCDS-Q68 select @@ -4497,25 +4495,25 @@ limit 1000 | group by: i_item_id, i_item_desc, i_category, i_class, i_current_price | 04:HASH JOIN [INNER JOIN] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF000 <- d_date_sk -| -|--02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2451941, d_date >= '2001-01-01', d_date <= '2001-01-31' -| -03:HASH JOIN [INNER JOIN] -| hash predicates: i_item_sk = ss_item_sk -| runtime filters: RF001 <- ss_item_sk +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF000 <- ss_sold_date_sk | -|--00:SCAN HDFS [tpcds.store_sales] -| partitions=2/120 files=2 size=480.36KB -| runtime filters: RF000 -> ss_sold_date_sk +|--03:HASH JOIN [INNER JOIN] +| | hash predicates: i_item_sk = ss_item_sk +| | runtime filters: RF001 <- ss_item_sk +| | +| |--00:SCAN HDFS [tpcds.store_sales] +| | partitions=2/120 files=2 size=480.36KB +| | +| 01:SCAN HDFS [tpcds.item] +| partitions=1/1 files=1 size=4.82MB +| predicates: i_category IN ('Jewelry', 'Sports', 'Books') +| runtime filters: RF001 -> i_item_sk | -01:SCAN HDFS [tpcds.item] - partitions=1/1 files=1 size=4.82MB - predicates: i_category IN ('Jewelry', 'Sports', 'Books') - runtime filters: RF001 -> i_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2451941, d_date >= '2001-01-01', d_date <= '2001-01-31' + runtime filters: RF000 -> d_date_sk ---- DISTRIBUTEDPLAN 13:MERGING-EXCHANGE [UNPARTITIONED] | order by: i_category ASC, i_class ASC, i_item_id ASC, i_item_desc ASC, sum(ss_ext_sales_price) * 100 / sum(sum(ss_ext_sales_price)) ASC @@ -4542,29 +4540,29 @@ limit 1000 | group by: i_item_id, i_item_desc, i_category, i_class, i_current_price | 04:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF000 <- d_date_sk +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF000 <- ss_sold_date_sk | |--10:EXCHANGE [BROADCAST] | | -| 02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2451941, d_date >= '2001-01-01', d_date <= '2001-01-31' -| -03:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: i_item_sk = ss_item_sk -| runtime filters: RF001 <- ss_item_sk -| -|--09:EXCHANGE [BROADCAST] +| 03:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: i_item_sk = ss_item_sk +| | runtime filters: RF001 <- ss_item_sk | | -| 00:SCAN HDFS [tpcds.store_sales] -| partitions=2/120 files=2 size=480.36KB -| runtime filters: RF000 -> ss_sold_date_sk +| |--09:EXCHANGE [BROADCAST] +| | | +| | 00:SCAN HDFS [tpcds.store_sales] +| | partitions=2/120 files=2 size=480.36KB +| | +| 01:SCAN HDFS [tpcds.item] +| partitions=1/1 files=1 size=4.82MB +| predicates: i_category IN ('Jewelry', 'Sports', 'Books') +| runtime filters: RF001 -> i_item_sk | -01:SCAN HDFS [tpcds.item] - partitions=1/1 files=1 size=4.82MB - predicates: i_category IN ('Jewelry', 'Sports', 'Books') - runtime filters: RF001 -> i_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2451941, d_date >= '2001-01-01', d_date <= '2001-01-31' + runtime filters: RF000 -> d_date_sk ---- PARALLELPLANS 13:MERGING-EXCHANGE [UNPARTITIONED] | order by: i_category ASC, i_class ASC, i_item_id ASC, i_item_desc ASC, sum(ss_ext_sales_price) * 100 / sum(sum(ss_ext_sales_price)) ASC @@ -4591,37 +4589,37 @@ limit 1000 | group by: i_item_id, i_item_desc, i_category, i_class, i_current_price | 04:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: ss_sold_date_sk = d_date_sk -| runtime filters: RF000 <- d_date_sk +| hash predicates: d_date_sk = ss_sold_date_sk +| runtime filters: RF000 <- ss_sold_date_sk | |--JOIN BUILD | | join-table-id=00 plan-id=01 cohort-id=01 -| | build expressions: d_date_sk +| | build expressions: ss_sold_date_sk | | | 10:EXCHANGE [BROADCAST] | | -| 02:SCAN HDFS [tpcds.date_dim] -| partitions=1/1 files=1 size=9.84MB -| predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2451941, d_date >= '2001-01-01', d_date <= '2001-01-31' -| -03:HASH JOIN [INNER JOIN, BROADCAST] -| hash predicates: i_item_sk = ss_item_sk -| runtime filters: RF001 <- ss_item_sk -| -|--JOIN BUILD -| | join-table-id=01 plan-id=02 cohort-id=01 -| | build expressions: ss_item_sk +| 03:HASH JOIN [INNER JOIN, BROADCAST] +| | hash predicates: i_item_sk = ss_item_sk +| | runtime filters: RF001 <- ss_item_sk | | -| 09:EXCHANGE [BROADCAST] +| |--JOIN BUILD +| | | join-table-id=01 plan-id=02 cohort-id=02 +| | | build expressions: ss_item_sk +| | | +| | 09:EXCHANGE [BROADCAST] +| | | +| | 00:SCAN HDFS [tpcds.store_sales] +| | partitions=2/120 files=2 size=480.36KB | | -| 00:SCAN HDFS [tpcds.store_sales] -| partitions=2/120 files=2 size=480.36KB -| runtime filters: RF000 -> ss_sold_date_sk +| 01:SCAN HDFS [tpcds.item] +| partitions=1/1 files=1 size=4.82MB +| predicates: i_category IN ('Jewelry', 'Sports', 'Books') +| runtime filters: RF001 -> i_item_sk | -01:SCAN HDFS [tpcds.item] - partitions=1/1 files=1 size=4.82MB - predicates: i_category IN ('Jewelry', 'Sports', 'Books') - runtime filters: RF001 -> i_item_sk +02:SCAN HDFS [tpcds.date_dim] + partitions=1/1 files=1 size=9.84MB + predicates: tpcds.date_dim.d_date_sk >= 2451911, tpcds.date_dim.d_date_sk <= 2451941, d_date >= '2001-01-01', d_date <= '2001-01-31' + runtime filters: RF000 -> d_date_sk ==== # TPCD-Q6 select * from ( @@ -5002,225 +5000,223 @@ with v1 as ( | order by: sum_sales - avg_monthly_sales ASC, d_year ASC | 34:HASH JOIN [INNER JOIN] -| hash predicates: rank() = rank() - 1, s_store_name = s_store_name, i_category = i_category, s_company_name = s_company_name, i_brand = i_brand +| hash predicates: rank() - 1 = rank(), s_store_name = s_store_name, i_category = i_category, s_company_name = s_company_name, i_brand = i_brand | -|--32:ANALYTIC -| | functions: rank() -| | partition by: i_category, i_brand, s_store_name, s_company_name -| | order by: d_year ASC, d_moy ASC -| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -| | -| 31:SORT -| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC -| | -| 30:AGGREGATE [FINALIZE] -| | output: sum(ss_sales_price) -| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy -| | -| 29:HASH JOIN [INNER JOIN] -| | hash predicates: ss_store_sk = s_store_sk -| | runtime filters: RF006 <- s_store_sk -| | -| |--26:SCAN HDFS [tpcds.store] -| | partitions=1/1 files=1 size=3.08KB -| | -| 28:HASH JOIN [INNER JOIN] -| | hash predicates: ss_sold_date_sk = d_date_sk -| | runtime filters: RF007 <- d_date_sk -| | -| |--25:SCAN HDFS [tpcds.date_dim] -| | partitions=1/1 files=1 size=9.84MB -| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1)) -| | -| 27:HASH JOIN [INNER JOIN] -| | hash predicates: ss_item_sk = i_item_sk -| | runtime filters: RF008 <- i_item_sk -| | -| |--23:SCAN HDFS [tpcds.item] -| | partitions=1/1 files=1 size=4.82MB -| | -| 24:SCAN HDFS [tpcds.store_sales] -| partitions=120/120 files=120 size=21.31MB -| runtime filters: RF006 -> ss_store_sk, RF007 -> ss_sold_date_sk, RF008 -> ss_item_sk -| -33:HASH JOIN [INNER JOIN] -| hash predicates: rank() + 1 = rank(), s_store_name = s_store_name, i_category = i_category, s_company_name = s_company_name, i_brand = i_brand -| -|--12:SELECT -| | predicates: d_year = 2000, avg(sum(ss_sales_price)) > 0, CASE WHEN avg(sum(ss_sales_price)) > 0 THEN abs(sum(ss_sales_price) - avg(sum(ss_sales_price))) / avg(sum(ss_sales_price)) ELSE NULL END > 0.1 +|--33:HASH JOIN [INNER JOIN] +| | hash predicates: rank() + 1 = rank(), s_store_name = s_store_name, i_category = i_category, s_company_name = s_company_name, i_brand = i_brand | | -| 11:ANALYTIC -| | functions: avg(sum(ss_sales_price)) -| | partition by: i_category, i_brand, s_store_name, s_company_name, d_year -| | -| 10:SORT -| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC NULLS FIRST +| |--12:SELECT +| | | predicates: d_year = 2000, avg(sum(ss_sales_price)) > 0, CASE WHEN avg(sum(ss_sales_price)) > 0 THEN abs(sum(ss_sales_price) - avg(sum(ss_sales_price))) / avg(sum(ss_sales_price)) ELSE NULL END > 0.1 +| | | +| | 11:ANALYTIC +| | | functions: avg(sum(ss_sales_price)) +| | | partition by: i_ca
<TRUNCATED>
