testdata/workloads/functional-planner/queries/PlannerTest/tpcds_tuple_cache/tpcds-q50.test

# TPCDS-Q50
# start query 50 in stream 0 using template query50.tpl using seed 1521398881
select
   s_store_name
  ,s_company_id
  ,s_street_number
  ,s_street_name
  ,s_street_type
  ,s_suite_number
  ,s_city
  ,s_county
  ,s_state
  ,s_zip
  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end)  as "30 days"
  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
                 (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end )  as "31-60 days"
  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
                 (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end)  as "61-90 days"
  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
                 (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end)  as "91-120 days"
  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk  > 120) then 1 else 0 end)  as ">120 days"
from
   store_sales
  ,store_returns
  ,store
  ,date_dim d1
  ,date_dim d2
where
    d2.d_year = 1999
and d2.d_moy  = 9
and ss_ticket_number = sr_ticket_number
and ss_item_sk = sr_item_sk
and ss_sold_date_sk   = d1.d_date_sk
and sr_returned_date_sk   = d2.d_date_sk
and ss_customer_sk = sr_customer_sk
and ss_store_sk = s_store_sk
group by
   s_store_name
  ,s_company_id
  ,s_street_number
  ,s_street_name
  ,s_street_type
  ,s_suite_number
  ,s_city
  ,s_county
  ,s_state
  ,s_zip
order by s_store_name
        ,s_company_id
        ,s_street_number
        ,s_street_name
        ,s_street_type
        ,s_suite_number
        ,s_city
        ,s_county
        ,s_state
        ,s_zip
limit 100;

