Re: pg_dump and --inserts / --column-inserts

Tom Lane wrote on 17.07.2010 16:36:
> Thomas Kellerer<spam_eater(at)gmx(dot)net> writes:
>> I'm till a bit surprised that parsing the statement _with_ a column list is mesurably slower than withou a column list.
>
> Well, nobody's offered any actual *numbers* here. It's clear that
> parsing the column list will take more time than not doing so, but
> whether that adds up to anything significant or even measurable
> compared to the rest of the statement's cost is not so clear.

> Want to do some experiments?

OK, I wrote a small Java program that inserts rows using a column list and without column list

I did that for 5,10,15 and 20 (integer) columns. then inserting 10000 rows into the empty table.

I measured the runtime as seen from the JDBC client and as reported by explain analyze (the last line reading "Total runtime:")
All times are milliseconds and are averaged over 20 runs

ColumnCount with columns without columns
5 1132 1092.6
10 1288.53 1148.33
15 1430 1215.67
20 1657.6 1313.2

Apparently there *is* a substiantial overhead, but I suspected the sending of the raw SQL literal to be a major factor here.
(Server and JDBC program were running on the same machine)

So I ran "EXECUTE ANALYZE" instead of INSERT to get the processing time of the server and remove the JDBC/SQL literal overhead.

ColumnCount with columns without columns
5 116.33 115.3
10 149.89 128.28
15 169.94 159.14
20 197.72 193.66

Which still shows an overhead, but less.

So apparently the "even slower" in the manual *is* right, as the overhead of sending the longer SQL Statement over the network does impact psql as well.