I tested after column types, and the colum that appears to create the
very slow behaviour is of type boolean. Retrieving only 1 column for
3000 records delays at ca. 1 minute.
Do you know any possible reason?
On 11/02/2011 11:58 PM, Fernando Hevia wrote:
On Tue, Nov 1, 2011 at 07:23, Guillaume Lelarge
<guilla...@lelarge.info <mailto:guilla...@lelarge.info>> wrote:
[...]
> With 10k rows (2.3 MB) it took 3.5 seconds to retrieve data from
DB and 40
> seconds to write the file to an SATA 7200 disk with
write-through cache.
> With 100k rows (23 MB) the DB retrieve went for 35 seconds while
the file
> writing part took over 4 minutes (didn't wait for it to finish).
>
I don't have the same numbers:
* 1000 : 1s
* 10000 : 1s
* 200000 : 7s
* 1000000 : 18s
Interesting... seeing your explanation below, our timing difference
probably has to do with me testing on a table with lots of columns,
many of type timestamp and inet which I assume are costlier to
convert. Boris, might that be the case with you also?
I'll repeat my tests with a smaller and simpler table later.
> The file is being written at an avg 60 KB per second, which is
extremely
> slow.
If the only thing pgAdmin does was writing, I would agree. But,
actually, it does a lot more things:
* for each row
* for each column
* adds the column separator, if needed
* grabs one cell's value
* quotes the value, if needed (which also means doubling the quote
if it's within the value)
* adds the line separator
* converts it to the encoding, if needed
* writes it to the file
That could take some time.
I see how I was completely underrating the workload involved in the
file writing process.
I searched if there were some parts that took much longer than others,
but failed to find one.
The effort is much appreciated. Thanks.
Regards,
Fernando.
--
*Boris Pezzatti
Swiss Federal Research Institute WSL
*
Research unit Community Ecology
Team Insubric Ecosystems
via Belsoggiorno 22
CH-6500 Bellinzona
Switzerland phone direct ++41 91 821 52 32
phone ++41 91 821 52 30
fax ++41 91 821 52 39
boris.pezza...@wsl.ch
http://www.wsl.ch <http://www.wsl.ch/>
