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

After closer study, I think this is an oversight in commit
e2fa76d80ba571d4de8992de6386536867250474, which quoth

+It can be useful for the parameter value to be passed down through
+intermediate layers of joins, for example:
+
+ NestLoop
+ -> Seq Scan on A
+ Hash Join
+ Join Condition: B.Y = C.W
+ -> Seq Scan on B
+ -> Index Scan using C_Z_IDX on C
+ Index Condition: C.Z = A.X
+
+If all joins are plain inner joins then this is unnecessary, because
+it's always possible to reorder the joins so that a parameter is used
+immediately below the nestloop node that provides it. But in the
+presence of outer joins, join reordering may not be possible, and then
+this option can be critical. Before version 9.2, Postgres used ad-hoc

This reasoning overlooked the fact that if we need parameters from
more than one relation, and there's no way to join those relations
to each other directly, then we have to allow passing the dim1 parameter
down through the join to dim2.

The attached patch seems to fix it (modulo the need for some updates
in the README, and maybe a regression test). Could you see if this
produces satisfactory plans for you?

regards, tom lane