Hi,
Böszörményi Zoltán írta:
Böszörményi Zoltán <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
