Re: Freeze the inserted tuples during CTAS?

> On Mar 3, 2021, at 1:35 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> On Sun, Feb 21, 2021 at 4:46 PM Paul Guo <guopa(at)vmware(dot)com> wrote:
>> Attached is the v2 version that fixes a test failure due to plan change (bitmap index scan -> index only scan).

> I think this is a good idea.

> BTW, how much does this patch affect the CTAS performance? I expect
> it's negligible but If there is much performance degradation due to
> populating visibility map, it might be better to provide a way to
> disable it.

Yes, this is a good suggestion. I did a quick test yesterday.

Configuration: shared_buffers = 1280M and the test system memory is 7G.

Test queries:
checkpoint;
\timing
create table t1 (a, b, c, d) as select i,i,i,i from generate_series(1,20000000) i;
\timing
select pg_size_pretty(pg_relation_size('t1'));

Here are the running time:

HEAD : Time: 10299.268 ms (00:10.299) + 1537.876 ms (00:01.538)
Patch : Time: 12257.044 ms (00:12.257) + 14.247 ms

The table size is 800+MB so the table should be all in the buffer. I was surprised
to see the patch increases the CTAS time by 19.x%, and also it is not better than
"CTAS+VACUUM" on HEAD version. In theory the visibility map buffer change should
not affect that much. I looked at related code again (heap_insert()). I believe
the overhead could decrease along with some discussed CTAS optimization
solutions (multi-insert, or raw-insert, etc).

I tested 'copy' also. The COPY FREEZE does not involve much overhead than COPY
according to the experiement results as below. COPY uses multi-insert. Seems there is
no other difference than CTAS when writing a new table.

COPY TO + VACUUM
Time: 8826.995 ms (00:08.827) + 1599.260 ms (00:01.599)
COPY TO FREEZE + VACUUM
Time: 8836.107 ms (00:08.836) + 13.581 ms

So maybe think about doing freeze in CTAS after optimizing the CTAS performance
later?

By the way, ‘REFRESH MatView’ does freeze by default. Matview is quite similar to CTAS.
I did test it also and the conclusion is similar to that of CTAS. Not sure why FREEZE was
enabled though, maybe I missed something?