[Scene_Cast2]

One example is that in Presto, joins assume that left (IIRC) table is the big one you stream, and the right one is the small one you hash. One of the newer features was that in some cases, the optimizer is able to ignore the SQL order and correctly pick which table to hash and which one to stream.

[legulere]

Isn't that just basic table/index statistics to know which table is the smallest?

[Scene_Cast2]

Not if you're joining on a filtered table or a result of another join.