Martijn van Oosterhout <kleptog@xxxxxxxxx> writes:
> There's a lot of discussion about this, yet no-one has demonstrated that
> COPY FROM STDIN isn't just as good and avoids all the issues entirely.
In any case here's some quick results from my system. There seems to a greater
than 21% slowdown associated with piping the data through two processes
instead of reading directly.
This file is small enough to have probably fit entirely within cache, but then
on the other hand I don't have the kind of fast RAID arrays data warehouses
live with. A fast raid array would mean both that reading in the raw data
would be more like my situation here where reading in the data is a small part
of the time and *also* that writing out the data which my machine had to
stream to a typical consumer level drive would take less time and that's
probably the dominant time in this test.
bash-3.00$ for i in `seq 1 10` ; do psql -d stark -c 'truncate table t' ; time psql -d stark -c '\copy t from '"'"'postalcodes.dat'"'"' with delimiter '"'"'\t'"'"'' ; done 2>&1 | grep real
real 0m5.223s
real 0m5.262s
real 0m5.322s
real 0m5.613s
real 0m5.394s
real 0m5.221s
real 0m5.365s
real 0m5.445s
real 0m5.247s
real 0m5.238s
bash-3.00$ for i in `seq 1 10` ; do psql -d stark -c 'truncate table t' ; time psql -U postgres -d stark -c 'copy t from '"'"'/home/stark/src/saleslookout/postalcodes.dat'"'"' with delimiter '"'"'\t'"'"'' ; done 2>&1 | grep real
real 0m4.011s
real 0m4.058s
real 0m4.308s
real 0m4.498s
real 0m4.220s
real 0m4.049s
real 0m4.131s
real 0m4.488s
real 0m4.166s
real 0m4.152s
--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
