Re: Why is outer Join way quicker?

On Wed, 24 Apr 2002, David Link wrote:

> Stephan Szabo wrote:
> >
> > 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.

You are explicitly mapping the join order in both queries. I'm not 100%
sure the page mentions, but I think it does... If you do
a,b OUTER JOIN c, postgres takes that as a request to explicitly join
b and c before any other joins with those tables, thus any plan that
joins a to b or c before the outer join is not considered. The thing for
explicitly mapping join order is because with a large number of tables
the decision making process can be complicated and take a fair amount of
time, so you can prune the number of possible join orders with explicitly
using the sql92 join syntax.