Re: star join optimization

On Mon, Nov 14, 2011 at 10:36 AM, Rudyar <rudyar(dot)cortes(at)gmail(dot)com> wrote:
> On 14/11/11 12:20, Robert Haas wrote:
>>
>> On Mon, Nov 14, 2011 at 8:25 AM, Rudyar<rudyar(dot)cortes(at)gmail(dot)com>  wrote:
>>>
>>> the hybrid hash join algorithm implemented in the current version of
>>> PostgreSQL has any kind of optimization
>>> for star join queries for Data Warehouse model?
>>
>> Not really.  As much as possible, we try to make the query optimizer a
>> general-purpose tool that can handle any query you happen to throw at
>> it, rather than putting in special-purpose hacks to cater to specific
>> types of queries.  I'm not aware of anything in particular that we
>> could do to better optimize the star-join case than what we do for any
>> other query.
>>
>> Now, one thing that was discussed a year or two ago was the
>> possibility of considering join algorithms that can handle more than
>> two tables at a time.  Currently, we don't do that, so a four-way join
>> will be implemented either by joining two tables, then the other two
>> tables, and then the results of those; or more commonly by joining two
>> tables, joining the results to a third table, and then joining those
>> results to the final table.  Due to the pipelined nature of our
>> executor, this works pretty well, but it's possible that there are
>> better algorithms out there.
>>
> Thanks Robert,
>
> I'm a new programmer in postgreSQL source code and I working in my tesis
> project about that optimizations to HHJ algorithm.
> I think so is very useful that optimizer recognize one star join and apply
> this optimizations..
> For example, SQL Server and Oracle databases implements star join query
> optimizations for OLAP queries in DW.
>
> How can contribute with my tesis project to postreSQL source code?

A good example might be to show us some of the specific cases that you
think can be improved. Perhaps with a script to set up the test data,
and EXPLAIN ANALYZE output from the queries involved, and a
description of where you see an opportunity for improvement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company