SET PLANNER TO COST
or similar, and default to a more "static" one.
For a cute little select statement, it might be true. However it's just not manageable most of the time.
Note that it's an introduction to building database systems (not just using them).
https://www.youtube.com/playlist?list=PLSE8ODhjZXja3hgmuwhf8...
As an example, consider a memo group that contains both a MergeJoin and a HashJoin operator. The MergeJoin "requires" its inputs to be sorted on the same key. If an input is not already sorted, then we insert a "sort enforcer" operator that will provide the required ordering. Of course, that increases the cost of producing the input, which the optimizer must take into account.
So, getting back to your question, for any given memo group, we always select the equivalent plan with the lowest cost, for a given set of physical properties. However, that has the effect of sometimes selecting a memo group operator that has a higher cost, because it "pays off" higher up the operator tree. Back to my example, say the SQL query is something like this:
SELECT * FROM a, b WHERE a.name=b.name ORDER BY a.name
Im pretty sure cost-based optimization is common enough and practically proven effective enough that its not really fair to call it a “leap of faith”; eg im pretty sure mysql and postgres both do it (and you can it see it in their EXPLAINs), and I imagine most commercial dbs do as well
Its more of just a natural progression as the db gets improved
Its a good article/work nonetheless, but nothing out of the ordinary in db implementation afaik
+1 on the sentiment, good to see them continuing to improve, they have lots of competition to look at for examples of what to do / not to do. Progress is good none the less.
https://www.postgresql.org/docs/current/planner-optimizer.ht...
Genetic query optimiser: https://www.postgresql.org/docs/current/geqo.html
postgres has a number of hooks, which can be used to override the default behaviour. In particular, there are a bunch of hooks that can be used to install a custom query planner. https://github.com/AmatanHead/psql-hooks/blob/master/Readme....
more generally, ignoring database query optimisation specifically, if you are interested in learning about discrete optimisation techniques, I recommend this course: https://www.coursera.org/learn/discrete-optimization
Edit: Grammar.
Thanks for the link. Wasn't aware of this podcast before.
Any chance you could share more about this? This general area of how to build a production grade SQL optimizer seems to be a thing that's more scattered in tiny pieces across a wide number of papers, or held in peoples' heads, than aggregated in a teaching manner. It seemed that the realistic general advice on how to build a SQL optimizer was to poach someone from the SQL Server team. ;)
I've generally just gone referring back to the unfinished Building Query Compilers[1] when pondering this subject. Not that the hundreds of references don't provide sufficient material to read though as well, but it'd be interesting to hear what a bootcamp like this presented as state of the art.
[1]: http://pi3.informatik.uni-mannheim.de/~moer/querycompiler.pd...
I don't know if Oracle still uses CBO now, or even SQL or PL/SQL but I am sure that a large chunk of their revenue still comes from supporting these legacy systems.
https://github.com/cockroachdb/cockroach/blob/master/pkg/sql...
Might be worth a read if you're interested in implementation techniques.
TLDR: check out CMU DB group at https://db.cs.cmu.edu/
Has any thought been given to whether this query planner could be adapted (much further down the road, I'd guess) to support dynamic replanning? (That is, altering the plan mid-query, if it should be found that the row-count estimates were way off.)
Another commenter posted an interesting paper that's related to your question:
https://www.microsoft.com/en-us/research/uploads/prod/2018/0...
All table statistics are stored in a system table (system.table_statistics). CREATE STATISTICS scans the table, calculating statistics on a column, and adds a new row to system.table_statistics.
We still have a ways to go in making statistics automatic (right now it requires a manual call to CREATE STATISTICS) and more useful to the optimizer (which currently only uses basic stats). We're working on that right now for the 2.2 release.
They have an entire section in their documentation for Python and SQLAlchemy: https://www.cockroachlabs.com/docs/stable/build-a-python-app...