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.klaver@xxxxxxxxxxx
