[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [HACKERS] star schema and the optimizer
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2015-02-28 16:11:05
Message-ID: 28132.1425139865 () sss ! pgh ! pa ! us
[Download RAW message or body]

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 for me.

> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[prev in list] [next in list] [prev in thread] [next in thread]