Re: COPY FROM STDIN instead of INSERT

On 10/18/06, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 10/18/06 09:47, Merlin Moncure wrote:
> > On 10/18/06, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
> >> > I tested binary quite a bit and only found it to be a win if moving
> >> > blobs in and out of the database. On 'normal' tables of mixed fields
> >> > types of small size, it can actually be slower. Binary is a bit
> >> > faster for native types and bytea, and slower for character types.
> >>
> >> "native types"?
> >
> > types operated on directly by the processor. int2, int4, int8, float4,
> > and float8, and their various aliases :).
> >
> > in short, i think using binary for anything other than bytea is a
> > waste of effort/time, except for bytea.
>
> That's counter-intuitive, since you'd (well, I'd) think that doing a
> binary copy would be faster since the code would bypass the int-to-
> ascii conversion.

you missed the point: binary copy is (very marginally) faster for
'native types' aka ints, etc. however text fields are slower
according to my testing. however, the speed differences are extremely
marginal overall. only exception to this is binary (bytea)
fields...you get a 2x speed improvement easily due to skipping the
encoding.

merlin