Re: star schema and the optimizer

I wrote:
> I had actually thought that we'd fixed this type of problem in recent
> versions, and that you should be able to get a plan that would look like

> Nestloop
> -> scan dim1
> -> Nestloop
> -> scan dim2
> -> indexscan fact table using dim1.a and dim2.b

> which would arise from developing an indexscan on fact that's
> parameterized by both other tables, resolving one of those
> parameterizations via a join to dim2, and then the other one
> via a join to dim1. I'm not sure offhand why that isn't working
> in this example.

It looks like the issue is that the computation of param_source_rels
in add_paths_to_joinrel() is overly restrictive: it thinks there is
no reason to generate a parameterized-by-dim2 path for the join
relation {fact, dim1}, or likewise a parameterized-by-dim1 path for
the join relation {fact, dim2}. So what we need is to understand
when it's appropriate to do that. Maybe the mere existence of a
multiply-parameterized path among fact's paths is sufficient.

regards, tom lane