Re: Why is outer Join way quicker?

Stephan Szabo wrote:
>
> On Tue, 23 Apr 2002, David Link wrote:
>
> > Hi.
> >
> > We have found that by using an Outer Join it speeds up this query
> > considerably.
> >
> > Why is that?
> >
> > According to the explain plan the hold up has to do with a single nexted
> > loop and a "Materilize"? This makes no sense to me. Can some one
> > explain.
> >
> > Thanks. -David
> >
> > The difference between these two queries is the first uses an outer join
> > between u and t, while the second (the slower) uses a straight forward
> > join).
>
> See:
> http://www.postgresql.org/idocs/index.php?explicit-joins.html
>
> You can probably use an inner join as well, you just need to use
> the explicit join syntax.

OK. I read that and it helps explain this behavior (new to 7.1)
somewhat. Thank you.

However, it also says that the planner spends some time figuring out
which method of joining is best (a join (b join c)) vs. ((a join b) join
c), etc. It says the user should not have to worry about explicitly
mapping the join order when few tables are involved. In this case I am
using three tables, though fairly large. See explain plans.

You'll notice the explain plans show a difference of magnatude in cost.
If the planner is checking those kind of things, it should pick up on
this and choose the better join order.

Thanks. David