Created
September 9, 2025 17:38
-
-
Save AdamGS/cea5816b321ca70323975c05d6048f36 to your computer and use it in GitHub Desktop.
TPC-DS q72 plan
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ┌───────────────────────────┐ | |
| │ SortPreservingMergeExec │ | |
| │ -------------------- │ | |
| │ total_cnt DESC, │ | |
| │ i_item_desc │ | |
| │ ASC, w_warehouse_name │ | |
| │ ASC, d_week_seq │ | |
| │ ASClimit: │ | |
| │ 100 │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ SortExec(TopK) │ | |
| │ -------------------- │ | |
| │ limit: 100 │ | |
| │ │ | |
| │ total_cnt@5 DESC, │ | |
| │ i_item_desc@0 │ | |
| │ ASC, w_warehouse_name │ | |
| │ @1 ASC, d_week_seq@2 ASC │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ ProjectionExec │ | |
| │ -------------------- │ | |
| │ d_week_seq: │ | |
| │ d_week_seq │ | |
| │ │ | |
| │ i_item_desc: │ | |
| │ i_item_desc │ | |
| │ │ | |
| │ no_promo: │ | |
| │ sum(CASE WHEN promotion │ | |
| │ .p_promo_sk IS NULL │ | |
| │ THEN Int64(1) ELSE │ | |
| │ Int64(0) END) │ | |
| │ │ | |
| │ promo: │ | |
| │ sum(CASE WHEN promotion │ | |
| │ .p_promo_sk IS NOT │ | |
| │ NULL THEN Int64(1) │ | |
| │ ELSE Int64(0) END) │ | |
| │ │ | |
| │ total_cnt: │ | |
| │ count(Int64(1)) │ | |
| │ │ | |
| │ w_warehouse_name: │ | |
| │ w_warehouse_name │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ AggregateExec │ | |
| │ -------------------- │ | |
| │ aggr: │ | |
| │ sum(CASE WHEN promotion │ | |
| │ .p_promo_sk IS NULL │ | |
| │ THEN 1 ELSE 0 END), │ | |
| │ sum(CASE WHEN │ | |
| │ promotion │ | |
| │ .p_promo_sk IS NOT │ | |
| │ NULL THEN 1 ELSE 0 END) │ | |
| │ , count(1) │ | |
| │ │ | |
| │ group_by: │ | |
| │ i_item_desc, │ | |
| │ w_warehouse │ | |
| │ _name, d_week_seq │ | |
| │ │ | |
| │ mode: │ | |
| │ FinalPartitioned │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec │ | |
| │ -------------------- │ | |
| │ target_batch_size: │ | |
| │ 8192 │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ RepartitionExec │ | |
| │ -------------------- │ | |
| │ partition_count(in->out): │ | |
| │ 14 -> 14 │ | |
| │ │ | |
| │ partitioning_scheme: │ | |
| │ Hash([i_item_desc@0, │ | |
| │ w_warehouse_name@1 │ | |
| │ , d_week_seq@2], 14) │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ AggregateExec │ | |
| │ -------------------- │ | |
| │ aggr: │ | |
| │ sum(CASE WHEN promotion │ | |
| │ .p_promo_sk IS NULL │ | |
| │ THEN 1 ELSE 0 END), │ | |
| │ sum(CASE WHEN │ | |
| │ promotion │ | |
| │ .p_promo_sk IS NOT │ | |
| │ NULL THEN 1 ELSE 0 END) │ | |
| │ , count(1) │ | |
| │ │ | |
| │ group_by: │ | |
| │ i_item_desc, │ | |
| │ w_warehouse │ | |
| │ _name, d_week_seq │ | |
| │ │ | |
| │ mode: Partial │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec │ | |
| │ -------------------- │ | |
| │ target_batch_size: │ | |
| │ 8192 │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ HashJoinExec │ | |
| │ -------------------- │ | |
| │ join_type: Right │ | |
| │ │ | |
| │ on: ├──────────────┐ | |
| │ (cr_item_sk = cs_item_sk),│ │ | |
| │ (cr_order_number = │ │ | |
| │ cs_order_number) │ │ | |
| └─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ target_batch_size: ││ target_batch_size: │ | |
| │ 8192 ││ 8192 │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ RepartitionExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ partition_count(in->out): │ | |
| │ 14 -> 14 ││ 14 -> 14 │ | |
| │ ││ │ | |
| │ partitioning_scheme: ││ partitioning_scheme: │ | |
| │ Hash([cr_item_sk@0, ││ Hash([cs_item_sk@0, │ | |
| │ cr_order_number@1 ││ cs_order_number@1 │ | |
| │ ], 14) ││ ], 14) │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ ProjectionExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ files: 14 ││ cs_item_sk: │ | |
| │ format: parquet ││ cs_item_sk │ | |
| │ ││ │ | |
| │ ││ cs_order_number: │ | |
| │ ││ cs_order_number │ | |
| │ ││ │ | |
| │ ││ d_week_seq: │ | |
| │ ││ d_week_seq │ | |
| │ ││ │ | |
| │ ││ i_item_desc: │ | |
| │ ││ i_item_desc │ | |
| │ ││ │ | |
| │ ││ p_promo_sk: │ | |
| │ ││ p_promo_sk │ | |
| │ ││ │ | |
| │ ││ w_warehouse_name: │ | |
| │ ││ w_warehouse_name │ | |
| └───────────────────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec │ | |
| │ -------------------- │ | |
| │ target_batch_size: │ | |
| │ 8192 │ | |
| └─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ HashJoinExec │ | |
| │ -------------------- │ | |
| │ join_type: Right │ | |
| │ ├──────────────┐ | |
| │ on: │ │ | |
| │ (p_promo_sk = cs_promo_sk)│ │ | |
| └─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ target_batch_size: │ | |
| │ format: parquet ││ 8192 │ | |
| └───────────────────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ HashJoinExec │ | |
| │ -------------------- │ | |
| │ filter: │ | |
| │ d_date > d_date + │ | |
| │ IntervalMonthDay │ | |
| │ Nano { months: 0, days: 5,│ | |
| │ nanoseconds: 0 } ├──────────────┐ | |
| │ │ │ | |
| │ on: │ │ | |
| │ (d_date_sk = │ │ | |
| │ cs_ship_dat │ │ | |
| │ e_sk) │ │ | |
| └─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ target_batch_size: ││ target_batch_size: │ | |
| │ 8192 ││ 8192 │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ RepartitionExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ partition_count(in->out): │ | |
| │ 14 -> 14 ││ 14 -> 14 │ | |
| │ ││ │ | |
| │ partitioning_scheme: ││ partitioning_scheme: │ | |
| │ Hash([d_date_sk@0], 14) ││ Hash([cs_ship_date_sk@0], │ | |
| │ ││ 14) │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ target_batch_size: │ | |
| │ 1 -> 14 ││ 8192 │ | |
| │ ││ │ | |
| │ partitioning_scheme: ││ │ | |
| │ RoundRobinBatch(14) ││ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ HashJoinExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ on: ├──────────────┐ | |
| │ format: parquet ││ (d_date_sk = inv_date_sk),│ │ | |
| │ ││ (d_week_seq = d_week_seq)│ │ | |
| └───────────────────────────┘└─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ target_batch_size: ││ target_batch_size: │ | |
| │ 8192 ││ 8192 │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ RepartitionExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ partition_count(in->out): │ | |
| │ 14 -> 14 ││ 14 -> 14 │ | |
| │ ││ │ | |
| │ partitioning_scheme: ││ partitioning_scheme: │ | |
| │ Hash([d_date_sk@0, ││ Hash([inv_date_sk@4, │ | |
| │ d_week_seq@1], ││ d_week_seq@8], 14) │ | |
| │ 14) ││ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ ProjectionExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ cs_item_sk: │ | |
| │ 1 -> 14 ││ cs_item_sk │ | |
| │ ││ │ | |
| │ partitioning_scheme: ││ cs_order_number: │ | |
| │ RoundRobinBatch(14) ││ cs_order_number │ | |
| │ ││ │ | |
| │ ││ cs_promo_sk: │ | |
| │ ││ cs_promo_sk │ | |
| │ ││ │ | |
| │ ││ cs_ship_date_sk: │ | |
| │ ││ cs_ship_date_sk │ | |
| │ ││ │ | |
| │ ││ d_date: d_date │ | |
| │ ││ │ | |
| │ ││ d_week_seq: │ | |
| │ ││ d_week_seq │ | |
| │ ││ │ | |
| │ ││ i_item_desc: │ | |
| │ ││ i_item_desc │ | |
| │ ││ │ | |
| │ ││ inv_date_sk: │ | |
| │ ││ inv_date_sk │ | |
| │ ││ │ | |
| │ ││ w_warehouse_name: │ | |
| │ ││ w_warehouse_name │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ target_batch_size: │ | |
| │ format: parquet ││ 8192 │ | |
| └───────────────────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐ | |
| │ HashJoinExec │ | |
| │ -------------------- │ | |
| │ on: │ | |
| │ (d_date_sk = ├──────────────┐ | |
| │ cs_sold_dat │ │ | |
| │ e_sk) │ │ | |
| └─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ CoalescePartitionsExec ││ CoalesceBatchesExec │ | |
| │ ││ -------------------- │ | |
| │ ││ target_batch_size: │ | |
| │ ││ 8192 │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ CoalesceBatchesExec ││ HashJoinExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ target_batch_size: ││ on: │ | |
| │ 8192 ││ (hd_demo_sk = ├──────────────┐ | |
| │ ││ cs_bill_hdem │ │ | |
| │ ││ o_sk) │ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ FilterExec ││ CoalescePartitionsExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ ││ -------------------- │ | |
| │ predicate: ││ ││ target_batch_size: │ | |
| │ d_year = 1999 ││ ││ 8192 │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ CoalesceBatchesExec ││ HashJoinExec │ | |
| │ -------------------- ││ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ target_batch_size: ││ on: │ | |
| │ 1 -> 14 ││ 8192 ││ (cd_demo_sk = ├──────────────┐ | |
| │ ││ ││ cs_bill_cdem │ │ | |
| │ partitioning_scheme: ││ ││ o_sk) │ │ | |
| │ RoundRobinBatch(14) ││ ││ │ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ FilterExec ││ CoalesceBatchesExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- ││ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ predicate: ││ target_batch_size: ││ target_batch_size: │ | |
| │ format: parquet ││ hd_buy_potential = >10000 ││ 8192 ││ 8192 │ | |
| │ ││ ││ ││ │ | |
| │ predicate: ││ ││ ││ │ | |
| │ d_year = 1999 ││ ││ ││ │ | |
| └───────────────────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ RepartitionExec ││ RepartitionExec │ | |
| │ -------------------- ││ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ partition_count(in->out): ││ partition_count(in->out): │ | |
| │ 1 -> 14 ││ 14 -> 14 ││ 14 -> 14 │ | |
| │ ││ ││ │ | |
| │ partitioning_scheme: ││ partitioning_scheme: ││ partitioning_scheme: │ | |
| │ RoundRobinBatch(14) ││ Hash([cd_demo_sk@0], 14) ││ Hash([cs_bill_cdemo_sk@2],│ | |
| │ ││ ││ 14) │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ CoalesceBatchesExec ││ ProjectionExec │ | |
| │ -------------------- ││ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ target_batch_size: ││ cs_bill_cdemo_sk: │ | |
| │ format: parquet ││ 8192 ││ cs_bill_cdemo_sk │ | |
| │ ││ ││ │ | |
| │ predicate: ││ ││ cs_bill_hdemo_sk: │ | |
| │ hd_buy_potential = >10000 ││ ││ cs_bill_hdemo_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_item_sk: │ | |
| │ ││ ││ cs_item_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_order_number: │ | |
| │ ││ ││ cs_order_number │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_promo_sk: │ | |
| │ ││ ││ cs_promo_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_ship_date_sk: │ | |
| │ ││ ││ cs_ship_date_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_sold_date_sk: │ | |
| │ ││ ││ cs_sold_date_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ i_item_desc: │ | |
| │ ││ ││ i_item_desc │ | |
| │ ││ ││ │ | |
| │ ││ ││ inv_date_sk: │ | |
| │ ││ ││ inv_date_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ w_warehouse_name: │ | |
| │ ││ ││ w_warehouse_name │ | |
| └───────────────────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ FilterExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ predicate: ││ target_batch_size: │ | |
| │ cd_marital_status = D ││ 8192 │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ HashJoinExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ on: │ | |
| │ 1 -> 14 ││ (i_item_sk = cs_item_sk) ├──────────────┐ | |
| │ ││ │ │ | |
| │ partitioning_scheme: ││ │ │ | |
| │ RoundRobinBatch(14) ││ │ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ │ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ CoalesceBatchesExec ││ ProjectionExec │ | |
| │ -------------------- ││ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ target_batch_size: ││ cs_bill_cdemo_sk: │ | |
| │ format: parquet ││ 8192 ││ cs_bill_cdemo_sk │ | |
| │ ││ ││ │ | |
| │ predicate: ││ ││ cs_bill_hdemo_sk: │ | |
| │ cd_marital_status = D ││ ││ cs_bill_hdemo_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_item_sk: │ | |
| │ ││ ││ cs_item_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_order_number: │ | |
| │ ││ ││ cs_order_number │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_promo_sk: │ | |
| │ ││ ││ cs_promo_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_ship_date_sk: │ | |
| │ ││ ││ cs_ship_date_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ cs_sold_date_sk: │ | |
| │ ││ ││ cs_sold_date_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ inv_date_sk: │ | |
| │ ││ ││ inv_date_sk │ | |
| │ ││ ││ │ | |
| │ ││ ││ w_warehouse_name: │ | |
| │ ││ ││ w_warehouse_name │ | |
| └───────────────────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ CoalesceBatchesExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ target_batch_size: │ | |
| │ 14 -> 14 ││ 8192 │ | |
| │ ││ │ | |
| │ partitioning_scheme: ││ │ | |
| │ Hash([i_item_sk@0], 14) ││ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ RepartitionExec ││ HashJoinExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ partition_count(in->out): ││ on: │ | |
| │ 1 -> 14 ││ (w_warehouse_sk = ├ | |
| │ ││ inv_warehouse_sk │ | |
| │ partitioning_scheme: ││ ) │ | |
| │ RoundRobinBatch(14) ││ │ | |
| └─────────────┬─────────────┘└─────────────┬─────────────┘ | |
| ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ | |
| │ DataSourceExec ││ DataSourceExec │ | |
| │ -------------------- ││ -------------------- │ | |
| │ files: 1 ││ files: 1 │ | |
| │ format: parquet ││ format: parquet │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| │ ││ │ | |
| └───────────────────────────┘└───────────────────────────┘ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment