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.kla...@aklaver.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 > > > > > 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.kla...@aklaver.com >
