pg_dump insert with column names speedup

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_dump insert with column names speedup
Date: 2013-10-04 14:23:52
Message-ID: CAApHDvrORPUhLipG8-cbThnKPSq3QKCH7Osq9yJZsN00+kBRmA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Here's a small patch which greatly increases the speed of
pg_dump --column-inserts.

Previous to this patch a string was being build for the names of the
columns for each row in the table... I've now changed this to only do this
once for the table.

I also did some clean ups to remove calling the va_args function to write
to the backup file when it was not needed, it instead now uses the puts
type function which should be faster.

Here are some benchmark results on how it performs:

*** patched ***
D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 5850 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 5790 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 5700 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 5760 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 5730 ms

*** unpatched ***

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 9580 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 9330 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 9250 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 9230 ms

D:\9.4\bin>timer pg_dump --column-inserts -f d:\postgres_backup.sql postgres
Ran for 9240 ms

The database contained a single 1 million record table with 6 columns.

Regards

David Rowley

Attachment Content-Type Size
pg_dump_colinsert_v0.1.patch application/octet-stream 2.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2013-10-04 14:31:03 Re: SSI freezing bug
Previous Message Robert Haas 2013-10-04 14:22:36 Re: GSOC13 proposal - extend RETURNING syntax