User-Agent: Microsoft-Entourage/11.1.0.040913
Date: Sat, 06 Aug 2005 22:21:03 -0700
Subject: Re: COPY FROM performance improvements
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Alon Goldshuv" <agoldshuv@greenplum.com>,
	pgsql-patches@postgresql.org
Message-ID: <BF1AE84F.AC5A%llonergan@greenplum.com>
In-Reply-To: <29007.1123387696@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom,

On 8/6/05 9:08 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> "Luke Lonergan" <llonergan@greenplum.com> writes:
>>> I had some difficulty in generating test cases that weren't largely
>>> I/O-bound, but AFAICT the patch as applied is about the same speed
>>> as what you submitted.
> 
>> You achieve the important objective of knocking the parsing stage down a
>> lot, but your parsing code is actually about 20% slower than Alon's.
> 
> I would like to see the exact test case you are using to make this
> claim; the tests I did suggested my code is the same speed or faster.

I showed mine - you show yours :-)  Apparently our e-mail crossed.
  
> As best I can tell, my version of CopyReadAttributes is significantly
> quicker than Alon's, approximately balancing out the fact that my
> version of CopyReadLine is slower.  I did the latter first, and would
> now be tempted to rewrite it in the same style as CopyReadAttributes,
> ie one pass of memory-to-memory copy using pointers rather than buffer
> indexes.

See previous timings - looks like Alon's parsing is substantially faster.
However, I'd like him to confirm by running with the "shunt" placed at
different stages, in this case between parse and attribute conversion (not
attribute parse).
 
> BTW, late today I figured out a way to get fairly reproducible
> non-I/O-bound numbers about COPY FROM: use a trigger that suppresses
> the actual inserts, thus:
> 
> create table foo ...
> create function noway() returns trigger as
> 'begin return null; end' language plpgsql;
> create trigger noway before insert on foo
>   for each row execute procedure noway();
> then repeat:
> copy foo from '/tmp/foo.data';

Cool!  That's a better way than hacking code and inserting shunts.
 
Alon will likely hit this tomorrow.

- Luke