public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jingtang Zhang <[email protected]>
To: Bharath Rupireddy <[email protected]>
Cc: [email protected]
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM
Date: Thu, 31 Oct 2024 12:17:14 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALj2ACUVE8CYvYrudem4bR7W3sNRE-akC4B996K65_7C6xTBJQ@mail.gmail.com>
References: <CALj2ACXdrOmB6Na9amHWZHKvRT3Z0nwTRsCwoMT-npOBtmXLXg@mail.gmail.com>
	<[email protected]>
	<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>
	<[email protected]>
	<CALj2ACWqVzhxDuWNTWAH-LuADvsyX0r-wpwgeJ+Q1FnAKjY5Yw@mail.gmail.com>
	<[email protected]>
	<CALj2ACU70HZm+0QRJdkGA5RdJUo4zPYnV2hzkiV-wH5QS2PAEQ@mail.gmail.com>
	<[email protected]>
	<CALj2ACVMV=gMROte2=0LBFnSCRvzL4D9WK6oQ9ZHr4Qj2S8xWA@mail.gmail.com>
	<[email protected]>
	<CALj2ACX9vVYHYkX8e6w058EuAs8JL5EsnzadTxGhpiE_Ep_ByA@mail.gmail.com>
	<[email protected]>
	<CALj2ACWTrx1zxWvq8Uj2rEwCsDgQHeJ53WdvzZUw3kW+_VPG6A@mail.gmail.com>
	<CALj2ACUz5+_YNEa4ZY-XG960_oXefM50MjD71VgSCAVDkF3bzQ@mail.gmail.com>
	<[email protected]>
	<CALj2ACX90L5Mb5Vv=jsvhOdZ8BVsfpZf-CdCGhtm2N+bGUCSjg@mail.gmail.com>
	<CALj2ACXnCvcYvaz4aDH_ezUBJanz_Boi8W=76fOwrxiwSnUFOQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CALj2ACUBwVu_1LOWF07Acv+dWx5NO7uTqAPMNwqi5WpJRw69_Q@mail.gmail.com>
	<[email protected]>
	<CALj2ACWUUTFrTF0L_xdj64i9xsjf_+4nt_HPEBiM17cxEk4CWg@mail.gmail.com>
	<CAPsk3_AuOsLcOrgTX4-QDP0Qv+AHBuuH3fTK6MwVacH6gvR1Nw@mail.gmail.com>
	<CALj2ACUVE8CYvYrudem4bR7W3sNRE-akC4B996K65_7C6xTBJQ@mail.gmail.com>

Hi~

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

From 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 
  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.


—
Regards, Jingtang

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox