Posts with tag: query-optimisation

TPC-H Query 3 - Join Ordering and Heap Sorting

I want to teach you an important skill that will serve your well as a database specialist. One blog entry is not going to be enough, but here is my goal:

When you look at an SQL query in the future, you should be able to answer this question: "What query plan do I want this query to have?" You will also be able to make an educated guess about what join order is best. As you hoard optimisation tricks in your arsenal - you will also be able to make guesses about what additional tricks the database could play (and you can force those tricks if the database does not do it for you).

Queries can be incredibly complex and still work very well, the beauty of SQL. But, no matter the complexity - the method I'm going to teach you will allow you to reason about the query. It starts with the selectivity of filters and Query 3 from TPC-H is a good way to illustrate it.

Read More...

Join Elimination in Query Planners

Most relational databases are both by human SQL query writers and tools that automatically generate queries. Query tools frequently add joins into the queries, even when those joins are not needed. This results in unnecessary query slowdowns and increase CPU usage.

For example, consider the classic Star Schema model with a fact table in the middle and dimension tables surrounding it. From a reporting tool perspective, it is useful to have a view on top of that schema which joins all the tables and presents the user with the illusion of a single table. This approach of creating large join views has some drawbacks that can only be offset by query optimisers if the database designer has correctly declared key constraints in the data model.

Read More...