> 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.
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,
it's 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 one client case 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