Re: 8.4 open item: copy performance regression?

On Sun, Jun 21, 2009 at 11:31 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> It would be interesting to see some gprof or oprofile output from that
>> test.   I went back and dug up the results that I got when I profiled
>> this patch during initial development, and my version of the patch
>> applied, the profile looked like this on my system:
>
> Were you testing with a temp table?

No.

> The lack of XLogInsert in your
> profile is striking.  Stefan's results upthread had it at the top,
> and I got more or less the same thing here (didn't keep my numbers
> unfortunately).

As Stephen guessed, I created the table in the same transaction that I
inserted into it...

>> Simon had the idea of further improving performance by keeping the
>> current buffer locked (this patch just kept it pinned, but not
>> locked), but I didn't see an obvious clean design for that.
>
> The potential for deadlock seems sufficient reason not to do that.

Yep, that was the problem. *thinks*

I think we had the idea of buffering up enough tuples to fill a page
(estimating conservatively so as to make sure we actually fill it),
and then inserting them all at once. But I'm not sure how much
trouble that causes in terms of the timing of inserting index entries
and firing of after row insert triggers. If the command ID doesn't
change, it seems like it might be OK. Or at worst, even if the
optimization would only work in cases where there are no after row
triggers, that would still be useful to a lot of people, I think.

I haven't really spent much time on this angle of attack and
completely confess to not understanding all of the issues...

...Robert