There could be a lot of reasons that are highly engine dependent, for this specific case. A general answer, perhaps... not specific to the case you've specifie

There could be a lot of reasons that are highly engine dependent, for this specific case. A general answer, perhaps... not specific to the case you've specified. Query optimization is a science with multiple dimensions [1]. I'd wager every problem in computer science plays a role somehow in query optimization. Query performance is based on a combination of actions you take to optimize the design of your system to get the best performance (e.g. data modelling, index design, hardware, query style, and more), and the patterns the system can recognize based on your inputs and the data itself, with the resources it has available, to optimize your queries. There are known patterns for optimization that are discovered over the years, many hard learned from practical experience. This is why older "popular" engines sometimes are more mature and more performant - they have optimizations built for the common use cases over long periods of time. That is not to say older engines are always better, just that they have often had more exposure to the variety of problems that occur. The reason why the engine "can't figure it out" is that most engines, even the best ones, are quite complex - combinations of known rules as well as more fuzzy logic, where the engine uses a combination of information and heuristics to essentially explore a possible solution space, to try to find the optimal execution plan. Making the right decision, well, can be difficult and given the nature of these things, sometimes the optimizer makes the wrong decision (this is why "hints" exist, sometimes, you can force the optimizer to do what you see is obvious - but this is suboptimal for you). In some cases, finding an optimal execution plan can actually be quite computationally expensive, and/or quite time consuming, or the engine in question may simply have no logic coded to handle the case. Optimization is all about finding the balance between finding the most performant query plan, but in the least amount of time, with the least computational and I/O impact to the overall system, that returns the right result. Optimizers are also highly depending on the capabilities of the engineering teams that build them. It is not an easy problem, and it is an area which one could liken to almost machine learning/artificial intelligence, in one way. There are so many possible options, the problem space so big, with so many different ways to approach a given scenario, that it can be difficult for the "engine" to decide. This is why known patterns were created, for example, dimensional data models for analytical queries vs. 3rd normal form. Dimensional data models enable certain optimizations, for example, star schemas [2]. If you take a combination of implementing known patterns, along with optimizers written by engineers that exploit those patterns, you can get to a world of better performance. However, in a world that is, let's say.. more "open ended" - for example, the world of data in a "data lake", where data models are not optimized, data comes in unpredictable multiple shapes/sizes, then it often comes down to combinations of elegant/complex engines that can interpret the shapes of data, cardinality, and other characteristics, make use of much larger distributed compute and system performance, and in some cases - often brute force to arrive at the best query plan or performance possible. There are so many levels of optimization.. for example, if you were to look at things like Trino [3], which started its genesis as PrestoDb in Facebook - you will see special CPU optimizations (e.g. SIMD instructions), vectorized/pipelined operations - there are storage engine optimizations, memory optimizations, etc. etc. It truly is a complex and fascinating problem. Source: I was a technical product manager for a federated query engine. [1] https://en.wikipedia.org/wiki/Query_optimization https://en.wikipedia.org/wiki/Query_optimization [2] https://en.wikipedia.org/wiki/Star_schema https://en.wikipedia.org/wiki/Star_schema [3] https://trino.io/ https://trino.io/