Materialized view rewriting with joins

[Michael Mior <mm...@uwaterloo.ca>]

Hi all,

In a thread a while back I mentioned an interest in implementing view-based
query rewriting rules for materialized views which include joins. Thanks to
some help from Jesús, I have an initial implementation ready for
discussion. It's very limited at this point, but does still handle some
interesting cases.

The JIRA (with a link to a GitHub PR) is below. I'd appreciate if someone
has the opportunity to review. Thanks!

https://issues.apache.org/jira/browse/CALCITE-1389

Cheers,
--
Michael Mior
michael.mior@gmail.com

Re: Materialized view rewriting with joins

[Michael Mior <mm...@uwaterloo.ca>]

I've considered using lattices, although I haven't done so at this point. I
will note that I also haven't built the filter tree structure mentioned in
the paper so there's still potential to fit in lattices for optimization.
Right now the algorithm just examines all possible views for each query. I
can see how lattices might be useful although I don't have any immediate
ideas on how to make use of them in the implementation.

--
Michael Mior
michael.mior@gmail.com

2016-09-27 16:59 GMT-04:00 Julian Hyde <jh...@apache.org>:

> I haven't looked at your code yet but I am familiar with the paper. Did
> you consider implementing that paper by building on top of Calcite's
> lattice construct?
>
> The problem with "free form" materialized views is that there tend to be a
> lot of them. The paper aims to solve that problem, and so do lattices. But
> lattices are better: they can gather statistics and recommend creating
> views that don't exist but would likely be useful.
>
> Lattices are essentially the same as the SPJ views described in the paper,
> but of course today they need to be created by hand. (I would argue that
> for DW-style workloads, creating lattices by hand is a lot more practical
> than creating MVs by hand. This is not just about making the optimizer's
> life easier, but also about making the DBA's life easier. MVs are not easy
> to manage operationally.) By anyway, if people have created a lot of MVs by
> hand, my idea was to have an algorithm that created lattices automatically,
> and thereby reduce the cost of examining all of those MVs.
>
> The main missing piece, as I see it, is an algorithm that, given a
> collection of MVs, creates an optimal set of lattices such that each MV
> belongs to a lattice.
>
> (I made the same comment on https://issues.apache.org/jira
> /browse/CALCITE-1389 <https://issues.apache.org/jira/browse/CALCITE-1389>,
> but I’m posting to this list because I think wider discussion would be
> useful.)
>
> Julian
>
> > On Sep 27, 2016, at 11:32 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> >
> > Hi all,
> >
> > In a thread a while back I mentioned an interest in implementing
> view-based
> > query rewriting rules for materialized views which include joins. Thanks
> to
> > some help from Jesús, I have an initial implementation ready for
> > discussion. It's very limited at this point, but does still handle some
> > interesting cases.
> >
> > The JIRA (with a link to a GitHub PR) is below. I'd appreciate if someone
> > has the opportunity to review. Thanks!
> >
> > https://issues.apache.org/jira/browse/CALCITE-1389
> >
> > Cheers,
> > --
> > Michael Mior
> > michael.mior@gmail.com
>
>

Re: Materialized view rewriting with joins

[Julian Hyde <jh...@apache.org>]

I haven't looked at your code yet but I am familiar with the paper. Did you consider implementing that paper by building on top of Calcite's lattice construct?

The problem with "free form" materialized views is that there tend to be a lot of them. The paper aims to solve that problem, and so do lattices. But lattices are better: they can gather statistics and recommend creating views that don't exist but would likely be useful.

Lattices are essentially the same as the SPJ views described in the paper, but of course today they need to be created by hand. (I would argue that for DW-style workloads, creating lattices by hand is a lot more practical than creating MVs by hand. This is not just about making the optimizer's life easier, but also about making the DBA's life easier. MVs are not easy to manage operationally.) By anyway, if people have created a lot of MVs by hand, my idea was to have an algorithm that created lattices automatically, and thereby reduce the cost of examining all of those MVs.

The main missing piece, as I see it, is an algorithm that, given a collection of MVs, creates an optimal set of lattices such that each MV belongs to a lattice.

(I made the same comment on https://issues.apache.org/jira/browse/CALCITE-1389 <https://issues.apache.org/jira/browse/CALCITE-1389>, but I’m posting to this list because I think wider discussion would be useful.)

Julian

> On Sep 27, 2016, at 11:32 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> 
> Hi all,
> 
> In a thread a while back I mentioned an interest in implementing view-based
> query rewriting rules for materialized views which include joins. Thanks to
> some help from Jesús, I have an initial implementation ready for
> discussion. It's very limited at this point, but does still handle some
> interesting cases.
> 
> The JIRA (with a link to a GitHub PR) is below. I'd appreciate if someone
> has the opportunity to review. Thanks!
> 
> https://issues.apache.org/jira/browse/CALCITE-1389
> 
> Cheers,
> --
> Michael Mior
> michael.mior@gmail.com