Re: Performance testing of COPY (SELECT) TO

Hi,

Böszörményi Zoltán írta:
>> Böszörményi Zoltán <zboszor(at)dunaweb(dot)hu> writes:
>>
>>> With PostgreSQL 8.1.4, I used this:
>>>
>>> begin;
>>> select ... into temp myquery1;
>>> copy myquery1 to stdout csv delimiter '|';
>>> rollback;
>>>
>> The performance of this would doubtless vary a lot with the temp_buffers
>> setting. Did you try different values?
>>
>
> Yes, I did, but now checked back with 8.2CVS.
> The previously quoted result was achieved with
> temp_buffers = 1000 on both 8.1.4 and 8.2CVS.
> On 8.2CVS with temp_buffers = 4096, the 10 client case kills
> the machine with swapping, but the 3 client runtime with
> COPY(SELECT) went down to 2:41. The SELECT INTO TEMP
> case went down to 3:36.
>
>
>> It'd also be interesting to time the same way (with a temp table) in
>> devel. I don't remember whether we did any performance work on the
>> COPY CSV data path in this cycle, or whether that was all present in
>> 8.1. In any case it'd be worth proving that the COPY SELECT patch isn't
>> degrading performance of the copy-a-relation case.
>>
>
> I will report back with that, say on Monday.
>

It seems my previous mail hasn't reached
the hackers list, I answer here.

In the export, there is a largish table,
that has both many columns and rows.

With COPY(SELECT) patch applied:

time psql -c "copy (select * from table) to 'file'" dbx
COPY 886046

real 0m13.253s
user 0m0.000s
sys 0m0.000s

time psql -c "copy table to 'file'" dbx
COPY 886046

real 0m13.234s
user 0m0.000s
sys 0m0.000s

time psql -c "copy table to stdout" dbx >file

real 0m15.155s
user 0m0.540s
sys 0m0.450s

time psql -c "copy (select * from table) to stdout" dbx >file

real 0m15.079s
user 0m0.540s
sys 0m0.590s

Surprisingly, without the COPY(SELECT) patch it's slower,
this is the lowest from five runs, e.g. with warm caches:

time psql -c "copy table to 'file'" dbx

real 0m20.464s
user 0m0.000s
sys 0m0.010s

time psql -c "copy table to stdout" dbx >file

real 0m25.753s
user 0m0.570s
sys 0m0.460s

With the original settings, temp_buffers = 1000 on 8.2CVS,
the export runtime with one client looks like this:
first run 1:44, second run 1:12, third run 1:04.
It seems it's a bit faster both on startup and on
subsequent runs.

Best regards,
Zoltán Böszörményi