Re: Speeding up Postgres INSERTs

Do you have AutoCommit turned Off ?
Snippet that use COPY:

my $table = 'table';
my @cols = (qw( a b c));
$" = ", ";
$dbh->do ("COPY $table (@cols) FROM STDIN");

while (...) {
  $dbh->pg_putline (join ("\t", @values) . "\n");
}
$dbh->pg_endcopy;
$dbh->commit;
[download]

Comment on Re: Speeding up Postgres INSERTs Download Code

Replies are listed 'Best First'.
Re^2: Speeding up Postgres INSERTs by punkish (Priest) on Jun 18, 2010 at 11:41 UTC
`my $table = 'table'; my @cols = (qw( a b c)); $" = ", "; $dbh->do ("COPY $table (@cols) FROM STDIN"); while (...) { $dbh->pg_putline (join ("\t", @values) . "\n"); } $dbh->pg_endcopy; $dbh->commit;` [download] Ahhh... that was what I was looking for. Many thanks. In answer to other queries -- AutoCommit is off (hence, the explicit COMMIT every 100_000 rows). I am not reading nor wanting to read from a CSV file, but directly from an array in memory. I don't have any INDEXes other than a primary key -- when small people start casting long shadows, it is time to go to bed	[reply] [d/l]
Re^2: Speeding up Postgres INSERTs by punkish (Priest) on Jun 19, 2010 at 01:11 UTC
Update: Using the COPY command, I am now getting around 15,000 INSERTs per second. Sooooo much better. -- when small people start casting long shadows, it is time to go to bed	[reply]