amit sehas <cun23(at)yahoo(dot)com> wrote:
> We are making some performance measurements, we are trying to
> determine query execution behavior.
I hope you're not doing this on an un-tuned server or "toy" tables.
There are a number of configuration parameters which should be tuned
for your particular server, which will affect the plans used on that
server. Also, the plans the optimizer will choose for small tables
are generally quite different for those chosen for large tables.
> 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...
The query looks at the estimated cost of various plans, and uses the
one with the lowest estimated cost. The plan consists of a set of
steps, with each step pulling rows from one or more lower steps; so
the process is driven from the top down -- you try to pull a result
row, and if the top level step needs something from a lower row it
tries to pull that, etc. Some plans involve "materializing" a set
of rows, some pull through indexes or heap scans as rows are
requested from them. Depending on the query, the indexes, the table
sizes, etc., a SELECT statement with joins like you describe might
use nested index joins and never have more than a few rows in RAM at
a time without ever writing anything to disk.
By the way, this is the wrong list for this question. The -hackers
list is for discussion about work on developing the product.
Questions like this about how it works are better posted to the
-general or -novice list.
-Kevin