testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q05.test

# TPCDS-Q5
with ssr as
 (select s_store_id,
        sum(sales_price) as sales,
        sum(profit) as profit,
        sum(return_amt) as `returns`,
        sum(net_loss) as profit_loss
 from
  ( select  ss_store_sk as store_sk,
            ss_sold_date_sk  as date_sk,
            ss_ext_sales_price as sales_price,
            ss_net_profit as profit,
            cast(0 as decimal(7,2)) as return_amt,
            cast(0 as decimal(7,2)) as net_loss
    from store_sales
    union all
    select sr_store_sk as store_sk,
           sr_returned_date_sk as date_sk,
           cast(0 as decimal(7,2)) as sales_price,
           cast(0 as decimal(7,2)) as profit,
           sr_return_amt as return_amt,
           sr_net_loss as net_loss
    from store_returns
   ) salesreturns,
     date_dim,
     store
 where date_sk = d_date_sk
       and d_date between cast('1998-08-04' as date)
                  and (cast('1998-08-04' as date) +  INTERVAL 14 days)
       and store_sk = s_store_sk
 group by s_store_id)
 ,
 csr as
 (select cp_catalog_page_id,
        sum(sales_price) as sales,
        sum(profit) as profit,
        sum(return_amt) as `returns`,
        sum(net_loss) as profit_loss
 from
  ( select  cs_catalog_page_sk as page_sk,
            cs_sold_date_sk  as date_sk,
            cs_ext_sales_price as sales_price,
            cs_net_profit as profit,
            cast(0 as decimal(7,2)) as return_amt,
            cast(0 as decimal(7,2)) as net_loss
    from catalog_sales
    union all
    select cr_catalog_page_sk as page_sk,
           cr_returned_date_sk as date_sk,
           cast(0 as decimal(7,2)) as sales_price,
           cast(0 as decimal(7,2)) as profit,
           cr_return_amount as return_amt,
           cr_net_loss as net_loss
    from catalog_returns
   ) salesreturns,
     date_dim,
     catalog_page
 where date_sk = d_date_sk
       and d_date between cast('1998-08-04' as date)
                  and (cast('1998-08-04' as date) +  INTERVAL 14 days)
       and page_sk = cp_catalog_page_sk
 group by cp_catalog_page_id)
 ,
 wsr as
 (select web_site_id,
        sum(sales_price) as sales,
        sum(profit) as profit,
        sum(return_amt) as `returns`,
        sum(net_loss) as profit_loss
 from
  ( select  ws_web_site_sk as wsr_web_site_sk,
            ws_sold_date_sk  as date_sk,
            ws_ext_sales_price as sales_price,
            ws_net_profit as profit,
            cast(0 as decimal(7,2)) as return_amt,
            cast(0 as decimal(7,2)) as net_loss
    from web_sales
    union all
    select ws_web_site_sk as wsr_web_site_sk,
           wr_returned_date_sk as date_sk,
           cast(0 as decimal(7,2)) as sales_price,
           cast(0 as decimal(7,2)) as profit,
           wr_return_amt as return_amt,
           wr_net_loss as net_loss
    from web_returns left outer join web_sales on
         ( wr_item_sk = ws_item_sk
           and wr_order_number = ws_order_number)
   ) salesreturns,
     date_dim,
     web_site
 where date_sk = d_date_sk
       and d_date between cast('1998-08-04' as date)
                  and (cast('1998-08-04' as date) +  INTERVAL 14 days)
       and wsr_web_site_sk = web_site_sk
 group by web_site_id)
  select  channel
        , id
        , sum(sales) as sales
        , sum(`returns`) as `returns`
        , sum(profit) as profit
 from
 (select 'store channel' as channel
        , 'store' || s_store_id as id
        , sales
        , `returns`
        , (profit - profit_loss) as profit
 from   ssr
 union all
 select 'catalog channel' as channel
        , 'catalog_page' || cp_catalog_page_id as id
        , sales
        , `returns`
        , (profit - profit_loss) as profit
 from  csr
 union all
 select 'web channel' as channel
        , 'web_site' || web_site_id as id
        , sales
        , `returns`
        , (profit - profit_loss) as profit
 from   wsr
 ) x
 group by rollup (channel, id)
 order by channel
         ,id
 limit 100;
