From: | David Link <dlink(at)soundscan(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Why is outer Join way quicker? |
Date: | 2002-04-25 14:26:20 |
Message-ID: | 3CC8120C.54D73772@soundscan.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Very Clear. It makes sense. Thanks Stephan.
Stephan Szabo wrote:
>
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2002-04-25 15:29:16 | Re: importing csv files into v7.2.1 on Solaris 8 x86 |
Previous Message | Tom Lane | 2002-04-25 13:56:55 | Re: No long-lived transaction, still can't delete tuples |