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

# TPCDS-Q8
SELECT s_store_name,
       sum(ss_net_profit)
FROM store_sales,
     date_dim,
     store,
  (SELECT ca_zip
   FROM
     (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip
      FROM customer_address
      WHERE SUBSTRING(ca_zip, 1, 5) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548',
                                     '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920',
                                     '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601',
                                     '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741',
                                     '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550',
                                     '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312',
                                     '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666',
                                     '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448',
                                     '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162',
                                     '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341',
                                     '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470',
                                     '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359',
                                     '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206',
                                     '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420',
                                     '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325',
                                     '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146',
                                     '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976',
                                     '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371',
                                     '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858',
                                     '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451',
                                     '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305',
                                     '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882',
                                     '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328',
                                     '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076',
                                     '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211',
                                     '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791',
                                     '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786',
                                     '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368',
                                     '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246',
                                     '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867',
                                     '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535',
                                     '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721',
                                     '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253',
                                     '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393',
                                     '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968',
                                     '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016',
                                     '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721',
                                     '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123',
                                     '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949',
                                     '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103',
                                     '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337',
                                     '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279',
                                     '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136',
                                     '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810',
                                     '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057',
                                     '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013',
                                     '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908',
                                     '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144',
                                     '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385',
                                     '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458',
                                     '35576') INTERSECT
        SELECT ca_zip
        FROM
          (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip,
                  count(*) cnt
           FROM customer_address,
                customer
           WHERE ca_address_sk = c_current_addr_sk
             AND c_preferred_cust_flag='Y'
           GROUP BY ca_zip
           HAVING count(*) > 10)A1)A2) V1
WHERE ss_store_sk = s_store_sk
  AND ss_sold_date_sk = d_date_sk
  AND d_qoy = 2
  AND d_year = 1998
  AND (SUBSTRING(s_zip, 1, 2) = SUBSTRING(V1.ca_zip, 1, 2))
