Read Primitives — Optimizer: Join Reordering

Read Primitives benchmark · all Read Primitives queries

Test join reordering based on cardinality

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  n.n_name,
  c.c_name,
  COUNT(o.o_orderkey) AS order_count,
  SUM(o.o_totalprice) AS total_value
FROM orders AS o
JOIN customer AS c
  ON o.o_custkey = c.c_custkey
JOIN nation AS n
  ON c.c_nationkey = n.n_nationkey
WHERE
  n.n_regionkey = 1
  AND o.o_orderdate >= CAST('1995-01-01' AS DATE)
  AND o.o_orderdate < CAST('1996-01-01' AS DATE)
GROUP BY
  n.n_name,
  c.c_name
HAVING
  COUNT(o.o_orderkey) > 5
ORDER BY
  total_value DESC
LIMIT 100

Representative DataFrame

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

def optimizer_join_reordering_expression_impl(ctx: DataFrameContext) -> Any:
    orders, customer, nation = _tables(ctx, "orders", "customer", "nation")
    col = ctx.col
    lit = ctx.lit

    return (
        orders.filter(col("o_orderdate") >= lit(date(1995, 1, 1)))
        .filter(col("o_orderdate") < lit(date(1996, 1, 1)))
        .join(customer, col("o_custkey") == col("c_custkey"))
        .join(nation, col("c_nationkey") == col("n_nationkey"))
        .filter(col("n_regionkey") == lit(1))
        .group_by("n_name", "c_name")
        .agg(
            col("o_orderkey").count().alias("order_count"),
            col("o_totalprice").sum().alias("total_value"),
        )
        .filter(col("order_count") > lit(5))
        .select("n_name", "c_name", "order_count", "total_value")
        .sort(["total_value", "n_name", "c_name"], descending=[True, False, False])
        .limit(100)
    )

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_join_reordering'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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