Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Introduce new multi insert Table AM and improve performance of
 various SQL commands with it for Heap AM
From: Jingtang Zhang <mrdrivingduck@gmail.com>
In-Reply-To: <CALj2ACUVE8CYvYrudem4bR7W3sNRE-akC4B996K65_7C6xTBJQ@mail.gmail.com>
Date: Thu, 31 Oct 2024 12:17:14 +0800
Cc: pgsql-hackers@lists.postgresql.org,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <9F9326B4-8AD9-4858-B1C1-559FC64E6E93@gmail.com>
References: <CALj2ACXdrOmB6Na9amHWZHKvRT3Z0nwTRsCwoMT-npOBtmXLXg@mail.gmail.com>
 <20230603223824.o7iyochli2dwwi7k@alap3.anarazel.de>
 <CALj2ACX5UMWVFdrRNUE0KDrg54WV1cumBXwcETXhrPc1ibKAQA@mail.gmail.com>
 <CAAWbhmj5Pio3nOUakObzLGCSS9dwFfgsNVDhwTGzXNwZc00uCQ@mail.gmail.com>
 <CALj2ACVHC=c6eC9SRxhcTUrnXvNDNkEBgedi2WkVJYRb=0sWYw@mail.gmail.com>
 <CALj2ACVE2h=LnFnpr3rh+6SZzdwzW5EZOYG2Z0t=p28Fn75eag@mail.gmail.com>
 <CALj2ACWT0Rz8oybWBm5W4CeS0DvFkwaw-pEvGArhDLyPbZnW_g@mail.gmail.com>
 <CALj2ACWxO3HPtpYZb765LZk-uKVuAvZPO1HDeZ8=mzMgVPgaww@mail.gmail.com>
 <CALj2ACXJA4QQ_6zAHez0Uy-9t-ebmpox2y1QBja+mF4QP+h8WQ@mail.gmail.com>
 <CAD21AoD97mhzF8cqsd2v1jg9z8xfvAJrPx6Wvi+Ev0Hmu96LJA@mail.gmail.com>
 <CALj2ACUcv5pZoB0=gRrz54M9+YT9JCmo6FYyo5WUS6wnS+em=A@mail.gmail.com>
 <CALj2ACWm77YofBMs9x3Zmp3ctNAhcS4TvPVuXKdfwCr22FqOHg@mail.gmail.com>
 <6be6f58815dc0844fbe058edf56b4e735a6efc1c.camel@j-davis.com>
 <CALj2ACWqVzhxDuWNTWAH-LuADvsyX0r-wpwgeJ+Q1FnAKjY5Yw@mail.gmail.com>
 <2280bf7241119bb88cbe0fe5eb36490cbd04c0c0.camel@j-davis.com>
 <CALj2ACU70HZm+0QRJdkGA5RdJUo4zPYnV2hzkiV-wH5QS2PAEQ@mail.gmail.com>
 <12f1a2d8dd3b6305c0354f1c701f44b7be5e54eb.camel@j-davis.com>
 <CALj2ACVMV=gMROte2=0LBFnSCRvzL4D9WK6oQ9ZHr4Qj2S8xWA@mail.gmail.com>
 <8633171cb034aafc260fdf37df04b6c779aa1e2f.camel@j-davis.com>
 <CALj2ACX9vVYHYkX8e6w058EuAs8JL5EsnzadTxGhpiE_Ep_ByA@mail.gmail.com>
 <f2540511eff777eca435e60b9129eaa43e2ad548.camel@j-davis.com>
 <CALj2ACWTrx1zxWvq8Uj2rEwCsDgQHeJ53WdvzZUw3kW+_VPG6A@mail.gmail.com>
 <CALj2ACUz5+_YNEa4ZY-XG960_oXefM50MjD71VgSCAVDkF3bzQ@mail.gmail.com>
 <229c4f7219ed164088dadc935df21e1cf125e191.camel@j-davis.com>
 <CALj2ACX90L5Mb5Vv=jsvhOdZ8BVsfpZf-CdCGhtm2N+bGUCSjg@mail.gmail.com>
 <CALj2ACXnCvcYvaz4aDH_ezUBJanz_Boi8W=76fOwrxiwSnUFOQ@mail.gmail.com>
 <d079c042180a698179384d6a7a3109cd8310e285.camel@j-davis.com>
 <23a29125a2d07f96d49f97c31fcdb09a7f0ff6c1.camel@j-davis.com>
 <CALj2ACUBwVu_1LOWF07Acv+dWx5NO7uTqAPMNwqi5WpJRw69_Q@mail.gmail.com>
 <d1217312e89c7f59489f90613fcbc9e36ac4afbf.camel@j-davis.com>
 <CALj2ACWUUTFrTF0L_xdj64i9xsjf_+4nt_HPEBiM17cxEk4CWg@mail.gmail.com>
 <CAPsk3_AuOsLcOrgTX4-QDP0Qv+AHBuuH3fTK6MwVacH6gvR1Nw@mail.gmail.com>
 <CALj2ACUVE8CYvYrudem4bR7W3sNRE-akC4B996K65_7C6xTBJQ@mail.gmail.com>
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/9F9326B4-8AD9-4858-B1C1-559FC64E6E93%40gmail.com>
Precedence: bulk

