Re: very slow when writing query to file

On Tue, Nov 1, 2011 at 07:23, Guillaume Lelarge <guillaume@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.