---- PLAN
Max Per-Host Resource Reservation: Memory=31.38MB Threads=5
Per-Host Resource Estimates: Memory=298MB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=297.88MB mem-reservation=31.38MB thread-reservation=5 runtime-filters-memory=8.00MB
PLAN-ROOT SINK
|  output exprs: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END, aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(sales) WHEN 28 THEN sum(sales) WHEN 29 THEN sum(sales) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(`returns`) WHEN 28 THEN sum(`returns`) WHEN 29 THEN sum(`returns`) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(profit) WHEN 28 THEN sum(profit) WHEN 29 THEN sum(profit) END)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
29:TOP-N [LIMIT=100]
|  order by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END ASC, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END ASC
|  mem-estimate=7.03KB mem-reservation=0B thread-reservation=0
|  tuple-ids=31 row-size=72B cardinality=100
|  in pipelines: 29(GETNEXT), 28(OPEN)
|
28:AGGREGATE [FINALIZE]
|  output: aggif(valid_tid(27,28,29) IN (CAST(27 AS INT), CAST(28 AS INT), CAST(29 AS INT)), CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN sum(sales) WHEN CAST(28 AS INT) THEN sum(sales) WHEN CAST(29 AS INT) THEN sum(sales) END), aggif(valid_tid(27,28,29) IN (CAST(27 AS INT), CAST(28 AS INT), CAST(29 AS INT)), CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN sum(`returns`) WHEN CAST(28 AS INT) THEN sum(`returns`) WHEN CAST(29 AS INT) THEN sum(`returns`) END), aggif(valid_tid(27,28,29) IN (CAST(27 AS INT), CAST(28 AS INT), CAST(29 AS INT)), CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN sum(profit) WHEN CAST(28 AS INT) THEN sum(profit) WHEN CAST(29 AS INT) THEN sum(profit) END)
|  group by: CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN channel WHEN CAST(28 AS INT) THEN channel WHEN CAST(29 AS INT) THEN NULL END, CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN id WHEN CAST(28 AS INT) THEN NULL WHEN CAST(29 AS INT) THEN NULL END, CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN CAST(27 AS INT) WHEN CAST(28 AS INT) THEN CAST(28 AS INT) WHEN CAST(29 AS INT) THEN CAST(29 AS INT) END
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=30 row-size=76B cardinality=11.56K
|  in pipelines: 28(GETNEXT), 27(OPEN)
|
27:AGGREGATE [FINALIZE]
|  Class 0
|    output: sum(sales), sum(returns), sum(profit)
|    group by: channel, id
|  Class 1
|    output: sum(sales), sum(returns), sum(profit)
|    group by: channel, NULL
|  Class 2
|    output: sum(sales), sum(returns), sum(profit)
|    group by: NULL, NULL
|  mem-estimate=30.00MB mem-reservation=8.62MB thread-reservation=0
|  tuple-ids=27N,28N,29N row-size=216B cardinality=11.56K
|  in pipelines: 27(GETNEXT), 08(OPEN), 16(OPEN), 26(OPEN)
|
00:UNION
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=25 row-size=72B cardinality=11.56K
|  in pipelines: 08(GETNEXT), 16(GETNEXT), 26(GETNEXT)
|
|--26:AGGREGATE [FINALIZE]
|  |  output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
|  |  group by: web_site_id
|  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=23 row-size=92B cardinality=15
|  |  in pipelines: 26(GETNEXT), 18(OPEN), 20(OPEN)
|  |
|  25:HASH JOIN [INNER JOIN]
|  |  hash predicates: wsr_web_site_sk = web_site_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF008[bloom] <- web_site_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=19,21,22 row-size=82B cardinality=791.15K
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT), 23(OPEN)
|  |
|  |--23:SCAN HDFS [tpcds_parquet.web_site]
|  |     HDFS partitions=1/1 files=1 size=11.91KB
|  |     stored statistics:
|  |       table: rows=30 size=11.91KB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=30
|  |     mem-estimate=32.00MB mem-reservation=16.00KB thread-reservation=1
|  |     tuple-ids=22 row-size=32B cardinality=30
|  |     in pipelines: 23(GETNEXT)
|  |
|  24:HASH JOIN [INNER JOIN]
|  |  hash predicates: date_sk = d_date_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF010[bloom] <- d_date_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=19,21 row-size=50B cardinality=791.15K
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT), 22(OPEN)
|  |
|  |--22:SCAN HDFS [tpcds_parquet.date_dim]
|  |     HDFS partitions=1/1 files=1 size=2.15MB
|  |     predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     stored statistics:
|  |       table: rows=73.05K size=2.15MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=73.05K
|  |     parquet statistics predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     parquet dictionary predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=1
|  |     tuple-ids=21 row-size=26B cardinality=7.30K
|  |     in pipelines: 22(GETNEXT)
|  |
|  17:UNION
|  |  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  |  tuple-ids=19 row-size=24B cardinality=791.15K
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT)
|  |
|  |--21:HASH JOIN [RIGHT OUTER JOIN]
|  |  |  hash predicates: ws_item_sk = wr_item_sk, ws_order_number = wr_order_number
|  |  |  fk/pk conjuncts: ws_item_sk = wr_item_sk, ws_order_number = wr_order_number
|  |  |  runtime filters: RF012[bloom] <- wr_item_sk, RF013[bloom] <- wr_order_number
|  |  |  mem-estimate=2.88MB mem-reservation=2.88MB spill-buffer=128.00KB thread-reservation=0
|  |  |  tuple-ids=18N,17 row-size=48B cardinality=71.76K
|  |  |  in pipelines: 20(GETNEXT), 19(OPEN)
|  |  |
|  |  |--19:SCAN HDFS [tpcds_parquet.web_returns]
|  |  |     HDFS partitions=1/1 files=1 size=5.66MB
|  |  |     runtime filters: RF010[bloom] -> tpcds_parquet.web_returns.wr_returned_date_sk
|  |  |     stored statistics:
|  |  |       table: rows=71.76K size=5.66MB
|  |  |       columns: all
|  |  |     extrapolated-rows=disabled max-scan-range-rows=71.76K
|  |  |     mem-estimate=80.00MB mem-reservation=2.00MB thread-reservation=1
|  |  |     tuple-ids=17 row-size=28B cardinality=71.76K
|  |  |     in pipelines: 19(GETNEXT)
|  |  |
|  |  20:SCAN HDFS [tpcds_parquet.web_sales]
|  |     HDFS partitions=1/1 files=2 size=45.09MB
|  |     runtime filters: RF008[bloom] -> tpcds_parquet.web_sales.ws_web_site_sk, RF012[bloom] -> ws_item_sk, RF013[bloom] -> ws_order_number
|  |     stored statistics:
|  |       table: rows=719.38K size=45.09MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=644.77K
|  |     mem-estimate=96.00MB mem-reservation=4.00MB thread-reservation=1
|  |     tuple-ids=18 row-size=20B cardinality=719.38K
|  |     in pipelines: 20(GETNEXT)
|  |
|  18:SCAN HDFS [tpcds_parquet.web_sales]
|     HDFS partitions=1/1 files=2 size=45.09MB
|     runtime filters: RF008[bloom] -> tpcds_parquet.web_sales.ws_web_site_sk, RF010[bloom] -> tpcds_parquet.web_sales.ws_sold_date_sk
|     stored statistics:
|       table: rows=719.38K size=45.09MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=644.77K
|     mem-estimate=128.00MB mem-reservation=8.00MB thread-reservation=1
|     tuple-ids=16 row-size=16B cardinality=719.38K
|     in pipelines: 18(GETNEXT)
|
|--16:AGGREGATE [FINALIZE]
|  |  output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
|  |  group by: cp_catalog_page_id
|  |  mem-estimate=10.00MB mem-reservation=2.88MB spill-buffer=128.00KB thread-reservation=0
|  |  tuple-ids=14 row-size=92B cardinality=11.54K
|  |  in pipelines: 16(GETNEXT), 10(OPEN), 11(OPEN)
|  |
|  15:HASH JOIN [INNER JOIN]
|  |  hash predicates: page_sk = cp_catalog_page_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF004[bloom] <- cp_catalog_page_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=10,12,13 row-size=82B cardinality=1.59M
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT), 13(OPEN)
|  |
|  |--13:SCAN HDFS [tpcds_parquet.catalog_page]
|  |     HDFS partitions=1/1 files=1 size=739.17KB
|  |     stored statistics:
|  |       table: rows=11.72K size=739.17KB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=11.72K
|  |     mem-estimate=32.00MB mem-reservation=256.00KB thread-reservation=1
|  |     tuple-ids=13 row-size=32B cardinality=11.72K
|  |     in pipelines: 13(GETNEXT)
|  |
|  14:HASH JOIN [INNER JOIN]
|  |  hash predicates: date_sk = d_date_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF006[bloom] <- d_date_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=10,12 row-size=50B cardinality=1.59M
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT), 12(OPEN)
|  |
|  |--12:SCAN HDFS [tpcds_parquet.date_dim]
|  |     HDFS partitions=1/1 files=1 size=2.15MB
|  |     predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     stored statistics:
|  |       table: rows=73.05K size=2.15MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=73.05K
|  |     parquet statistics predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     parquet dictionary predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=1
|  |     tuple-ids=12 row-size=26B cardinality=7.30K
|  |     in pipelines: 12(GETNEXT)
|  |
|  09:UNION
|  |  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  |  tuple-ids=10 row-size=24B cardinality=1.59M
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT)
|  |
|  |--11:SCAN HDFS [tpcds_parquet.catalog_returns]
|  |     HDFS partitions=1/1 files=1 size=10.62MB
|  |     runtime filters: RF004[bloom] -> tpcds_parquet.catalog_returns.cr_catalog_page_sk, RF006[bloom] -> tpcds_parquet.catalog_returns.cr_returned_date_sk
|  |     stored statistics:
|  |       table: rows=144.07K size=10.62MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=144.07K
|  |     mem-estimate=96.00MB mem-reservation=2.00MB thread-reservation=1
|  |     tuple-ids=9 row-size=16B cardinality=144.07K
|  |     in pipelines: 11(GETNEXT)
|  |
|  10:SCAN HDFS [tpcds_parquet.catalog_sales]
|     HDFS partitions=1/1 files=3 size=96.62MB
|     runtime filters: RF004[bloom] -> tpcds_parquet.catalog_sales.cs_catalog_page_sk, RF006[bloom] -> tpcds_parquet.catalog_sales.cs_sold_date_sk
|     stored statistics:
|       table: rows=1.44M size=96.62MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=650.14K
|     mem-estimate=192.00MB mem-reservation=8.00MB thread-reservation=1
|     tuple-ids=8 row-size=16B cardinality=1.44M
|     in pipelines: 10(GETNEXT)
|
08:AGGREGATE [FINALIZE]
|  output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
|  group by: s_store_id
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=6 row-size=92B cardinality=6
|  in pipelines: 08(GETNEXT), 02(OPEN), 03(OPEN)
|
07:HASH JOIN [INNER JOIN]
|  hash predicates: store_sk = s_store_sk
|  fk/pk conjuncts: assumed fk/pk
|  runtime filters: RF000[bloom] <- s_store_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=2,4,5 row-size=82B cardinality=3.17M
|  in pipelines: 02(GETNEXT), 03(GETNEXT), 05(OPEN)
|
|--05:SCAN HDFS [tpcds_parquet.store]
|     HDFS partitions=1/1 files=1 size=9.93KB
|     stored statistics:
|       table: rows=12 size=9.93KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=12
|     mem-estimate=32.00MB mem-reservation=16.00KB thread-reservation=1
|     tuple-ids=5 row-size=32B cardinality=12
|     in pipelines: 05(GETNEXT)
|
06:HASH JOIN [INNER JOIN]
|  hash predicates: date_sk = d_date_sk
|  fk/pk conjuncts: assumed fk/pk
|  runtime filters: RF002[bloom] <- d_date_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=2,4 row-size=50B cardinality=3.17M
|  in pipelines: 02(GETNEXT), 03(GETNEXT), 04(OPEN)
|
|--04:SCAN HDFS [tpcds_parquet.date_dim]
|     HDFS partitions=1/1 files=1 size=2.15MB
|     predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|     stored statistics:
|       table: rows=73.05K size=2.15MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|     parquet dictionary predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|     mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=1
|     tuple-ids=4 row-size=26B cardinality=7.30K
|     in pipelines: 04(GETNEXT)
|
01:UNION
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=24B cardinality=3.17M
|  in pipelines: 02(GETNEXT), 03(GETNEXT)
|
|--03:SCAN HDFS [tpcds_parquet.store_returns]
|     HDFS partitions=1/1 files=1 size=15.43MB
|     runtime filters: RF000[bloom] -> tpcds_parquet.store_returns.sr_store_sk, RF002[bloom] -> tpcds_parquet.store_returns.sr_returned_date_sk
|     stored statistics:
|       table: rows=287.51K size=15.43MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=287.51K
|     mem-estimate=96.00MB mem-reservation=4.00MB thread-reservation=1
|     tuple-ids=1 row-size=16B cardinality=287.51K
|     in pipelines: 03(GETNEXT)
|
02:SCAN HDFS [tpcds_parquet.store_sales]
   HDFS partitions=1824/1824 files=1824 size=200.95MB
   runtime filters: RF000[bloom] -> tpcds_parquet.store_sales.ss_store_sk, RF002[bloom] -> tpcds_parquet.store_sales.ss_sold_date_sk
   stored statistics:
     table: rows=2.88M size=200.95MB
     partitions: 1824/1824 rows=2.88M
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=130.09K
   mem-estimate=48.00MB mem-reservation=2.00MB thread-reservation=1
   tuple-ids=0 row-size=16B cardinality=2.88M
   in pipelines: 02(GETNEXT)
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=77.53MB Threads=25
Per-Host Resource Estimates: Memory=919MB
F22:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=24.61KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END, aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(sales) WHEN 28 THEN sum(sales) WHEN 29 THEN sum(sales) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(`returns`) WHEN 28 THEN sum(`returns`) WHEN 29 THEN sum(`returns`) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(profit) WHEN 28 THEN sum(profit) WHEN 29 THEN sum(profit) END)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
46:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END ASC, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END ASC
|  limit: 100
|  mem-estimate=24.61KB mem-reservation=0B thread-reservation=0
|  tuple-ids=31 row-size=72B cardinality=100
|  in pipelines: 29(GETNEXT)
|
F21:PLAN FRAGMENT [HASH(CASE valid_tid(27,28,29) WHEN 27 THEN murmur_hash(channel) WHEN 28 THEN murmur_hash(channel) WHEN 29 THEN murmur_hash(NULL) END,CASE valid_tid(27,28,29) WHEN 27 THEN murmur_hash(id) WHEN 28 THEN murmur_hash(NULL) WHEN 29 THEN murmur_hash(NULL) END)] hosts=3 instances=3
Per-Host Resources: mem-estimate=40.00MB mem-reservation=8.69MB thread-reservation=1
29:TOP-N [LIMIT=100]
|  order by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END ASC, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END ASC
|  mem-estimate=7.03KB mem-reservation=0B thread-reservation=0
|  tuple-ids=31 row-size=72B cardinality=100
|  in pipelines: 29(GETNEXT), 28(OPEN)
|
28:AGGREGATE [FINALIZE]
|  output: aggif(valid_tid(27,28,29) IN (CAST(27 AS INT), CAST(28 AS INT), CAST(29 AS INT)), CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN sum(sales) WHEN CAST(28 AS INT) THEN sum(sales) WHEN CAST(29 AS INT) THEN sum(sales) END), aggif(valid_tid(27,28,29) IN (CAST(27 AS INT), CAST(28 AS INT), CAST(29 AS INT)), CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN sum(`returns`) WHEN CAST(28 AS INT) THEN sum(`returns`) WHEN CAST(29 AS INT) THEN sum(`returns`) END), aggif(valid_tid(27,28,29) IN (CAST(27 AS INT), CAST(28 AS INT), CAST(29 AS INT)), CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN sum(profit) WHEN CAST(28 AS INT) THEN sum(profit) WHEN CAST(29 AS INT) THEN sum(profit) END)
|  group by: CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN channel WHEN CAST(28 AS INT) THEN channel WHEN CAST(29 AS INT) THEN NULL END, CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN id WHEN CAST(28 AS INT) THEN NULL WHEN CAST(29 AS INT) THEN NULL END, CASE valid_tid(27,28,29) WHEN CAST(27 AS INT) THEN CAST(27 AS INT) WHEN CAST(28 AS INT) THEN CAST(28 AS INT) WHEN CAST(29 AS INT) THEN CAST(29 AS INT) END
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=30 row-size=76B cardinality=11.56K
|  in pipelines: 28(GETNEXT), 45(OPEN)
|
45:AGGREGATE [FINALIZE]
|  Class 0
|    output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
|    group by: channel, id
|  Class 1
|    output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
|    group by: channel, NULL
|  Class 2
|    output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
|    group by: NULL, NULL
|  mem-estimate=30.00MB mem-reservation=6.75MB thread-reservation=0
|  tuple-ids=27N,28N,29N row-size=216B cardinality=11.56K
|  in pipelines: 45(GETNEXT), 33(OPEN), 37(OPEN), 43(OPEN)
|
44:EXCHANGE [HASH(CASE valid_tid(27,28,29) WHEN 27 THEN murmur_hash(channel) WHEN 28 THEN murmur_hash(channel) WHEN 29 THEN murmur_hash(NULL) END,CASE valid_tid(27,28,29) WHEN 27 THEN murmur_hash(id) WHEN 28 THEN murmur_hash(NULL) WHEN 29 THEN murmur_hash(NULL) END)]
|  mem-estimate=1.46MB mem-reservation=0B thread-reservation=0
|  tuple-ids=27N,28N,29N row-size=216B cardinality=11.56K
|  in pipelines: 33(GETNEXT), 37(GETNEXT), 43(GETNEXT)
|
F20:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=40.62MB mem-reservation=8.94MB thread-reservation=1
27:AGGREGATE [STREAMING]
|  Class 0
|    output: sum(sales), sum(returns), sum(profit)
|    group by: channel, id
|  Class 1
|    output: sum(sales), sum(returns), sum(profit)
|    group by: channel, NULL
|  Class 2
|    output: sum(sales), sum(returns), sum(profit)
|    group by: NULL, NULL
|  mem-estimate=30.00MB mem-reservation=7.00MB thread-reservation=0
|  tuple-ids=27N,28N,29N row-size=216B cardinality=11.56K
|  in pipelines: 33(GETNEXT), 37(GETNEXT), 43(GETNEXT)
|
00:UNION
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=25 row-size=72B cardinality=11.56K
|  in pipelines: 33(GETNEXT), 37(GETNEXT), 43(GETNEXT)
|
|--43:AGGREGATE [FINALIZE]
|  |  output: sum:merge(sales_price), sum:merge(profit), sum:merge(return_amt), sum:merge(net_loss)
|  |  group by: web_site_id
|  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=23 row-size=92B cardinality=15
|  |  in pipelines: 43(GETNEXT), 18(OPEN), 20(OPEN)
|  |
|  42:EXCHANGE [HASH(web_site_id)]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=23 row-size=92B cardinality=15
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT)
|  |
|  F16:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
|  Per-Host Resources: mem-estimate=146.10MB mem-reservation=17.88MB thread-reservation=2 runtime-filters-memory=4.00MB
|  26:AGGREGATE [STREAMING]
|  |  output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
|  |  group by: web_site_id
|  |  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=23 row-size=92B cardinality=15
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT)
|  |
|  25:HASH JOIN [INNER JOIN, BROADCAST]
|  |  hash predicates: wsr_web_site_sk = web_site_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF008[bloom] <- web_site_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=19,21,22 row-size=82B cardinality=791.15K
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT), 23(OPEN)
|  |
|  |--41:EXCHANGE [BROADCAST]
|  |  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=22 row-size=32B cardinality=30
|  |  |  in pipelines: 23(GETNEXT)
|  |  |
|  |  F18:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  Per-Host Resources: mem-estimate=32.00MB mem-reservation=16.00KB thread-reservation=2
|  |  23:SCAN HDFS [tpcds_parquet.web_site, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=11.91KB
|  |     stored statistics:
|  |       table: rows=30 size=11.91KB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=30
|  |     mem-estimate=32.00MB mem-reservation=16.00KB thread-reservation=1
|  |     tuple-ids=22 row-size=32B cardinality=30
|  |     in pipelines: 23(GETNEXT)
|  |
|  24:HASH JOIN [INNER JOIN, BROADCAST]
|  |  hash predicates: date_sk = d_date_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF010[bloom] <- d_date_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=19,21 row-size=50B cardinality=791.15K
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT), 22(OPEN)
|  |
|  |--40:EXCHANGE [BROADCAST]
|  |  |  mem-estimate=215.48KB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=21 row-size=26B cardinality=7.30K
|  |  |  in pipelines: 22(GETNEXT)
|  |  |
|  |  F17:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  Per-Host Resources: mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=2
|  |  22:SCAN HDFS [tpcds_parquet.date_dim, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=2.15MB
|  |     predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     stored statistics:
|  |       table: rows=73.05K size=2.15MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=73.05K
|  |     parquet statistics predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     parquet dictionary predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=1
|  |     tuple-ids=21 row-size=26B cardinality=7.30K
|  |     in pipelines: 22(GETNEXT)
|  |
|  17:UNION
|  |  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  |  tuple-ids=19 row-size=24B cardinality=791.15K
|  |  in pipelines: 18(GETNEXT), 20(GETNEXT)
|  |
|  |--21:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  |  |  hash predicates: ws_item_sk = wr_item_sk, ws_order_number = wr_order_number
|  |  |  fk/pk conjuncts: ws_item_sk = wr_item_sk, ws_order_number = wr_order_number
|  |  |  runtime filters: RF012[bloom] <- wr_item_sk, RF013[bloom] <- wr_order_number
|  |  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=18N,17 row-size=48B cardinality=71.76K
|  |  |  in pipelines: 20(GETNEXT), 19(OPEN)
|  |  |
|  |  |--39:EXCHANGE [HASH(wr_item_sk,wr_order_number)]
|  |  |  |  mem-estimate=1.95MB mem-reservation=0B thread-reservation=0
|  |  |  |  tuple-ids=17 row-size=28B cardinality=71.76K
|  |  |  |  in pipelines: 19(GETNEXT)
|  |  |  |
|  |  |  F14:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  |  Per-Host Resources: mem-estimate=81.00MB mem-reservation=3.00MB thread-reservation=2 runtime-filters-memory=1.00MB
|  |  |  19:SCAN HDFS [tpcds_parquet.web_returns, RANDOM]
|  |  |     HDFS partitions=1/1 files=1 size=5.66MB
|  |  |     runtime filters: RF010[bloom] -> tpcds_parquet.web_returns.wr_returned_date_sk
|  |  |     stored statistics:
|  |  |       table: rows=71.76K size=5.66MB
|  |  |       columns: all
|  |  |     extrapolated-rows=disabled max-scan-range-rows=71.76K
|  |  |     mem-estimate=80.00MB mem-reservation=2.00MB thread-reservation=1
|  |  |     tuple-ids=17 row-size=28B cardinality=71.76K
|  |  |     in pipelines: 19(GETNEXT)
|  |  |
|  |  38:EXCHANGE [HASH(ws_item_sk,ws_order_number)]
|  |  |  mem-estimate=6.91MB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=18 row-size=20B cardinality=719.38K
|  |  |  in pipelines: 20(GETNEXT)
|  |  |
|  |  F13:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
|  |  Per-Host Resources: mem-estimate=99.00MB mem-reservation=7.00MB thread-reservation=2 runtime-filters-memory=3.00MB
|  |  20:SCAN HDFS [tpcds_parquet.web_sales, RANDOM]
|  |     HDFS partitions=1/1 files=2 size=45.09MB
|  |     runtime filters: RF008[bloom] -> tpcds_parquet.web_sales.ws_web_site_sk, RF012[bloom] -> ws_item_sk, RF013[bloom] -> ws_order_number
|  |     stored statistics:
|  |       table: rows=719.38K size=45.09MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=644.77K
|  |     mem-estimate=96.00MB mem-reservation=4.00MB thread-reservation=1
|  |     tuple-ids=18 row-size=20B cardinality=719.38K
|  |     in pipelines: 20(GETNEXT)
|  |
|  18:SCAN HDFS [tpcds_parquet.web_sales, RANDOM]
|     HDFS partitions=1/1 files=2 size=45.09MB
|     runtime filters: RF008[bloom] -> tpcds_parquet.web_sales.ws_web_site_sk, RF010[bloom] -> tpcds_parquet.web_sales.ws_sold_date_sk
|     stored statistics:
|       table: rows=719.38K size=45.09MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=644.77K
|     mem-estimate=128.00MB mem-reservation=8.00MB thread-reservation=1
|     tuple-ids=16 row-size=16B cardinality=719.38K
|     in pipelines: 18(GETNEXT)
|
|--37:AGGREGATE [FINALIZE]
|  |  output: sum:merge(sales_price), sum:merge(profit), sum:merge(return_amt), sum:merge(net_loss)
|  |  group by: cp_catalog_page_id
|  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=14 row-size=92B cardinality=11.54K
|  |  in pipelines: 37(GETNEXT), 10(OPEN), 11(OPEN)
|  |
|  36:EXCHANGE [HASH(cp_catalog_page_id)]
|  |  mem-estimate=633.57KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=14 row-size=92B cardinality=11.54K
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT)
|  |
|  F08:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
|  Per-Host Resources: mem-estimate=208.48MB mem-reservation=16.88MB thread-reservation=2 runtime-filters-memory=2.00MB
|  16:AGGREGATE [STREAMING]
|  |  output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
|  |  group by: cp_catalog_page_id
|  |  mem-estimate=10.00MB mem-reservation=3.00MB spill-buffer=128.00KB thread-reservation=0
|  |  tuple-ids=14 row-size=92B cardinality=11.54K
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT)
|  |
|  15:HASH JOIN [INNER JOIN, BROADCAST]
|  |  hash predicates: page_sk = cp_catalog_page_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF004[bloom] <- cp_catalog_page_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=10,12,13 row-size=82B cardinality=1.59M
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT), 13(OPEN)
|  |
|  |--35:EXCHANGE [BROADCAST]
|  |  |  mem-estimate=402.19KB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=13 row-size=32B cardinality=11.72K
|  |  |  in pipelines: 13(GETNEXT)
|  |  |
|  |  F10:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  Per-Host Resources: mem-estimate=32.00MB mem-reservation=256.00KB thread-reservation=2
|  |  13:SCAN HDFS [tpcds_parquet.catalog_page, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=739.17KB
|  |     stored statistics:
|  |       table: rows=11.72K size=739.17KB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=11.72K
|  |     mem-estimate=32.00MB mem-reservation=256.00KB thread-reservation=1
|  |     tuple-ids=13 row-size=32B cardinality=11.72K
|  |     in pipelines: 13(GETNEXT)
|  |
|  14:HASH JOIN [INNER JOIN, BROADCAST]
|  |  hash predicates: date_sk = d_date_sk
|  |  fk/pk conjuncts: assumed fk/pk
|  |  runtime filters: RF006[bloom] <- d_date_sk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=10,12 row-size=50B cardinality=1.59M
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT), 12(OPEN)
|  |
|  |--34:EXCHANGE [BROADCAST]
|  |  |  mem-estimate=215.48KB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=12 row-size=26B cardinality=7.30K
|  |  |  in pipelines: 12(GETNEXT)
|  |  |
|  |  F09:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  Per-Host Resources: mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=2
|  |  12:SCAN HDFS [tpcds_parquet.date_dim, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=2.15MB
|  |     predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     stored statistics:
|  |       table: rows=73.05K size=2.15MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=73.05K
|  |     parquet statistics predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     parquet dictionary predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|  |     mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=1
|  |     tuple-ids=12 row-size=26B cardinality=7.30K
|  |     in pipelines: 12(GETNEXT)
|  |
|  09:UNION
|  |  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  |  tuple-ids=10 row-size=24B cardinality=1.59M
|  |  in pipelines: 10(GETNEXT), 11(GETNEXT)
|  |
|  |--11:SCAN HDFS [tpcds_parquet.catalog_returns, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=10.62MB
|  |     runtime filters: RF004[bloom] -> tpcds_parquet.catalog_returns.cr_catalog_page_sk, RF006[bloom] -> tpcds_parquet.catalog_returns.cr_returned_date_sk
|  |     stored statistics:
|  |       table: rows=144.07K size=10.62MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=144.07K
|  |     mem-estimate=96.00MB mem-reservation=2.00MB thread-reservation=1
|  |     tuple-ids=9 row-size=16B cardinality=144.07K
|  |     in pipelines: 11(GETNEXT)
|  |
|  10:SCAN HDFS [tpcds_parquet.catalog_sales, RANDOM]
|     HDFS partitions=1/1 files=3 size=96.62MB
|     runtime filters: RF004[bloom] -> tpcds_parquet.catalog_sales.cs_catalog_page_sk, RF006[bloom] -> tpcds_parquet.catalog_sales.cs_sold_date_sk
|     stored statistics:
|       table: rows=1.44M size=96.62MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=650.14K
|     mem-estimate=192.00MB mem-reservation=8.00MB thread-reservation=1
|     tuple-ids=8 row-size=16B cardinality=1.44M
|     in pipelines: 10(GETNEXT)
|
33:AGGREGATE [FINALIZE]
|  output: sum:merge(sales_price), sum:merge(profit), sum:merge(return_amt), sum:merge(net_loss)
|  group by: s_store_id
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=6 row-size=92B cardinality=6
|  in pipelines: 33(GETNEXT), 02(OPEN), 03(OPEN)
|
32:EXCHANGE [HASH(s_store_id)]
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=6 row-size=92B cardinality=6
|  in pipelines: 02(GETNEXT), 03(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=112.10MB mem-reservation=11.88MB thread-reservation=2 runtime-filters-memory=2.00MB
08:AGGREGATE [STREAMING]
|  output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
|  group by: s_store_id
|  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=6 row-size=92B cardinality=6
|  in pipelines: 02(GETNEXT), 03(GETNEXT)
|
07:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: store_sk = s_store_sk
|  fk/pk conjuncts: assumed fk/pk
|  runtime filters: RF000[bloom] <- s_store_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=2,4,5 row-size=82B cardinality=3.17M
|  in pipelines: 02(GETNEXT), 03(GETNEXT), 05(OPEN)
|
|--31:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=5 row-size=32B cardinality=12
|  |  in pipelines: 05(GETNEXT)
|  |
|  F04:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=32.00MB mem-reservation=16.00KB thread-reservation=2
|  05:SCAN HDFS [tpcds_parquet.store, RANDOM]
|     HDFS partitions=1/1 files=1 size=9.93KB
|     stored statistics:
|       table: rows=12 size=9.93KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=12
|     mem-estimate=32.00MB mem-reservation=16.00KB thread-reservation=1
|     tuple-ids=5 row-size=32B cardinality=12
|     in pipelines: 05(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: date_sk = d_date_sk
|  fk/pk conjuncts: assumed fk/pk
|  runtime filters: RF002[bloom] <- d_date_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=2,4 row-size=50B cardinality=3.17M
|  in pipelines: 02(GETNEXT), 03(GETNEXT), 04(OPEN)
|
|--30:EXCHANGE [BROADCAST]
|  |  mem-estimate=215.48KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=26B cardinality=7.30K
|  |  in pipelines: 04(GETNEXT)
|  |
|  F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=2
|  04:SCAN HDFS [tpcds_parquet.date_dim, RANDOM]
|     HDFS partitions=1/1 files=1 size=2.15MB
|     predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|     stored statistics:
|       table: rows=73.05K size=2.15MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|     parquet dictionary predicates: CAST(d_date AS DATE) <= DATE '1998-08-18', CAST(d_date AS DATE) >= DATE '1998-08-04'
|     mem-estimate=32.00MB mem-reservation=1.00MB thread-reservation=1
|     tuple-ids=4 row-size=26B cardinality=7.30K
|     in pipelines: 04(GETNEXT)
|
01:UNION
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=24B cardinality=3.17M
|  in pipelines: 02(GETNEXT), 03(GETNEXT)
|
|--03:SCAN HDFS [tpcds_parquet.store_returns, RANDOM]
|     HDFS partitions=1/1 files=1 size=15.43MB
|     runtime filters: RF000[bloom] -> tpcds_parquet.store_returns.sr_store_sk, RF002[bloom] -> tpcds_parquet.store_returns.sr_returned_date_sk
|     stored statistics:
|       table: rows=287.51K size=15.43MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=287.51K
|     mem-estimate=96.00MB mem-reservation=4.00MB thread-reservation=1
|     tuple-ids=1 row-size=16B cardinality=287.51K
|     in pipelines: 03(GETNEXT)
|
02:SCAN HDFS [tpcds_parquet.store_sales, RANDOM]
   HDFS partitions=1824/1824 files=1824 size=200.95MB
   runtime filters: RF000[bloom] -> tpcds_parquet.store_sales.ss_store_sk, RF002[bloom] -> tpcds_parquet.store_sales.ss_sold_date_sk
   stored statistics:
     table: rows=2.88M size=200.95MB
     partitions: 1824/1824 rows=2.88M
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=130.09K
   mem-estimate=48.00MB mem-reservation=2.00MB thread-reservation=1
   tuple-ids=0 row-size=16B cardinality=2.88M
   in pipelines: 02(GETNEXT)
====