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

Tom Lane wrote on 16.07.2010 18:40:
> Thomas Kellerer<spam_eater@gmx.net>  writes:
>> the explanation of the --inserts option of pg_dumps states that
>
>> "The --column-inserts option is safe against column order changes, though even slower."
>
>> The way I read this is, that
>>     INSERT INTO table (column, ...) VALUES ...
>> is slower than
>>     INSERT INTO table VALUES ...
>
>> Is that really true?
>
> I believe so, though I've not measured by how much.
>
>> Why would explicitely stating the columns be slower than relying on implicit column ordering?
>
> Well, first off, the volume of pg_dump'd data gets a lot larger due to
> all the extra text.  If your column values aren't textually wide, you
> could easily be looking at 2x the space.  That costs in I/O and network
> transmission.

Of course

> In the second place, it does take time to parse those
> column names and look them up in the catalog.  Not much, but it'll add
> up since it's done over again for every row.

Hmm.
For years I have been advocating to always use fully qualified column lists in INSERTs (for clarity and stability)
And now I learn it's slower when I do so :(

Thomas