public
Created

test pgsql batch insert performance

  • Download Gist
session.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
psql (9.1.1)
Type "help" for help.
 
-- create sample data 60000 samples in 5 minutes
luben=# COPY (SELECT generate_series(now()::timestamp without time zone-'5 minutes'::interval, now(), '5000 microsecond'::interval) AS ts, random()::float AS val) TO '/tmp/test.csv' WITH ( FORMAT 'csv' );
COPY 60001
Time: 332.955 ms
 
--creating tables and indexes
luben=# CREATE TABLE test(ts timestamp without time zone, val float NOT NULL);
CREATE TABLE
Time: 1.255 ms
luben=# CREATE INDEX test_ts_idx ON test(ts);
CREATE INDEX
Time: 6.422 ms
 
-- batch import data
luben=# COPY test FROM '/tmp/test.csv' WITH (FORMAT 'csv');
COPY 60001
Time: 899.097 ms
 
-- verify
luben=# select count(*) from test;
count
-------
60001
(1 row)
 
Time: 43.648 ms
 
-- again
luben=# COPY test FROM '/tmp/test.csv' WITH (FORMAT 'csv');
COPY 60001
Time: 981.032 ms
luben=# select count(*) from test;
count
--------
120002
(1 row)Time: 34.472 ms
 
 
-- show some data
luben=# SELECT * FROM test limit 10;
ts | val
----------------------------+--------------------
2011-11-06 20:02:24.369484 | 0.0362018141895533
2011-11-06 20:02:24.374484 | 0.347665118984878
2011-11-06 20:02:24.379484 | 0.373926763888448
2011-11-06 20:02:24.384484 | 0.781608667690307
2011-11-06 20:02:24.389484 | 0.696035301312804
2011-11-06 20:02:24.394484 | 0.619301837403327
2011-11-06 20:02:24.399484 | 0.573932913132012
2011-11-06 20:02:24.404484 | 0.725775045342743
2011-11-06 20:02:24.409484 | 0.62408871203661
2011-11-06 20:02:24.414484 | 0.427382475696504
(10 rows)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.