Re:SQLite 3.8.0 Release with next-generation query planner
by chris_wot on Tuesday August 27, 2013 @10:33AM
(#6283635)
I love query planners and optimisers. However, here's where I get a bit bemused: as it states in the article, a query planner must often go on incomplete information. Therefore you have to know the underlying assumptions built into the planner.
This is where explain/execution plans are great. Unfortunately, even these aren't completely documented. SQL Server is not bad, and Postgres is not only well documented but you can read the source. Oracle is often a basket case - there are literally thousands of undocumented parameters that you can set, not to mention underlying data views that also haven't been documented. In fact, there have been whole books trying to unravel the Oracle CBO, but even the most obscure often admit that Oracle fix optimiser bugs which can cause an impact on query plans.
In some cases, you must implement hints, because there is no way of catering for all queries under all loads based on all possible data distributions. Sure, you can add a variety of different types of indexes, histograms and other statistics to try to influence the execution plan, but at the end of the day sometimes you simply have to add a hint to force a particular sort of join, etc.
I guess my main point as I muse on query optimizers/planners is that none of them are perfect. It occurs to me that one of the best so far is actually Postgres, but alas - they don't allow for hints.
What's my main point here? It really comes down to the fact that the ideal for database queries is to allow for a declarative syntax ("give me all rows of product x that are over $100") without having to worry about the mechanics of retrieving the data, it's just not entirely possible. You really need to give developers and database folks the ability to nail down the actual mechanics of data manipulation.
This is where explain/execution plans are great. Unfortunately, even these aren't completely documented. SQL Server is not bad, and Postgres is not only well documented but you can read the source. Oracle is often a basket case - there are literally thousands of undocumented parameters that you can set, not to mention underlying data views that also haven't been documented. In fact, there have been whole books trying to unravel the Oracle CBO, but even the most obscure often admit that Oracle fix optimiser bugs which can cause an impact on query plans.
In some cases, you must implement hints, because there is no way of catering for all queries under all loads based on all possible data distributions. Sure, you can add a variety of different types of indexes, histograms and other statistics to try to influence the execution plan, but at the end of the day sometimes you simply have to add a hint to force a particular sort of join, etc.
I guess my main point as I muse on query optimizers/planners is that none of them are perfect. It occurs to me that one of the best so far is actually Postgres, but alas - they don't allow for hints.
What's my main point here? It really comes down to the fact that the ideal for database queries is to allow for a declarative syntax ("give me all rows of product x that are over $100") without having to worry about the mechanics of retrieving the data, it's just not entirely possible. You really need to give developers and database folks the ability to nail down the actual mechanics of data manipulation.