Hi,
Böszörményi Zoltán írta:
>> Böszörményi Zoltán <zboszor@dunaweb.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