Read Primitives — Optimizer: Common Subexpression

Read Primitives benchmark · all Read Primitives queries

Test Common Subexpression Elimination (CSE)

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  l_orderkey,
  l_partkey,
  l_suppkey,
  l_linenumber,
  l_quantity * l_extendedprice * (
    1 - l_discount
  ) * (
    1 + l_tax
  ) AS revenue_with_tax,
  CASE
    WHEN l_quantity * l_extendedprice * (
      1 - l_discount
    ) * (
      1 + l_tax
    ) > 50000
    THEN 'High Value'
    WHEN l_quantity * l_extendedprice * (
      1 - l_discount
    ) * (
      1 + l_tax
    ) > 10000
    THEN 'Medium Value'
    ELSE 'Low Value'
  END AS value_category,
  ROUND(l_quantity * l_extendedprice * (
    1 - l_discount
  ) * (
    1 + l_tax
  ), 2) AS rounded_revenue
FROM lineitem
WHERE
  l_shipdate >= CAST('1995-01-01' AS DATE)
  AND l_shipdate < CAST('1996-01-01' AS DATE)
  AND l_quantity * l_extendedprice * (
    1 - l_discount
  ) * (
    1 + l_tax
  ) > 1000
ORDER BY
  l_quantity * l_extendedprice * (
    1 - l_discount
  ) * (
    1 + l_tax
  ) DESC,
  l_orderkey,
  l_linenumber
LIMIT 5000

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def optimizer_common_subexpression_expression_impl(ctx: DataFrameContext) -> Any:
    import polars as pl

    revenue = pl.col("l_quantity") * pl.col("l_extendedprice") * (1 - pl.col("l_discount")) * (1 + pl.col("l_tax"))
    lf = (
        ctx.get_table("lineitem")
        .native.filter((pl.col("l_shipdate") >= date(1995, 1, 1)) & (pl.col("l_shipdate") < date(1996, 1, 1)))
        .with_columns(revenue.alias("revenue_with_tax"))
        .filter(pl.col("revenue_with_tax") > 1000)
    )
    return (
        lf.with_columns(
            pl.when(pl.col("revenue_with_tax") > 50000)
            .then(pl.lit("High Value"))
            .when(pl.col("revenue_with_tax") > 10000)
            .then(pl.lit("Medium Value"))
            .otherwise(pl.lit("Low Value"))
            .alias("value_category"),
            pl.col("revenue_with_tax").round(2).alias("rounded_revenue"),
        )
        .select(
            "l_orderkey",
            "l_partkey",
            "l_suppkey",
            "l_linenumber",
            "revenue_with_tax",
            "value_category",
            "rounded_revenue",
        )
        .sort(["revenue_with_tax", "l_orderkey", "l_linenumber"], descending=[True, False, False])
        .limit(5000)
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import ReadPrimitives

bench = ReadPrimitives(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['optimizer_common_subexpression'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('read_primitives', 'optimizer_common_subexpression', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/read_primitives/queries.py