Hi~

I did some performance test these days, and I have some findings.

=46rom the archive months ago, I found there were discussions about =
which type
of TupleTableSlot to use for buffering tuples. A single column mat view =
was
used for evaluation. Finally we used virtual one.

However when I test with a 32-columns mat view, I get regression.

Test case:

-- prepare
create table test as=20
  select
    i as id0,
    i + 1 as id1,
    i + 2 as id2,
    i + 3 as id3,
    i + 4 as id4,
    i + 5 as id5,
    i + 6 as id6,
    i + 7 as id7,
    i + 8 as id8,
    i + 9 as id9,
    i + 10 as id10,
    i + 11 as id11,
    i + 12 as id12,
    i + 13 as id13,
    i + 14 as id14,
    i + 15 as id15,
    i + 0.01 as f0,
    i + 0.1 as f1,
    i + 0.2 as f2,
    i + 0.3 as f3,
    i + 0.4 as f4,
    i + 0.5 as f5,
    i + 0.6 as f6,
    i + 0.7 as f7,
    i + 0.8 as f8,
    i + 0.9 as f9,
    i + 1.01 as f10,
    i + 1.1 as f11,
    i + 1.2 as f12,
    i + 1.3 as f13,
    i + 1.4 as f14,
    i + 1.5 as f15,
    i + 1.6 as f16
  from generate_series(1,5000000) i;

-- run
create materialized view m1 as select * from test;


HEAD:
Time: 13615.542 ms (00:13.616)
Time: 13545.706 ms (00:13.546)
Time: 13578.475 ms (00:13.578)

Patched
Time: 20112.734 ms (00:20.113)
Time: 19996.957 ms (00:19.997)
Time: 19936.871 ms (00:19.937)


I did a quick perf, the overhead seems to come from virtual tuple =
materialization.

HEAD:
  12.29%  postgres            [.] pg_checksum_block
   6.33%  postgres            [.] GetPrivateRefCountEntry
   5.40%  postgres            [.] pg_comp_crc32c_sse42
   4.54%  [kernel]            [k] copy_user_enhanced_fast_string
   2.69%  postgres            [.] BufferIsValid
   1.52%  postgres            [.] XLogRecordAssemble

Patched:
  11.75%  postgres            [.] tts_virtual_materialize
   8.87%  postgres            [.] pg_checksum_block
   8.17%  postgres            [.] slot_deform_heap_tuple
   8.09%  postgres            [.] heap_compute_data_size
   6.17%  postgres            [.] fill_val
   3.81%  postgres            [.] heap_fill_tuple
   3.37%  postgres            [.] tts_virtual_copyslot
   2.62%  [kernel]            [k] copy_user_enhanced_fast_string

Not sure if it is a universal situation.


=E2=80=94
Regards, Jingtang