Re: star schema and the optimizer

Marc Cousin <cousinmarc@gmail.com> writes:
> So I gave a look at the optimizer's code to try to understand why I got this problem. If I understand correctly, the
optimizerwon't do cross joins, except if it has no choice.
 

That's right, and as you say, the planning-speed consequences of doing
otherwise would be disastrous.  However, all you need to help it find the
right plan is some dummy join condition between the dimension tables,
which will allow the join path you want to be considered.  Perhaps you
could do something like

SELECT * FROM dim1,dim2,facts WHERE facts.dim1=dim1.a and facts.dim2=dim2.a and dim1.b=12 AND dim2.b=17 and
(dim1.a+dim2.a)is not null;
 

The details of the extra condition aren't too important as long as it
mentions all the dimension tables and (a) is always true but (b) is
not so obviously always true that the planner can reduce it to constant
true.  (Thus, for example, you might think you could do this with zero
runtime cost by writing "dummy(dim1.a,dim2.a)" where dummy is an
inlineable SQL function that just returns constant TRUE ... but that's
too cute, it won't fix your problem.)
        regards, tom lane