[dspillett]

> it would take potentially minutes

This is a key part of the problem, and something that people don't realise about query planners. The goal of the planner is not to find the best query plan no matter what, or even to find the best plan at all, it is instead to try to find a good enough plan quickly.

The QPs two constraints (find something good enough, do so very quickly) are often diametrically opposed. It must be an interesting bit of code to work on, especially as new features are slowly added to the query language.

[nasretdinov]

Yeah, exactly. You need to optimise for the overall query duration, including the optimiser itself, and obviously everyone's workload is different, so the right balance may as well not exist at all

[jononor]

This highlights that there is space for an offline automated query omptimizer/rewriter, to compliment the online query planning?

[dspillett]

MS are tinkering with things a little in that direction with recent versions of SQL Server (usually in AzureSQL first then pushed to on-prem releases), though this is mostly around avoiding over-caching. A significant problem with query planners is that to make them better you make them slower, so you introduce caching, but that means you might end up using a bad plan when inputs change.