Re: Performance degradation of REFRESH MATERIALIZED VIEW

On 5/11/21 7:35 PM, Tomas Vondra wrote:
>
>
> On 5/11/21 7:25 PM, Andres Freund wrote:
>> Hi,
>>
>> On 2021-05-11 16:07:44 +0200, Tomas Vondra wrote:
>>> On 5/11/21 11:04 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).
>>
>> Is there a reason not to apply a polished version of my proposal? And
>> then to look at the remaining difference?
>>
>
> Probably not, I was just a little bit confused what exactly is going on,
> unsure what to do about it. But if RMV freezes the rows, that probably
> explains it and your patch is the way to go.
>
>>
>>> I'm still a bit puzzled where does the extra overhead (in cases when
>>> freeze
>>> is not requested) come from, TBH. Intuitively, I'd hope there's a way to
>>> eliminate that entirely, and only pay the cost when requested (with the
>>> expectation that it's cheaper than freezing it that later).
>>
>> I'd like to see a profile comparison between those two cases. Best with
>> both profiles done in master, just once with the freeze path disabled...
>>
>
> OK. I'm mostly afk at the moment, I'll do that once I get back home,
> sometime over the weekend / maybe early next week.
>

OK, so here are the flamegraphs, for all three cases - current master,
0c7d3bb99 (i.e. before heap_insert changes) and with the pinning patch
applied. I did this using the same test case as before (50M table), but
with -fno-omit-frame-pointer to get better profiles. It may add some
overhead, but hopefully that applies to all cases equally.

The first 10 runs for each case look like this:

old master patched
----------------------
55045 74284 58246
53927 74283 57273
54090 74114 57336
54194 74059 57223
54189 74186 57287
54090 74113 57278
54095 74036 57176
53896 74215 57303
54101 74060 57524
54062 74021 57278
----------------------
54168 74137 57392
1.36x 1.05x

which is mostly in line with previous findings (the master overhead is a
bit worse, possibly due to the frame pointers).

Attached are the flame graphs for all three cases. The change in master
is pretty clearly visible, but I don't see any clear difference between
old and patched code :-(

regards

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