Re: Performance degradation of REFRESH MATERIALIZED VIEW

On Thu, Jun 3, 2021 at 8:02 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> OK,
>
> As mentioned in the previous message, I've reverted most of 39b66a91bd.
> It took a bit longer to test, because the revert patch I shared a couple
> days ago was actually incorrect/buggy in one place.
>
> I'm not entirely happy about the end result (as it does not really help
> with TOAST tables), so hopefully we'll be able to do something about
> that soon.

Me too and +1 for addressing the problem soon for PG15.

> I'm not sure what, though - we've spent quite a bit of time
> trying to address the regression, and I don't envision some major
> breakthrough.
>
> As for the regression example, I think in practice the impact would be
> much lower, because the queries are likely much more complex (not just a
> seqscan from a table), so the query execution will be a much bigger part
> of execution time.
>
> I do think the optimization would be a win in most cases where freezing
> is desirable. From this POV the problem is rather that REFRESH MV does
> not allow not freezing the result, so it has to pay the price always. So
> perhaps the way forward is to add "NO FREEZE" option to REFRESH MV, or
> something like that.

That could be an option. Is it worth analyzing the cause of overhead
and why my patch seemed to avoid it? If we can resolve the performance
problem by fixing heap_insert() and related codes, we can use
HEAP_INSERT_FROZEN for CREATE TABLE AS as well.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/