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

Tom Lane wrote on 17.07.2010 16:36:
> Thomas Kellerer<spam_eater@gmx.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
networkdoes impact psql as well.