Re: Query Rewrite for Materialized Views (Postgres Extension)

Hi Nico,

By the way, I do agree with your point about MERGE — if we can factor MV updates in that fashion, it will certainly save.

I didn’t reply immediately because your point caught me off guard:

> […] If you look at my
> sketch for how to do it, you'll notice that many of the sorts of queries
> that one would choose to materialize... are not really amenable to this
> treatment […]

I’d rather presumed that there would be many examples of DML on base relations that could trigger a direct (incremental) update to the MV. I had presumed it, but not actually done any research.

So I did a bit of a trawl. There’s actually quite a lot of academic research out there, including [1] and [2]. [2] references a bunch of methods for incremental MV refresh, and ties them into a graph query context. I’m not sure if the graph query context itself is relevant for Postgres, but it’s certainly interesting and perhaps suggests that incremental refresh of at least some RECURSIVE MVs may not be entirely impossible. I also found [3], which is /very/ dated, but it strongly supports that MVs are a performant path to executing certain types of query.

So I definitely agree with you in the general case, but it seems there is scope to provide an incremental MV refresh capability that is broadly useful.

Almost certainly, any initial implementation would quickly fall back to a “full refresh”. But the refresh planner’s capability develops, I wonder if it could not embody an intelligent strategy that might even recognise common recursive patterns such as the transitive closure you mention, and refresh on an incremental basis — that would be really quite a cool capability to have.

denty.

[1] http://www.dbnet.ece.ntua.gr/pubs/uploads/TR-1998-14.ps <http://www.dbnet.ece.ntua.gr/pubs/uploads/TR-1998-14.ps>
[2] https://arxiv.org/pdf/1806.07344.pdf <https://arxiv.org/pdf/1806.07344.pdf>
[3] https://www.cs.indiana.edu/pub/techreports/TR280.pdf <https://www.cs.indiana.edu/pub/techreports/TR280.pdf>