4 min

September 7, 2026

TPC-H Query 15 - Scalar Constants and Materialisation

Today's TPC-H query is the first one to use a WITH clause.

The same expression is used twice, and the query quietly tests for a simple, yet powerful, optimisation: materialisation.

As we shall see, Databricks, Trino and DataFusion all fail the test and scan 6M more rows than the databases that implement the optimisation.

Query 15

Here is Query 15, which is the first query featuring a Common Table Expression (aka: a CTE, it's that thing after WITH).

Notice that the same CTE is referenced twice in this query.

WITH revenue AS (
    SELECT 
      l_suppkey AS supplier_no,
      SUM(l_extendedprice * (1 - l_discount)) AS total_revenue
    FROM lineitem
    WHERE l_shipdate >= '1997-09-01'
      AND l_shipdate < '1997-12-01'
    GROUP BY l_suppkey)
SELECT 
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
FROM supplier
INNER JOIN revenue
  ON s_suppkey = supplier_no
WHERE total_revenue = (SELECT MAX(total_revenue) FROM revenue)
ORDER BY s_suppkey

Estimation and Join Order

There is only one filter in the query, on l_shipdate.

Filter	Selectivity	Cardinality
`l_shipdate >= '1997-09-01' AND l_shipdate < '1997-12-01'`	3.7%	226K

Even with the filter, the output of lineitem is still much larger than supplier (which has no filter and emits 10K rows).

The join order of the outer query is trivial: lineitem ⨝ supplier.

Scalar Constants and Cross Joining

Notice this expression: (SELECT MAX(total_revenue) FROM revenue). This value can be calculated before running the join between supplier and lineitem in the outer query.

How do we then recombine the constant value with the query to evaluate the WHERE total_revenue filter?

There are two general approaches:

Run the MAX(total_revenue) subquery first and store the value as a parameter - then run the outer query
Run the MAX(total_revenue) as part of the outer query, and just do a cross join to evaluate the filter

If your join implementation is solid - then option 2 saves you a lot of trouble because it allows you to stay in a space that looks like relational algebra. You will be running this join:

FROM supplier
INNER JOIN revenue
CROSS JOIN (SELECT MAX(total_revenue) AS the_max FROM revenue) AS F
WHERE total_revenue = F.the_max

This is elegant and avoids duplication of code in the Execution Engine of the database. For example, here is DataFusion doing exactly that (Trino does the same thing):

Operator
SORT s_suppkey
SORT s_suppkey
INNER JOIN HASH ON MAX(revenue.total_revenue) = total_revenue <--- Here is the cross join!
│└INNER JOIN HASH ON s_suppkey = supplier_no
│ │└PROJECT supplier_no, total_revenue
│ │ AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey
│ │ DISTRIBUTE HASH ON l_suppkey
│ │ AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey
│ │ FILTER (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')
│ │ TABLE SCAN lineitem WHERE ((l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')) 
| |   AND (l_suppkey <= 10000))
│ TABLE SCAN supplier
AGGREGATE MAX(total_revenue)
DISTRIBUTE GATHER
AGGREGATE MAX(total_revenue)
PROJECT total_revenue
AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey
DISTRIBUTE HASH ON l_suppkey
AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey
FILTER (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')
TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')

But, if you fail to realise that this scalar value is really a cross join in disguise, you end up with special, scalar handling code in your Execution Engine.

For example, here is Databricks scanning lineitem, storing it as a scalar, and then using it again on the second scan of lineitem:

Operator
SEQUENCE
├─DISTRIBUTE HASH ON s_suppkey
│ INNER JOIN HASH ON s_suppkey = revenue.supplier_no
│ │└DISTRIBUTE GATHER
│ │ TABLE SCAN supplier
│ FILTER 
│ AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_suppkey
│ DISTRIBUTE HASH ON l_suppkey
│ AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_suppkey
│ TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')
└─AGGREGATE MAX(revenue.total_revenue)
  DISTRIBUTE GATHER
  AGGREGATE MAX(revenue.total_revenue)
  AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_suppkey
  DISTRIBUTE HASH
  TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')

Materialisation

Remember that TPC-H is cleverly designed - and this query is no exception. It is fishing for a special optimisation.

First, realise that the 6M row lineitem table (with a filter bringing it to 226K rows) is mentioned twice in this query.

The outer query accesses it with a GROUP BY and this aggregate:

FROM lineitem
WHERE l_shipdate >= '1997-09-01'
  AND l_shipdate < '1997-12-01'
GROUP BY l_suppkey

This aggregate can easily be estimated; it can't possibly be larger than the size of supplier - which is 10K rows. This is much smaller than the rowcount of lineitem (by 22x).

The subquery makes use of the same construct, like this:

SELECT MAX(total_revenue)
FROM (
  SELECT SUM(...) AS total_revenue
  FROM lineitem
  WHERE l_shipdate >= '1997-09-01'
    AND l_shipdate < '1997-12-01'
) as subquery

Note: I specifically didn't represent the subquery by its CTE alias. What the optimiser is about to do often fine without that help from the user specifying a CTE.

Here is the realisation that a query optimiser might have:

The same query is used in two places
The output size of that query is small
If I save the results of this query - I can eliminate one of the two 6M row scans of lineitem

This insight is sometimes called "materialisation".

DuckDB can do it, and so can Postgres and SQL Server. Here is the DuckDB query plan:

Operator
SEQUENCE
├─MATERIALISE AS m_revenue <--- Construct the aggregate
│ AGGREGATE sum_no_overflow #1 GROUP BY HASH #0
│ PROJECT l_suppkey, l_extendedprice * (1.00 - l_discount)
│ TABLE SCAN lineitem WHERE l_shipdate >= '1997-09-01' AND l_shipdate < '1997-12-01'
└─SORT s_suppkey
  INNER JOIN HASH ON s_suppkey = supplier_no
  │└INNER JOIN HASH ON total_revenue = SUBQUERY
  │ │└PROJECT CASE WHEN (#1 > 1) THEN ...
  │ │ AGGREGATE "first" #0, count_star() <--- Calculate the max from the cached
  │ │ AGGREGATE MAX(#0)
  │ │ PROJECT total_revenue
  │ │ SCAN MATERIALISED revenue
  │ SCAN MATERIALISED revenue <--- Scan from the cached
  TABLE SCAN supplier

Notice how the aggregate is materialised - then reused twice by the code.

In the case of TPC-H Q15 - this small optimisation saves a 6M row scan.

Summary

In today's analysis of Query 15, you saw how storing an intermediate result that is referenced more than once can greatly reduce the work needed to run the query.

In a way, this type of materialisation is almost like the automatic creation of a temp table.

Some databases, like DuckDB, SQL Server and Postgres, handle this gracefully and pass the test Q15 sets up before us.

We also saw that even though this optimisation is straightforward - Databricks, DataFusion and Trino all fail to make it.