# end query 50 in stream 0 using template query50.tpl
---- PLAN
Max Per-Host Resource Reservation: Memory=122.75MB Threads=1
Per-Host Resource Estimates: Memory=40.78GB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=40.78GB mem-reservation=122.75MB thread-reservation=1 runtime-filters-memory=36.00MB
|  max-parallelism=1 segment-costs=[9817718693, 7914, 1500]
PLAN-ROOT SINK
|  output exprs: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip, sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1 ELSE 0 END)
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=1500
|
10:TOP-N [LIMIT=100]
|  order by: s_store_name ASC, s_company_id ASC, s_street_number ASC, s_street_name ASC, s_street_type ASC, s_suite_number ASC, s_city ASC, s_county ASC, s_state ASC, s_zip ASC
|  mem-estimate=20.47KB mem-reservation=0B thread-reservation=0
|  tuple-ids=6 row-size=210B cardinality=100 cost=7914
|  in pipelines: 10(GETNEXT), 09(OPEN)
|
13:TUPLE CACHE
|  cache key: 4e006e223e242d59f6d022cb0bc98963
|  input scan node ids: 0,1,4,2,3
|  estimated serialized size: 281.58KB
|  estimated serialized size per node: 28.16KB
|  cumulative processing cost: 9817718693
|  cache read processing cost: 179
|  cache write processing cost: 778
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=1.35K cost=0
|  in pipelines: 09(GETNEXT)
|
09:AGGREGATE [FINALIZE]
|  output: sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(30 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(30 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(60 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(60 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(90 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(90 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(120 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(120 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT))
|  group by: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip
|  mem-estimate=644.46MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=1.35K cost=534861705
|  in pipelines: 09(GETNEXT), 00(OPEN)
|
08:HASH JOIN [INNER JOIN]
|  hash predicates: ss_sold_date_sk = d1.d_date_sk
|  fk/pk conjuncts: ss_sold_date_sk = d1.d_date_sk
|  runtime filters: RF000[bloom] <- d1.d_date_sk
|  mem-estimate=2.88MB mem-reservation=2.88MB spill-buffer=128.00KB thread-reservation=0
|  tuple-ids=0,1,4,2,3 row-size=234B cardinality=323.35M cost=141603967
|  in pipelines: 00(GETNEXT), 03(OPEN)
|
|--03: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
|     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=3 row-size=4B cardinality=73.05K cost=4207
|     in pipelines: 03(GETNEXT)
|
07:HASH JOIN [INNER JOIN]
|  hash predicates: ss_store_sk = s_store_sk
|  fk/pk conjuncts: ss_store_sk = s_store_sk
|  runtime filters: RF002[bloom] <- s_store_sk, RF003[min_max] <- s_store_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,4,2 row-size=230B cardinality=323.35M cost=141532268
|  in pipelines: 00(GETNEXT), 02(OPEN)
|
|--12:TUPLE CACHE
|  |  cache key: 1323aa5a12f8a6aa59c763a21ec6413b
|  |  input scan node ids: 2
|  |  estimated serialized size: 234.12KB
|  |  estimated serialized size per node: 234.12KB
|  |  cumulative processing cost: 1274
|  |  cache read processing cost: 179
|  |  cache write processing cost: 647
|  |  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  |  tuple-ids=2 row-size=174B cardinality=1.35K cost=0
|  |  in pipelines: 02(GETNEXT)
|  |
|  02:SCAN HDFS [tpcds_partitioned_parquet_snap.store]
|     HDFS partitions=1/1 files=1 size=119.76KB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     stored statistics:
|       table: rows=1.35K size=119.76KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=1.35K
|     mem-estimate=16.00MB mem-reservation=88.00KB thread-reservation=0
|     tuple-ids=2 row-size=174B cardinality=1.35K cost=1274
|     in pipelines: 02(GETNEXT)
|
06:HASH JOIN [INNER JOIN]
|  hash predicates: sr_returned_date_sk = d2.d_date_sk
|  fk/pk conjuncts: sr_returned_date_sk = d2.d_date_sk
|  runtime filters: RF004[bloom] <- d2.d_date_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,4 row-size=56B cardinality=323.35M cost=1597582491
|  in pipelines: 00(GETNEXT), 04(OPEN)
|
|--11:TUPLE CACHE
|  |  cache key: d11fd35c7b957f2a770b8c26800ce562
|  |  input scan node ids: 4
|  |  estimated serialized size: 1.69KB
|  |  estimated serialized size per node: 1.69KB
|  |  cumulative processing cost: 16728
|  |  cache read processing cost: 14
|  |  cache write processing cost: 4
|  |  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=108 cost=0
|  |  in pipelines: 04(GETNEXT)
|  |
|  04:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d2]
|     HDFS partitions=1/1 files=1 size=2.17MB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     stored statistics:
|       table: rows=73.05K size=2.17MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     parquet dictionary predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=4 row-size=12B cardinality=108 cost=16728
|     in pipelines: 04(GETNEXT)
|
05:HASH JOIN [INNER JOIN]
|  hash predicates: ss_customer_sk = sr_customer_sk, ss_item_sk = sr_item_sk, ss_ticket_number = sr_ticket_number
|  fk/pk conjuncts: ss_customer_sk = sr_customer_sk, ss_item_sk = sr_item_sk, ss_ticket_number = sr_ticket_number
|  runtime filters: RF006[bloom] <- sr_customer_sk, RF007[bloom] <- sr_item_sk, RF008[bloom] <- sr_ticket_number, RF009[min_max] <- sr_customer_sk, RF010[min_max] <- sr_item_sk, RF011[min_max] <- sr_ticket_number
|  mem-estimate=40.09GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=0,1 row-size=44B cardinality=6.00G cost=4167325725
|  in pipelines: 00(GETNEXT), 01(OPEN)
|
|--01:SCAN HDFS [tpcds_partitioned_parquet_snap.store_returns]
|     HDFS partitions=2004/2004 files=2004 size=48.14GB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     runtime filters: RF004[bloom] -> sr_returned_date_sk
|     stored statistics:
|       table: rows=863.99M size=48.14GB
|       partitions: 2004/2004 rows=863.99M
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=30.09M
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=1 row-size=20B cardinality=863.99M cost=248828726
|     in pipelines: 01(GETNEXT)
|
00:SCAN HDFS [tpcds_partitioned_parquet_snap.store_sales]
   HDFS partitions=1824/1824 files=1824 size=389.90GB
   deterministic scan range assignment: true
   schedule scan ranges oldest to newest: true
   runtime filters: RF003[min_max] -> ss_store_sk, RF009[min_max] -> ss_customer_sk, RF010[min_max] -> ss_item_sk, RF011[min_max] -> ss_ticket_number, RF000[bloom] -> ss_sold_date_sk, RF002[bloom] -> ss_store_sk, RF006[bloom] -> ss_customer_sk, RF007[bloom] -> ss_item_sk, RF008[bloom] -> ss_ticket_number
   stored statistics:
     table: rows=8.64G size=389.90GB
     partitions: 1824/1824 rows=8.64G
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=390.22M
   mem-estimate=16.00MB mem-reservation=8.00MB thread-reservation=0
   tuple-ids=0 row-size=24B cardinality=8.64G cost=2985961602
   in pipelines: 00(GETNEXT)
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=697.59MB Threads=42
Per-Host Resource Estimates: Memory=7.49GB
F07:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=4.21MB mem-reservation=4.00MB thread-reservation=1
|  max-parallelism=1 segment-costs=[1567] cpu-comparison-result=203 [max(1 (self) vs 203 (sum children))]
PLAN-ROOT SINK
|  output exprs: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip, sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1 ELSE 0 END)
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=1500
|
18:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: s_store_name ASC, s_company_id ASC, s_street_number ASC, s_street_name ASC, s_street_type ASC, s_suite_number ASC, s_city ASC, s_county ASC, s_state ASC, s_zip ASC
|  limit: 100
|  mem-estimate=210.63KB mem-reservation=0B thread-reservation=0
|  tuple-ids=6 row-size=210B cardinality=100 cost=67
|  in pipelines: 10(GETNEXT)
|
F06:PLAN FRAGMENT [HASH(s_store_name,s_company_id,s_street_number,s_street_name,s_street_type,s_suite_number,s_city,s_county,s_state,s_zip)] hosts=10 instances=10 (adjusted from 120)
Per-Instance Resources: mem-estimate=23.67MB mem-reservation=4.75MB thread-reservation=1
max-parallelism=10 segment-costs=[448257, 7914, 580] cpu-comparison-result=203 [max(10 (self) vs 203 (sum children))]
10:TOP-N [LIMIT=100]
|  order by: s_store_name ASC, s_company_id ASC, s_street_number ASC, s_street_name ASC, s_street_type ASC, s_suite_number ASC, s_city ASC, s_county ASC, s_state ASC, s_zip ASC
|  mem-estimate=20.47KB mem-reservation=0B thread-reservation=0
|  tuple-ids=6 row-size=210B cardinality=100 cost=7914
|  in pipelines: 10(GETNEXT), 17(OPEN)
|
17:AGGREGATE [FINALIZE]
|  output: sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1 ELSE 0 END)
|  group by: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip
|  mem-estimate=10.00MB mem-reservation=4.75MB spill-buffer=256.00KB thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=1.35K cost=274077
|  in pipelines: 17(GETNEXT), 00(OPEN)
|
16:EXCHANGE [HASH(s_store_name,s_company_id,s_street_number,s_street_name,s_street_type,s_suite_number,s_city,s_county,s_state,s_zip)]
|  mem-estimate=13.67MB mem-reservation=0B thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=162.00K cost=174180
|  in pipelines: 00(GETNEXT)
|
F02:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=50 (adjusted from 120)
Per-Instance Resources: mem-estimate=534.63MB mem-reservation=34.00MB thread-reservation=1
max-parallelism=770 segment-costs=[7633316166, 2270017] cpu-comparison-result=203 [max(170 (self) vs 203 (sum children))]
09:AGGREGATE [STREAMING]
|  output: sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(30 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(30 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(60 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(60 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(90 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(90 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(120 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(120 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT))
|  group by: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip
|  mem-estimate=513.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=162.00K cost=535589209
|  in pipelines: 00(GETNEXT)
|
08:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=00
|  hash predicates: ss_sold_date_sk = d1.d_date_sk
|  fk/pk conjuncts: ss_sold_date_sk = d1.d_date_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=128.00KB thread-reservation=0
|  tuple-ids=0,1,4,2,3 row-size=234B cardinality=323.35M cost=141530918
|  in pipelines: 00(GETNEXT), 03(OPEN)
|
|--F08:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=10
|  |  Per-Instance Resources: mem-estimate=15.66MB mem-reservation=15.38MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  max-parallelism=10 segment-costs=[170129]
|  JOIN BUILD
|  |  join-table-id=00 plan-id=01 cohort-id=01
|  |  build expressions: d1.d_date_sk
|  |  runtime filters: RF000[bloom] <- d1.d_date_sk
|  |  mem-estimate=14.38MB mem-reservation=14.38MB spill-buffer=128.00KB thread-reservation=0 cost=73049
|  |
|  15:EXCHANGE [BROADCAST]
|  |  mem-estimate=293.35KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=3 row-size=4B cardinality=73.05K cost=97080
|  |  in pipelines: 03(GETNEXT)
|  |
|  F05:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=16.03MB mem-reservation=512.00KB thread-reservation=1
|  max-parallelism=1 segment-costs=[6573]
|  03:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d1, 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=512.00KB thread-reservation=0
|     tuple-ids=3 row-size=4B cardinality=73.05K cost=4207
|     in pipelines: 03(GETNEXT)
|
07:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=01
|  hash predicates: ss_store_sk = s_store_sk
|  fk/pk conjuncts: ss_store_sk = s_store_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,4,2 row-size=230B cardinality=323.35M cost=141530918
|  in pipelines: 00(GETNEXT), 02(OPEN)
|
|--F09:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=10
|  |  Per-Instance Resources: mem-estimate=11.08MB mem-reservation=10.69MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  max-parallelism=10 segment-costs=[3140]
|  JOIN BUILD
|  |  join-table-id=01 plan-id=02 cohort-id=01
|  |  build expressions: s_store_sk
|  |  runtime filters: RF002[bloom] <- s_store_sk, RF003[min_max] <- s_store_sk
|  |  mem-estimate=9.69MB mem-reservation=9.69MB spill-buffer=64.00KB thread-reservation=0 cost=1350
|  |
|  14:EXCHANGE [BROADCAST]
|  |  mem-estimate=406.43KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=2 row-size=174B cardinality=1.35K cost=1790
|  |  in pipelines: 02(GETNEXT)
|  |
|  F04:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=20.69MB mem-reservation=4.09MB thread-reservation=1
|  max-parallelism=1 segment-costs=[1935]
|  20:TUPLE CACHE
|  |  cache key: 1323aa5a12f8a6aa59c763a21ec6413b
|  |  input scan node ids: 2
|  |  estimated serialized size: 234.12KB
|  |  estimated serialized size per node: 234.12KB
|  |  cumulative processing cost: 1274
|  |  cache read processing cost: 179
|  |  cache write processing cost: 647
|  |  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  |  tuple-ids=2 row-size=174B cardinality=1.35K cost=0
|  |  in pipelines: 02(GETNEXT)
|  |
|  02:SCAN HDFS [tpcds_partitioned_parquet_snap.store, RANDOM]
|     HDFS partitions=1/1 files=1 size=119.76KB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     stored statistics:
|       table: rows=1.35K size=119.76KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=1.35K
|     mem-estimate=16.00MB mem-reservation=88.00KB thread-reservation=0
|     tuple-ids=2 row-size=174B cardinality=1.35K cost=1274
|     in pipelines: 02(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=02
|  hash predicates: sr_returned_date_sk = d2.d_date_sk
|  fk/pk conjuncts: sr_returned_date_sk = d2.d_date_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,4 row-size=56B cardinality=323.35M cost=1597582383
|  in pipelines: 00(GETNEXT), 04(OPEN)
|
|--F10:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=10
|  |  Per-Instance Resources: mem-estimate=10.70MB mem-reservation=10.69MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  max-parallelism=10 segment-costs=[248]
|  JOIN BUILD
|  |  join-table-id=02 plan-id=03 cohort-id=01
|  |  build expressions: d2.d_date_sk
|  |  runtime filters: RF004[bloom] <- d2.d_date_sk
|  |  mem-estimate=9.69MB mem-reservation=9.69MB spill-buffer=64.00KB thread-reservation=0 cost=108
|  |
|  13:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=108 cost=140
|  |  in pipelines: 04(GETNEXT)
|  |
|  F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=20.06MB mem-reservation=4.50MB thread-reservation=1
|  max-parallelism=1 segment-costs=[16733]
|  19:TUPLE CACHE
|  |  cache key: d11fd35c7b957f2a770b8c26800ce562
|  |  input scan node ids: 4
|  |  estimated serialized size: 1.69KB
|  |  estimated serialized size per node: 1.69KB
|  |  cumulative processing cost: 16728
|  |  cache read processing cost: 14
|  |  cache write processing cost: 4
|  |  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=108 cost=0
|  |  in pipelines: 04(GETNEXT)
|  |
|  04:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d2, RANDOM]
|     HDFS partitions=1/1 files=1 size=2.17MB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     stored statistics:
|       table: rows=73.05K size=2.17MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     parquet dictionary predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=4 row-size=12B cardinality=108 cost=16728
|     in pipelines: 04(GETNEXT)
|
05:HASH JOIN [INNER JOIN, PARTITIONED]
|  hash-table-id=03
|  hash predicates: ss_customer_sk = sr_customer_sk, ss_item_sk = sr_item_sk, ss_ticket_number = sr_ticket_number
|  fk/pk conjuncts: ss_customer_sk = sr_customer_sk, ss_item_sk = sr_item_sk, ss_ticket_number = sr_ticket_number
|  mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=0,1 row-size=44B cardinality=6.00G cost=3303337093
|  in pipelines: 00(GETNEXT), 01(OPEN)
|
|--F11:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=50 (adjusted from 120)
|  |  Per-Instance Resources: mem-estimate=866.92MB mem-reservation=67.00MB thread-reservation=1 runtime-filters-memory=33.00MB
|  |  max-parallelism=770 segment-costs=[1039464723]
|  JOIN BUILD
|  |  join-table-id=03 plan-id=04 cohort-id=01
|  |  build expressions: sr_customer_sk, sr_item_sk, sr_ticket_number
|  |  runtime filters: RF006[bloom] <- sr_customer_sk, RF007[bloom] <- sr_item_sk, RF008[bloom] <- sr_ticket_number, RF009[min_max] <- sr_customer_sk, RF010[min_max] <- sr_item_sk, RF011[min_max] <- sr_ticket_number
|  |  mem-estimate=821.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=863988632
|  |
|  12:EXCHANGE [HASH(sr_customer_sk,sr_item_sk,sr_ticket_number)]
|  |  mem-estimate=12.81MB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=1 row-size=20B cardinality=863.99M cost=175476091
|  |  in pipelines: 01(GETNEXT)
|  |
|  F01: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=20.69MB mem-reservation=512.00KB thread-reservation=1
|  max-parallelism=190 segment-costs=[1806773027]
|  01:SCAN HDFS [tpcds_partitioned_parquet_snap.store_returns, RANDOM]
|     HDFS partitions=2004/2004 files=2004 size=48.14GB
|     runtime filters: RF004[bloom] -> sr_returned_date_sk
|     stored statistics:
|       table: rows=863.99M size=48.14GB
|       partitions: 2004/2004 rows=863.99M
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=30.09M
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=1 row-size=20B cardinality=863.99M cost=248828726
|     in pipelines: 01(GETNEXT)
|
11:EXCHANGE [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)]
|  mem-estimate=13.28MB mem-reservation=0B thread-reservation=0
|  tuple-ids=0 row-size=24B cardinality=8.64G cost=1913745645
|  in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=120
Per-Host Shared Resources: mem-estimate=35.00MB mem-reservation=35.00MB thread-reservation=0 runtime-filters-memory=35.00MB
Per-Instance Resources: mem-estimate=21.47MB mem-reservation=8.00MB thread-reservation=1
max-parallelism=1824 segment-costs=[20791140047]
00:SCAN HDFS [tpcds_partitioned_parquet_snap.store_sales, RANDOM]
   HDFS partitions=1824/1824 files=1824 size=389.90GB
   runtime filters: RF003[min_max] -> ss_store_sk, RF009[min_max] -> ss_customer_sk, RF010[min_max] -> ss_item_sk, RF011[min_max] -> ss_ticket_number, RF000[bloom] -> ss_sold_date_sk, RF002[bloom] -> ss_store_sk, RF006[bloom] -> ss_customer_sk, RF007[bloom] -> ss_item_sk, RF008[bloom] -> ss_ticket_number
   stored statistics:
     table: rows=8.64G size=389.90GB
     partitions: 1824/1824 rows=8.64G
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=390.22M
   mem-estimate=16.00MB mem-reservation=8.00MB thread-reservation=0
   tuple-ids=0 row-size=24B cardinality=8.64G cost=2985961602
   in pipelines: 00(GETNEXT)
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=697.59MB Threads=42
Per-Host Resource Estimates: Memory=7.49GB
F07:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=4.21MB mem-reservation=4.00MB thread-reservation=1
|  max-parallelism=1 segment-costs=[1567] cpu-comparison-result=203 [max(1 (self) vs 203 (sum children))]
PLAN-ROOT SINK
|  output exprs: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip, sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1 ELSE 0 END), sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1 ELSE 0 END)
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=1500
|
18:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: s_store_name ASC, s_company_id ASC, s_street_number ASC, s_street_name ASC, s_street_type ASC, s_suite_number ASC, s_city ASC, s_county ASC, s_state ASC, s_zip ASC
|  limit: 100
|  mem-estimate=210.63KB mem-reservation=0B thread-reservation=0
|  tuple-ids=6 row-size=210B cardinality=100 cost=67
|  in pipelines: 10(GETNEXT)
|
F06:PLAN FRAGMENT [HASH(s_store_name,s_company_id,s_street_number,s_street_name,s_street_type,s_suite_number,s_city,s_county,s_state,s_zip)] hosts=10 instances=10 (adjusted from 120)
Per-Instance Resources: mem-estimate=23.67MB mem-reservation=4.75MB thread-reservation=1
max-parallelism=10 segment-costs=[448257, 7914, 580] cpu-comparison-result=203 [max(10 (self) vs 203 (sum children))]
10:TOP-N [LIMIT=100]
|  order by: s_store_name ASC, s_company_id ASC, s_street_number ASC, s_street_name ASC, s_street_type ASC, s_suite_number ASC, s_city ASC, s_county ASC, s_state ASC, s_zip ASC
|  mem-estimate=20.47KB mem-reservation=0B thread-reservation=0
|  tuple-ids=6 row-size=210B cardinality=100 cost=7914
|  in pipelines: 10(GETNEXT), 17(OPEN)
|
17:AGGREGATE [FINALIZE]
|  output: sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1 ELSE 0 END), sum:merge(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1 ELSE 0 END)
|  group by: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip
|  mem-estimate=10.00MB mem-reservation=4.75MB spill-buffer=256.00KB thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=1.35K cost=274077
|  in pipelines: 17(GETNEXT), 00(OPEN)
|
16:EXCHANGE [HASH(s_store_name,s_company_id,s_street_number,s_street_name,s_street_type,s_suite_number,s_city,s_county,s_state,s_zip)]
|  mem-estimate=13.67MB mem-reservation=0B thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=162.00K cost=174180
|  in pipelines: 00(GETNEXT)
|
F02:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=50 (adjusted from 120)
Per-Instance Resources: mem-estimate=534.63MB mem-reservation=34.00MB thread-reservation=1
max-parallelism=770 segment-costs=[7633316166, 2270017] cpu-comparison-result=203 [max(170 (self) vs 203 (sum children))]
09:AGGREGATE [STREAMING]
|  output: sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(30 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(30 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(60 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(60 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(90 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(90 AS BIGINT)) AND (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) <= CAST(120 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT)), sum(CAST(CASE WHEN (CAST(sr_returned_date_sk AS BIGINT) - CAST(ss_sold_date_sk AS BIGINT) > CAST(120 AS BIGINT)) THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS BIGINT))
|  group by: s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip
|  mem-estimate=513.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=5 row-size=210B cardinality=162.00K cost=535589209
|  in pipelines: 00(GETNEXT)
|
08:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=00
|  hash predicates: ss_sold_date_sk = d1.d_date_sk
|  fk/pk conjuncts: ss_sold_date_sk = d1.d_date_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=128.00KB thread-reservation=0
|  tuple-ids=0,1,4,2,3 row-size=234B cardinality=323.35M cost=141530918
|  in pipelines: 00(GETNEXT), 03(OPEN)
|
|--F08:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=10
|  |  Per-Instance Resources: mem-estimate=15.66MB mem-reservation=15.38MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  max-parallelism=10 segment-costs=[170129]
|  JOIN BUILD
|  |  join-table-id=00 plan-id=01 cohort-id=01
|  |  build expressions: d1.d_date_sk
|  |  runtime filters: RF000[bloom] <- d1.d_date_sk
|  |  mem-estimate=14.38MB mem-reservation=14.38MB spill-buffer=128.00KB thread-reservation=0 cost=73049
|  |
|  15:EXCHANGE [BROADCAST]
|  |  mem-estimate=293.35KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=3 row-size=4B cardinality=73.05K cost=97080
|  |  in pipelines: 03(GETNEXT)
|  |
|  F05:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=16.03MB mem-reservation=512.00KB thread-reservation=1
|  max-parallelism=1 segment-costs=[6573]
|  03:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d1, 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=512.00KB thread-reservation=0
|     tuple-ids=3 row-size=4B cardinality=73.05K cost=4207
|     in pipelines: 03(GETNEXT)
|
07:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=01
|  hash predicates: ss_store_sk = s_store_sk
|  fk/pk conjuncts: ss_store_sk = s_store_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,4,2 row-size=230B cardinality=323.35M cost=141530918
|  in pipelines: 00(GETNEXT), 02(OPEN)
|
|--F09:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=10
|  |  Per-Instance Resources: mem-estimate=11.08MB mem-reservation=10.69MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  max-parallelism=10 segment-costs=[3140]
|  JOIN BUILD
|  |  join-table-id=01 plan-id=02 cohort-id=01
|  |  build expressions: s_store_sk
|  |  runtime filters: RF002[bloom] <- s_store_sk, RF003[min_max] <- s_store_sk
|  |  mem-estimate=9.69MB mem-reservation=9.69MB spill-buffer=64.00KB thread-reservation=0 cost=1350
|  |
|  14:EXCHANGE [BROADCAST]
|  |  mem-estimate=406.43KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=2 row-size=174B cardinality=1.35K cost=1790
|  |  in pipelines: 02(GETNEXT)
|  |
|  F04:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=20.69MB mem-reservation=4.09MB thread-reservation=1
|  max-parallelism=1 segment-costs=[1935]
|  20:TUPLE CACHE
|  |  cache key: 1323aa5a12f8a6aa59c763a21ec6413b
|  |  input scan node ids: 2
|  |  estimated serialized size: 234.12KB
|  |  estimated serialized size per node: 234.12KB
|  |  cumulative processing cost: 1274
|  |  cache read processing cost: 179
|  |  cache write processing cost: 647
|  |  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  |  tuple-ids=2 row-size=174B cardinality=1.35K cost=0
|  |  in pipelines: 02(GETNEXT)
|  |
|  02:SCAN HDFS [tpcds_partitioned_parquet_snap.store, RANDOM]
|     HDFS partitions=1/1 files=1 size=119.76KB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     stored statistics:
|       table: rows=1.35K size=119.76KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=1.35K
|     mem-estimate=16.00MB mem-reservation=88.00KB thread-reservation=0
|     tuple-ids=2 row-size=174B cardinality=1.35K cost=1274
|     in pipelines: 02(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=02
|  hash predicates: sr_returned_date_sk = d2.d_date_sk
|  fk/pk conjuncts: sr_returned_date_sk = d2.d_date_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,4 row-size=56B cardinality=323.35M cost=1597582383
|  in pipelines: 00(GETNEXT), 04(OPEN)
|
|--F10:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=10
|  |  Per-Instance Resources: mem-estimate=10.70MB mem-reservation=10.69MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  max-parallelism=10 segment-costs=[248]
|  JOIN BUILD
|  |  join-table-id=02 plan-id=03 cohort-id=01
|  |  build expressions: d2.d_date_sk
|  |  runtime filters: RF004[bloom] <- d2.d_date_sk
|  |  mem-estimate=9.69MB mem-reservation=9.69MB spill-buffer=64.00KB thread-reservation=0 cost=108
|  |
|  13:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=108 cost=140
|  |  in pipelines: 04(GETNEXT)
|  |
|  F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=20.06MB mem-reservation=4.50MB thread-reservation=1
|  max-parallelism=1 segment-costs=[16733]
|  19:TUPLE CACHE
|  |  cache key: d11fd35c7b957f2a770b8c26800ce562
|  |  input scan node ids: 4
|  |  estimated serialized size: 1.69KB
|  |  estimated serialized size per node: 1.69KB
|  |  cumulative processing cost: 16728
|  |  cache read processing cost: 14
|  |  cache write processing cost: 4
|  |  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=108 cost=0
|  |  in pipelines: 04(GETNEXT)
|  |
|  04:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim d2, RANDOM]
|     HDFS partitions=1/1 files=1 size=2.17MB
|     deterministic scan range assignment: true
|     schedule scan ranges oldest to newest: true
|     predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     stored statistics:
|       table: rows=73.05K size=2.17MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     parquet dictionary predicates: d2.d_year = CAST(1999 AS INT), d2.d_moy = CAST(9 AS INT)
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=4 row-size=12B cardinality=108 cost=16728
|     in pipelines: 04(GETNEXT)
|
05:HASH JOIN [INNER JOIN, PARTITIONED]
|  hash-table-id=03
|  hash predicates: ss_customer_sk = sr_customer_sk, ss_item_sk = sr_item_sk, ss_ticket_number = sr_ticket_number
|  fk/pk conjuncts: ss_customer_sk = sr_customer_sk, ss_item_sk = sr_item_sk, ss_ticket_number = sr_ticket_number
|  mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=0,1 row-size=44B cardinality=6.00G cost=3303337093
|  in pipelines: 00(GETNEXT), 01(OPEN)
|
|--F11:PLAN FRAGMENT [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)] hosts=10 instances=50 (adjusted from 120)
|  |  Per-Instance Resources: mem-estimate=866.92MB mem-reservation=67.00MB thread-reservation=1 runtime-filters-memory=33.00MB
|  |  max-parallelism=770 segment-costs=[1039464723]
|  JOIN BUILD
|  |  join-table-id=03 plan-id=04 cohort-id=01
|  |  build expressions: sr_customer_sk, sr_item_sk, sr_ticket_number
|  |  runtime filters: RF006[bloom] <- sr_customer_sk, RF007[bloom] <- sr_item_sk, RF008[bloom] <- sr_ticket_number, RF009[min_max] <- sr_customer_sk, RF010[min_max] <- sr_item_sk, RF011[min_max] <- sr_ticket_number
|  |  mem-estimate=821.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=863988632
|  |
|  12:EXCHANGE [HASH(sr_customer_sk,sr_item_sk,sr_ticket_number)]
|  |  mem-estimate=12.81MB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=1 row-size=20B cardinality=863.99M cost=175476091
|  |  in pipelines: 01(GETNEXT)
|  |
|  F01: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=20.69MB mem-reservation=512.00KB thread-reservation=1
|  max-parallelism=190 segment-costs=[1806773027]
|  01:SCAN HDFS [tpcds_partitioned_parquet_snap.store_returns, RANDOM]
|     HDFS partitions=2004/2004 files=2004 size=48.14GB
|     runtime filters: RF004[bloom] -> sr_returned_date_sk
|     stored statistics:
|       table: rows=863.99M size=48.14GB
|       partitions: 2004/2004 rows=863.99M
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=30.09M
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=1 row-size=20B cardinality=863.99M cost=248828726
|     in pipelines: 01(GETNEXT)
|
11:EXCHANGE [HASH(ss_customer_sk,ss_item_sk,ss_ticket_number)]
|  mem-estimate=13.28MB mem-reservation=0B thread-reservation=0
|  tuple-ids=0 row-size=24B cardinality=8.64G cost=1913745645
|  in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=120
Per-Host Shared Resources: mem-estimate=35.00MB mem-reservation=35.00MB thread-reservation=0 runtime-filters-memory=35.00MB
Per-Instance Resources: mem-estimate=21.47MB mem-reservation=8.00MB thread-reservation=1
max-parallelism=1824 segment-costs=[20791140047]
00:SCAN HDFS [tpcds_partitioned_parquet_snap.store_sales, RANDOM]
   HDFS partitions=1824/1824 files=1824 size=389.90GB
   runtime filters: RF003[min_max] -> ss_store_sk, RF009[min_max] -> ss_customer_sk, RF010[min_max] -> ss_item_sk, RF011[min_max] -> ss_ticket_number, RF000[bloom] -> ss_sold_date_sk, RF002[bloom] -> ss_store_sk, RF006[bloom] -> ss_customer_sk, RF007[bloom] -> ss_item_sk, RF008[bloom] -> ss_ticket_number
   stored statistics:
     table: rows=8.64G size=389.90GB
     partitions: 1824/1824 rows=8.64G
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=390.22M
   mem-estimate=16.00MB mem-reservation=8.00MB thread-reservation=0
   tuple-ids=0 row-size=24B cardinality=8.64G cost=2985961602
   in pipelines: 00(GETNEXT)
====