Re: Performance degradation of REFRESH MATERIALIZED VIEW

On 5/18/21 4:20 AM, Masahiko Sawada wrote:
> ...
>>>>
>>>>> I think the changes for heap_multi_insert() are fine so we can revert
>>>>> only heap_insert() part if we revert something from the v14 tree,
>>>>> although we will end up not inserting frozen tuples into toast tables.
>>>>>
>>>>
>>>> I'd be somewhat unhappy about reverting just this bit, because it'd mean
>>>> that we freeze rows in the main table but not rows in the TOAST tables
>>>> (that was kinda why we concluded we need the heap_insert part too).
>>>>
>>>> I'm still a bit puzzled where does the extra overhead (in cases when
>>>> freeze is not requested) come from, TBH.
>>>
>>> Which cases do you mean? Doesn't matview refresh always request to
>>> freeze tuples even after applying the patch proposed on this thread?
>>>
>>
>> Oh, I didn't realize that! That'd make this much less of an issue, I'd
>> say, because if we're intentionally freezing the rows it's reasonable to
>> pay a bit of time (in exchange for not having to do it later). The
>> original +25% was a bit too much, of course, but +5% seems reasonable.
>
> Yes. It depends on how much the matview refresh gets slower but I
> think the problem here is that users always are forced to pay the cost
> for freezing tuple during refreshing the matview. There is no way to
> disable it unlike FREEZE option of COPY command.
>

Yeah, I see your point. I agree it's unfortunate there's no way to
disable freezing during REFRESH MV. For most users that trade-off is
probably fine, but for some cases (matviews refreshed often, or cases
where it's fine to pay more but later) it may be an issue.

From this POV, however, it may not be enough to optimize the current
freezing code - it's always going to be a bit slower than before. So the
only *real* solution may be adding a FREEZE option to the REFRESH
MATERIALIZED VIEW command.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company