• performance
• query‑planning
• tpch

1 min

1/1/2025

Listen

TPC series - TPC-H Query 5 - Diamond shaped Joins

Query 5

SELECT n_name
       , SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM tpch.lineitem
INNER JOIN tpch.supplier ON l_suppkey = s_suppkey
INNER JOIN tpch.orders ON l_orderkey = o_orderkey
INNER JOIN tpch.customer ON o_custkey = c_custkey
INNER JOIN tpch.nation ON s_nationkey = n_nationkey
INNER JOIN tpch.region ON r_regionkey = n_regionkey
WHERE c_nationkey = s_nationkey
  AND r_name = 'ASIA'
  AND o_orderdate >= '1994-01-01'
  AND o_orderdate < '1995-01-01'
GROUP BY n_name
ORDER BY revenue DESC;

Join order dependency

customer must be joined to orders which in turn must join lineitems that must join supplier

Diagramming:

        customer ---------- 
      /                     \
orders                       c_nationkey = s_nationkey
      \                     /
        lineitem - Supplier

Transitive Closure of r_name and bloom push

TODO