Prevent shared ancestor of left/right join from running twice?

[seanlaff]

In the following query, expensive_query is ran twice because clickhouse plans the left and right side of the join separately.

WITH
  expensive_query AS (SELECT id FROM dist_table1 WHERE ...),
  query_1 AS (SELECT val FROM dist_table2 WHERE id IN (expensive_query),
  query_2 AS (SELECT val FROM dist_table3 WHERE id IN (expensive_query)
SELECT 
  query_1.val,
  cheap_query_2.val
FROM query_1 INNER JOIN query_2 ON query_1.id = query_2.id

In this trace, the two yellow lines that are both 1.82s are the duplicate invocations of the expensive query. (sidenote: I found it curious that the two queries ran serially- would we expect them to be parallel?)

I can mitigate this behavior by putting the expensive query in a temporary table rather than an alias

CREATE TEMPORARY TABLE  expensive_query AS (SELECT ... FROM dist_table1);

WITH
  query_1 AS (SELECT val FROM dist_table2 WHERE id IN (expensive_query),
  query_2 AS (SELECT val FROM dist_table3 WHERE id IN (expensive_query)
SELECT 
  query_1.val,
  cheap_query_2.val
FROM query_1 INNER JOIN query_2 ON query_1.id = query_2.id

but doing so turns my query into a 2 statement query rather than a one statement query, which causes some headache.

Are there any other tricks to prevent clickhouse from doing the extra work?

[seanlaff]

It looks like I need something like https://sqlite.org/lang_with.html#mathint

Which had some discussion here #23539 (comment)

[Alex-Cheng]

What is the client you are using? It can show the performance, it is wonderful.

[seanlaff]

Its called jaeger, Im viewing the open telemetry traces from clickhouse https://clickhouse.com/docs/en/operations/opentelemetry

The spans clickhouse sends aren't that great, but theres a query span that is tagged with the query being run in each context. Most times thats good enough for me to perform an analysis on the distributed query