Kusto: Full table scan on join even join conditions are time-based?

Question

I have a Kusto Query like:

(Events
| take 1)
| join kind=leftouter Sensor_Data on $left.start_timestmp == 
$right.timestmp, someotherfield

and it will never return. The right side of the join has several billion entries.

If I do a

Events
| take 1

and use the result in the where clause of Sensor_Data it returns in no time.

The MS Support Team explains that this query requires a full table scan of the Sensors_Data table. The join parameters are not taken into consideration by the query optimizer.

Question: Is the Kusto Query Optimizer really no able to optimize queries based on the join condition? To me it sounds a little bit like 1999 to have to first do the left side of the query manualy and then do the right side manualy as well? Is there some hint or so, to make this run fast?

Answer 1

consider rewriting your query as follows (for example) and see if that helps it perform better:

let x = toscalar(Events | take 1 | project pack("ts", start_timestmp, "sof", someotherfield));
Sensor_Data
| where timestmp == todatetime(x["ts"])
| where someotherfield == tostring(x["sof"])