GROUP BY s_store_name
ORDER BY s_store_name
LIMIT 100;
---- PLAN
Max Per-Host Resource Reservation: Memory=15.84MB Threads=7
Per-Host Resource Estimates: Memory=228MB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=227.81MB mem-reservation=15.84MB thread-reservation=7 runtime-filters-memory=4.00MB
PLAN-ROOT SINK
|  output exprs: s_store_name, sum(ss_net_profit)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
14:TOP-N [LIMIT=100]
|  order by: s_store_name ASC
|  mem-estimate=258B mem-reservation=0B thread-reservation=0
|  tuple-ids=14 row-size=32B cardinality=8
|  in pipelines: 14(GETNEXT), 13(OPEN)
|
13:AGGREGATE [FINALIZE]
|  output: sum(ss_net_profit)
|  group by: s_store_name
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 13(GETNEXT), 00(OPEN)
|
12:HASH JOIN [INNER JOIN]
|  hash predicates: substring(s_zip, 1, 2) = substring(substring(ca_zip, 1, 5), 1, 2)
|  fk/pk conjuncts: assumed fk/pk
|  runtime filters: RF000[bloom] <- substring(substring(ca_zip, 1, 5), 1, 2)
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,2,4 row-size=73B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 04(OPEN)
|
|--09:HASH JOIN [LEFT SEMI JOIN]
|  |  hash predicates: substring(ca_zip, 1, 5) IS NOT DISTINCT FROM substring(ca_zip, 1, 5)
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=396
|  |  in pipelines: 04(GETNEXT), 08(OPEN)
|  |
|  |--08:AGGREGATE [FINALIZE]
|  |  |  output: count(*)
|  |  |  group by: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT))
|  |  |  having: count(*) > CAST(10 AS BIGINT)
|  |  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=396
|  |  |  in pipelines: 08(GETNEXT), 05(OPEN)
|  |  |
|  |  07:HASH JOIN [INNER JOIN]
|  |  |  hash predicates: ca_address_sk = c_current_addr_sk
|  |  |  fk/pk conjuncts: none
|  |  |  runtime filters: RF006[bloom] <- c_current_addr_sk
|  |  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=6,7 row-size=38B cardinality=51.30K
|  |  |  in pipelines: 05(GETNEXT), 06(OPEN)
|  |  |
|  |  |--06:SCAN HDFS [tpcds_parquet.customer]
|  |  |     HDFS partitions=1/1 files=1 size=5.49MB
|  |  |     predicates: c_preferred_cust_flag = 'Y'
|  |  |     stored statistics:
|  |  |       table: rows=100.00K size=5.49MB
|  |  |       columns: all
|  |  |     extrapolated-rows=disabled max-scan-range-rows=100.00K
|  |  |     parquet statistics predicates: c_preferred_cust_flag = 'Y'
|  |  |     parquet dictionary predicates: c_preferred_cust_flag = 'Y'
|  |  |     mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=1
|  |  |     tuple-ids=7 row-size=17B cardinality=50.00K
|  |  |     in pipelines: 06(GETNEXT)
|  |  |
|  |  05:SCAN HDFS [tpcds_parquet.customer_address]
|  |     HDFS partitions=1/1 files=1 size=1.16MB
|  |     runtime filters: RF006[bloom] -> ca_address_sk
|  |     stored statistics:
|  |       table: rows=50.00K size=1.16MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=50.00K
|  |     mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=1
|  |     tuple-ids=6 row-size=21B cardinality=50.00K
|  |     in pipelines: 05(GETNEXT)
|  |
|  04:AGGREGATE [FINALIZE]
|  |  group by: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT))
|  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 04(GETNEXT), 03(OPEN)
|  |
|  03:SCAN HDFS [tpcds_parquet.customer_address]
|     HDFS partitions=1/1 files=1 size=1.16MB
|     predicates: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT)) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
|     stored statistics:
|       table: rows=50.00K size=1.16MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=50.00K
|     parquet dictionary predicates: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT)) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
|     mem-estimate=16.00MB mem-reservation=128.00KB thread-reservation=1
|     tuple-ids=3 row-size=17B cardinality=5.00K
|     in pipelines: 03(GETNEXT)
|
11: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
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,2 row-size=61B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 02(OPEN)
|
|--02:SCAN HDFS [tpcds_parquet.store]
|     HDFS partitions=1/1 files=1 size=9.93KB
|     runtime filters: RF000[bloom] -> substring(s_zip, 1, 2)
|     stored statistics:
|       table: rows=12 size=9.93KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=12
|     mem-estimate=48.00MB mem-reservation=24.00KB thread-reservation=1
|     tuple-ids=2 row-size=37B cardinality=12
|     in pipelines: 02(GETNEXT)
|
10:HASH JOIN [INNER JOIN]
|  hash predicates: ss_sold_date_sk = d_date_sk
|  fk/pk conjuncts: ss_sold_date_sk = d_date_sk
|  runtime filters: RF004[bloom] <- d_date_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1 row-size=24B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 01(OPEN)
|
|--01:SCAN HDFS [tpcds_parquet.date_dim]
|     HDFS partitions=1/1 files=1 size=2.15MB
|     predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     stored statistics:
|       table: rows=73.05K size=2.15MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     parquet dictionary predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     mem-estimate=48.00MB mem-reservation=512.00KB thread-reservation=1
|     tuple-ids=1 row-size=12B cardinality=186
|     in pipelines: 01(GETNEXT)
|
00:SCAN HDFS [tpcds_parquet.store_sales]
   HDFS partitions=1824/1824 files=1824 size=200.95MB
   runtime filters: RF002[bloom] -> ss_store_sk, RF004[bloom] -> 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=32.00MB mem-reservation=1.00MB thread-reservation=1
   tuple-ids=0 row-size=12B cardinality=2.88M
   in pipelines: 00(GETNEXT)
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=29.15MB Threads=15
Per-Host Resource Estimates: Memory=284MB
F09:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: s_store_name, sum(ss_net_profit)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
25:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: s_store_name ASC
|  limit: 100
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=14 row-size=32B cardinality=8
|  in pipelines: 14(GETNEXT)
|
F08:PLAN FRAGMENT [HASH(s_store_name)] hosts=3 instances=3
Per-Host Resources: mem-estimate=10.02MB mem-reservation=1.94MB thread-reservation=1
14:TOP-N [LIMIT=100]
|  order by: s_store_name ASC
|  mem-estimate=258B mem-reservation=0B thread-reservation=0
|  tuple-ids=14 row-size=32B cardinality=8
|  in pipelines: 14(GETNEXT), 24(OPEN)
|
24:AGGREGATE [FINALIZE]
|  output: sum:merge(ss_net_profit)
|  group by: s_store_name
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 24(GETNEXT), 00(OPEN)
|
23:EXCHANGE [HASH(s_store_name)]
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=50.86MB mem-reservation=11.81MB thread-reservation=2 runtime-filters-memory=3.00MB
13:AGGREGATE [STREAMING]
|  output: sum(ss_net_profit)
|  group by: s_store_name
|  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 00(GETNEXT)
|
12:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: substring(s_zip, 1, 2) = substring(substring(ca_zip, 1, 5), 1, 2)
|  fk/pk conjuncts: assumed fk/pk
|  runtime filters: RF000[bloom] <- substring(substring(ca_zip, 1, 5), 1, 2)
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,2,4 row-size=73B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 18(OPEN)
|
|--22:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=396
|  |  in pipelines: 18(GETNEXT)
|  |
|  F04:PLAN FRAGMENT [HASH(substring(ca_zip, 1, 5))] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=22.00MB mem-reservation=5.81MB thread-reservation=1
|  09:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
|  |  hash predicates: substring(ca_zip, 1, 5) IS NOT DISTINCT FROM substring(ca_zip, 1, 5)
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=396
|  |  in pipelines: 18(GETNEXT), 21(OPEN)
|  |
|  |--21:AGGREGATE [FINALIZE]
|  |  |  output: count:merge(*)
|  |  |  group by: substring(ca_zip, 1, 5)
|  |  |  having: count(*) > CAST(10 AS BIGINT)
|  |  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=396
|  |  |  in pipelines: 21(GETNEXT), 05(OPEN)
|  |  |
|  |  20:EXCHANGE [HASH(substring(ca_zip, 1, 5))]
|  |  |  mem-estimate=101.36KB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=3.96K
|  |  |  in pipelines: 05(GETNEXT)
|  |  |
|  |  F05:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  Per-Host Resources: mem-estimate=45.77MB mem-reservation=5.44MB thread-reservation=2 runtime-filters-memory=1.00MB
|  |  08:AGGREGATE [STREAMING]
|  |  |  output: count(*)
|  |  |  group by: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT))
|  |  |  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=3.96K
|  |  |  in pipelines: 05(GETNEXT)
|  |  |
|  |  07:HASH JOIN [INNER JOIN, BROADCAST]
|  |  |  hash predicates: ca_address_sk = c_current_addr_sk
|  |  |  fk/pk conjuncts: none
|  |  |  runtime filters: RF006[bloom] <- c_current_addr_sk
|  |  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=6,7 row-size=38B cardinality=51.30K
|  |  |  in pipelines: 05(GETNEXT), 06(OPEN)
|  |  |
|  |  |--19:EXCHANGE [BROADCAST]
|  |  |  |  mem-estimate=851.08KB mem-reservation=0B thread-reservation=0
|  |  |  |  tuple-ids=7 row-size=17B cardinality=50.00K
|  |  |  |  in pipelines: 06(GETNEXT)
|  |  |  |
|  |  |  F06:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  |  Per-Host Resources: mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=2
|  |  |  06:SCAN HDFS [tpcds_parquet.customer, RANDOM]
|  |  |     HDFS partitions=1/1 files=1 size=5.49MB
|  |  |     predicates: c_preferred_cust_flag = 'Y'
|  |  |     stored statistics:
|  |  |       table: rows=100.00K size=5.49MB
|  |  |       columns: all
|  |  |     extrapolated-rows=disabled max-scan-range-rows=100.00K
|  |  |     parquet statistics predicates: c_preferred_cust_flag = 'Y'
|  |  |     parquet dictionary predicates: c_preferred_cust_flag = 'Y'
|  |  |     mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=1
|  |  |     tuple-ids=7 row-size=17B cardinality=50.00K
|  |  |     in pipelines: 06(GETNEXT)
|  |  |
|  |  05:SCAN HDFS [tpcds_parquet.customer_address, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=1.16MB
|  |     runtime filters: RF006[bloom] -> ca_address_sk
|  |     stored statistics:
|  |       table: rows=50.00K size=1.16MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=50.00K
|  |     mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=1
|  |     tuple-ids=6 row-size=21B cardinality=50.00K
|  |     in pipelines: 05(GETNEXT)
|  |
|  18:AGGREGATE [FINALIZE]
|  |  group by: substring(ca_zip, 1, 5)
|  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 18(GETNEXT), 03(OPEN)
|  |
|  17:EXCHANGE [HASH(substring(ca_zip, 1, 5))]
|  |  mem-estimate=62.42KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 03(GETNEXT)
|  |
|  F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=26.00MB mem-reservation=2.12MB thread-reservation=2
|  04:AGGREGATE [STREAMING]
|  |  group by: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT))
|  |  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 03(GETNEXT)
|  |
|  03:SCAN HDFS [tpcds_parquet.customer_address, RANDOM]
|     HDFS partitions=1/1 files=1 size=1.16MB
|     predicates: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT)) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
|     stored statistics:
|       table: rows=50.00K size=1.16MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=50.00K
|     parquet dictionary predicates: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT)) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
|     mem-estimate=16.00MB mem-reservation=128.00KB thread-reservation=1
|     tuple-ids=3 row-size=17B cardinality=5.00K
|     in pipelines: 03(GETNEXT)
|
11:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: ss_store_sk = s_store_sk
|  fk/pk conjuncts: ss_store_sk = s_store_sk
|  runtime filters: RF002[bloom] <- s_store_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,2 row-size=61B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 02(OPEN)
|
|--16:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=2 row-size=37B cardinality=12
|  |  in pipelines: 02(GETNEXT)
|  |
|  F02:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=49.00MB mem-reservation=1.02MB thread-reservation=2 runtime-filters-memory=1.00MB
|  02:SCAN HDFS [tpcds_parquet.store, RANDOM]
|     HDFS partitions=1/1 files=1 size=9.93KB
|     runtime filters: RF000[bloom] -> substring(s_zip, 1, 2)
|     stored statistics:
|       table: rows=12 size=9.93KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=12
|     mem-estimate=48.00MB mem-reservation=24.00KB thread-reservation=1
|     tuple-ids=2 row-size=37B cardinality=12
|     in pipelines: 02(GETNEXT)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: ss_sold_date_sk = d_date_sk
|  fk/pk conjuncts: ss_sold_date_sk = d_date_sk
|  runtime filters: RF004[bloom] <- d_date_sk
|  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1 row-size=24B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 01(OPEN)
|
|--15:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=1 row-size=12B cardinality=186
|  |  in pipelines: 01(GETNEXT)
|  |
|  F01:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=48.00MB mem-reservation=512.00KB thread-reservation=2
|  01:SCAN HDFS [tpcds_parquet.date_dim, RANDOM]
|     HDFS partitions=1/1 files=1 size=2.15MB
|     predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     stored statistics:
|       table: rows=73.05K size=2.15MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     parquet dictionary predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     mem-estimate=48.00MB mem-reservation=512.00KB thread-reservation=1
|     tuple-ids=1 row-size=12B cardinality=186
|     in pipelines: 01(GETNEXT)
|
00:SCAN HDFS [tpcds_parquet.store_sales, RANDOM]
   HDFS partitions=1824/1824 files=1824 size=200.95MB
   runtime filters: RF002[bloom] -> ss_store_sk, RF004[bloom] -> 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=32.00MB mem-reservation=1.00MB thread-reservation=1
   tuple-ids=0 row-size=12B cardinality=2.88M
   in pipelines: 00(GETNEXT)
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=44.84MB Threads=16
Per-Host Resource Estimates: Memory=218MB
F09:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: s_store_name, sum(ss_net_profit)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
25:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: s_store_name ASC
|  limit: 100
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=14 row-size=32B cardinality=8
|  in pipelines: 14(GETNEXT)
|
F08:PLAN FRAGMENT [HASH(s_store_name)] hosts=3 instances=6
Per-Instance Resources: mem-estimate=10.02MB mem-reservation=1.94MB thread-reservation=1
14:TOP-N [LIMIT=100]
|  order by: s_store_name ASC
|  mem-estimate=258B mem-reservation=0B thread-reservation=0
|  tuple-ids=14 row-size=32B cardinality=8
|  in pipelines: 14(GETNEXT), 24(OPEN)
|
24:AGGREGATE [FINALIZE]
|  output: sum:merge(ss_net_profit)
|  group by: s_store_name
|  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 24(GETNEXT), 00(OPEN)
|
23:EXCHANGE [HASH(s_store_name)]
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=6
Per-Host Shared Resources: mem-estimate=2.00MB mem-reservation=2.00MB thread-reservation=0 runtime-filters-memory=2.00MB
Per-Instance Resources: mem-estimate=26.00MB mem-reservation=3.00MB thread-reservation=1
13:AGGREGATE [STREAMING]
|  output: sum(ss_net_profit)
|  group by: s_store_name
|  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=13 row-size=32B cardinality=8
|  in pipelines: 00(GETNEXT)
|
12:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=00
|  hash predicates: substring(s_zip, 1, 2) = substring(substring(ca_zip, 1, 5), 1, 2)
|  fk/pk conjuncts: assumed fk/pk
|  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1,2,4 row-size=73B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 18(OPEN)
|
|--F10:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
|  |  Per-Instance Resources: mem-estimate=4.89MB mem-reservation=4.88MB thread-reservation=1 runtime-filters-memory=1.00MB
|  JOIN BUILD
|  |  join-table-id=00 plan-id=01 cohort-id=01
|  |  build expressions: substring(substring(ca_zip, 1, 5), 1, 2)
|  |  runtime filters: RF000[bloom] <- substring(substring(ca_zip, 1, 5), 1, 2)
|  |  mem-estimate=3.88MB mem-reservation=3.88MB spill-buffer=64.00KB thread-reservation=0
|  |
|  22:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=396
|  |  in pipelines: 18(GETNEXT)
|  |
|  F04:PLAN FRAGMENT [HASH(substring(ca_zip, 1, 5))] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=10.06MB mem-reservation=1.94MB thread-reservation=1
|  09:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
|  |  hash-table-id=01
|  |  hash predicates: substring(ca_zip, 1, 5) IS NOT DISTINCT FROM substring(ca_zip, 1, 5)
|  |  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=396
|  |  in pipelines: 18(GETNEXT), 21(OPEN)
|  |
|  |--F11:PLAN FRAGMENT [HASH(substring(ca_zip, 1, 5))] hosts=1 instances=1
|  |  |  Per-Instance Resources: mem-estimate=11.94MB mem-reservation=3.88MB thread-reservation=1
|  |  JOIN BUILD
|  |  |  join-table-id=01 plan-id=02 cohort-id=02
|  |  |  build expressions: substring(ca_zip, 1, 5)
|  |  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |
|  |  21:AGGREGATE [FINALIZE]
|  |  |  output: count:merge(*)
|  |  |  group by: substring(ca_zip, 1, 5)
|  |  |  having: count(*) > CAST(10 AS BIGINT)
|  |  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=396
|  |  |  in pipelines: 21(GETNEXT), 05(OPEN)
|  |  |
|  |  20:EXCHANGE [HASH(substring(ca_zip, 1, 5))]
|  |  |  mem-estimate=101.36KB mem-reservation=0B thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=3.96K
|  |  |  in pipelines: 05(GETNEXT)
|  |  |
|  |  F05: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=26.00MB mem-reservation=2.50MB thread-reservation=1
|  |  08:AGGREGATE [STREAMING]
|  |  |  output: count(*)
|  |  |  group by: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT))
|  |  |  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=8 row-size=20B cardinality=3.96K
|  |  |  in pipelines: 05(GETNEXT)
|  |  |
|  |  07:HASH JOIN [INNER JOIN, BROADCAST]
|  |  |  hash-table-id=02
|  |  |  hash predicates: ca_address_sk = c_current_addr_sk
|  |  |  fk/pk conjuncts: none
|  |  |  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  |  |  tuple-ids=6,7 row-size=38B cardinality=51.30K
|  |  |  in pipelines: 05(GETNEXT), 06(OPEN)
|  |  |
|  |  |--F12:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  |  |  Per-Instance Resources: mem-estimate=5.71MB mem-reservation=4.88MB thread-reservation=1 runtime-filters-memory=1.00MB
|  |  |  JOIN BUILD
|  |  |  |  join-table-id=02 plan-id=03 cohort-id=03
|  |  |  |  build expressions: c_current_addr_sk
|  |  |  |  runtime filters: RF006[bloom] <- c_current_addr_sk
|  |  |  |  mem-estimate=3.88MB mem-reservation=3.88MB spill-buffer=64.00KB thread-reservation=0
|  |  |  |
|  |  |  19:EXCHANGE [BROADCAST]
|  |  |  |  mem-estimate=851.08KB mem-reservation=0B thread-reservation=0
|  |  |  |  tuple-ids=7 row-size=17B cardinality=50.00K
|  |  |  |  in pipelines: 06(GETNEXT)
|  |  |  |
|  |  |  F06:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  |  |  Per-Instance Resources: mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=1
|  |  |  06:SCAN HDFS [tpcds_parquet.customer, RANDOM]
|  |  |     HDFS partitions=1/1 files=1 size=5.49MB
|  |  |     predicates: c_preferred_cust_flag = 'Y'
|  |  |     stored statistics:
|  |  |       table: rows=100.00K size=5.49MB
|  |  |       columns: all
|  |  |     extrapolated-rows=disabled max-scan-range-rows=100.00K
|  |  |     parquet statistics predicates: c_preferred_cust_flag = 'Y'
|  |  |     parquet dictionary predicates: c_preferred_cust_flag = 'Y'
|  |  |     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|  |  |     tuple-ids=7 row-size=17B cardinality=50.00K
|  |  |     in pipelines: 06(GETNEXT)
|  |  |
|  |  05:SCAN HDFS [tpcds_parquet.customer_address, RANDOM]
|  |     HDFS partitions=1/1 files=1 size=1.16MB
|  |     runtime filters: RF006[bloom] -> ca_address_sk
|  |     stored statistics:
|  |       table: rows=50.00K size=1.16MB
|  |       columns: all
|  |     extrapolated-rows=disabled max-scan-range-rows=50.00K
|  |     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|  |     tuple-ids=6 row-size=21B cardinality=50.00K
|  |     in pipelines: 05(GETNEXT)
|  |
|  18:AGGREGATE [FINALIZE]
|  |  group by: substring(ca_zip, 1, 5)
|  |  mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 18(GETNEXT), 03(OPEN)
|  |
|  17:EXCHANGE [HASH(substring(ca_zip, 1, 5))]
|  |  mem-estimate=62.42KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 03(GETNEXT)
|  |
|  F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=26.00MB mem-reservation=2.12MB thread-reservation=1
|  04:AGGREGATE [STREAMING]
|  |  group by: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT))
|  |  mem-estimate=10.00MB mem-reservation=2.00MB spill-buffer=64.00KB thread-reservation=0
|  |  tuple-ids=4 row-size=12B cardinality=3.96K
|  |  in pipelines: 03(GETNEXT)
|  |
|  03:SCAN HDFS [tpcds_parquet.customer_address, RANDOM]
|     HDFS partitions=1/1 files=1 size=1.16MB
|     predicates: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT)) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
|     stored statistics:
|       table: rows=50.00K size=1.16MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=50.00K
|     parquet dictionary predicates: substring(ca_zip, CAST(1 AS BIGINT), CAST(5 AS BIGINT)) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
|     mem-estimate=16.00MB mem-reservation=128.00KB thread-reservation=0
|     tuple-ids=3 row-size=17B cardinality=5.00K
|     in pipelines: 03(GETNEXT)
|
11:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=03
|  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,2 row-size=61B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 02(OPEN)
|
|--F13:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
|  |  Per-Instance Resources: mem-estimate=4.89MB mem-reservation=4.88MB thread-reservation=1 runtime-filters-memory=1.00MB
|  JOIN BUILD
|  |  join-table-id=03 plan-id=04 cohort-id=01
|  |  build expressions: s_store_sk
|  |  runtime filters: RF002[bloom] <- s_store_sk
|  |  mem-estimate=3.88MB mem-reservation=3.88MB spill-buffer=64.00KB thread-reservation=0
|  |
|  16:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=2 row-size=37B cardinality=12
|  |  in pipelines: 02(GETNEXT)
|  |
|  F02: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.00MB mem-reservation=24.00KB thread-reservation=1
|  02:SCAN HDFS [tpcds_parquet.store, RANDOM]
|     HDFS partitions=1/1 files=1 size=9.93KB
|     runtime filters: RF000[bloom] -> substring(s_zip, 1, 2)
|     stored statistics:
|       table: rows=12 size=9.93KB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=12
|     mem-estimate=16.00MB mem-reservation=24.00KB thread-reservation=0
|     tuple-ids=2 row-size=37B cardinality=12
|     in pipelines: 02(GETNEXT)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
|  hash-table-id=04
|  hash predicates: ss_sold_date_sk = d_date_sk
|  fk/pk conjuncts: ss_sold_date_sk = d_date_sk
|  mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0
|  tuple-ids=0,1 row-size=24B cardinality=293.73K
|  in pipelines: 00(GETNEXT), 01(OPEN)
|
|--F14:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
|  |  Per-Instance Resources: mem-estimate=4.89MB mem-reservation=4.88MB thread-reservation=1 runtime-filters-memory=1.00MB
|  JOIN BUILD
|  |  join-table-id=04 plan-id=05 cohort-id=01
|  |  build expressions: d_date_sk
|  |  runtime filters: RF004[bloom] <- d_date_sk
|  |  mem-estimate=3.88MB mem-reservation=3.88MB spill-buffer=64.00KB thread-reservation=0
|  |
|  15:EXCHANGE [BROADCAST]
|  |  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  |  tuple-ids=1 row-size=12B cardinality=186
|  |  in pipelines: 01(GETNEXT)
|  |
|  F01:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
|  Per-Instance Resources: mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=1
|  01:SCAN HDFS [tpcds_parquet.date_dim, RANDOM]
|     HDFS partitions=1/1 files=1 size=2.15MB
|     predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     stored statistics:
|       table: rows=73.05K size=2.15MB
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=73.05K
|     parquet statistics predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     parquet dictionary predicates: d_year = CAST(1998 AS INT), d_qoy = CAST(2 AS INT)
|     mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0
|     tuple-ids=1 row-size=12B cardinality=186
|     in pipelines: 01(GETNEXT)
|
00:SCAN HDFS [tpcds_parquet.store_sales, RANDOM]
   HDFS partitions=1824/1824 files=1824 size=200.95MB
   runtime filters: RF002[bloom] -> ss_store_sk, RF004[bloom] -> 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=16.00MB mem-reservation=1.00MB thread-reservation=0
   tuple-ids=0 row-size=12B cardinality=2.88M
   in pipelines: 00(GETNEXT)
====