CODEWITHSUNDEEP
sqlpostgresqloptimizationquery-optimization
ehmicky
ehmicky

Reputation: 2046

Optimizing regular join SQL queries

How to optimize the speed of SQL queries looking like this:

select ... from TABLE 
  left join TABLE2 on TABLE2.COL2 = TABLE.COL 
  left join TABLE3 on TABLE3.COL2 = TABLE2.COL
  etc.

I am asking from a SQL (precisely Postgres) point of view, e.g.: does the order of the joins matter? Do subqueries or CTE help? Does the type of join matter?

I am not asking from a database implementation point of view, e.g. indexes, tablespaces, configuration variables, etc.

Upvotes: 0

Views: 93

Answers (1)

Antoan Milkov
Antoan Milkov

Reputation: 2228

In theory the order of the joins should not matter since the built-in query optimizer should put the joins that limit more the volume of the result-set before those that has less effect on the volume.
However in my practice I learned that it is always best to try to help the performance as much as you can and put the more restrictive joins before the less restrictive ones.
So generally speaking the less you relay on the query optimizer the better will be the performance in the edge cases.

Here you can learn more about the query optimizer: http://www.postgresql.org/docs/9.1/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-GEQO

As a rule of the thumb using join should be faster than CTE or sub-queries, but this is just a rule and exceptions are still possible.
Also some of the problems need both joins and CTE.

This is kind of killing question: Does the type of join matter?
Yes it does! Actually this matters most of all! :)
Here you can see the idea behind the different join types: http://en.wikipedia.org/wiki/Join_(SQL)

For the left and right join these 2 statements are equal:
... table1 LEFT JOIN table2 ...
... table2 RIGHT JOIN table1 ...

Right and left outer joins are functionally equivalent. Neither provides any functionality that the other does not, so right and left outer joins may replace each other as long as the table order is switched.

Upvotes: 1

Related Questions