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