0Today, we don’t want to talk about cost based optimisation, i.e. optimisations that depend on a database’s cost model. We’ll look into much simpler optimisations that can be implemented purely based on meta data (e.g. constraints) and the query itself. They’re usually no-brainers for a database to optimise, because the optimisation will always lead to a better execution plan, independently of whether there are any indexes, or how much data you have, or how skewed your data distribution is.
So, they’re not no-brainers in the sense whether they’re easy for the optimiser teams to implement, but they’re no-brainers in the sense whether they should be done.
These optimisations remove needless, optional work (as opposed to needless, mandatorywork, which I’ve blogged about before).
Where do these optimisations apply?
Most of these optimisations are applied to:
- Fix mistakes in queries.
- Allow for reusing complex views without actually executing the entire logic from the view.
In the first case, you could claim: “Well, then fix the stupid SQL already”, but then again, who never makes any mistakes, right?
Specifically, the second case is really cool, as these optimisations allow us to build complex libraries of views and table valued functions, which we can reuse in several layers.
Databases being used:
This post will evaluate 10 SQL optimisations on the 5 most popular RDBMS:
- Oracle 12.2.
- MySQL 8.0.2.
- SQL Server 2014.
- PostgreSQL 9.6.
- DB2 LUW 10.5.
These will be the 10 optimisation types:
i. Transitive Closure
ii. Impossible Predicates and Unneeded Table Accesses.
iii. Join EliminationRemoving “Silly” Predicates.
iv. Projections in Exists Subqueries.
v. Predicate Merging.
vi. Check Constraints.
vii. Unneeded Self Join
viii. Predicate Pushdown.
