TPC-H Query 2 and 17 - De-correlation
The great promise databases make to programmers is: "Tell me what you want and I will figure out the fastest way to do it."
A database is a computer science engine — it knows things and optimisations that the average programmer has not heard about...
Sometimes...
Some queries look "easy" to programmers — but databases often need to apply a method called de-correlation to make them effective. Even back in the 90ies, the great minds of the TPC council knew how to design queries that look for this optimisation.
Today we will learn how to spot these cases and what happens when databases fail at optimising them.