Re: query execution question

2011/2/9 amit sehas <cun23(at)yahoo(dot)com>:

> Lets say that the cost based optimizer determines that the order of the
> joins should be   T1.a=T2.b followed by T2.c = T3.d followed by T3.e = T4.f
>
> the question we have is during query execution are the joins evaluated
> completely one by one in that order, or the first join is evaluated
> completely and generates an intermediate table which is then utilized
> to perform the next join....this means that for such a query we will need
> space for all the intermediate tables, which if they are very large tables
> as they are in our case can significantly alter the cost of the operations...

[ This is a question more appropriate for pgsql-performance. ]

The optimizer doesn't only determine the order (or "tree" actually) in
which to perform the joins, but also how to perform them: nested loop,
merge, or hash join. Depending on those physical join types, something
might need to be materialized (merge: the intermediate sort "tapes";
hash: the full outer operand's contents) or not (nested loop).

Please see the EXPLAIN statement if you want to know how the query
would be executed.

Nicolas