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.
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?
2. If a table is already created, is it possible to do bulk inserts via multiple txns inserting into the same table (EXP 3)?
Best,
-SB