public inbox for [email protected]
help / color / mirror / Atom feedFrom: Zhihong Yu <[email protected]>
To: Bharath Rupireddy <[email protected]>
Cc: Luc Vlaming <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Paul Guo <[email protected]>
Cc: Jeff Davis <[email protected]>
Subject: Re: New Table Access Methods for Multi and Single Inserts
Date: Fri, 19 Feb 2021 23:25:51 -0800
Message-ID: <CALNJ-vQa=gV0i5Sz_zwm0Hb78U0Ro97rycML0bXDadkLyTYWig@mail.gmail.com> (raw)
In-Reply-To: <CALj2ACVbcYCvTw_jMnuGjLMBiQug7YAL3ezJFM9QMdewoJZLcw@mail.gmail.com>
References: <CALj2ACVi9eTRYR=gdca5wxtj3Kk_9q9qVccxsS1hngTGOCjPwQ@mail.gmail.com>
<[email protected]>
<CALj2ACVgT1iocd5nQ+rEmqt3xcCONkR037qbc8PiojdR39Ag=w@mail.gmail.com>
<[email protected]>
<CALj2ACW3BC5kgdffZ2LD_CT2wQoXVc29kGB74SVWnGZ=UFqcAQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CALj2ACWMnZZCu=G0PJkEeYYicKeuJ-X=SU19i6vQ1+=uXz8u0Q@mail.gmail.com>
<[email protected]>
<CALj2ACVDtYYRYD2SC+X2ALOUkhnUcgC7RLxiEYVWW2HxxrfRww@mail.gmail.com>
<[email protected]>
<CALj2ACVsiAZMsP8p5MPg6SSEtoMFFaiAa6j2AFtEQJDhfbgs3Q@mail.gmail.com>
<[email protected]>
<CALj2ACUmL3+xLFtVbdcNpo_=ubdi=_nsp6MNq__xWwL=NGkdgA@mail.gmail.com>
<CALj2ACXoKTQuz8FKJxgB_=Jr_2_ZCy7gDteBrUa_5pd7Ov_1Tg@mail.gmail.com>
<CALj2ACVbcYCvTw_jMnuGjLMBiQug7YAL3ezJFM9QMdewoJZLcw@mail.gmail.com>
Hi,
bq. case 3 - 2 integer(of 4 bytes each) columns, tuple size 32 bytes
Is there some other column(s) per row apart from the integer columns ?
Since the 2 integer columns only occupy 8 bytes. I wonder where the other
32-8=24 bytes come from.
Thanks
On Fri, Feb 19, 2021 at 9:45 PM Bharath Rupireddy <
[email protected]> wrote:
> On Wed, Feb 17, 2021 at 12:46 PM Bharath Rupireddy
> <[email protected]> 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
>
>
>
view thread (36+ 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], [email protected], [email protected], [email protected], [email protected]
Subject: Re: New Table Access Methods for Multi and Single Inserts
In-Reply-To: <CALNJ-vQa=gV0i5Sz_zwm0Hb78U0Ro97rycML0bXDadkLyTYWig@mail.gmail.com>
* 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