TPC-DS query performance improvements

[linhr]

This is a follow-up for #747.

We'd like to further improve TPC-DS queries to achieve similar performance as other well-optimized engines such as DuckDB. Some collaboration with DataFusion may be needed as well, since some work may need to happen for particular physical plan nodes or the physical optimizer.

The improvements will apply to both Parquet and lakehouse formats (e.g. Delta Lake) built on top of Parquet.

[keen85]

Hi @linhr ,
I worked on integrating Sail into LakeBench.
I did some testing and compared performance of Sail and DuckDB. So far my verdict is that Sail outruns DuckDB nearly on all queries - except for TPC-DS query 72 where Sail is magnitudes slower.

The query involves several joins and my theory is that Sail is missing some predicated pushdown that would reduce the join result.

Would it be helpful if I post the execution plan of Sail and DuckDB here?

[linhr]

Would it be helpful if I post the execution plan of Sail and DuckDB here?

That would be great!

[keen85]

Hi @linhr

I collected the query plans for both engines DuckDB and Sail, hope this helps.

Both engines execute the query on Delta Lake tables that were created by the engines themselves prior. So both queries run on different Delta Tables containing the same data.

This is the actual optimized logical and physical plan of DuckDB (via EXPLAIN ANALYZE):

┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││               Total Time: 5.37s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────┐
│       QUERY       │
└─────────┬─────────┘
┌─────────┴─────────┐
│  EXPLAIN_ANALYZE  │
│    ────────────   │
│       0 Rows      │
│      (0.00s)      │
└─────────┬─────────┘
┌─────────┴─────────┐
│       TOP_N       │
│    ────────────   │
│      Top: 100     │
│                   │
│     Order By:     │
│ count_star() DESC │
│  item.i_item_desc │
│         ASC       │
│     warehouse     │
│ .w_warehouse_name │
│         ASC       │
│ d1.d_week_seq ASC │
│                   │
│      100 Rows     │
│      (0.00s)      │
└─────────┬─────────┘
┌─────────┴─────────┐
│   HASH_GROUP_BY   │
│    ────────────   │
│      Groups:      │
│         #0        │
│         #1        │
│         #2        │
│                   │
│    Aggregates:    │
│      sum(#3)      │
│      sum(#4)      │
│    count_star()   │
│                   │
│     2275 Rows     │
│      (0.01s)      │
└─────────┬─────────┘
┌─────────┴─────────┐
│     PROJECTION    │
│    ────────────   │
│    i_item_desc    │
│  w_warehouse_name │
│     d_week_seq    │
│  CASE  WHEN (#3)  │
│   THEN (1) ELSE 0 │
│         END       │
│ CASE  WHEN ((NOT  │
│ #3)) THEN (1) ELSE│
│        0 END      │
│                   │
│     2299 Rows     │
│      (0.00s)      │
└─────────┬─────────┘
┌─────────┴─────────┐
│     PROJECTION    │
│    ────────────   │
│    i_item_desc    │
│  w_warehouse_name │
│     d_week_seq    │
│   (p_promo_sk IS  │
│        NULL)      │
│                   │
│     2299 Rows     │
│      (0.00s)      │
└─────────┬─────────┘
┌─────────┴─────────┐
│     HASH_JOIN     │
│    ────────────   │
│     Join Type:    │
│        LEFT       │
│                   │
│    Conditions:    │
│    cs_item_sk =   ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│     cr_item_sk    │                                                                                                                                                                                                       │
│ cs_order_number = │                                                                                                                                                                                                       │
│   cr_order_number │                                                                                                                                                                                                       │
│                   │                                                                                                                                                                                                       │
│     2299 Rows     │                                                                                                                                                                                                       │
│      (0.01s)      │                                                                                                                                                                                                       │
└─────────┬─────────┘                                                                                                                                                                                                       │
┌─────────┴─────────┐                                                                                                                                                                                             ┌─────────┴─────────┐
│     HASH_JOIN     │                                                                                                                                                                                             │     TABLE_SCAN    │
│    ────────────   │                                                                                                                                                                                             │    ────────────   │
│     Join Type:    │                                                                                                                                                                                             │    Projections:   │
│        LEFT       │                                                                                                                                                                                             │     cr_item_sk    │
│                   │                                                                                                                                                                                             │  cr_order_number  │
│    Conditions:    ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐          │                   │
│   cs_promo_sk =   │                                                                                                                                                                                  │          │ Total Files Read: │
│     p_promo_sk    │                                                                                                                                                                                  │          │         1         │
│                   │                                                                                                                                                                                  │          │                   │
│     2299 Rows     │                                                                                                                                                                                  │          │    144067 Rows    │
│      (0.00s)      │                                                                                                                                                                                  │          │      (0.07s)      │
└─────────┬─────────┘                                                                                                                                                                                  │          └───────────────────┘
┌─────────┴─────────┐                                                                                                                                                                        ┌─────────┴─────────┐
│     HASH_JOIN     │                                                                                                                                                                        │     TABLE_SCAN    │
│    ────────────   │                                                                                                                                                                        │    ────────────   │
│     Join Type:    │                                                                                                                                                                        │    Projections:   │
│       INNER       │                                                                                                                                                                        │     p_promo_sk    │
│                   │                                                                                                                                                                        │                   │
│    Conditions:    │                                                                                                                                                                        │ Total Files Read: │
│    d_date_sk =    │                                                                                                                                                                        │         1         │
│   cs_sold_date_sk │                                                                                                                                                                        │                   │
│   inv_item_sk =   │                                                                                                                                                                        │                   │
│      i_item_sk    ├─────────────────────────────────────────────────────────────────────────┐                                                                                              │                   │
│ (d_date + '5 days'│                                                                         │                                                                                              │                   │
│ ::INTERVAL) < CAST│                                                                         │                                                                                              │                   │
│     (d_date AS    │                                                                         │                                                                                              │                   │
│     TIMESTAMP)    │                                                                         │                                                                                              │                   │
│inv_quantity_on_han│                                                                         │                                                                                              │                   │
│  d < cs_quantity  │                                                                         │                                                                                              │                   │
│                   │                                                                         │                                                                                              │                   │
│     2299 Rows     │                                                                         │                                                                                              │      300 Rows     │
│      (1.02s)      │                                                                         │                                                                                              │      (0.01s)      │
└─────────┬─────────┘                                                                         │                                                                                              └───────────────────┘
┌─────────┴─────────┐                                                               ┌─────────┴─────────┐
│     HASH_JOIN     │                                                               │     HASH_JOIN     │
│    ────────────   │                                                               │    ────────────   │
│     Join Type:    │                                                               │     Join Type:    │
│       INNER       │                                                               │       INNER       │
│                   │                                                               │                   │
│    Conditions:    ├────────────────────────────────────────────────────┐          │    Conditions:    ├─────────────────────────────────────────────────────────────────────────┐
│ inv_warehouse_sk =│                                                    │          │ cs_ship_date_sk = │                                                                         │
│   w_warehouse_sk  │                                                    │          │      d_date_sk    │                                                                         │
│                   │                                                    │          │                   │                                                                         │
│   16470000 Rows   │                                                    │          │     47529 Rows    │                                                                         │
│      (0.26s)      │                                                    │          │      (0.01s)      │                                                                         │
└─────────┬─────────┘                                                    │          └─────────┬─────────┘                                                                         │
┌─────────┴─────────┐                                          ┌─────────┴─────────┐┌─────────┴─────────┐                                                               ┌─────────┴─────────┐
│     HASH_JOIN     │                                          │     TABLE_SCAN    ││     HASH_JOIN     │                                                               │     TABLE_SCAN    │
│    ────────────   │                                          │    ────────────   ││    ────────────   │                                                               │    ────────────   │
│     Join Type:    │                                          │    Projections:   ││     Join Type:    │                                                               │    Projections:   │
│       INNER       │                                          │   w_warehouse_sk  ││       INNER       │                                                               │     d_date_sk     │
│                   │                                          │  w_warehouse_name ││                   │                                                               │       d_date      │
│    Conditions:    ├──────────┐                               │                   ││    Conditions:    ├────────────────────────────────────────────────────┐          │                   │
│   inv_date_sk =   │          │                               │ Total Files Read: ││    cs_item_sk =   │                                                    │          │ Total Files Read: │
│      d_date_sk    │          │                               │         1         ││      i_item_sk    │                                                    │          │         1         │
│                   │          │                               │                   ││                   │                                                    │          │                   │
│   16470000 Rows   │          │                               │       5 Rows      ││     47529 Rows    │                                                    │          │     73049 Rows    │
│      (0.23s)      │          │                               │      (0.00s)      ││      (0.01s)      │                                                    │          │      (0.06s)      │
└─────────┬─────────┘          │                               └───────────────────┘└─────────┬─────────┘                                                    │          └───────────────────┘
┌─────────┴─────────┐┌─────────┴─────────┐                                          ┌─────────┴─────────┐                                          ┌─────────┴─────────┐
│     TABLE_SCAN    ││     HASH_JOIN     │                                          │     HASH_JOIN     │                                          │     TABLE_SCAN    │
│    ────────────   ││    ────────────   │                                          │    ────────────   │                                          │    ────────────   │
│    Projections:   ││     Join Type:    │                                          │     Join Type:    │                                          │    Projections:   │
│    inv_date_sk    ││       INNER       │                                          │       INNER       │                                          │     i_item_sk     │
│    inv_item_sk    ││                   │                                          │                   │                                          │    i_item_desc    │
│  inv_warehouse_sk ││    Conditions:    │                                          │    Conditions:    │                                          │                   │
│inv_quantity_on_han││    d_week_seq =   │                                          │ cs_bill_cdemo_sk =│                                          │ Total Files Read: │
│         d         ││     d_week_seq    ├──────────┐                               │     cd_demo_sk    ├───────────────────────────────┐          │         1         │
│                   ││                   │          │                               │                   │                               │          │                   │
│ Total Files Read: ││                   │          │                               │                   │                               │          │                   │
│         8         ││                   │          │                               │                   │                               │          │                   │
│                   ││                   │          │                               │                   │                               │          │                   │
│    2385000 Rows   ││     2562 Rows     │          │                               │     47529 Rows    │                               │          │     18000 Rows    │
│      (1.55s)      ││      (0.00s)      │          │                               │      (0.03s)      │                               │          │      (0.07s)      │
└───────────────────┘└─────────┬─────────┘          │                               └─────────┬─────────┘                               │          └───────────────────┘
                     ┌─────────┴─────────┐┌─────────┴─────────┐                     ┌─────────┴─────────┐                     ┌─────────┴─────────┐
                     │     TABLE_SCAN    ││     TABLE_SCAN    │                     │     HASH_JOIN     │                     │     TABLE_SCAN    │
                     │    ────────────   ││    ────────────   │                     │    ────────────   │                     │    ────────────   │
                     │    Projections:   ││    Projections:   │                     │     Join Type:    │                     │    Projections:   │
                     │     d_date_sk     ││     d_date_sk     │                     │       INNER       │                     │     cd_demo_sk    │
                     │     d_week_seq    ││       d_date      │                     │                   │                     │                   │
                     │                   ││     d_week_seq    │                     │    Conditions:    │                     │      Filters:     │
                     │ Total Files Read: ││                   │                     │ cs_bill_hdemo_sk =│                     │ cd_marital_status=│
                     │         1         ││      Filters:     │                     │     hd_demo_sk    │                     │        'S'        │
                     │                   ││    d_year=2000    │                     │                   │                     │                   │
                     │                   ││                   │                     │                   │                     │   File Filters:   │
                     │                   ││   File Filters:   │                     │                   ├──────────┐          │ cd_marital_status=│
                     │                   ││    d_year=2000    │                     │                   │          │          │        'S'        │
                     │                   ││                   │                     │                   │          │          │                   │
                     │                   ││  Scanning Files:  │                     │                   │          │          │  Scanning Files:  │
                     │                   ││        1/1        │                     │                   │          │          │        2/2        │
                     │                   ││                   │                     │                   │          │          │                   │
                     │                   ││ Total Files Read: │                     │                   │          │          │ Total Files Read: │
                     │                   ││         1         │                     │                   │          │          │         2         │
                     │                   ││                   │                     │                   │          │          │                   │
                     │      371 Rows     ││      366 Rows     │                     │    237729 Rows    │          │          │    384160 Rows    │
                     │      (0.00s)      ││      (0.06s)      │                     │      (0.01s)      │          │          │      (0.37s)      │
                     └───────────────────┘└───────────────────┘                     └─────────┬─────────┘          │          └───────────────────┘
                                                                                    ┌─────────┴─────────┐┌─────────┴─────────┐
                                                                                    │     TABLE_SCAN    ││     TABLE_SCAN    │
                                                                                    │    ────────────   ││    ────────────   │
                                                                                    │    Projections:   ││    Projections:   │
                                                                                    │  cs_sold_date_sk  ││     hd_demo_sk    │
                                                                                    │  cs_ship_date_sk  ││                   │
                                                                                    │  cs_bill_cdemo_sk ││      Filters:     │
                                                                                    │  cs_bill_hdemo_sk ││ hd_buy_potential='│
                                                                                    │     cs_item_sk    ││      >10000'      │
                                                                                    │    cs_promo_sk    ││                   │
                                                                                    │  cs_order_number  ││   File Filters:   │
                                                                                    │    cs_quantity    ││ hd_buy_potential='│
                                                                                    │                   ││      >10000'      │
                                                                                    │ Total Files Read: ││                   │
                                                                                    │         2         ││  Scanning Files:  │
                                                                                    │                   ││        1/1        │
                                                                                    │                   ││                   │
                                                                                    │                   ││ Total Files Read: │
                                                                                    │                   ││         1         │
                                                                                    │                   ││                   │
                                                                                    │    1408527 Rows   ││     1200 Rows     │
                                                                                    │      (1.70s)      ││      (0.01s)      │
                                                                                    └───────────────────┘└───────────────────┘

This is the logical and physical plan of Sail (via spark.sql("...").explain()):

+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Projection: #218, #219, #220, #221, #222, #223                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |   Sort: #223 DESC NULLS LAST, #218 ASC NULLS FIRST, #219 ASC NULLS FIRST, d1.#92 ASC NULLS FIRST, fetch=100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               |     Projection: item.#56 AS #218, warehouse.#40 AS #219, d1.#92 AS #220, sum(CASE WHEN promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END) AS no_promo AS #221, sum(CASE WHEN NOT promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END) AS promo AS #222, count(Int64(1)) AS total_cnt AS #223, d1.#92                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |       Aggregate: groupBy=[[item.#56, warehouse.#40, d1.#92]], aggr=[[sum(CAST(CASE WHEN promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END AS Int64)), sum(CAST(CASE WHEN promotion.#172 IS NOT NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END AS Int64)) AS sum(CASE WHEN NOT promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END), count(Int64(1))]]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |         Projection: warehouse.#40, item.#56, d1.#92, promotion.#172                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |           Left Join: catalog_sales.#15 = catalog_returns.#193, catalog_sales.#17 = catalog_returns.#207                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |             Projection: catalog_sales.#15, catalog_sales.#17, warehouse.#40, item.#56, d1.#92, promotion.#172                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |               Left Join: catalog_sales.#16 = promotion.#172                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               |                 Projection: catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, warehouse.#40, item.#56, d1.#92                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                   Inner Join: catalog_sales.#2 = d3.#144 Filter: d3.#146 > CAST(d1.#90 AS Date32) + IntervalDayTime("IntervalDayTime { days: 5, milliseconds: 0 }")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |                     Projection: catalog_sales.#2, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, warehouse.#40, item.#56, d1.#90, d1.#92                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                       Inner Join: inventory.#34 = d2.#116, d1.#92 = d2.#120                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               |                         Projection: catalog_sales.#2, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, inventory.#34, warehouse.#40, item.#56, d1.#90, d1.#92                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               |                           Inner Join: catalog_sales.#0 = d1.#88                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                             Projection: catalog_sales.#0, catalog_sales.#2, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, inventory.#34, warehouse.#40, item.#56                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               |                               Inner Join: catalog_sales.#5 = household_demographics.#83                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                 Projection: catalog_sales.#0, catalog_sales.#2, catalog_sales.#5, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, inventory.#34, warehouse.#40, item.#56                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                                   Inner Join: catalog_sales.#4 = customer_demographics.#74                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                     Projection: catalog_sales.#0, catalog_sales.#2, catalog_sales.#4, catalog_sales.#5, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, inventory.#34, warehouse.#40, item.#56                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                       Inner Join: catalog_sales.#15 = item.#52                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                                         Projection: catalog_sales.#0, catalog_sales.#2, catalog_sales.#4, catalog_sales.#5, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, inventory.#34, warehouse.#40                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                                           Inner Join: inventory.#36 = warehouse.#38                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |                                             Projection: catalog_sales.#0, catalog_sales.#2, catalog_sales.#4, catalog_sales.#5, catalog_sales.#15, catalog_sales.#16, catalog_sales.#17, inventory.#34, inventory.#36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               |                                               Inner Join: catalog_sales.#15 = inventory.#35 Filter: inventory.#37 < catalog_sales.#18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               |                                                 SubqueryAlias: catalog_sales                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                   Projection: catalog_sales.#0 AS #0, catalog_sales.#2 AS #2, catalog_sales.#4 AS #4, catalog_sales.#5 AS #5, catalog_sales.#15 AS #15, catalog_sales.#16 AS #16, catalog_sales.#17 AS #17, catalog_sales.#18 AS #18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                     SubqueryAlias: catalog_sales                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                       TableScan: ?table? projection=[#0, #2, #4, #5, #15, #16, #17, #18]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                                 SubqueryAlias: inventory                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                                   Projection: inventory.#0 AS #34, inventory.#1 AS #35, inventory.#2 AS #36, inventory.#3 AS #37                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                     SubqueryAlias: inventory                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                       TableScan: ?table? projection=[#0, #1, #2, #3]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                             SubqueryAlias: warehouse                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                               Projection: warehouse.#0 AS #38, warehouse.#2 AS #40                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                                 SubqueryAlias: warehouse                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                                   TableScan: ?table? projection=[#0, #2]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                         SubqueryAlias: item                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               |                                           Projection: item.#0 AS #52, item.#4 AS #56                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                             SubqueryAlias: item                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                               TableScan: ?table? projection=[#0, #4]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                     SubqueryAlias: customer_demographics                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                       Projection: customer_demographics.#0 AS #74                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                                         SubqueryAlias: customer_demographics                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                           Projection: ?table?.#0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                             Filter: ?table?.#2 = Utf8("S")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                               TableScan: ?table? projection=[#0, #2], partial_filters=[?table?.#2 = Utf8("S")]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                                 SubqueryAlias: household_demographics                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               |                                   Projection: household_demographics.#0 AS #83                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                                     SubqueryAlias: household_demographics                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                                       Projection: ?table?.#0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                         Filter: ?table?.#2 = Utf8(">10000")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               |                                           TableScan: ?table? projection=[#0, #2], partial_filters=[?table?.#2 = Utf8(">10000")]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                             SubqueryAlias: d1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |                               Projection: date_dim.#0 AS #88, date_dim.#2 AS #90, date_dim.#4 AS #92                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                 SubqueryAlias: date_dim                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                   Projection: ?table?.#0, ?table?.#2, ?table?.#4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                     Filter: ?table?.#6 = Int32(2000)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                       TableScan: ?table? projection=[#0, #2, #4, #6], partial_filters=[?table?.#6 = Int32(2000)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                         SubqueryAlias: d2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                           Projection: date_dim.#0 AS #116, date_dim.#4 AS #120                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                             SubqueryAlias: date_dim                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |                               TableScan: ?table? projection=[#0, #4]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                     SubqueryAlias: d3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               |                       Projection: date_dim.#0 AS #144, date_dim.#2 AS #146                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                         SubqueryAlias: date_dim                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                           TableScan: ?table? projection=[#0, #2]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                 SubqueryAlias: promotion                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                   Projection: promotion.#0 AS #172                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                     SubqueryAlias: promotion                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                       TableScan: ?table? projection=[#0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |             SubqueryAlias: catalog_returns                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |               Projection: catalog_returns.#2 AS #193, catalog_returns.#16 AS #207                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                 SubqueryAlias: catalog_returns                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                   TableScan: ?table? projection=[#2, #16]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| physical_plan | ProjectionExec: expr=[#218@0 as #218, #219@1 as #219, #220@2 as #220, #221@3 as #221, #222@4 as #222, #223@5 as #223]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               |   SortPreservingMergeExec: [#223@5 DESC NULLS LAST, #218@0 ASC, #219@1 ASC, #92@6 ASC], fetch=100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |     SortExec: TopK(fetch=100), expr=[#223@5 DESC NULLS LAST, #218@0 ASC, #219@1 ASC, #220@2 ASC], preserve_partitioning=[true]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |       ProjectionExec: expr=[#56@0 as #218, #40@1 as #219, #92@2 as #220, sum(CASE WHEN promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END)@3 as #221, sum(CASE WHEN NOT promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END)@4 as #222, count(Int64(1))@5 as #223, #92@2 as #92]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |         AggregateExec: mode=FinalPartitioned, gby=[#56@0 as #56, #40@1 as #40, #92@2 as #92], aggr=[sum(CASE WHEN promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END), sum(CASE WHEN NOT promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END), count(Int64(1))]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |           CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |             RepartitionExec: partitioning=Hash([#56@0, #40@1, #92@2], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |               AggregateExec: mode=Partial, gby=[#56@1 as #56, #40@0 as #40, #92@2 as #92], aggr=[sum(CASE WHEN promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END), sum(CASE WHEN NOT promotion.#172 IS NULL THEN Int32(1) WHEN Boolean(true) THEN Int32(0) END), count(Int64(1))]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                 CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                   HashJoinExec: mode=Partitioned, join_type=Right, on=[(#193@0, #15@0), (#207@1, #17@1)], projection=[#40@4, #56@5, #92@6, #172@7]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                     CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                       RepartitionExec: partitioning=Hash([#193@0, #207@1], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                         ProjectionExec: expr=[cr_item_sk@0 as #193, cr_order_number@1 as #207]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                           DataSourceExec: file_groups={8 groups: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_returns/part-00001-f1987651-abd5-43e9-9ba0-95dc32f18505-c000.snappy.parquet:0..1409275], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_returns/part-00001-f1987651-abd5-43e9-9ba0-95dc32f18505-c000.snappy.parquet:1409275..2818550], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_returns/part-00001-f1987651-abd5-43e9-9ba0-95dc32f18505-c000.snappy.parquet:2818550..4227825], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_returns/part-00001-f1987651-abd5-43e9-9ba0-95dc32f18505-c000.snappy.parquet:4227825..5637100], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_returns/part-00001-f1987651-abd5-43e9-9ba0-95dc32f18505-c000.snappy.parquet:5637100..7046375], ...]}, projection=[cr_item_sk, cr_order_number], file_type=parquet                                                                                                                                                    |
|               |                     CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                       RepartitionExec: partitioning=Hash([#15@0, #17@1], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                         ProjectionExec: expr=[#15@1 as #15, #17@2 as #17, #40@3 as #40, #56@4 as #56, #92@5 as #92, #172@0 as #172]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |                           CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |                             HashJoinExec: mode=CollectLeft, join_type=Right, on=[(#172@0, #16@1)], projection=[#172@0, #15@1, #17@3, #40@4, #56@5, #92@6]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                               ProjectionExec: expr=[p_promo_sk@0 as #172]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                                 DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/promotion/part-00001-2b22ebbb-6937-4632-9086-ff26c43560ca-c000.snappy.parquet]]}, projection=[p_promo_sk], file_type=parquet                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |                               CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                 HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(#144@0, #2@0)], filter=#146@1 > #90@0 + IntervalDayTime { days: 5, milliseconds: 0 }, projection=[#15@3, #16@4, #17@5, #40@6, #56@7, #92@9]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                                   ProjectionExec: expr=[d_date_sk@0 as #144, d_date@1 as #146]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                                     DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/date_dim/part-00001-edeca01e-fb6d-4ff3-89c6-6ca4da1a0fee-c000.snappy.parquet]]}, projection=[d_date_sk, d_date], file_type=parquet                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                   CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                                     HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(#116@0, #34@4), (#120@1, #92@8)], projection=[#2@2, #15@3, #16@4, #17@5, #40@7, #56@8, #90@9, #92@10]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                       ProjectionExec: expr=[d_date_sk@0 as #116, d_week_seq@1 as #120]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |                                         DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/date_dim/part-00001-edeca01e-fb6d-4ff3-89c6-6ca4da1a0fee-c000.snappy.parquet]]}, projection=[d_date_sk, d_week_seq], file_type=parquet                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                       ProjectionExec: expr=[#2@2 as #2, #15@3 as #15, #16@4 as #16, #17@5 as #17, #34@6 as #34, #40@7 as #40, #56@8 as #56, #90@0 as #90, #92@1 as #92]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                         CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                           HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(#88@0, #0@0)], projection=[#90@1, #92@2, #2@4, #15@5, #16@6, #17@7, #34@8, #40@9, #56@10]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                                             CoalescePartitionsExec                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                               ProjectionExec: expr=[#0@0 as #88, #2@1 as #90, #4@2 as #92]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                                 CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                   FilterExec: #6@3 = 2000, projection=[#0@0, #2@1, #4@2]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                                     RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                                       ProjectionExec: expr=[d_date_sk@0 as #0, d_date@1 as #2, d_week_seq@2 as #4, d_year@3 as #6]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                                         DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/date_dim/part-00001-edeca01e-fb6d-4ff3-89c6-6ca4da1a0fee-c000.snappy.parquet]]}, projection=[d_date_sk, d_date, d_week_seq, d_year], file_type=parquet, predicate=d_year@3 = 2000, pruning_predicate=d_year_null_count@2 != row_count@3 AND d_year_min@0 <= 2000 AND 2000 <= d_year_max@1, required_guarantees=[d_year in (2000)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                             CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                               HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(#83@0, #5@2)], projection=[#0@1, #2@2, #15@4, #16@5, #17@6, #34@7, #40@8, #56@9]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                 CoalescePartitionsExec                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |                                                   ProjectionExec: expr=[#0@0 as #83]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                     CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                       FilterExec: #2@1 = >10000, projection=[#0@0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                                         RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                           ProjectionExec: expr=[hd_demo_sk@0 as #0, hd_buy_potential@1 as #2]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |                                                             DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/household_demographics/part-00001-6420d2f8-ad31-4317-8906-a5b1a3a892a5-c000.snappy.parquet]]}, projection=[hd_demo_sk, hd_buy_potential], file_type=parquet, predicate=hd_buy_potential@1 = >10000, pruning_predicate=hd_buy_potential_null_count@2 != row_count@3 AND hd_buy_potential_min@0 <= >10000 AND >10000 <= hd_buy_potential_max@1, required_guarantees=[hd_buy_potential in (>10000)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                                 CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                   HashJoinExec: mode=Partitioned, join_type=Inner, on=[(#74@0, #4@2)], projection=[#0@1, #2@2, #5@4, #15@5, #16@6, #17@7, #34@8, #40@9, #56@10]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                                     CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                       RepartitionExec: partitioning=Hash([#74@0], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               |                                                         ProjectionExec: expr=[#0@0 as #74]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                                           CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |                                                             FilterExec: #2@1 = S, projection=[#0@0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |                                                               RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                                                 ProjectionExec: expr=[cd_demo_sk@0 as #0, cd_marital_status@1 as #2]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                                   DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/customer_demographics/part-00001-58a795bd-8d27-4344-91b1-7b9fd9ff872e-c000.snappy.parquet]]}, projection=[cd_demo_sk, cd_marital_status], file_type=parquet, predicate=cd_marital_status@1 = S, pruning_predicate=cd_marital_status_null_count@2 != row_count@3 AND cd_marital_status_min@0 <= S AND S <= cd_marital_status_max@1, required_guarantees=[cd_marital_status in (S)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               |                                                     CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                       RepartitionExec: partitioning=Hash([#4@2], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                                         ProjectionExec: expr=[#0@1 as #0, #2@2 as #2, #4@3 as #4, #5@4 as #5, #15@5 as #15, #16@6 as #16, #17@7 as #17, #34@8 as #34, #40@9 as #40, #56@0 as #56]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               |                                                           CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |                                                             HashJoinExec: mode=Partitioned, join_type=Inner, on=[(#52@0, #15@4)], projection=[#56@1, #0@2, #2@3, #4@4, #5@5, #15@6, #16@7, #17@8, #34@9, #40@10]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                               CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                                                 RepartitionExec: partitioning=Hash([#52@0], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                                                   RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               |                                                                     ProjectionExec: expr=[i_item_sk@0 as #52, i_item_desc@1 as #56]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               |                                                                       DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/item/part-00001-429e5bc0-341a-4c9a-aec7-0368d93608dd-c000.snappy.parquet]]}, projection=[i_item_sk, i_item_desc], file_type=parquet                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               |                                                               ProjectionExec: expr=[#0@1 as #0, #2@2 as #2, #4@3 as #4, #5@4 as #5, #15@5 as #15, #16@6 as #16, #17@7 as #17, #34@8 as #34, #40@0 as #40]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                                                                 CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                                   HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(#38@0, #36@8)], projection=[#40@1, #0@2, #2@3, #4@4, #5@5, #15@6, #16@7, #17@8, #34@9]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|               |                                                                     ProjectionExec: expr=[w_warehouse_sk@0 as #38, w_warehouse_name@1 as #40]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               |                                                                       DataSourceExec: file_groups={1 group: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/warehouse/part-00001-a648d784-edf7-49d5-b829-1b98282dc348-c000.snappy.parquet]]}, projection=[w_warehouse_sk, w_warehouse_name], file_type=parquet                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                                                                     CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               |                                                                       HashJoinExec: mode=Partitioned, join_type=Inner, on=[(#15@4, #35@1)], filter=#37@1 < #18@0, projection=[#0@0, #2@1, #4@2, #5@3, #15@4, #16@5, #17@6, #34@8, #36@10]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               |                                                                         CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                                           RepartitionExec: partitioning=Hash([#15@4], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                                             ProjectionExec: expr=[cs_sold_date_sk@0 as #0, cs_ship_date_sk@1 as #2, cs_bill_cdemo_sk@2 as #4, cs_bill_hdemo_sk@3 as #5, cs_item_sk@4 as #15, cs_promo_sk@5 as #16, cs_order_number@6 as #17, cs_quantity@7 as #18]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               |                                                                               DataSourceExec: file_groups={8 groups: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_sales/part-00001-6ccd3b9f-0fdc-4822-a259-c4ac24b4c687-c000.snappy.parquet:0..12633705], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_sales/part-00001-6ccd3b9f-0fdc-4822-a259-c4ac24b4c687-c000.snappy.parquet:12633705..25267410], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_sales/part-00001-6ccd3b9f-0fdc-4822-a259-c4ac24b4c687-c000.snappy.parquet:25267410..37901115], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_sales/part-00001-6ccd3b9f-0fdc-4822-a259-c4ac24b4c687-c000.snappy.parquet:37901115..50534820], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/catalog_sales/part-00001-6ccd3b9f-0fdc-4822-a259-c4ac24b4c687-c000.snappy.parquet:50534820..63168525], ...]}, projection=[cs_sold_date_sk, cs_ship_date_sk, cs_bill_cdemo_sk, cs_bill_hdemo_sk, cs_item_sk, cs_promo_sk, cs_order_number, cs_quantity], file_type=parquet |
|               |                                                                         CoalesceBatchesExec: target_batch_size=16384                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               |                                                                           RepartitionExec: partitioning=Hash([#35@1], 8), input_partitions=8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               |                                                                             ProjectionExec: expr=[inv_date_sk@0 as #34, inv_item_sk@1 as #35, inv_warehouse_sk@2 as #36, inv_quantity_on_hand@3 as #37]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               |                                                                               DataSourceExec: file_groups={8 groups: [[lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/inventory/part-00001-b13c8cca-6be5-48a2-b99d-2b3814ec6ff8-c000.snappy.parquet:0..4668732], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/inventory/part-00001-b13c8cca-6be5-48a2-b99d-2b3814ec6ff8-c000.snappy.parquet:4668732..9337464], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/inventory/part-00001-b13c8cca-6be5-48a2-b99d-2b3814ec6ff8-c000.snappy.parquet:9337464..14006196], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/inventory/part-00001-b13c8cca-6be5-48a2-b99d-2b3814ec6ff8-c000.snappy.parquet:14006196..18674928], [lh_user_mbe_lakebench.Lakehouse/Files/tmp/TPCDS/sf1/Sail/inventory/part-00001-b13c8cca-6be5-48a2-b99d-2b3814ec6ff8-c000.snappy.parquet:18674928..23343660], ...]}, projection=[inv_date_sk, inv_item_sk, inv_warehouse_sk, inv_quantity_on_hand], file_type=parquet                                                                                    |
|               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[linhr]

Thanks for sharing this! I'll take a look shortly and see if there are any improvements we can have (either in Sail or in the upstream DataFusion repo).

[djouallah]

i think the main reason reason, I like colab is that everything is reproducible including the same hardware, duckdb is substantially faster and even more when running from data hosted in an object store.
https://colab.research.google.com/drive/1NGFqooYmbKs2ffQkTzMu_AWTHCHbE419?usp=sharing#scrollTo=gEz-IPJH2ZKR

same data, same delta metadata, based on previous experience with datafusion, I expect the difference to be 2 x, clearly there is a bug here somewhere.

[linhr]

@djouallah Would you like to try out LakeBench in Colab? It seems it's a Python package that covers data generation and query execution for benchmarks. (I'm going to play with it as well.) It would be interesting to see if the performance difference is due to hardware settings or dataset configuration (e.g. scaling factor and number of partitions).

[linhr]

Also the actual TPC-DS queries may vary across setup. It's possible that the query being used affects how the execution plan is generated and optimized, resulting in different performance characteristics.

[djouallah]

@linhr sorry I think, there is a misunderstanding here, I am not interested in how other setup works or not, I am not looking for a winner, I find a weird behaviour in lakesail, I provided a reproducible example, I was hoping someone debug the issue, if it is upstream or it is sail implementation of delta.

edit : i will add datafusion, that may help too

[linhr]

Ahh, my questions were also intended to identify potential clues for the performance issue. The goal was not to draw a conclusion on the winner either. I was just curious if the difference in the queries, hardware, or dataset can provide hints on the direction for optimization.

[djouallah]

@linhr which row group you prefer ?

[shehabgamin]

@djouallah It appears that your notebook is using .show() when it should instead be using .collect() or .toPandas().

Other engines could be adding various optimizations to show string such as a partial execution since show string only displays the top x records.

[linhr]

which row group you prefer ?

I'm not sure if I understand this question. Could you elaborate?

[djouallah]

@linhr which row group size in parquet, lakesail prefers, different engines, works better with different size

@shehabgamin fair point , I will change it, although I doubt they are so smart :)

[linhr]

I haven't explored the performance difference for different row group size. But since this is a property of the dataset instead of some runtime parameter, I feel we need to keep it the same across engines.