Re: star schema and the optimizer

Marc Cousin <cousinmarc@gmail.com> writes:
> I gave it another look this morning. It works very well with the initial test schema. The situation is much improved
forme.
 

> I still have one issue: I've extended the test to more than 2 dimensions.

I tried your original test script with 3 dimension tables, and it gave me
a three-deep nestloop plan once I made the fact table big enough.  I think
your test case has simply not got statistics that encourage doing it that
way.  Notice that the planner thinks (correctly) that it's already down
to fetching only one row from the facts table with dim1 and dim2 as
inputs; so there's no cost advantage to stacking up more nestloops, and
it might as well just materialize the result from that and then join
against dim3 and dim4.  Another factor is that it predicts (less
correctly) that it will get 10 rows from dim3, which would make a straight
nestloop plan about 10x more expensive than what it did here.

You could experiment with turning off enable_material, but I think
the planner may actually be making the right choice here.
        regards, tom lane