| # TPCDS-Q72 |
| # start query 72 in stream 0 using template query72.tpl using seed 1400421344 |
| select i_item_desc |
| ,w_warehouse_name |
| ,d1.d_week_seq |
| ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo |
| ,sum(case when p_promo_sk is not null then 1 else 0 end) promo |
| ,count(*) total_cnt |
| from catalog_sales |
| join inventory on (cs_item_sk = inv_item_sk) |
| join warehouse on (w_warehouse_sk=inv_warehouse_sk) |
| join item on (i_item_sk = cs_item_sk) |
| join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) |
| join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) |
| join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) |
| join date_dim d2 on (inv_date_sk = d2.d_date_sk) |
| join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) |
| left outer join promotion on (cs_promo_sk=p_promo_sk) |
| left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) |
| where d1.d_week_seq = d2.d_week_seq |
| and inv_quantity_on_hand < cs_quantity |
| and cast(d3.d_date as date) > cast(d1.d_date as date) + interval 5 days |
| and hd_buy_potential = '>10000' |
| and d1.d_year = 2002 |
| and cd_marital_status = 'D' |
| group by i_item_desc,w_warehouse_name,d1.d_week_seq |
| order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq |
| limit 100; |
| |
| # end query 72 in stream 0 using template query72.tpl |
| ---- PLAN |
| Max Per-Host Resource Reservation: Memory=197.25MB Threads=1 |
| Per-Host Resource Estimates: Memory=116.95GB |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=116.95GB mem-reservation=197.25MB thread-reservation=1 runtime-filters-memory=9.00MB |
| | max-parallelism=1 segment-costs=[1946370237431, 1196340444652, 600] |
| PLAN-ROOT SINK |
| | output exprs: i_item_desc, w_warehouse_name, d1.d_week_seq, sum(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END), sum(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END), count(*) |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=600 |
| | |
| 22:TOP-N [LIMIT=100] |
| | order by: count(*) DESC, i_item_desc ASC, w_warehouse_name ASC, d1.d_week_seq ASC |
| | mem-estimate=16.47KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=12 row-size=169B cardinality=100 cost=1196340444652 |
| | in pipelines: 22(GETNEXT), 21(OPEN) |
| | |
| 21:AGGREGATE [FINALIZE] |
| | output: sum(CAST(CASE WHEN p_promo_sk IS NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN p_promo_sk IS NOT NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), count(*) |
| | group by: i_item_desc, w_warehouse_name, d1.d_week_seq |
| | mem-estimate=50.00GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=59.29G cost=1111641993133 |
| | in pipelines: 21(GETNEXT), 00(OPEN) |
| | |
| 20:HASH JOIN [LEFT OUTER JOIN] |
| | hash predicates: cs_item_sk = cr_item_sk, cs_order_number = cr_order_number |
| | fk/pk conjuncts: cs_item_sk = cr_item_sk, cs_order_number = cr_order_number |
| | mem-estimate=16.83GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2,9N,10N row-size=289B cardinality=556.85G cost=244167428758 |
| | in pipelines: 00(GETNEXT), 10(OPEN) |
| | |
| |--10:SCAN HDFS [tpcds_partitioned_parquet_snap.catalog_returns] |
| | HDFS partitions=2060/2060 files=2060 size=32.77GB |
| | stored statistics: |
| | table: rows=432.02M size=32.77GB |
| | partitions: 2060/2060 rows=432.02M |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=416.82K |
| | mem-estimate=16.00MB mem-reservation=32.00KB thread-reservation=0 |
| | tuple-ids=10 row-size=12B cardinality=432.02M cost=74652536 |
| | in pipelines: 10(GETNEXT) |
| | |
| 19:HASH JOIN [LEFT OUTER JOIN] |
| | hash predicates: cs_promo_sk = p_promo_sk |
| | fk/pk conjuncts: cs_promo_sk = p_promo_sk |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2,9N row-size=277B cardinality=556.85G cost=243735413567 |
| | in pipelines: 00(GETNEXT), 09(OPEN) |
| | |
| |--09:SCAN HDFS [tpcds_partitioned_parquet_snap.promotion] |
| | HDFS partitions=1/1 files=1 size=100.50KB |
| | stored statistics: |
| | table: rows=1.80K size=100.50KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=1.80K |
| | mem-estimate=16.00MB mem-reservation=32.00KB thread-reservation=0 |
| | tuple-ids=9 row-size=4B cardinality=1.80K cost=103 |
| | in pipelines: 09(GETNEXT) |
| | |
| 18:HASH JOIN [INNER JOIN] |
| | hash predicates: inv_warehouse_sk = w_warehouse_sk |
| | fk/pk conjuncts: inv_warehouse_sk = w_warehouse_sk |
| | runtime filters: RF000[bloom] <- w_warehouse_sk, RF001[min_max] <- w_warehouse_sk |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2 row-size=273B cardinality=556.85G cost=243735411789 |
| | in pipelines: 00(GETNEXT), 02(OPEN) |
| | |
| |--27:TUPLE CACHE |
| | | cache key: 43c99cdabe1f96001a59b22167d0bf1a |
| | | input scan node ids: 2 |
| | | estimated serialized size: 793B |
| | | estimated serialized size per node: 793B |
| | | cumulative processing cost: 6 |
| | | cache read processing cost: 2 |
| | | cache write processing cost: 2 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=2 row-size=32B cardinality=22 cost=0 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | 02:SCAN HDFS [tpcds_partitioned_parquet_snap.warehouse] |
| | HDFS partitions=1/1 files=1 size=5.99KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | stored statistics: |
| | table: rows=22 size=5.99KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=22 |
| | mem-estimate=16.00MB mem-reservation=16.00KB thread-reservation=0 |
| | tuple-ids=2 row-size=32B cardinality=22 cost=6 |
| | in pipelines: 02(GETNEXT) |
| | |
| 17:HASH JOIN [INNER JOIN] |
| | hash predicates: cs_item_sk = inv_item_sk, d2.d_date_sk = inv_date_sk |
| | fk/pk conjuncts: none |
| | other predicates: inv_quantity_on_hand < cs_quantity |
| | runtime filters: RF002[bloom] <- inv_item_sk, RF003[bloom] <- inv_date_sk, RF004[min_max] <- inv_item_sk, RF005[min_max] <- inv_date_sk |
| | mem-estimate=50.00GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1 row-size=241B cardinality=556.85G cost=101985452242 |
| | in pipelines: 00(GETNEXT), 01(OPEN) |
| | |
| |--01:SCAN HDFS [tpcds_partitioned_parquet_snap.inventory] |
| | HDFS partitions=261/261 files=261 size=5.10GB |
| | runtime filters: RF001[min_max] -> inv_warehouse_sk, RF000[bloom] -> inv_warehouse_sk |
| | stored statistics: |
| | table: rows=1.03G size=5.10GB |
| | partitions: 261/261 rows=1.03G |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=3.98M |
| | mem-estimate=16.00MB mem-reservation=256.00KB thread-reservation=0 |
| | tuple-ids=1 row-size=16B cardinality=1.03G cost=238132224 |
| | in pipelines: 01(GETNEXT) |
| | |
| 16:HASH JOIN [INNER JOIN] |
| | hash predicates: d1.d_week_seq = d2.d_week_seq |
| | fk/pk conjuncts: none |
| | runtime filters: RF006[bloom] <- d2.d_week_seq, RF007[min_max] <- d2.d_week_seq |
| | mem-estimate=4.75MB mem-reservation=4.75MB spill-buffer=256.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7 row-size=225B cardinality=194.06M cost=42487315 |
| | in pipelines: 00(GETNEXT), 07(OPEN) |
| | |
| |--07:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d2] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | runtime filters: RF005[min_max] -> d2.d_date_sk, RF003[bloom] -> d2.d_date_sk |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=7 row-size=8B cardinality=73.05K cost=8415 |
| | in pipelines: 07(GETNEXT) |
| | |
| 15:HASH JOIN [INNER JOIN] |
| | hash predicates: cs_ship_date_sk = d3.d_date_sk |
| | fk/pk conjuncts: cs_ship_date_sk = d3.d_date_sk |
| | other predicates: d3.d_date > d1.d_date + INTERVAL CAST(5 AS INT) days |
| | runtime filters: RF008[bloom] <- d3.d_date_sk, RF009[min_max] <- d3.d_date_sk |
| | mem-estimate=4.75MB mem-reservation=4.75MB spill-buffer=256.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8 row-size=217B cardinality=28.27M cost=12447088 |
| | in pipelines: 00(GETNEXT), 08(OPEN) |
| | |
| |--08:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d3] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=8 row-size=8B cardinality=73.05K cost=8415 |
| | in pipelines: 08(GETNEXT) |
| | |
| 14:HASH JOIN [INNER JOIN] |
| | hash predicates: cs_item_sk = i_item_sk |
| | fk/pk conjuncts: cs_item_sk = i_item_sk |
| | runtime filters: RF010[bloom] <- i_item_sk, RF011[min_max] <- i_item_sk |
| | mem-estimate=52.03MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3 row-size=209B cardinality=28.27M cost=12737665 |
| | in pipelines: 00(GETNEXT), 03(OPEN) |
| | |
| |--26:TUPLE CACHE |
| | | cache key: e49fc99c509ce58e44a74b65d95a63d9 |
| | | input scan node ids: 3 |
| | | estimated serialized size: 41.40MB |
| | | estimated serialized size per node: 10.35MB |
| | | cumulative processing cost: 542243 |
| | | cache read processing cost: 47844 |
| | | cache write processing cost: 117222 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=3 row-size=117B cardinality=360.00K cost=0 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | 03:SCAN HDFS [tpcds_partitioned_parquet_snap.item] |
| | HDFS partitions=1/1 files=1 size=33.54MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | runtime filters: RF004[min_max] -> tpcds_partitioned_parquet_snap.item.i_item_sk, RF002[bloom] -> tpcds_partitioned_parquet_snap.item.i_item_sk |
| | stored statistics: |
| | table: rows=360.00K size=33.54MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=101.54K |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=3 row-size=117B cardinality=360.00K cost=542243 |
| | in pipelines: 03(GETNEXT) |
| | |
| 13:HASH JOIN [INNER JOIN] |
| | hash predicates: cs_sold_date_sk = d1.d_date_sk |
| | fk/pk conjuncts: cs_sold_date_sk = d1.d_date_sk |
| | runtime filters: RF012[bloom] <- d1.d_date_sk |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6 row-size=93B cardinality=28.28M cost=40739416 |
| | in pipelines: 00(GETNEXT), 06(OPEN) |
| | |
| |--25:TUPLE CACHE |
| | | cache key: bf35fe341c9b96e96e1644a992d46926 |
| | | input scan node ids: 6 |
| | | estimated serialized size: 7.29KB |
| | | estimated serialized size per node: 7.29KB |
| | | cumulative processing cost: 18883 |
| | | cache read processing cost: 49 |
| | | cache write processing cost: 20 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=6 row-size=16B cardinality=373 cost=0 |
| | | in pipelines: 06(GETNEXT) |
| | | |
| | 06:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d1] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: d1.d_year = CAST(2002 AS INT) |
| | runtime filters: RF007[min_max] -> d1.d_week_seq, RF006[bloom] -> d1.d_week_seq |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | parquet statistics predicates: d1.d_year = CAST(2002 AS INT) |
| | parquet dictionary predicates: d1.d_year = CAST(2002 AS INT) |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=6 row-size=16B cardinality=373 cost=18883 |
| | in pipelines: 06(GETNEXT) |
| | |
| 12:HASH JOIN [INNER JOIN] |
| | hash predicates: cs_bill_cdemo_sk = cd_demo_sk |
| | fk/pk conjuncts: cs_bill_cdemo_sk = cd_demo_sk |
| | runtime filters: RF014[bloom] <- cd_demo_sk, RF015[min_max] <- cd_demo_sk |
| | mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4 row-size=77B cardinality=138.85M cost=62267778 |
| | in pipelines: 00(GETNEXT), 04(OPEN) |
| | |
| |--24:TUPLE CACHE |
| | | cache key: 5bd7f6c2dfb5fa39f3229d420351bc83 |
| | | input scan node ids: 4 |
| | | estimated serialized size: 7.69MB |
| | | estimated serialized size per node: 787.83KB |
| | | cumulative processing cost: 192272 |
| | | cache read processing cost: 51054 |
| | | cache write processing cost: 21781 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=4 row-size=17B cardinality=384.16K cost=0 |
| | | in pipelines: 04(GETNEXT) |
| | | |
| | 04:SCAN HDFS [tpcds_partitioned_parquet_snap.customer_demographics] |
| | HDFS partitions=1/1 files=1 size=11.15MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: cd_marital_status = 'D' |
| | stored statistics: |
| | table: rows=1.92M size=11.15MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=197.97K |
| | parquet statistics predicates: cd_marital_status = 'D' |
| | parquet dictionary predicates: cd_marital_status = 'D' |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=17B cardinality=384.16K cost=192272 |
| | in pipelines: 04(GETNEXT) |
| | |
| 11:HASH JOIN [INNER JOIN] |
| | hash predicates: cs_bill_hdemo_sk = hd_demo_sk |
| | fk/pk conjuncts: cs_bill_hdemo_sk = hd_demo_sk |
| | runtime filters: RF016[bloom] <- hd_demo_sk, RF017[min_max] <- hd_demo_sk |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5 row-size=60B cardinality=143.18M(filtered from 702.83M) cost=164079304 |
| | in pipelines: 00(GETNEXT), 05(OPEN) |
| | |
| |--23:TUPLE CACHE |
| | | cache key: 973e7121ac4f2a99a0f7771d7ebd8e3b |
| | | input scan node ids: 5 |
| | | estimated serialized size: 32.23KB |
| | | estimated serialized size per node: 32.23KB |
| | | cumulative processing cost: 1394 |
| | | cache read processing cost: 159 |
| | | cache write processing cost: 89 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=5 row-size=24B cardinality=1.20K cost=0 |
| | | in pipelines: 05(GETNEXT) |
| | | |
| | 05:SCAN HDFS [tpcds_partitioned_parquet_snap.household_demographics] |
| | HDFS partitions=1/1 files=1 size=41.69KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: hd_buy_potential = '>10000' |
| | stored statistics: |
| | table: rows=7.20K size=41.69KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=7.20K |
| | parquet statistics predicates: hd_buy_potential = '>10000' |
| | parquet dictionary predicates: hd_buy_potential = '>10000' |
| | mem-estimate=16.00MB mem-reservation=64.00KB thread-reservation=0 |
| | tuple-ids=5 row-size=24B cardinality=1.20K cost=1394 |
| | in pipelines: 05(GETNEXT) |
| | |
| 00:SCAN HDFS [tpcds_partitioned_parquet_snap.catalog_sales] |
| HDFS partitions=1831/1831 files=1831 size=280.96GB |
| runtime filters: RF017[min_max] -> cs_bill_hdemo_sk, RF015[min_max] -> cs_bill_cdemo_sk, RF011[min_max] -> cs_item_sk, RF009[min_max] -> cs_ship_date_sk, RF004[min_max] -> cs_item_sk, RF016[bloom] -> cs_bill_hdemo_sk, RF014[bloom] -> cs_bill_cdemo_sk, RF012[bloom] -> cs_sold_date_sk, RF010[bloom] -> cs_item_sk, RF008[bloom] -> cs_ship_date_sk, RF002[bloom] -> cs_item_sk |
| stored statistics: |
| table: rows=4.32G size=280.96GB |
| partitions: 1831/1831 rows=4.32G |
| columns: all |
| extrapolated-rows=disabled max-scan-range-rows=21.52M est-scan-range=374(filtered from 1831) |
| mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| tuple-ids=0 row-size=36B cardinality=143.18M(filtered from 4.32G) cost=456222885 |
| in pipelines: 00(GETNEXT) |
| ---- DISTRIBUTEDPLAN |
| Max Per-Host Resource Reservation: Memory=2.67GB Threads=79 |
| Per-Host Resource Estimates: Memory=127.19GB |
| F13:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=5.98MB mem-reservation=4.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[659] cpu-comparison-result=360 [max(1 (self) vs 360 (sum children))] |
| PLAN-ROOT SINK |
| | output exprs: i_item_desc, w_warehouse_name, d1.d_week_seq, sum(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END), sum(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END), count(*) |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=600 |
| | |
| 36:MERGING-EXCHANGE [UNPARTITIONED] |
| | order by: count(*) DESC, i_item_desc ASC, w_warehouse_name ASC, d1.d_week_seq ASC |
| | limit: 100 |
| | mem-estimate=1.98MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=12 row-size=169B cardinality=100 cost=59 |
| | in pipelines: 22(GETNEXT) |
| | |
| F12:PLAN FRAGMENT [HASH(i_item_desc,w_warehouse_name,d1.d_week_seq)] hosts=10 instances=120 |
| Per-Instance Resources: mem-estimate=4.20GB mem-reservation=34.00MB thread-reservation=1 |
| max-parallelism=155460 segment-costs=[1554521531826, 1196340444652, 473] cpu-comparison-result=360 [max(120 (self) vs 360 (sum children))] |
| 22:TOP-N [LIMIT=100] |
| | order by: count(*) DESC, i_item_desc ASC, w_warehouse_name ASC, d1.d_week_seq ASC |
| | mem-estimate=16.47KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=12 row-size=169B cardinality=100 cost=1196340444652 |
| | in pipelines: 22(GETNEXT), 35(OPEN) |
| | |
| 35:AGGREGATE [FINALIZE] |
| | output: sum:merge(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END), sum:merge(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END), count:merge(*) |
| | group by: i_item_desc, w_warehouse_name, d1.d_week_seq |
| | mem-estimate=4.17GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=59.29G cost=1079491668669 |
| | in pipelines: 35(GETNEXT), 00(OPEN) |
| | |
| 34:EXCHANGE [HASH(i_item_desc,w_warehouse_name,d1.d_week_seq)] |
| | mem-estimate=30.23MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=535.62G cost=475029863157 |
| | in pipelines: 00(GETNEXT) |
| | |
| F08:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=120 |
| Per-Instance Resources: mem-estimate=4.28GB mem-reservation=34.00MB thread-reservation=1 |
| max-parallelism=609330 segment-costs=[4101117248955, 6093265120169] cpu-comparison-result=360 [max(240 (self) vs 360 (sum children))] |
| 21:AGGREGATE [STREAMING] |
| | output: sum(CAST(CASE WHEN p_promo_sk IS NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN p_promo_sk IS NOT NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), count(*) |
| | group by: i_item_desc, w_warehouse_name, d1.d_week_seq |
| | mem-estimate=4.17GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=535.62G cost=3268693778097 |
| | in pipelines: 00(GETNEXT) |
| | |
| 20:HASH JOIN [LEFT OUTER JOIN, BROADCAST] |
| | hash-table-id=00 |
| | hash predicates: cs_item_sk = cr_item_sk, cs_order_number = cr_order_number |
| | fk/pk conjuncts: cs_item_sk = cr_item_sk, cs_order_number = cr_order_number |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2,9N,10N row-size=289B cardinality=556.85G cost=243735411767 |
| | in pipelines: 00(GETNEXT), 10(OPEN) |
| | |
| |--F14:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=16.84GB mem-reservation=408.00MB thread-reservation=1 |
| | | max-parallelism=10 segment-costs=[1006167571] |
| | JOIN BUILD |
| | | join-table-id=00 plan-id=01 cohort-id=01 |
| | | build expressions: cr_item_sk, cr_order_number |
| | | mem-estimate=16.83GB mem-reservation=408.00MB spill-buffer=2.00MB thread-reservation=0 cost=432016991 |
| | | |
| | 33:EXCHANGE [BROADCAST] |
| | | mem-estimate=10.16MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=10 row-size=12B cardinality=432.02M cost=574150580 |
| | | in pipelines: 10(GETNEXT) |
| | | |
| | F11:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 (adjusted from 120) |
| | Per-Instance Resources: mem-estimate=16.06MB mem-reservation=32.00KB thread-reservation=1 |
| | max-parallelism=10 segment-costs=[97981453] |
| | 10:SCAN HDFS [tpcds_partitioned_parquet_snap.catalog_returns, RANDOM] |
| | HDFS partitions=2060/2060 files=2060 size=32.77GB |
| | stored statistics: |
| | table: rows=432.02M size=32.77GB |
| | partitions: 2060/2060 rows=432.02M |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=416.82K |
| | mem-estimate=16.00MB mem-reservation=32.00KB thread-reservation=0 |
| | tuple-ids=10 row-size=12B cardinality=432.02M cost=74652536 |
| | in pipelines: 10(GETNEXT) |
| | |
| 19:HASH JOIN [LEFT OUTER JOIN, BROADCAST] |
| | hash-table-id=01 |
| | hash predicates: cs_promo_sk = p_promo_sk |
| | fk/pk conjuncts: cs_promo_sk = p_promo_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2,9N row-size=277B cardinality=556.85G cost=243735411767 |
| | in pipelines: 00(GETNEXT), 09(OPEN) |
| | |
| |--F15:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=23.27MB mem-reservation=23.25MB thread-reservation=1 |
| | | max-parallelism=10 segment-costs=[4190] |
| | JOIN BUILD |
| | | join-table-id=01 plan-id=02 cohort-id=01 |
| | | build expressions: p_promo_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=1800 |
| | | |
| | 32:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=9 row-size=4B cardinality=1.80K cost=2390 |
| | | in pipelines: 09(GETNEXT) |
| | | |
| | F10:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=16.03MB mem-reservation=32.00KB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[161] |
| | 09:SCAN HDFS [tpcds_partitioned_parquet_snap.promotion, RANDOM] |
| | HDFS partitions=1/1 files=1 size=100.50KB |
| | stored statistics: |
| | table: rows=1.80K size=100.50KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=1.80K |
| | mem-estimate=16.00MB mem-reservation=32.00KB thread-reservation=0 |
| | tuple-ids=9 row-size=4B cardinality=1.80K cost=103 |
| | in pipelines: 09(GETNEXT) |
| | |
| 18:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=02 |
| | hash predicates: inv_warehouse_sk = w_warehouse_sk |
| | fk/pk conjuncts: inv_warehouse_sk = w_warehouse_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2 row-size=273B cardinality=556.85G cost=243735411767 |
| | in pipelines: 00(GETNEXT), 02(OPEN) |
| | |
| |--F16:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=24.27MB mem-reservation=24.25MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[42] |
| | JOIN BUILD |
| | | join-table-id=02 plan-id=03 cohort-id=01 |
| | | build expressions: w_warehouse_sk |
| | | runtime filters: RF000[bloom] <- w_warehouse_sk, RF001[min_max] <- w_warehouse_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=22 |
| | | |
| | 31:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=2 row-size=32B cardinality=22 cost=20 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | F09:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.14MB mem-reservation=4.02MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[8] |
| | 41:TUPLE CACHE |
| | | cache key: 43c99cdabe1f96001a59b22167d0bf1a |
| | | input scan node ids: 2 |
| | | estimated serialized size: 793B |
| | | estimated serialized size per node: 793B |
| | | cumulative processing cost: 6 |
| | | cache read processing cost: 2 |
| | | cache write processing cost: 2 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=2 row-size=32B cardinality=22 cost=0 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | 02:SCAN HDFS [tpcds_partitioned_parquet_snap.warehouse, RANDOM] |
| | HDFS partitions=1/1 files=1 size=5.99KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | stored statistics: |
| | table: rows=22 size=5.99KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=22 |
| | mem-estimate=16.00MB mem-reservation=16.00KB thread-reservation=0 |
| | tuple-ids=2 row-size=32B cardinality=22 cost=6 |
| | in pipelines: 02(GETNEXT) |
| | |
| 17:HASH JOIN [INNER JOIN, PARTITIONED] |
| | hash-table-id=03 |
| | hash predicates: cs_item_sk = inv_item_sk, d2.d_date_sk = inv_date_sk |
| | fk/pk conjuncts: none |
| | other predicates: inv_quantity_on_hand < cs_quantity |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1 row-size=241B cardinality=556.85G cost=100951892242 |
| | in pipelines: 00(GETNEXT), 01(OPEN) |
| | |
| |--F17:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=120 |
| | | Per-Instance Resources: mem-estimate=470.04MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB |
| | | max-parallelism=609330 segment-costs=[1224458532] |
| | JOIN BUILD |
| | | join-table-id=03 plan-id=04 cohort-id=01 |
| | | build expressions: inv_item_sk, inv_date_sk |
| | | runtime filters: RF002[bloom] <- inv_item_sk, RF003[bloom] <- inv_date_sk, RF004[min_max] <- inv_item_sk, RF005[min_max] <- inv_date_sk |
| | | mem-estimate=455.69MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=1033560000 |
| | | |
| | 30:EXCHANGE [HASH(inv_item_sk,inv_date_sk)] |
| | | mem-estimate=12.34MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=1 row-size=16B cardinality=1.03G cost=190898532 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | F07:PLAN FRAGMENT [RANDOM] hosts=10 instances=120 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=25.38MB mem-reservation=256.00KB thread-reservation=1 |
| | max-parallelism=190 segment-costs=[1835602560] |
| | 01:SCAN HDFS [tpcds_partitioned_parquet_snap.inventory, RANDOM] |
| | HDFS partitions=261/261 files=261 size=5.10GB |
| | runtime filters: RF001[min_max] -> inv_warehouse_sk, RF000[bloom] -> inv_warehouse_sk |
| | stored statistics: |
| | table: rows=1.03G size=5.10GB |
| | partitions: 261/261 rows=1.03G |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=3.98M |
| | mem-estimate=16.00MB mem-reservation=256.00KB thread-reservation=0 |
| | tuple-ids=1 row-size=16B cardinality=1.03G cost=238132224 |
| | in pipelines: 01(GETNEXT) |
| | |
| 29:EXCHANGE [HASH(cs_item_sk,d2.d_date_sk)] |
| | mem-estimate=39.66MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7 row-size=225B cardinality=194.06M cost=265343315 |
| | in pipelines: 00(GETNEXT) |
| | |
| F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=120 |
| Per-Host Shared Resources: mem-estimate=6.00MB mem-reservation=6.00MB thread-reservation=0 runtime-filters-memory=6.00MB |
| Per-Instance Resources: mem-estimate=134.64MB mem-reservation=1.00MB thread-reservation=1 |
| max-parallelism=374 segment-costs=[4303039497] |
| 16:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=04 |
| | hash predicates: d1.d_week_seq = d2.d_week_seq |
| | fk/pk conjuncts: none |
| | mem-estimate=0B mem-reservation=0B spill-buffer=256.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7 row-size=225B cardinality=194.06M cost=42414266 |
| | in pipelines: 00(GETNEXT), 07(OPEN) |
| | |
| |--F18:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=58.57MB mem-reservation=58.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[170129] |
| | JOIN BUILD |
| | | join-table-id=04 plan-id=05 cohort-id=01 |
| | | build expressions: d2.d_week_seq |
| | | runtime filters: RF006[bloom] <- d2.d_week_seq, RF007[min_max] <- d2.d_week_seq |
| | | mem-estimate=57.00MB mem-reservation=57.00MB spill-buffer=256.00KB thread-reservation=0 cost=73049 |
| | | |
| | 28:EXCHANGE [BROADCAST] |
| | | mem-estimate=582.70KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=7 row-size=8B cardinality=73.05K cost=97080 |
| | | in pipelines: 07(GETNEXT) |
| | | |
| | F06:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=16.05MB mem-reservation=512.00KB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[11570] |
| | 07:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d2, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | runtime filters: RF005[min_max] -> d2.d_date_sk, RF003[bloom] -> d2.d_date_sk |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=7 row-size=8B cardinality=73.05K cost=8415 |
| | in pipelines: 07(GETNEXT) |
| | |
| 15:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=05 |
| | hash predicates: cs_ship_date_sk = d3.d_date_sk |
| | fk/pk conjuncts: cs_ship_date_sk = d3.d_date_sk |
| | other predicates: d3.d_date > d1.d_date + INTERVAL CAST(5 AS INT) days |
| | mem-estimate=0B mem-reservation=0B spill-buffer=256.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8 row-size=217B cardinality=28.27M cost=12374039 |
| | in pipelines: 00(GETNEXT), 08(OPEN) |
| | |
| |--F19:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=58.57MB mem-reservation=58.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[170129] |
| | JOIN BUILD |
| | | join-table-id=05 plan-id=06 cohort-id=01 |
| | | build expressions: d3.d_date_sk |
| | | runtime filters: RF008[bloom] <- d3.d_date_sk, RF009[min_max] <- d3.d_date_sk |
| | | mem-estimate=57.00MB mem-reservation=57.00MB spill-buffer=256.00KB thread-reservation=0 cost=73049 |
| | | |
| | 27:EXCHANGE [BROADCAST] |
| | | mem-estimate=582.70KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=8 row-size=8B cardinality=73.05K cost=97080 |
| | | in pipelines: 08(GETNEXT) |
| | | |
| | F05:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=16.05MB mem-reservation=1.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[11570] |
| | 08:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d3, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=8 row-size=8B cardinality=73.05K cost=8415 |
| | in pipelines: 08(GETNEXT) |
| | |
| 14:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=06 |
| | hash predicates: cs_item_sk = i_item_sk |
| | fk/pk conjuncts: cs_item_sk = i_item_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3 row-size=209B cardinality=28.27M cost=12377665 |
| | in pipelines: 00(GETNEXT), 03(OPEN) |
| | |
| |--F20:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=419.47MB mem-reservation=409.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[838440] |
| | JOIN BUILD |
| | | join-table-id=06 plan-id=07 cohort-id=01 |
| | | build expressions: i_item_sk |
| | | runtime filters: RF010[bloom] <- i_item_sk, RF011[min_max] <- i_item_sk |
| | | mem-estimate=408.00MB mem-reservation=408.00MB spill-buffer=2.00MB thread-reservation=0 cost=360000 |
| | | |
| | 26:EXCHANGE [BROADCAST] |
| | | mem-estimate=10.47MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=3 row-size=117B cardinality=360.00K cost=478440 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | F04:PLAN FRAGMENT [RANDOM] hosts=4 instances=4 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=20.47MB mem-reservation=4.50MB thread-reservation=1 |
| | max-parallelism=4 segment-costs=[663353] |
| | 40:TUPLE CACHE |
| | | cache key: 8476b8472ed6c02970a33a86b14a8db6 |
| | | input scan node ids: 3 |
| | | estimated serialized size: 41.40MB |
| | | estimated serialized size per node: 10.35MB |
| | | cumulative processing cost: 542243 |
| | | cache read processing cost: 47844 |
| | | cache write processing cost: 117222 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=3 row-size=117B cardinality=360.00K cost=0 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | 03:SCAN HDFS [tpcds_partitioned_parquet_snap.item, RANDOM] |
| | HDFS partitions=1/1 files=1 size=33.54MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | runtime filters: RF004[min_max] -> tpcds_partitioned_parquet_snap.item.i_item_sk, RF002[bloom] -> tpcds_partitioned_parquet_snap.item.i_item_sk |
| | stored statistics: |
| | table: rows=360.00K size=33.54MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=101.54K |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=3 row-size=117B cardinality=360.00K cost=542243 |
| | in pipelines: 03(GETNEXT) |
| | |
| 13:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=07 |
| | hash predicates: cs_sold_date_sk = d1.d_date_sk |
| | fk/pk conjuncts: cs_sold_date_sk = d1.d_date_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6 row-size=93B cardinality=28.28M cost=40739043 |
| | in pipelines: 00(GETNEXT), 06(OPEN) |
| | |
| |--F21:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=24.27MB mem-reservation=24.25MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[863] |
| | JOIN BUILD |
| | | join-table-id=07 plan-id=08 cohort-id=01 |
| | | build expressions: d1.d_date_sk |
| | | runtime filters: RF012[bloom] <- d1.d_date_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=373 |
| | | |
| | 25:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=6 row-size=16B cardinality=373 cost=490 |
| | | in pipelines: 06(GETNEXT) |
| | | |
| | F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=20.08MB mem-reservation=5.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[18907] |
| | 39:TUPLE CACHE |
| | | cache key: 3c4c99376f4e562f65284076c8ca0dd6 |
| | | input scan node ids: 6 |
| | | estimated serialized size: 7.29KB |
| | | estimated serialized size per node: 7.29KB |
| | | cumulative processing cost: 18883 |
| | | cache read processing cost: 49 |
| | | cache write processing cost: 20 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=6 row-size=16B cardinality=373 cost=0 |
| | | in pipelines: 06(GETNEXT) |
| | | |
| | 06:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d1, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: d1.d_year = CAST(2002 AS INT) |
| | runtime filters: RF007[min_max] -> d1.d_week_seq, RF006[bloom] -> d1.d_week_seq |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | parquet statistics predicates: d1.d_year = CAST(2002 AS INT) |
| | parquet dictionary predicates: d1.d_year = CAST(2002 AS INT) |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=6 row-size=16B cardinality=373 cost=18883 |
| | in pipelines: 06(GETNEXT) |
| | |
| 12:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=08 |
| | hash predicates: cs_bill_cdemo_sk = cd_demo_sk |
| | fk/pk conjuncts: cs_bill_cdemo_sk = cd_demo_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4 row-size=77B cardinality=138.85M cost=61883618 |
| | in pipelines: 00(GETNEXT), 04(OPEN) |
| | |
| |--F22:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=415.43MB mem-reservation=409.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[894700] |
| | JOIN BUILD |
| | | join-table-id=08 plan-id=09 cohort-id=01 |
| | | build expressions: cd_demo_sk |
| | | runtime filters: RF014[bloom] <- cd_demo_sk, RF015[min_max] <- cd_demo_sk |
| | | mem-estimate=408.00MB mem-reservation=408.00MB spill-buffer=2.00MB thread-reservation=0 cost=384160 |
| | | |
| | 24:EXCHANGE [BROADCAST] |
| | | mem-estimate=6.43MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=4 row-size=17B cardinality=384.16K cost=510540 |
| | | in pipelines: 04(GETNEXT) |
| | | |
| | F02:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | Per-Instance Resources: mem-estimate=20.08MB mem-reservation=5.00MB thread-reservation=1 |
| | max-parallelism=10 segment-costs=[218202] |
| | 38:TUPLE CACHE |
| | | cache key: 5bd7f6c2dfb5fa39f3229d420351bc83 |
| | | input scan node ids: 4 |
| | | estimated serialized size: 7.69MB |
| | | estimated serialized size per node: 787.83KB |
| | | cumulative processing cost: 192272 |
| | | cache read processing cost: 51054 |
| | | cache write processing cost: 21781 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=4 row-size=17B cardinality=384.16K cost=0 |
| | | in pipelines: 04(GETNEXT) |
| | | |
| | 04:SCAN HDFS [tpcds_partitioned_parquet_snap.customer_demographics, RANDOM] |
| | HDFS partitions=1/1 files=1 size=11.15MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: cd_marital_status = 'D' |
| | stored statistics: |
| | table: rows=1.92M size=11.15MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=197.97K |
| | parquet statistics predicates: cd_marital_status = 'D' |
| | parquet dictionary predicates: cd_marital_status = 'D' |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=17B cardinality=384.16K cost=192272 |
| | in pipelines: 04(GETNEXT) |
| | |
| 11:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=09 |
| | hash predicates: cs_bill_hdemo_sk = hd_demo_sk |
| | fk/pk conjuncts: cs_bill_hdemo_sk = hd_demo_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5 row-size=60B cardinality=143.18M(filtered from 702.83M) cost=164078104 |
| | in pipelines: 00(GETNEXT), 05(OPEN) |
| | |
| |--F23:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=24.30MB mem-reservation=24.25MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[2790] |
| | JOIN BUILD |
| | | join-table-id=09 plan-id=10 cohort-id=01 |
| | | build expressions: hd_demo_sk |
| | | runtime filters: RF016[bloom] <- hd_demo_sk, RF017[min_max] <- hd_demo_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=1200 |
| | | |
| | 23:EXCHANGE [BROADCAST] |
| | | mem-estimate=55.04KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=5 row-size=24B cardinality=1.20K cost=1590 |
| | | in pipelines: 05(GETNEXT) |
| | | |
| | F01:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.11MB mem-reservation=4.06MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[1496] |
| | 37:TUPLE CACHE |
| | | cache key: 973e7121ac4f2a99a0f7771d7ebd8e3b |
| | | input scan node ids: 5 |
| | | estimated serialized size: 32.23KB |
| | | estimated serialized size per node: 32.23KB |
| | | cumulative processing cost: 1394 |
| | | cache read processing cost: 159 |
| | | cache write processing cost: 89 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=5 row-size=24B cardinality=1.20K cost=0 |
| | | in pipelines: 05(GETNEXT) |
| | | |
| | 05:SCAN HDFS [tpcds_partitioned_parquet_snap.household_demographics, RANDOM] |
| | HDFS partitions=1/1 files=1 size=41.69KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: hd_buy_potential = '>10000' |
| | stored statistics: |
| | table: rows=7.20K size=41.69KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=7.20K |
| | parquet statistics predicates: hd_buy_potential = '>10000' |
| | parquet dictionary predicates: hd_buy_potential = '>10000' |
| | mem-estimate=16.00MB mem-reservation=64.00KB thread-reservation=0 |
| | tuple-ids=5 row-size=24B cardinality=1.20K cost=1394 |
| | in pipelines: 05(GETNEXT) |
| | |
| 00:SCAN HDFS [tpcds_partitioned_parquet_snap.catalog_sales, RANDOM] |
| HDFS partitions=1831/1831 files=1831 size=280.96GB |
| runtime filters: RF017[min_max] -> cs_bill_hdemo_sk, RF015[min_max] -> cs_bill_cdemo_sk, RF011[min_max] -> cs_item_sk, RF009[min_max] -> cs_ship_date_sk, RF004[min_max] -> cs_item_sk, RF016[bloom] -> cs_bill_hdemo_sk, RF014[bloom] -> cs_bill_cdemo_sk, RF012[bloom] -> cs_sold_date_sk, RF010[bloom] -> cs_item_sk, RF008[bloom] -> cs_ship_date_sk, RF002[bloom] -> cs_item_sk |
| stored statistics: |
| table: rows=4.32G size=280.96GB |
| partitions: 1831/1831 rows=4.32G |
| columns: all |
| extrapolated-rows=disabled max-scan-range-rows=21.52M est-scan-range=374(filtered from 1831) |
| mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| tuple-ids=0 row-size=36B cardinality=143.18M(filtered from 4.32G) cost=456222885 |
| in pipelines: 00(GETNEXT) |
| ---- PARALLELPLANS |
| Max Per-Host Resource Reservation: Memory=2.67GB Threads=79 |
| Per-Host Resource Estimates: Memory=127.19GB |
| F13:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=5.98MB mem-reservation=4.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[659] cpu-comparison-result=360 [max(1 (self) vs 360 (sum children))] |
| PLAN-ROOT SINK |
| | output exprs: i_item_desc, w_warehouse_name, d1.d_week_seq, sum(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END), sum(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END), count(*) |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=600 |
| | |
| 36:MERGING-EXCHANGE [UNPARTITIONED] |
| | order by: count(*) DESC, i_item_desc ASC, w_warehouse_name ASC, d1.d_week_seq ASC |
| | limit: 100 |
| | mem-estimate=1.98MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=12 row-size=169B cardinality=100 cost=59 |
| | in pipelines: 22(GETNEXT) |
| | |
| F12:PLAN FRAGMENT [HASH(i_item_desc,w_warehouse_name,d1.d_week_seq)] hosts=10 instances=120 |
| Per-Instance Resources: mem-estimate=4.20GB mem-reservation=34.00MB thread-reservation=1 |
| max-parallelism=155460 segment-costs=[1554521531826, 1196340444652, 473] cpu-comparison-result=360 [max(120 (self) vs 360 (sum children))] |
| 22:TOP-N [LIMIT=100] |
| | order by: count(*) DESC, i_item_desc ASC, w_warehouse_name ASC, d1.d_week_seq ASC |
| | mem-estimate=16.47KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=12 row-size=169B cardinality=100 cost=1196340444652 |
| | in pipelines: 22(GETNEXT), 35(OPEN) |
| | |
| 35:AGGREGATE [FINALIZE] |
| | output: sum:merge(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END), sum:merge(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END), count:merge(*) |
| | group by: i_item_desc, w_warehouse_name, d1.d_week_seq |
| | mem-estimate=4.17GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=59.29G cost=1079491668669 |
| | in pipelines: 35(GETNEXT), 00(OPEN) |
| | |
| 34:EXCHANGE [HASH(i_item_desc,w_warehouse_name,d1.d_week_seq)] |
| | mem-estimate=30.23MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=535.62G cost=475029863157 |
| | in pipelines: 00(GETNEXT) |
| | |
| F08:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=120 |
| Per-Instance Resources: mem-estimate=4.28GB mem-reservation=34.00MB thread-reservation=1 |
| max-parallelism=609330 segment-costs=[4101117248955, 6093265120169] cpu-comparison-result=360 [max(240 (self) vs 360 (sum children))] |
| 21:AGGREGATE [STREAMING] |
| | output: sum(CAST(CASE WHEN p_promo_sk IS NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN p_promo_sk IS NOT NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), count(*) |
| | group by: i_item_desc, w_warehouse_name, d1.d_week_seq |
| | mem-estimate=4.17GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=11 row-size=169B cardinality=535.62G cost=3268693778097 |
| | in pipelines: 00(GETNEXT) |
| | |
| 20:HASH JOIN [LEFT OUTER JOIN, BROADCAST] |
| | hash-table-id=00 |
| | hash predicates: cs_item_sk = cr_item_sk, cs_order_number = cr_order_number |
| | fk/pk conjuncts: cs_item_sk = cr_item_sk, cs_order_number = cr_order_number |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2,9N,10N row-size=289B cardinality=556.85G cost=243735411767 |
| | in pipelines: 00(GETNEXT), 10(OPEN) |
| | |
| |--F14:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=16.84GB mem-reservation=408.00MB thread-reservation=1 |
| | | max-parallelism=10 segment-costs=[1006167571] |
| | JOIN BUILD |
| | | join-table-id=00 plan-id=01 cohort-id=01 |
| | | build expressions: cr_item_sk, cr_order_number |
| | | mem-estimate=16.83GB mem-reservation=408.00MB spill-buffer=2.00MB thread-reservation=0 cost=432016991 |
| | | |
| | 33:EXCHANGE [BROADCAST] |
| | | mem-estimate=10.16MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=10 row-size=12B cardinality=432.02M cost=574150580 |
| | | in pipelines: 10(GETNEXT) |
| | | |
| | F11:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 (adjusted from 120) |
| | Per-Instance Resources: mem-estimate=16.06MB mem-reservation=32.00KB thread-reservation=1 |
| | max-parallelism=10 segment-costs=[97981453] |
| | 10:SCAN HDFS [tpcds_partitioned_parquet_snap.catalog_returns, RANDOM] |
| | HDFS partitions=2060/2060 files=2060 size=32.77GB |
| | stored statistics: |
| | table: rows=432.02M size=32.77GB |
| | partitions: 2060/2060 rows=432.02M |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=416.82K |
| | mem-estimate=16.00MB mem-reservation=32.00KB thread-reservation=0 |
| | tuple-ids=10 row-size=12B cardinality=432.02M cost=74652536 |
| | in pipelines: 10(GETNEXT) |
| | |
| 19:HASH JOIN [LEFT OUTER JOIN, BROADCAST] |
| | hash-table-id=01 |
| | hash predicates: cs_promo_sk = p_promo_sk |
| | fk/pk conjuncts: cs_promo_sk = p_promo_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2,9N row-size=277B cardinality=556.85G cost=243735411767 |
| | in pipelines: 00(GETNEXT), 09(OPEN) |
| | |
| |--F15:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=23.27MB mem-reservation=23.25MB thread-reservation=1 |
| | | max-parallelism=10 segment-costs=[4190] |
| | JOIN BUILD |
| | | join-table-id=01 plan-id=02 cohort-id=01 |
| | | build expressions: p_promo_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=1800 |
| | | |
| | 32:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=9 row-size=4B cardinality=1.80K cost=2390 |
| | | in pipelines: 09(GETNEXT) |
| | | |
| | F10:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=16.03MB mem-reservation=32.00KB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[161] |
| | 09:SCAN HDFS [tpcds_partitioned_parquet_snap.promotion, RANDOM] |
| | HDFS partitions=1/1 files=1 size=100.50KB |
| | stored statistics: |
| | table: rows=1.80K size=100.50KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=1.80K |
| | mem-estimate=16.00MB mem-reservation=32.00KB thread-reservation=0 |
| | tuple-ids=9 row-size=4B cardinality=1.80K cost=103 |
| | in pipelines: 09(GETNEXT) |
| | |
| 18:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=02 |
| | hash predicates: inv_warehouse_sk = w_warehouse_sk |
| | fk/pk conjuncts: inv_warehouse_sk = w_warehouse_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1,2 row-size=273B cardinality=556.85G cost=243735411767 |
| | in pipelines: 00(GETNEXT), 02(OPEN) |
| | |
| |--F16:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=24.27MB mem-reservation=24.25MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[42] |
| | JOIN BUILD |
| | | join-table-id=02 plan-id=03 cohort-id=01 |
| | | build expressions: w_warehouse_sk |
| | | runtime filters: RF000[bloom] <- w_warehouse_sk, RF001[min_max] <- w_warehouse_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=22 |
| | | |
| | 31:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=2 row-size=32B cardinality=22 cost=20 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | F09:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.14MB mem-reservation=4.02MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[8] |
| | 41:TUPLE CACHE |
| | | cache key: 43c99cdabe1f96001a59b22167d0bf1a |
| | | input scan node ids: 2 |
| | | estimated serialized size: 793B |
| | | estimated serialized size per node: 793B |
| | | cumulative processing cost: 6 |
| | | cache read processing cost: 2 |
| | | cache write processing cost: 2 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=2 row-size=32B cardinality=22 cost=0 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | 02:SCAN HDFS [tpcds_partitioned_parquet_snap.warehouse, RANDOM] |
| | HDFS partitions=1/1 files=1 size=5.99KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | stored statistics: |
| | table: rows=22 size=5.99KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=22 |
| | mem-estimate=16.00MB mem-reservation=16.00KB thread-reservation=0 |
| | tuple-ids=2 row-size=32B cardinality=22 cost=6 |
| | in pipelines: 02(GETNEXT) |
| | |
| 17:HASH JOIN [INNER JOIN, PARTITIONED] |
| | hash-table-id=03 |
| | hash predicates: cs_item_sk = inv_item_sk, d2.d_date_sk = inv_date_sk |
| | fk/pk conjuncts: none |
| | other predicates: inv_quantity_on_hand < cs_quantity |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7,1 row-size=241B cardinality=556.85G cost=100951892242 |
| | in pipelines: 00(GETNEXT), 01(OPEN) |
| | |
| |--F17:PLAN FRAGMENT [HASH(cs_item_sk,d2.d_date_sk)] hosts=10 instances=120 |
| | | Per-Instance Resources: mem-estimate=470.04MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB |
| | | max-parallelism=609330 segment-costs=[1224458532] |
| | JOIN BUILD |
| | | join-table-id=03 plan-id=04 cohort-id=01 |
| | | build expressions: inv_item_sk, inv_date_sk |
| | | runtime filters: RF002[bloom] <- inv_item_sk, RF003[bloom] <- inv_date_sk, RF004[min_max] <- inv_item_sk, RF005[min_max] <- inv_date_sk |
| | | mem-estimate=455.69MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=1033560000 |
| | | |
| | 30:EXCHANGE [HASH(inv_item_sk,inv_date_sk)] |
| | | mem-estimate=12.34MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=1 row-size=16B cardinality=1.03G cost=190898532 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | F07:PLAN FRAGMENT [RANDOM] hosts=10 instances=120 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=25.38MB mem-reservation=256.00KB thread-reservation=1 |
| | max-parallelism=190 segment-costs=[1835602560] |
| | 01:SCAN HDFS [tpcds_partitioned_parquet_snap.inventory, RANDOM] |
| | HDFS partitions=261/261 files=261 size=5.10GB |
| | runtime filters: RF001[min_max] -> inv_warehouse_sk, RF000[bloom] -> inv_warehouse_sk |
| | stored statistics: |
| | table: rows=1.03G size=5.10GB |
| | partitions: 261/261 rows=1.03G |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=3.98M |
| | mem-estimate=16.00MB mem-reservation=256.00KB thread-reservation=0 |
| | tuple-ids=1 row-size=16B cardinality=1.03G cost=238132224 |
| | in pipelines: 01(GETNEXT) |
| | |
| 29:EXCHANGE [HASH(cs_item_sk,d2.d_date_sk)] |
| | mem-estimate=39.66MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7 row-size=225B cardinality=194.06M cost=265343315 |
| | in pipelines: 00(GETNEXT) |
| | |
| F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=120 |
| Per-Host Shared Resources: mem-estimate=6.00MB mem-reservation=6.00MB thread-reservation=0 runtime-filters-memory=6.00MB |
| Per-Instance Resources: mem-estimate=134.64MB mem-reservation=1.00MB thread-reservation=1 |
| max-parallelism=374 segment-costs=[4303039497] |
| 16:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=04 |
| | hash predicates: d1.d_week_seq = d2.d_week_seq |
| | fk/pk conjuncts: none |
| | mem-estimate=0B mem-reservation=0B spill-buffer=256.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8,7 row-size=225B cardinality=194.06M cost=42414266 |
| | in pipelines: 00(GETNEXT), 07(OPEN) |
| | |
| |--F18:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=58.57MB mem-reservation=58.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[170129] |
| | JOIN BUILD |
| | | join-table-id=04 plan-id=05 cohort-id=01 |
| | | build expressions: d2.d_week_seq |
| | | runtime filters: RF006[bloom] <- d2.d_week_seq, RF007[min_max] <- d2.d_week_seq |
| | | mem-estimate=57.00MB mem-reservation=57.00MB spill-buffer=256.00KB thread-reservation=0 cost=73049 |
| | | |
| | 28:EXCHANGE [BROADCAST] |
| | | mem-estimate=582.70KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=7 row-size=8B cardinality=73.05K cost=97080 |
| | | in pipelines: 07(GETNEXT) |
| | | |
| | F06:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=16.05MB mem-reservation=512.00KB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[11570] |
| | 07:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d2, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | runtime filters: RF005[min_max] -> d2.d_date_sk, RF003[bloom] -> d2.d_date_sk |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=7 row-size=8B cardinality=73.05K cost=8415 |
| | in pipelines: 07(GETNEXT) |
| | |
| 15:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=05 |
| | hash predicates: cs_ship_date_sk = d3.d_date_sk |
| | fk/pk conjuncts: cs_ship_date_sk = d3.d_date_sk |
| | other predicates: d3.d_date > d1.d_date + INTERVAL CAST(5 AS INT) days |
| | mem-estimate=0B mem-reservation=0B spill-buffer=256.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3,8 row-size=217B cardinality=28.27M cost=12374039 |
| | in pipelines: 00(GETNEXT), 08(OPEN) |
| | |
| |--F19:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=58.57MB mem-reservation=58.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[170129] |
| | JOIN BUILD |
| | | join-table-id=05 plan-id=06 cohort-id=01 |
| | | build expressions: d3.d_date_sk |
| | | runtime filters: RF008[bloom] <- d3.d_date_sk, RF009[min_max] <- d3.d_date_sk |
| | | mem-estimate=57.00MB mem-reservation=57.00MB spill-buffer=256.00KB thread-reservation=0 cost=73049 |
| | | |
| | 27:EXCHANGE [BROADCAST] |
| | | mem-estimate=582.70KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=8 row-size=8B cardinality=73.05K cost=97080 |
| | | in pipelines: 08(GETNEXT) |
| | | |
| | F05:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=16.05MB mem-reservation=1.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[11570] |
| | 08:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d3, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=8 row-size=8B cardinality=73.05K cost=8415 |
| | in pipelines: 08(GETNEXT) |
| | |
| 14:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=06 |
| | hash predicates: cs_item_sk = i_item_sk |
| | fk/pk conjuncts: cs_item_sk = i_item_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4,6,3 row-size=209B cardinality=28.27M cost=12377665 |
| | in pipelines: 00(GETNEXT), 03(OPEN) |
| | |
| |--F20:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=419.47MB mem-reservation=409.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[838440] |
| | JOIN BUILD |
| | | join-table-id=06 plan-id=07 cohort-id=01 |
| | | build expressions: i_item_sk |
| | | runtime filters: RF010[bloom] <- i_item_sk, RF011[min_max] <- i_item_sk |
| | | mem-estimate=408.00MB mem-reservation=408.00MB spill-buffer=2.00MB thread-reservation=0 cost=360000 |
| | | |
| | 26:EXCHANGE [BROADCAST] |
| | | mem-estimate=10.47MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=3 row-size=117B cardinality=360.00K cost=478440 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | F04:PLAN FRAGMENT [RANDOM] hosts=4 instances=4 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=20.47MB mem-reservation=4.50MB thread-reservation=1 |
| | max-parallelism=4 segment-costs=[663353] |
| | 40:TUPLE CACHE |
| | | cache key: 8476b8472ed6c02970a33a86b14a8db6 |
| | | input scan node ids: 3 |
| | | estimated serialized size: 41.40MB |
| | | estimated serialized size per node: 10.35MB |
| | | cumulative processing cost: 542243 |
| | | cache read processing cost: 47844 |
| | | cache write processing cost: 117222 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=3 row-size=117B cardinality=360.00K cost=0 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | 03:SCAN HDFS [tpcds_partitioned_parquet_snap.item, RANDOM] |
| | HDFS partitions=1/1 files=1 size=33.54MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | runtime filters: RF004[min_max] -> tpcds_partitioned_parquet_snap.item.i_item_sk, RF002[bloom] -> tpcds_partitioned_parquet_snap.item.i_item_sk |
| | stored statistics: |
| | table: rows=360.00K size=33.54MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=101.54K |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=3 row-size=117B cardinality=360.00K cost=542243 |
| | in pipelines: 03(GETNEXT) |
| | |
| 13:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=07 |
| | hash predicates: cs_sold_date_sk = d1.d_date_sk |
| | fk/pk conjuncts: cs_sold_date_sk = d1.d_date_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5,4,6 row-size=93B cardinality=28.28M cost=40739043 |
| | in pipelines: 00(GETNEXT), 06(OPEN) |
| | |
| |--F21:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=24.27MB mem-reservation=24.25MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[863] |
| | JOIN BUILD |
| | | join-table-id=07 plan-id=08 cohort-id=01 |
| | | build expressions: d1.d_date_sk |
| | | runtime filters: RF012[bloom] <- d1.d_date_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=373 |
| | | |
| | 25:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=6 row-size=16B cardinality=373 cost=490 |
| | | in pipelines: 06(GETNEXT) |
| | | |
| | F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Host Shared Resources: mem-estimate=1.00MB mem-reservation=1.00MB thread-reservation=0 runtime-filters-memory=1.00MB |
| | Per-Instance Resources: mem-estimate=20.08MB mem-reservation=5.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[18907] |
| | 39:TUPLE CACHE |
| | | cache key: 3c4c99376f4e562f65284076c8ca0dd6 |
| | | input scan node ids: 6 |
| | | estimated serialized size: 7.29KB |
| | | estimated serialized size per node: 7.29KB |
| | | cumulative processing cost: 18883 |
| | | cache read processing cost: 49 |
| | | cache write processing cost: 20 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=6 row-size=16B cardinality=373 cost=0 |
| | | in pipelines: 06(GETNEXT) |
| | | |
| | 06:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d1, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: d1.d_year = CAST(2002 AS INT) |
| | runtime filters: RF007[min_max] -> d1.d_week_seq, RF006[bloom] -> d1.d_week_seq |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | parquet statistics predicates: d1.d_year = CAST(2002 AS INT) |
| | parquet dictionary predicates: d1.d_year = CAST(2002 AS INT) |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=6 row-size=16B cardinality=373 cost=18883 |
| | in pipelines: 06(GETNEXT) |
| | |
| 12:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=08 |
| | hash predicates: cs_bill_cdemo_sk = cd_demo_sk |
| | fk/pk conjuncts: cs_bill_cdemo_sk = cd_demo_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0,5,4 row-size=77B cardinality=138.85M cost=61883618 |
| | in pipelines: 00(GETNEXT), 04(OPEN) |
| | |
| |--F22:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=415.43MB mem-reservation=409.00MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[894700] |
| | JOIN BUILD |
| | | join-table-id=08 plan-id=09 cohort-id=01 |
| | | build expressions: cd_demo_sk |
| | | runtime filters: RF014[bloom] <- cd_demo_sk, RF015[min_max] <- cd_demo_sk |
| | | mem-estimate=408.00MB mem-reservation=408.00MB spill-buffer=2.00MB thread-reservation=0 cost=384160 |
| | | |
| | 24:EXCHANGE [BROADCAST] |
| | | mem-estimate=6.43MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=4 row-size=17B cardinality=384.16K cost=510540 |
| | | in pipelines: 04(GETNEXT) |
| | | |
| | F02:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | Per-Instance Resources: mem-estimate=20.08MB mem-reservation=5.00MB thread-reservation=1 |
| | max-parallelism=10 segment-costs=[218202] |
| | 38:TUPLE CACHE |
| | | cache key: 5bd7f6c2dfb5fa39f3229d420351bc83 |
| | | input scan node ids: 4 |
| | | estimated serialized size: 7.69MB |
| | | estimated serialized size per node: 787.83KB |
| | | cumulative processing cost: 192272 |
| | | cache read processing cost: 51054 |
| | | cache write processing cost: 21781 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=4 row-size=17B cardinality=384.16K cost=0 |
| | | in pipelines: 04(GETNEXT) |
| | | |
| | 04:SCAN HDFS [tpcds_partitioned_parquet_snap.customer_demographics, RANDOM] |
| | HDFS partitions=1/1 files=1 size=11.15MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: cd_marital_status = 'D' |
| | stored statistics: |
| | table: rows=1.92M size=11.15MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=197.97K |
| | parquet statistics predicates: cd_marital_status = 'D' |
| | parquet dictionary predicates: cd_marital_status = 'D' |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=17B cardinality=384.16K cost=192272 |
| | in pipelines: 04(GETNEXT) |
| | |
| 11:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=09 |
| | hash predicates: cs_bill_hdemo_sk = hd_demo_sk |
| | fk/pk conjuncts: cs_bill_hdemo_sk = hd_demo_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,5 row-size=60B cardinality=143.18M(filtered from 702.83M) cost=164078104 |
| | in pipelines: 00(GETNEXT), 05(OPEN) |
| | |
| |--F23:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=24.30MB mem-reservation=24.25MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[2790] |
| | JOIN BUILD |
| | | join-table-id=09 plan-id=10 cohort-id=01 |
| | | build expressions: hd_demo_sk |
| | | runtime filters: RF016[bloom] <- hd_demo_sk, RF017[min_max] <- hd_demo_sk |
| | | mem-estimate=23.25MB mem-reservation=23.25MB spill-buffer=64.00KB thread-reservation=0 cost=1200 |
| | | |
| | 23:EXCHANGE [BROADCAST] |
| | | mem-estimate=55.04KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=5 row-size=24B cardinality=1.20K cost=1590 |
| | | in pipelines: 05(GETNEXT) |
| | | |
| | F01:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.11MB mem-reservation=4.06MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[1496] |
| | 37:TUPLE CACHE |
| | | cache key: 973e7121ac4f2a99a0f7771d7ebd8e3b |
| | | input scan node ids: 5 |
| | | estimated serialized size: 32.23KB |
| | | estimated serialized size per node: 32.23KB |
| | | cumulative processing cost: 1394 |
| | | cache read processing cost: 159 |
| | | cache write processing cost: 89 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=5 row-size=24B cardinality=1.20K cost=0 |
| | | in pipelines: 05(GETNEXT) |
| | | |
| | 05:SCAN HDFS [tpcds_partitioned_parquet_snap.household_demographics, RANDOM] |
| | HDFS partitions=1/1 files=1 size=41.69KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: hd_buy_potential = '>10000' |
| | stored statistics: |
| | table: rows=7.20K size=41.69KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=7.20K |
| | parquet statistics predicates: hd_buy_potential = '>10000' |
| | parquet dictionary predicates: hd_buy_potential = '>10000' |
| | mem-estimate=16.00MB mem-reservation=64.00KB thread-reservation=0 |
| | tuple-ids=5 row-size=24B cardinality=1.20K cost=1394 |
| | in pipelines: 05(GETNEXT) |
| | |
| 00:SCAN HDFS [tpcds_partitioned_parquet_snap.catalog_sales, RANDOM] |
| HDFS partitions=1831/1831 files=1831 size=280.96GB |
| runtime filters: RF017[min_max] -> cs_bill_hdemo_sk, RF015[min_max] -> cs_bill_cdemo_sk, RF011[min_max] -> cs_item_sk, RF009[min_max] -> cs_ship_date_sk, RF004[min_max] -> cs_item_sk, RF016[bloom] -> cs_bill_hdemo_sk, RF014[bloom] -> cs_bill_cdemo_sk, RF012[bloom] -> cs_sold_date_sk, RF010[bloom] -> cs_item_sk, RF008[bloom] -> cs_ship_date_sk, RF002[bloom] -> cs_item_sk |
| stored statistics: |
| table: rows=4.32G size=280.96GB |
| partitions: 1831/1831 rows=4.32G |
| columns: all |
| extrapolated-rows=disabled max-scan-range-rows=21.52M est-scan-range=374(filtered from 1831) |
| mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| tuple-ids=0 row-size=36B cardinality=143.18M(filtered from 4.32G) cost=456222885 |
| in pipelines: 00(GETNEXT) |
| ==== |
