Re: star schema and the optimizer

On 27/02/2015 19:45, Tom Lane wrote:
>> 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?

From what I see, it's just perfect. I'll give it a more thorough look a 
bit later, but it seems to be exactly what I was waiting for.

Thanks a lot.

Regards