Re: Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a

On Wed, Dec 2, 2009 at 10:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Not sure what you mean.  There's already a special-case code path for
>> cross joins; but I think it's probably considering a lot of silly
>> paths.  Is there a case where it makes sense to do cross joins at some
>> stage of the process other than last?
>
> They *are* done last, as a rule, because of the heuristic that prefers to
> join where there's a join clause.

Well, when I was testing, I believe I observed that an n-way join with
1 cross join was slower to plan than an n-way join with no cross
joins. ISTM that it should actually be faster, because you should
plan it like an (n-1)-way join and then do the cross join at the end.

> (However I've gotten negative comments
> about that --- some people think that when joining small detail tables
> to a big fact table, it'd be better to cross-join the detail tables and
> then do one multi-clause join to the big table.  I'm unconvinced myself
> but there does seem to be more than one school of thought about it.)

Sounds weird to me. There might be a sweet spot where that's true (3
or 4 detail tables with 2 or 3 rows each, that aren't too wide?) but
even if there is, I bet it's not very big. If someone cares though it
should be possible to convince the planner to execute the query that
way (using OFFSET 0, maybe) and benchmark it vs. whatever the planner
wants to do otherwise.

...Robert