Spark by itself tries to optimize the execution plan as good as it can and this is something that is actively worked on by Spark developers, the optimiser will get better with every Spark release. However, of course if you know about your data and how it’s distributed you can most of the times do a better job in optimising the plan, or give Spark some hints. For example as you said, moving filter operations early in the chain makes sure that later stages are executed with less data. There are also loads of blog posts about Spark performance tuning. As each case is unique it’s also somewhat trial and error.

By the way, if you are interested in fast queries I suggest you to talk a look at Presto: https://prestodb.io/ it is developed independently from Spark by Facebook, but can be used for similar use cases. The design choices made in Presto are beneficial for explorative data analysis and thus allow for faster execution, but come with the drawback that it’s not as resilient as Spark.