CODEWITHSUNDEEP
pythonsqldatabasepostgresqlpsycopg2
shevski
shevski

Reputation: 1022

Bulk insert with returning IDs performance

I've got a table which I want to insert around 1000 items each query, and get their PK after creation, for later use as FK for other tables.

I've tried inserting them using returning syntax in postgresql. but it takes around 10 sec to insert

INSERT INTO table_name (col1, col2, col3) VALUES (a1,a2,a3)....(a(n-2),a(n-1),a(n)) returning id;

By removing RETURNING I get much better performance ~50ms.

I think that if I can get an atomic operation to get the first id and insert the rows at the same time I could get better performance by removing the RETURNING. but don't understand if that is possible.

Upvotes: 0

Views: 1361

Answers (1)

yaroslav
yaroslav

Reputation: 26

Generate id using the nextval http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

CREATE TEMP TABLE temp_val AS(
    VALUES(nextval('table_name_id_seq'),a1,a2,a3), 
        (nextval('table_name_id_seq'),a1,a2,a3) 
);
INSERT INTO table_name (id, col1, col2, col3)(
    SELECT column1,column2,column3,column4 
    FROM temp_val
);
SELECT column1 FROM temp_val;

Upvotes: 1

Related Questions