Faster insert for multiple rows
As you all probably know the fastest way to get data into the database is the COPY statement, but there is also a means to speed up inserting multiple rows. PostgreSQL supports inserting multiple rows with one INSERT statment. The syntax is as follows:
INSERT INTO tablename (column, column...) VALUES (row1_val1, row1_val2...), (row2_val1, row2_val2)..;
I did some quick tests to compare the performance difference between multiple insert statments inside one transaction versus one multirow insert. The testset was 100K records and i run the tests for several times, the magnitude of difference in performance was constant over the tests, showing that multi-row insert is approximately 4 times faster than normal insert statements. Multi-row insert needs the statment to be parsed, prepared and changes written to WAL only once therefore resulting in less overhead.
Test:
tmpdb=# create table things (things_id serial primary key, thing text); NOTICE: CREATE TABLE will create implicit sequence "things_things_id_seq" for serial column "things.things_id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "things_pkey" for table "things" CREATE TABLE
Multi-row insert SQL file
insert into things (thing) values ('thing nr. 0'), ('thing nr. 1'), ('thing nr. 2'), ('thing nr. 3'), ... ('thing nr. 99999), ('thing nr. 100000);
Multiple insert statments SQL file
begin; insert into things (thing) values ('thing nr. 0'); insert into things (thing) values ('thing nr. 1'); insert into things (thing) values ('thing nr. 2'); .... insert into things (thing) values ('thing nr. 99999'); insert into things (thing) values ('thing nr. 100000'); commit;
About this entry
You’re currently reading “Faster insert for multiple rows,” an entry on <(-_-)> on PostgreSQL
- Published:
- July 19, 2007 / 12:06 pm
- Category:
- postgresql, query tuning, sql, tips&tricks
- Tags:
10 Comments
Jump to comment form | comment rss [?] | trackback uri [?]