Re: star join optimization

On 14/11/11 12:37, Robert Haas wrote:
> 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.
>
Ok, I'm working in that project. I will send you my results
and comparision with SQL server HHJ optimization in one or two months.

Regards

--
Rudyar Cortés.
Estudiante de Ingeniería Civil Informática
Universidad Técnica Federico Santa María.