MIME-Version: 1.0
References: 
 <CALj2ACVi9eTRYR=gdca5wxtj3Kk_9q9qVccxsS1hngTGOCjPwQ@mail.gmail.com>
 <20201217050522.GU30237@telsasoft.com>
 <CALj2ACVgT1iocd5nQ+rEmqt3xcCONkR037qbc8PiojdR39Ag=w@mail.gmail.com>
 <20201217204442.GX30237@telsasoft.com>
 <CALj2ACW3BC5kgdffZ2LD_CT2wQoXVc29kGB74SVWnGZ=UFqcAQ@mail.gmail.com>
 <20201218175439.GA30237@telsasoft.com> <20201221074725.GF30237@telsasoft.com>
 <CALj2ACWMnZZCu=G0PJkEeYYicKeuJ-X=SU19i6vQ1+=uXz8u0Q@mail.gmail.com>
 <20201225023958.GW30237@telsasoft.com>
 <CALj2ACVDtYYRYD2SC+X2ALOUkhnUcgC7RLxiEYVWW2HxxrfRww@mail.gmail.com>
 <96eaa813-4ad6-e80a-04a4-cc8082d356dd@swarm64.com>
 <CALj2ACVsiAZMsP8p5MPg6SSEtoMFFaiAa6j2AFtEQJDhfbgs3Q@mail.gmail.com>
 <508af801-6356-d36b-1867-011ac6df8f55@swarm64.com>
 <CALj2ACUmL3+xLFtVbdcNpo_=ubdi=_nsp6MNq__xWwL=NGkdgA@mail.gmail.com>
 <CALj2ACXoKTQuz8FKJxgB_=Jr_2_ZCy7gDteBrUa_5pd7Ov_1Tg@mail.gmail.com>
In-Reply-To: 
 <CALj2ACXoKTQuz8FKJxgB_=Jr_2_ZCy7gDteBrUa_5pd7Ov_1Tg@mail.gmail.com>
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 20 Feb 2021 11:15:24 +0530
Message-ID: 
 <CALj2ACVbcYCvTw_jMnuGjLMBiQug7YAL3ezJFM9QMdewoJZLcw@mail.gmail.com>
Subject: Re: New Table Access Methods for Multi and Single Inserts
To: Luc Vlaming <luc@swarm64.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Andres Freund <andres@anarazel.de>,
	Paul Guo <guopa@vmware.com>, Jeff Davis <pgsql@j-davis.com>
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

On Wed, Feb 17, 2021 at 12:46 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
> Hi,
>
> I addressed the following review comments and attaching v3 patch set.
>
> 1) ExecClearTuple happens before ExecCopySlot in heap_multi_insert_v2
> and this allowed us to remove clear_mi_slots flag from
> TableInsertState.
> 2) I retained the flushed variable inside TableInsertState so that the
> callers can know whether the buffered slots have been flushed. If yes,
> the callers can execute after insert row triggers or perform index
> insertions. This is easier than passing the after insert row triggers
> info and index info to new multi insert table am and let it do. This
> way the functionalities can be kept separate i.e. multi insert ams do
> only buffering, decisions on when to flush, insertions and the callers
> will execute triggers or index insertions. And also none of the
> existing table ams are performing these operations within them, so
> this is inline with the current design of the table ams.
> 3) I have kept the single and multi insert API separate. The previous
> suggestion was to have only a single insert API and let the callers
> provide initially whether they want multi or single inserts. One
> problem with that approach is that we have to allow table ams to
> execute the after row triggers or index insertions. That is something
> I personally don't like.
>
> 0001 - new table ams implementation
> 0002 - the new multi table ams used in CREATE TABLE AS and REFRESH
> MATERIALIZED VIEW
> 0003 - the new multi table ams used in COPY
>
> Please review the v3 patch set further.

Below is the performance gain measured for CREATE TABLE AS with the
new multi insert am propsed in this thread:

case 1 - 2 integer(of 4 bytes each) columns, 3 varchar(8), tuple size
59 bytes, 100mn tuples
on master - 185sec
on master with multi inserts - 121sec, gain - 1.52X

case 2 - 2 bigint(of 8 bytes each) columns, 3 name(of 64 bytes each)
columns, 1 varchar(8), tuple size 241 bytes, 100mn tuples
on master - 367sec
on master with multi inserts - 291sec, gain - 1.26X

case 3 - 2 integer(of 4 bytes each) columns, tuple size 32 bytes, 100mn tuples
on master - 130sec
on master with multi inserts - 105sec, gain - 1.23X

case 4 - 2 bigint(of 8 bytes each) columns, 16 name(of 64 bytes each)
columns, tuple size 1064 bytes, 10mn tuples
on master - 120sec
on master with multi inserts - 115sec, gain - 1.04X

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com