> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee <kivuosb(at)gmail(dot)com> wrote:
>
> Hi Adrian,
>
> Thanks for the response.
>
> > Yes, but you will some code via client or function that batches the
> > inserts for you.
>
> Could you please elaborate a bit on how EXP 1 could be performed such that it uses bulk inserts?
>
> Best,
> -SB
>
> On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
> > Hi,
> >
> > I'm trying to measure the performance of the following: Multiple txns
> > inserting tuples into a table concurrently vs single txn doing the whole
> > insertion.
> >
> > *new table created as:*
> > create table tab2 (
> > id serial,
> > attr1 integer not null,
> > attr2 integer not null,
> > primary key(id)
> > );
> >
> > *EXP 1: inserts with multiple txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
> > attr2 = 10);
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
> > attr2 = 20);
> >
> > note: attr2 has only two values 10 and 20
> >
> > *EXP 2: inserts with a single txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
> >
> > I also performed another experiment as follows:
> > *EXP 3:* select attr1, attr2 into tab2 from tab1;
> >
> > The observation here is EXP 3 is much faster than EXP 2 probably due to
> > bulk inserts used by Postgres. However I could not find a way to insert
> > id values in tab2 using EXP 3. Also select .. into .. from .. throws an
> > error if we create a table first and then populate the tuples using the
> > command.
>
> Yes as SELECT INTO is functionally the same as CREATE TABLE AS:
>
> https://www.postgresql.org/docs/11/sql-selectinto.html <https://www.postgresql.org/docs/11/sql-selectinto.html>
>
> >
> > I have the following questions:
> > 1. Is it possible to have an id column in tab2 and perform a bulk insert
> > using select .. into .. from .. or using some other means?
>
> Not using SELECT INTO for reasons given above.
> Though it is possible to SELECT INTO as you show in EXP 3 and then:
> alter table tab2 add column id serial primary key;
> EXP 2 shows the other means.
>
> > 2. If a table is already created, is it possible to do bulk inserts via
> > multiple txns inserting into the same table (EXP 3)?
>
> Yes, but you will some code via client or function that batches the
> inserts for you.
>
> >
> > Best,
> > -SB
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
Top-posting (i.e. putting your reply at the top is discouraged here)
Does this appeal to you:
COPY (SELECT * FROM relation) TO ... (https://www.postgresql.org/docs/10/sql-copy.html <https://www.postgresql.org/docs/10/sql-copy.html>)