Re: Implementing Incremental View Maintenance

On Wed, 11 Nov 2020 19:10:35 +0300
Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:

Thank you for reviewing this patch!

>
> The patch is not applied to the current master because makeFuncCall
> prototype is changed,
> I fixed it by adding COAERCE_CALL_EXPLICIT.

The rebased patch was submitted.

> Ooops! Now TPS are much lower:
>
> tps = 141.767347 (including connections establishing)
>
> Speed of updates is reduced more than 70 times!
> Looks like we loose parallelism because almost the same result I get
> with just one connection.

As you and Ishii-san mentioned in other posts, I think the reason would be a
table lock on the materialized view that is acquired during view maintenance.
I will explain more a bit in another post.

> 4. Finally let's create one more view (it is reasonable to expect that
> analytics will run many different queries and so need multiple views).
>
> create incremental materialized view teller_avgs as select
> t.tid,avg(abalance) from pgbench_accounts a join pgbench_tellers t on
> a.bid=t.bid group by t.tid;
>
> It is great that not only simple aggregates like SUM are supported, but
> also AVG.
> But insertion speed now is reduced twice - 72TPS.

Yes, the current implementation takes twice time for updating a table time
when a new incrementally maintainable materialized view is defined on the
table because view maintenance is performed for each view.

>
> So good news is that incremental materialized views really work.
> And bad news is that maintenance overhead is too large which
> significantly restrict applicability of this approach.
> Certainly in case of dominated read-only workload such materialized
> views can significantly improve performance.
> But unfortunately my dream that them allow to combine OLAP+OLPT is not
> currently realized.

As you concluded, there is a large overhead on updating base tables in the
current implementation because it is immediate maintenance in which the view
is updated in the same sentence where its base table is modified. Therefore,
this is not suitable to OLTP workload where there are frequent updates of
tables.

For suppressing maintenance overhead in such workload, we have to implement
"deferred maintenance" which collects table change logs and updates the view
in another transaction afterward.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>