Outer Joins and IVM

[koureasstavros]

I understand the difficulty of having outer joins on incremental materialized views but I think this could be a huge enhancement into PostgreSQL, it would be the perfect combination for near to real time analytics with lightweight modifications to get rid of the old ETL Style of staging and datawarehouse. Most of the transformations usually includes outer joins.

The difficulty just for memorial purposes (based on MS White Paper): A. Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

[yugo-n]

Actually, we have already an outer-join support implementation although it is not pushed in pg_ivm repository. I plan to support it in the near future release.

However, there are a few restrictions in the implementation; for example, columns used in the join condition must appear in the target list, and using an outer-join with an aggregate or DISTINCT is not allowed, etc. (It might be improved in future release, though.)

[koureasstavros]

Hmm that's sounds interesting, thanks a lot for providing this information and I appreciate your work on the Incremental Materialized Views. In case you want people to test this new enhancement just let me know.

One last thing, you mention that outer-join with an aggregate or DISTINCT is not allowed, but what about GROUP BY? Do we really need DISTINCT?

[artu-ole]

@yugo-n Hi there! We'd also be very interested to do some tests with it, do you any updates on the matter?

[yugo-n]

@koureasstavros Sorry for my late reply.

One last thing, you mention that outer-join with an aggregate or DISTINCT is not allowed, but what about GROUP BY? Do we really need DISTINCT?

In the current pg_ivm, GROUP BY without aggregate functions are not allowed, so GROUP BY will not be used with outer-join. Maybe, DISTINCT can be used with outer-join although I have to check it carefully....

[yugo-n]

@yugo-n Hi there! We'd also be very interested to do some tests with it, do you any updates on the matter?

Currently, we plan to release pg_ivm supporting outer-join by this Summer or Autumn.

[koureasstavros]

Hi @yugo-n, is there any news on pg_ivm supporting outer joins?

[ShadowNight001]

Is there any news ?

[konstantingreger]

Hi @yugo-n, we would also be very interested in IVM supporting outer joins and are happy to test for you!