| # TPCDS-Q21 |
| # start query 21 in stream 0 using template query21.tpl using seed 921979785 |
| select * |
| from(select w_warehouse_name |
| ,i_item_id |
| ,sum(case when d_date < cast ('2000-05-22' as date) |
| then inv_quantity_on_hand |
| else 0 end) as inv_before |
| ,sum(case when d_date >= cast ('2000-05-22' as date) |
| then inv_quantity_on_hand |
| else 0 end) as inv_after |
| from inventory |
| ,warehouse |
| ,item |
| ,date_dim |
| where i_current_price between 0.99 and 1.49 |
| and i_item_sk = inv_item_sk |
| and inv_warehouse_sk = w_warehouse_sk |
| and inv_date_sk = d_date_sk |
| and d_date between (cast ('2000-05-22' as date) - interval 30 days) |
| and (cast ('2000-05-22' as date) + interval 30 days) |
| group by w_warehouse_name, i_item_id) x |
| where (case when inv_before > 0 |
| then inv_after / inv_before |
| else null |
| end) between 2.0/3.0 and 3.0/2.0 |
| order by w_warehouse_name |
| ,i_item_id |
| limit 100; |
| |
| # end query 21 in stream 0 using template query21.tpl |
| ---- PLAN |
| Max Per-Host Resource Reservation: Memory=52.00MB Threads=1 |
| Per-Host Resource Estimates: Memory=346MB |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=346.11MB mem-reservation=52.00MB thread-reservation=1 runtime-filters-memory=3.00MB |
| | max-parallelism=1 segment-costs=[154409711, 4080496, 400] |
| PLAN-ROOT SINK |
| | output exprs: w_warehouse_name, i_item_id, inv_before, inv_after |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=400 |
| | |
| 08:TOP-N [LIMIT=100] |
| | order by: w_warehouse_name ASC, i_item_id ASC |
| | mem-estimate=7.04KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=6 row-size=72B cardinality=100 cost=4080496 |
| | in pipelines: 08(GETNEXT), 07(OPEN) |
| | |
| 13:TUPLE CACHE |
| | cache key: aaedc97b81968470e5e737959609eee9 |
| | input scan node ids: 0,2,3,1 |
| | estimated serialized size: 28.26MB |
| | estimated serialized size per node: 2.83MB |
| | cumulative processing cost: 154409711 |
| | cache read processing cost: 51790 |
| | cache write processing cost: 80016 |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=389.70K cost=0 |
| | in pipelines: 07(GETNEXT) |
| | |
| 07:AGGREGATE [FINALIZE] |
| | output: sum(CAST(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE CAST(0 AS INT) END AS BIGINT)), sum(CAST(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE CAST(0 AS INT) END AS BIGINT)) |
| | group by: w_warehouse_name, i_item_id |
| | having: (CASE WHEN sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) > CAST(0 AS BIGINT) THEN CAST(sum(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) / CAST(sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) ELSE NULL END) <= CAST(1.5 AS DOUBLE), (CASE WHEN sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) > CAST(0 AS BIGINT) THEN CAST(sum(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) / CAST(sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) ELSE NULL END) >= CAST(0.66666700000000000958522150540375150740146636962890625 AS DOUBLE) |
| | mem-estimate=312.36MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=389.70K cost=52513771 |
| | in pipelines: 07(GETNEXT), 00(OPEN) |
| | |
| 06:HASH JOIN [INNER JOIN] |
| | hash predicates: inv_warehouse_sk = w_warehouse_sk |
| | fk/pk conjuncts: inv_warehouse_sk = w_warehouse_sk |
| | runtime filters: RF000[bloom] <- w_warehouse_sk, RF001[min_max] <- w_warehouse_sk |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,2,3,1 row-size=92B cardinality=24.14M cost=10567819 |
| | in pipelines: 00(GETNEXT), 01(OPEN) |
| | |
| |--12:TUPLE CACHE |
| | | cache key: 6ffdb1b4bffa14530778a288224883b2 |
| | | input scan node ids: 1 |
| | | estimated serialized size: 793B |
| | | estimated serialized size per node: 793B |
| | | cumulative processing cost: 6 |
| | | cache read processing cost: 2 |
| | | cache write processing cost: 2 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=1 row-size=32B cardinality=22 cost=0 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | 01:SCAN HDFS [tpcds_partitioned_parquet_snap.warehouse] |
| | HDFS partitions=1/1 files=1 size=5.99KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | stored statistics: |
| | table: rows=22 size=5.99KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=22 |
| | mem-estimate=16.00MB mem-reservation=16.00KB thread-reservation=0 |
| | tuple-ids=1 row-size=32B cardinality=22 cost=6 |
| | in pipelines: 01(GETNEXT) |
| | |
| 05:HASH JOIN [INNER JOIN] |
| | hash predicates: inv_date_sk = d_date_sk |
| | fk/pk conjuncts: inv_date_sk = d_date_sk |
| | runtime filters: RF002[bloom] <- d_date_sk |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,2,3 row-size=60B cardinality=24.14M cost=10567858 |
| | in pipelines: 00(GETNEXT), 03(OPEN) |
| | |
| |--11:TUPLE CACHE |
| | | cache key: 529d511ea9c2fd1d5bc2588fdb56706f |
| | | input scan node ids: 3 |
| | | estimated serialized size: 732B |
| | | estimated serialized size per node: 732B |
| | | cumulative processing cost: 12520 |
| | | cache read processing cost: 8 |
| | | cache write processing cost: 1 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=3 row-size=8B cardinality=61 cost=0 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | 03:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | parquet statistics predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | parquet dictionary predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=3 row-size=8B cardinality=61 cost=12520 |
| | in pipelines: 03(GETNEXT) |
| | |
| 04:HASH JOIN [INNER JOIN] |
| | hash predicates: inv_item_sk = i_item_sk |
| | fk/pk conjuncts: inv_item_sk = i_item_sk |
| | runtime filters: RF004[bloom] <- i_item_sk, RF005[min_max] <- i_item_sk |
| | mem-estimate=2.88MB mem-reservation=2.88MB spill-buffer=128.00KB thread-reservation=0 |
| | tuple-ids=0,2 row-size=52B cardinality=24.14M(filtered from 103.30M) cost=24947665 |
| | in pipelines: 00(GETNEXT), 02(OPEN) |
| | |
| |--10:TUPLE CACHE |
| | | cache key: e16614d3b61fa01ae612c1645b02bfa1 |
| | | input scan node ids: 2 |
| | | estimated serialized size: 1.37MB |
| | | estimated serialized size per node: 351.56KB |
| | | cumulative processing cost: 144648 |
| | | cache read processing cost: 4784 |
| | | cache write processing cost: 3888 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=2 row-size=36B cardinality=36.00K cost=0 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | 02:SCAN HDFS [tpcds_partitioned_parquet_snap.item] |
| | HDFS partitions=1/1 files=1 size=33.54MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | stored statistics: |
| | table: rows=360.00K size=33.54MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=101.54K |
| | parquet statistics predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | parquet dictionary predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=2 row-size=36B cardinality=36.00K cost=144648 |
| | in pipelines: 02(GETNEXT) |
| | |
| 09:TUPLE CACHE |
| | cache key: 18e575645c7ef603f08e33024e6ccb6f |
| | input scan node ids: 0 |
| | estimated serialized size: 460.51MB |
| | estimated serialized size per node: 46.05MB |
| | cumulative processing cost: 55655424 |
| | cache read processing cost: 3208728 |
| | cache write processing cost: 1303772 |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0 row-size=16B cardinality=24.14M(filtered from 1.03G) cost=0 |
| | in pipelines: 00(GETNEXT) |
| | |
| 00:SCAN HDFS [tpcds_partitioned_parquet_snap.inventory] |
| HDFS partitions=261/261 files=261 size=5.10GB |
| deterministic scan range assignment: true |
| schedule scan ranges oldest to newest: true |
| runtime filters: RF001[min_max] -> inv_warehouse_sk, RF005[min_max] -> inv_item_sk, RF000[bloom] -> inv_warehouse_sk, RF002[bloom] -> inv_date_sk, RF004[bloom] -> inv_item_sk |
| stored statistics: |
| table: rows=1.03G size=5.10GB |
| partitions: 261/261 rows=1.03G |
| columns: all |
| extrapolated-rows=disabled max-scan-range-rows=3.98M est-scan-range=61(filtered from 261) |
| mem-estimate=16.00MB mem-reservation=256.00KB thread-reservation=0 |
| tuple-ids=0 row-size=16B cardinality=24.14M(filtered from 1.03G) cost=55655424 |
| in pipelines: 00(GETNEXT) |
| ---- DISTRIBUTEDPLAN |
| Max Per-Host Resource Reservation: Memory=192.52MB Threads=11 |
| Per-Host Resource Estimates: Memory=352MB |
| F05:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=4.07MB mem-reservation=4.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[439] cpu-comparison-result=36 [max(1 (self) vs 36 (sum children))] |
| PLAN-ROOT SINK |
| | output exprs: w_warehouse_name, i_item_id, inv_before, inv_after |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=400 |
| | |
| 14:MERGING-EXCHANGE [UNPARTITIONED] |
| | order by: w_warehouse_name ASC, i_item_id ASC |
| | limit: 100 |
| | mem-estimate=74.97KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=6 row-size=72B cardinality=100 cost=39 |
| | in pipelines: 08(GETNEXT) |
| | |
| F04:PLAN FRAGMENT [HASH(w_warehouse_name,i_item_id)] hosts=10 instances=10 (adjusted from 120) |
| Per-Instance Resources: mem-estimate=46.23MB mem-reservation=34.00MB thread-reservation=1 |
| max-parallelism=10 segment-costs=[62042311, 4080496, 220] cpu-comparison-result=36 [max(10 (self) vs 36 (sum children))] |
| 08:TOP-N [LIMIT=100] |
| | order by: w_warehouse_name ASC, i_item_id ASC |
| | mem-estimate=7.04KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=6 row-size=72B cardinality=100 cost=4080496 |
| | in pipelines: 08(GETNEXT), 13(OPEN) |
| | |
| 13:AGGREGATE [FINALIZE] |
| | output: sum:merge(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END), sum:merge(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) |
| | group by: w_warehouse_name, i_item_id |
| | having: (CASE WHEN sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) > CAST(0 AS BIGINT) THEN CAST(sum(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) / CAST(sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) ELSE NULL END) <= CAST(1.5 AS DOUBLE), (CASE WHEN sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) > CAST(0 AS BIGINT) THEN CAST(sum(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) / CAST(sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) ELSE NULL END) >= CAST(0.66666700000000000958522150540375150740146636962890625 AS DOUBLE) |
| | mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=389.70K cost=51629218 |
| | in pipelines: 13(GETNEXT), 00(OPEN) |
| | |
| 12:EXCHANGE [HASH(w_warehouse_name,i_item_id)] |
| | mem-estimate=12.23MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=23.53M cost=10413093 |
| | in pipelines: 00(GETNEXT) |
| | |
| F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=30 (adjusted from 120) |
| Per-Host Shared Resources: mem-estimate=3.00MB mem-reservation=3.00MB thread-reservation=0 runtime-filters-memory=3.00MB |
| Per-Instance Resources: mem-estimate=71.35MB mem-reservation=38.25MB thread-reservation=1 |
| max-parallelism=30 segment-costs=[243130875, 121305941] cpu-comparison-result=36 [max(30 (self) vs 36 (sum children))] |
| 07:AGGREGATE [STREAMING] |
| | output: sum(CAST(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE CAST(0 AS INT) END AS BIGINT)), sum(CAST(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE CAST(0 AS INT) END AS BIGINT)) |
| | group by: w_warehouse_name, i_item_id |
| | mem-estimate=48.38MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=23.53M cost=141428192 |
| | in pipelines: 00(GETNEXT) |
| | |
| 06:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=00 |
| | hash predicates: inv_warehouse_sk = w_warehouse_sk |
| | fk/pk conjuncts: inv_warehouse_sk = w_warehouse_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,2,3,1 row-size=92B cardinality=24.14M cost=10567797 |
| | in pipelines: 00(GETNEXT), 01(OPEN) |
| | |
| |--F06:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=6.83MB mem-reservation=6.81MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[42] |
| | JOIN BUILD |
| | | join-table-id=00 plan-id=01 cohort-id=01 |
| | | build expressions: w_warehouse_sk |
| | | runtime filters: RF000[bloom] <- w_warehouse_sk, RF001[min_max] <- w_warehouse_sk |
| | | mem-estimate=5.81MB mem-reservation=5.81MB spill-buffer=64.00KB thread-reservation=0 cost=22 |
| | | |
| | 11:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=1 row-size=32B cardinality=22 cost=20 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.14MB mem-reservation=4.02MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[8] |
| | 18:TUPLE CACHE |
| | | cache key: 6ffdb1b4bffa14530778a288224883b2 |
| | | input scan node ids: 1 |
| | | estimated serialized size: 793B |
| | | estimated serialized size per node: 793B |
| | | cumulative processing cost: 6 |
| | | cache read processing cost: 2 |
| | | cache write processing cost: 2 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=1 row-size=32B cardinality=22 cost=0 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | 01:SCAN HDFS [tpcds_partitioned_parquet_snap.warehouse, RANDOM] |
| | HDFS partitions=1/1 files=1 size=5.99KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | stored statistics: |
| | table: rows=22 size=5.99KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=22 |
| | mem-estimate=16.00MB mem-reservation=16.00KB thread-reservation=0 |
| | tuple-ids=1 row-size=32B cardinality=22 cost=6 |
| | in pipelines: 01(GETNEXT) |
| | |
| 05:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=01 |
| | hash predicates: inv_date_sk = d_date_sk |
| | fk/pk conjuncts: inv_date_sk = d_date_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,2,3 row-size=60B cardinality=24.14M cost=10567797 |
| | in pipelines: 00(GETNEXT), 03(OPEN) |
| | |
| |--F07:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=6.83MB mem-reservation=6.81MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[141] |
| | JOIN BUILD |
| | | join-table-id=01 plan-id=02 cohort-id=01 |
| | | build expressions: d_date_sk |
| | | runtime filters: RF002[bloom] <- d_date_sk |
| | | mem-estimate=5.81MB mem-reservation=5.81MB spill-buffer=64.00KB thread-reservation=0 cost=61 |
| | | |
| | 10:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=3 row-size=8B cardinality=61 cost=80 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | F02:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.05MB mem-reservation=5.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[12522] |
| | 17:TUPLE CACHE |
| | | cache key: 529d511ea9c2fd1d5bc2588fdb56706f |
| | | input scan node ids: 3 |
| | | estimated serialized size: 732B |
| | | estimated serialized size per node: 732B |
| | | cumulative processing cost: 12520 |
| | | cache read processing cost: 8 |
| | | cache write processing cost: 1 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=3 row-size=8B cardinality=61 cost=0 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | 03:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | parquet statistics predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | parquet dictionary predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=3 row-size=8B cardinality=61 cost=12520 |
| | in pipelines: 03(GETNEXT) |
| | |
| 04:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=02 |
| | hash predicates: inv_item_sk = i_item_sk |
| | fk/pk conjuncts: inv_item_sk = i_item_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=128.00KB thread-reservation=0 |
| | tuple-ids=0,2 row-size=52B cardinality=24.14M(filtered from 103.30M) cost=24911665 |
| | in pipelines: 00(GETNEXT), 02(OPEN) |
| | |
| |--F08:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=11.02MB mem-reservation=9.62MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[83840] |
| | JOIN BUILD |
| | | join-table-id=02 plan-id=03 cohort-id=01 |
| | | build expressions: i_item_sk |
| | | runtime filters: RF004[bloom] <- i_item_sk, RF005[min_max] <- i_item_sk |
| | | mem-estimate=8.62MB mem-reservation=8.62MB spill-buffer=128.00KB thread-reservation=0 cost=36000 |
| | | |
| | 09:EXCHANGE [BROADCAST] |
| | | mem-estimate=1.39MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=2 row-size=36B cardinality=36.00K cost=47840 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | F01:PLAN FRAGMENT [RANDOM] hosts=4 instances=4 |
| | Per-Instance Resources: mem-estimate=20.16MB mem-reservation=4.50MB thread-reservation=1 |
| | max-parallelism=4 segment-costs=[148924] |
| | 16:TUPLE CACHE |
| | | cache key: e16614d3b61fa01ae612c1645b02bfa1 |
| | | input scan node ids: 2 |
| | | estimated serialized size: 1.37MB |
| | | estimated serialized size per node: 351.56KB |
| | | cumulative processing cost: 144648 |
| | | cache read processing cost: 4784 |
| | | cache write processing cost: 3888 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=2 row-size=36B cardinality=36.00K cost=0 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | 02:SCAN HDFS [tpcds_partitioned_parquet_snap.item, RANDOM] |
| | HDFS partitions=1/1 files=1 size=33.54MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | stored statistics: |
| | table: rows=360.00K size=33.54MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=101.54K |
| | parquet statistics predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | parquet dictionary predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=2 row-size=36B cardinality=36.00K cost=144648 |
| | in pipelines: 02(GETNEXT) |
| | |
| 15:TUPLE CACHE |
| | cache key: 215cc8a0feeccc17372358d999a36a02 |
| | input scan node ids: 0 |
| | estimated serialized size: 460.51MB |
| | estimated serialized size per node: 46.05MB |
| | cumulative processing cost: 55655424 |
| | cache read processing cost: 3208728 |
| | cache write processing cost: 1303772 |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0 row-size=16B cardinality=24.14M(filtered from 1.03G) cost=0 |
| | in pipelines: 00(GETNEXT) |
| | |
| 00:SCAN HDFS [tpcds_partitioned_parquet_snap.inventory, RANDOM] |
| HDFS partitions=261/261 files=261 size=5.10GB |
| deterministic scan range assignment: true |
| schedule scan ranges oldest to newest: true |
| runtime filters: RF001[min_max] -> inv_warehouse_sk, RF005[min_max] -> inv_item_sk, RF000[bloom] -> inv_warehouse_sk, RF002[bloom] -> inv_date_sk, RF004[bloom] -> inv_item_sk |
| stored statistics: |
| table: rows=1.03G size=5.10GB |
| partitions: 261/261 rows=1.03G |
| columns: all |
| extrapolated-rows=disabled max-scan-range-rows=3.98M est-scan-range=61(filtered from 261) |
| mem-estimate=16.00MB mem-reservation=256.00KB thread-reservation=0 |
| tuple-ids=0 row-size=16B cardinality=24.14M(filtered from 1.03G) cost=55655424 |
| in pipelines: 00(GETNEXT) |
| ---- PARALLELPLANS |
| Max Per-Host Resource Reservation: Memory=192.52MB Threads=11 |
| Per-Host Resource Estimates: Memory=352MB |
| F05:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=4.07MB mem-reservation=4.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[439] cpu-comparison-result=36 [max(1 (self) vs 36 (sum children))] |
| PLAN-ROOT SINK |
| | output exprs: w_warehouse_name, i_item_id, inv_before, inv_after |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=400 |
| | |
| 14:MERGING-EXCHANGE [UNPARTITIONED] |
| | order by: w_warehouse_name ASC, i_item_id ASC |
| | limit: 100 |
| | mem-estimate=74.97KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=6 row-size=72B cardinality=100 cost=39 |
| | in pipelines: 08(GETNEXT) |
| | |
| F04:PLAN FRAGMENT [HASH(w_warehouse_name,i_item_id)] hosts=10 instances=10 (adjusted from 120) |
| Per-Instance Resources: mem-estimate=46.23MB mem-reservation=34.00MB thread-reservation=1 |
| max-parallelism=10 segment-costs=[62042311, 4080496, 220] cpu-comparison-result=36 [max(10 (self) vs 36 (sum children))] |
| 08:TOP-N [LIMIT=100] |
| | order by: w_warehouse_name ASC, i_item_id ASC |
| | mem-estimate=7.04KB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=6 row-size=72B cardinality=100 cost=4080496 |
| | in pipelines: 08(GETNEXT), 13(OPEN) |
| | |
| 13:AGGREGATE [FINALIZE] |
| | output: sum:merge(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END), sum:merge(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) |
| | group by: w_warehouse_name, i_item_id |
| | having: (CASE WHEN sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) > CAST(0 AS BIGINT) THEN CAST(sum(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) / CAST(sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) ELSE NULL END) <= CAST(1.5 AS DOUBLE), (CASE WHEN sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) > CAST(0 AS BIGINT) THEN CAST(sum(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) / CAST(sum(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE 0 END) AS DOUBLE) ELSE NULL END) >= CAST(0.66666700000000000958522150540375150740146636962890625 AS DOUBLE) |
| | mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=389.70K cost=51629218 |
| | in pipelines: 13(GETNEXT), 00(OPEN) |
| | |
| 12:EXCHANGE [HASH(w_warehouse_name,i_item_id)] |
| | mem-estimate=12.23MB mem-reservation=0B thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=23.53M cost=10413093 |
| | in pipelines: 00(GETNEXT) |
| | |
| F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=30 (adjusted from 120) |
| Per-Host Shared Resources: mem-estimate=3.00MB mem-reservation=3.00MB thread-reservation=0 runtime-filters-memory=3.00MB |
| Per-Instance Resources: mem-estimate=71.35MB mem-reservation=38.25MB thread-reservation=1 |
| max-parallelism=30 segment-costs=[243130875, 121305941] cpu-comparison-result=36 [max(30 (self) vs 36 (sum children))] |
| 07:AGGREGATE [STREAMING] |
| | output: sum(CAST(CASE WHEN d_date < DATE '2000-05-22' THEN inv_quantity_on_hand ELSE CAST(0 AS INT) END AS BIGINT)), sum(CAST(CASE WHEN d_date >= DATE '2000-05-22' THEN inv_quantity_on_hand ELSE CAST(0 AS INT) END AS BIGINT)) |
| | group by: w_warehouse_name, i_item_id |
| | mem-estimate=48.38MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=4 row-size=72B cardinality=23.53M cost=141428192 |
| | in pipelines: 00(GETNEXT) |
| | |
| 06:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=00 |
| | hash predicates: inv_warehouse_sk = w_warehouse_sk |
| | fk/pk conjuncts: inv_warehouse_sk = w_warehouse_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,2,3,1 row-size=92B cardinality=24.14M cost=10567797 |
| | in pipelines: 00(GETNEXT), 01(OPEN) |
| | |
| |--F06:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=6.83MB mem-reservation=6.81MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[42] |
| | JOIN BUILD |
| | | join-table-id=00 plan-id=01 cohort-id=01 |
| | | build expressions: w_warehouse_sk |
| | | runtime filters: RF000[bloom] <- w_warehouse_sk, RF001[min_max] <- w_warehouse_sk |
| | | mem-estimate=5.81MB mem-reservation=5.81MB spill-buffer=64.00KB thread-reservation=0 cost=22 |
| | | |
| | 11:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=1 row-size=32B cardinality=22 cost=20 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | F03:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.14MB mem-reservation=4.02MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[8] |
| | 18:TUPLE CACHE |
| | | cache key: 6ffdb1b4bffa14530778a288224883b2 |
| | | input scan node ids: 1 |
| | | estimated serialized size: 793B |
| | | estimated serialized size per node: 793B |
| | | cumulative processing cost: 6 |
| | | cache read processing cost: 2 |
| | | cache write processing cost: 2 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=1 row-size=32B cardinality=22 cost=0 |
| | | in pipelines: 01(GETNEXT) |
| | | |
| | 01:SCAN HDFS [tpcds_partitioned_parquet_snap.warehouse, RANDOM] |
| | HDFS partitions=1/1 files=1 size=5.99KB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | stored statistics: |
| | table: rows=22 size=5.99KB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=22 |
| | mem-estimate=16.00MB mem-reservation=16.00KB thread-reservation=0 |
| | tuple-ids=1 row-size=32B cardinality=22 cost=6 |
| | in pipelines: 01(GETNEXT) |
| | |
| 05:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=01 |
| | hash predicates: inv_date_sk = d_date_sk |
| | fk/pk conjuncts: inv_date_sk = d_date_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=64.00KB thread-reservation=0 |
| | tuple-ids=0,2,3 row-size=60B cardinality=24.14M cost=10567797 |
| | in pipelines: 00(GETNEXT), 03(OPEN) |
| | |
| |--F07:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=6.83MB mem-reservation=6.81MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[141] |
| | JOIN BUILD |
| | | join-table-id=01 plan-id=02 cohort-id=01 |
| | | build expressions: d_date_sk |
| | | runtime filters: RF002[bloom] <- d_date_sk |
| | | mem-estimate=5.81MB mem-reservation=5.81MB spill-buffer=64.00KB thread-reservation=0 cost=61 |
| | | |
| | 10:EXCHANGE [BROADCAST] |
| | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=3 row-size=8B cardinality=61 cost=80 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | F02:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 |
| | Per-Instance Resources: mem-estimate=20.05MB mem-reservation=5.00MB thread-reservation=1 |
| | max-parallelism=1 segment-costs=[12522] |
| | 17:TUPLE CACHE |
| | | cache key: 529d511ea9c2fd1d5bc2588fdb56706f |
| | | input scan node ids: 3 |
| | | estimated serialized size: 732B |
| | | estimated serialized size per node: 732B |
| | | cumulative processing cost: 12520 |
| | | cache read processing cost: 8 |
| | | cache write processing cost: 1 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=3 row-size=8B cardinality=61 cost=0 |
| | | in pipelines: 03(GETNEXT) |
| | | |
| | 03:SCAN HDFS [tpcds_partitioned_parquet_snap.date_dim, RANDOM] |
| | HDFS partitions=1/1 files=1 size=2.17MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | stored statistics: |
| | table: rows=73.05K size=2.17MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=73.05K |
| | parquet statistics predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | parquet dictionary predicates: d_date <= DATE '2000-06-21', d_date >= DATE '2000-04-22' |
| | mem-estimate=16.00MB mem-reservation=1.00MB thread-reservation=0 |
| | tuple-ids=3 row-size=8B cardinality=61 cost=12520 |
| | in pipelines: 03(GETNEXT) |
| | |
| 04:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash-table-id=02 |
| | hash predicates: inv_item_sk = i_item_sk |
| | fk/pk conjuncts: inv_item_sk = i_item_sk |
| | mem-estimate=0B mem-reservation=0B spill-buffer=128.00KB thread-reservation=0 |
| | tuple-ids=0,2 row-size=52B cardinality=24.14M(filtered from 103.30M) cost=24911665 |
| | in pipelines: 00(GETNEXT), 02(OPEN) |
| | |
| |--F08:PLAN FRAGMENT [RANDOM] hosts=10 instances=10 |
| | | Per-Instance Resources: mem-estimate=11.02MB mem-reservation=9.62MB thread-reservation=1 runtime-filters-memory=1.00MB |
| | | max-parallelism=10 segment-costs=[83840] |
| | JOIN BUILD |
| | | join-table-id=02 plan-id=03 cohort-id=01 |
| | | build expressions: i_item_sk |
| | | runtime filters: RF004[bloom] <- i_item_sk, RF005[min_max] <- i_item_sk |
| | | mem-estimate=8.62MB mem-reservation=8.62MB spill-buffer=128.00KB thread-reservation=0 cost=36000 |
| | | |
| | 09:EXCHANGE [BROADCAST] |
| | | mem-estimate=1.39MB mem-reservation=0B thread-reservation=0 |
| | | tuple-ids=2 row-size=36B cardinality=36.00K cost=47840 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | F01:PLAN FRAGMENT [RANDOM] hosts=4 instances=4 |
| | Per-Instance Resources: mem-estimate=20.16MB mem-reservation=4.50MB thread-reservation=1 |
| | max-parallelism=4 segment-costs=[148924] |
| | 16:TUPLE CACHE |
| | | cache key: e16614d3b61fa01ae612c1645b02bfa1 |
| | | input scan node ids: 2 |
| | | estimated serialized size: 1.37MB |
| | | estimated serialized size per node: 351.56KB |
| | | cumulative processing cost: 144648 |
| | | cache read processing cost: 4784 |
| | | cache write processing cost: 3888 |
| | | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | | tuple-ids=2 row-size=36B cardinality=36.00K cost=0 |
| | | in pipelines: 02(GETNEXT) |
| | | |
| | 02:SCAN HDFS [tpcds_partitioned_parquet_snap.item, RANDOM] |
| | HDFS partitions=1/1 files=1 size=33.54MB |
| | deterministic scan range assignment: true |
| | schedule scan ranges oldest to newest: true |
| | predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | stored statistics: |
| | table: rows=360.00K size=33.54MB |
| | columns: all |
| | extrapolated-rows=disabled max-scan-range-rows=101.54K |
| | parquet statistics predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | parquet dictionary predicates: i_current_price <= CAST(1.49 AS DECIMAL(3,2)), i_current_price >= CAST(0.99 AS DECIMAL(2,2)) |
| | mem-estimate=16.00MB mem-reservation=512.00KB thread-reservation=0 |
| | tuple-ids=2 row-size=36B cardinality=36.00K cost=144648 |
| | in pipelines: 02(GETNEXT) |
| | |
| 15:TUPLE CACHE |
| | cache key: 215cc8a0feeccc17372358d999a36a02 |
| | input scan node ids: 0 |
| | estimated serialized size: 460.51MB |
| | estimated serialized size per node: 46.05MB |
| | cumulative processing cost: 55655424 |
| | cache read processing cost: 3208728 |
| | cache write processing cost: 1303772 |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 |
| | tuple-ids=0 row-size=16B cardinality=24.14M(filtered from 1.03G) cost=0 |
| | in pipelines: 00(GETNEXT) |
| | |
| 00:SCAN HDFS [tpcds_partitioned_parquet_snap.inventory, RANDOM] |
| HDFS partitions=261/261 files=261 size=5.10GB |
| deterministic scan range assignment: true |
| schedule scan ranges oldest to newest: true |
| runtime filters: RF001[min_max] -> inv_warehouse_sk, RF005[min_max] -> inv_item_sk, RF000[bloom] -> inv_warehouse_sk, RF002[bloom] -> inv_date_sk, RF004[bloom] -> inv_item_sk |
| stored statistics: |
| table: rows=1.03G size=5.10GB |
| partitions: 261/261 rows=1.03G |
| columns: all |
| extrapolated-rows=disabled max-scan-range-rows=3.98M est-scan-range=61(filtered from 261) |
| mem-estimate=16.00MB mem-reservation=256.00KB thread-reservation=0 |
| tuple-ids=0 row-size=16B cardinality=24.14M(filtered from 1.03G) cost=55655424 |
| in pipelines: 00(GETNEXT) |
| ==== |
