Обсуждение: very slow when writing query to file

I have a postgresql database 8.3 on a server. When querying the data 
with the pgAdmin sql editor, I can get an answer in about 10 s, for 
100'000 rows. When I'm pressing the button to execute the query to a 
file it takes more than 1 hour to get the query results saved (writes 
about 10 MB in 45 minutes).

Am I doing something wrong?
Are there some parameters to set?

This happens with the version 1.14 of pgAdmin, but used also to be very 
slow with the previous versions (2-3 years ago).

OS : Archlinux (but had the same problem 1-2 years ago with ubuntu)

Thank you ,
Boris

On Wed, 2011-10-26 at 14:57 +0200, boris pezzatti wrote:
> I have a postgresql database 8.3 on a server. When querying the data 
> with the pgAdmin sql editor, I can get an answer in about 10 s, for 
> 100'000 rows. When I'm pressing the button to execute the query to a 
> file it takes more than 1 hour to get the query results saved (writes 
> about 10 MB in 45 minutes).
> 
> Am I doing something wrong?
> Are there some parameters to set?
> 

Never heard of such a behaviour before. I'm not sure what happens on
your computer.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

Does anyone have any proposition how I could further test where the 
problem is. This is really a strange behaviour, that I noticed with 
different versions of pgAdmin.
Could a firewall produce a difference when querying data visually or for 
a write to file? (should not ...)

Thank you,
Boris

 Guillaume Lelarge wrote:
> On Wed, 2011-10-26 at 14:57 +0200, boris pezzatti wrote:
>> I have a postgresql database 8.3 on a server. When querying the data
>> with the pgAdmin sql editor, I can get an answer in about 10 s, for
>> 100'000 rows. When I'm pressing the button to execute the query to a
>> file it takes more than 1 hour to get the query results saved (writes
>> about 10 MB in 45 minutes).
>>
>> Am I doing something wrong?
>> Are there some parameters to set?
>>
> Never heard of such a behaviour before. I'm not sure what happens on
> your computer.
>
>

Is the file where you write the query output located on the same server as the DB?

---

From: boris pezzatti <boris.pezzatti@wsl.ch>
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgadmin-support@postgresql.org
Sent: Saturday, October 29, 2011 5:44 PM
Subject: Re: [pgadmin-support] very slow when writing query to file

Does anyone have any proposition how I could further test where the problem is. This is really a strange behaviour, that I noticed with different versions of pgAdmin.
Could a firewall produce a difference when querying data visually or for a write to file? (should not ...)

Thank you,
Boris


Guillaume Lelarge wrote:
> On Wed, 2011-10-26 at 14:57 +0200, boris pezzatti wrote:
>> I have a postgresql database 8.3 on a server. When querying the data
>> with the pgAdmin sql editor, I can get an answer in about 10 s, for
>> 100'000 rows. When I'm pressing the button to execute the query to a
>> file it takes more than 1 hour to get the query results saved (writes
>> about 10 MB in 45 minutes).
>>
>> Am I doing something wrong?
>> Are there some parameters to set?
>>
> Never heard of such a behaviour before. I'm not sure what happens on
> your computer.
>
>


-- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

I propose you output to a file located on the same server as the DB and then copy that file to your PC

IMO your problem is network related.

From: boris pezzatti <boris.pezzatti@wsl.ch>
To: Francisco Leovey <fleovey@yahoo.com>
Cc: "pgadmin-support@postgresql.org" <pgadmin-support@postgresql.org>
Sent: Sunday, October 30, 2011 6:28 PM
Subject: Re: [pgadmin-support] very slow when writing query to file

No, actually it is on my machine ... On 10/29/2011 11:01 PM, Francisco Leovey wrote:

Is the file where you write the query output located on the same server as the DB?

From: boris pezzatti <boris.pezzatti@wsl.ch>
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgadmin-support@postgresql.org
Sent: Saturday, October 29, 2011 5:44 PM
Subject: Re: [pgadmin-support] very slow when writing query to file
Does anyone have any proposition how I could further test where the problem is. This is really a strange behaviour, that I noticed with different versions of pgAdmin. Could a firewall produce a difference when querying data visually or for a write to file? (should not ...) Thank you, Boris Guillaume Lelarge wrote: > On Wed, 2011-10-26 at 14:57 +0200, boris pezzatti wrote: >> I have a postgresql database 8.3 on a server. When querying the data >> with the pgAdmin sql editor, I can get an answer in about 10 s, for >> 100'000 rows. When I'm pressing the button to execute the query to a >> file it takes more than 1 hour to get the query results saved (writes >> about 10 MB in 45 minutes). >> >> Am I doing something wrong? >> Are there some parameters to set? >> > Never heard of such a behaviour before. I'm not sure what happens on > your computer. > > -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support

--

On Mon, 2011-10-31 at 18:26 -0300, Fernando Hevia wrote:
> [...]
> I could reproduce the issue in a fresh Windows 7 install with no other apps
> running other than pgAdmin v1.14.0.
> From what I could see, the execute-to-file function runs in 2 stages:
> 1. Rows are retrieved from DB server to RAM
> 2. Rows are written from RAM to file
> 

That's right.

> The delay clearly occurs in step 2.

That's also right.

> While with small datasets (<2000) the writing to disk delay is barely
> perceivable, when the number of rows is incremented (>10k) it is quite
> distinctive how step 1 keeps completing in the expected time frame but step
> 2 takes much much longer. In any case it should be the other way around.
> 

Nope, step 2 does a lot of work.

> 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

> 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
itto the encoding, if needed * writes it to the file
 

That could take some time.

I searched if there were some parts that took much longer than others,
but failed to find one.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

Thank you Fernando for reproducing this.<br /> I suspect there must be some part of code in the <br /><pre wrap="">*
foreach row * for each column</pre> loops that result inefficient only on some machines or OS's (I'm using
Archlinux).<br/> In fact the extra time I and Fernando get can not only be attributed to adding commas, " and line
feeds.The same file which I retrived in more than one hour (32Mb), could be saved with openoffice in 10 seconds,
changingcolumn separators, text delimiters and encoding.<br /><br /> Could it be possible that the use of e.g. error
catchingin the loops results in poor performance on some systems? (I'm not a c++ programmer ...)<br /><br /><br /><br
/><br/><br /><br /><br /> On 11/01/2011 11:23 AM, Guillaume Lelarge wrote: <blockquote
cite="mid:1320142985.2122.13.camel@localhost.localdomain"type="cite"><pre wrap="">On Mon, 2011-10-31 at 18:26 -0300,
FernandoHevia wrote:
 
</pre><blockquote type="cite"><pre wrap="">[...]
I could reproduce the issue in a fresh Windows 7 install with no other apps
running other than pgAdmin v1.14.0.
From what I could see, the execute-to-file function runs in 2 stages:
1. Rows are retrieved from DB server to RAM
2. Rows are written from RAM to file

</pre></blockquote><pre wrap="">
That's right.

</pre><blockquote type="cite"><pre wrap="">The delay clearly occurs in step 2.
</pre></blockquote><pre wrap="">
That's also right.

</pre><blockquote type="cite"><pre wrap="">While with small datasets (<2000) the writing to disk delay is barely
perceivable, when the number of rows is incremented (>10k) it is quite
distinctive how step 1 keeps completing in the expected time frame but step
2 takes much much longer. In any case it should be the other way around.

</pre></blockquote><pre wrap="">
Nope, step 2 does a lot of work.

</pre><blockquote type="cite"><pre wrap="">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).

</pre></blockquote><pre wrap="">
I don't have the same numbers:

*    1000 : 1s
*   10000 : 1s
*  200000 : 7s
* 1000000 : 18s

</pre><blockquote type="cite"><pre wrap="">The file is being written at an avg 60 KB per second, which is extremely
slow.
</pre></blockquote><pre wrap="">
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
itto the encoding, if needed * writes it to the file
 

That could take some time.

I searched if there were some parts that took much longer than others,
but failed to find one.


</pre></blockquote><br /><div class="moz-signature">-- <br /></div><small><font face="Verdana"><small><b>Boris
Pezzatti<br/> Swiss Federal Research Institute WSL<br /></b></small></font></small><table border="0" cellpadding="2"
cellspacing="2"style="text-align: left; width: 400px;"><tbody><tr><td style="vertical-align: top;"><small><font
face="Verdana"><small>Researchunit Community Ecology<br /> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br />
CH-6500Bellinzona <br /> Switzerland</small></font></small></td><td style="vertical-align: top; text-align:
right;"><small><fontface="Verdana"><small> phone direct ++41 91 821 52 32<br /></small></font><font
face="Verdana"><small>phone</small></font><font face="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br
/><aclass="moz-txt-link-abbreviated" href="mailto:boris.pezzatti@wsl.ch">boris.pezzatti@wsl.ch</a><br /><a
eudora="autourl"href="http://www.wsl.ch/">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br /> 

<br /> or maybe the retrieved data in RAM are somehow lazy bound ... ? <br /><br /><br /><br /> On 11/02/2011 08:58 AM,
borispezzatti wrote: <blockquote cite="mid:4EB0F81E.70708@wsl.ch" type="cite"> Thank you Fernando for reproducing
this.<br/> I suspect there must be some part of code in the <br /><pre wrap="">* for each row * for each column</pre>
loopsthat result inefficient only on some machines or OS's (I'm using Archlinux).<br /> In fact the extra time I and
Fernandoget can not only be attributed to adding commas, " and line feeds. The same file which I retrived in more than
onehour (32Mb), could be saved with openoffice in 10 seconds, changing column separators, text delimiters and
encoding.<br/><br /> Could it be possible that the use of e.g. error catching in the loops results in poor performance
onsome systems? (I'm not a c++ programmer ...)<br /><br /><br /><br /><br /><br /><br /><br /> On 11/01/2011 11:23 AM,
GuillaumeLelarge wrote: <blockquote cite="mid:1320142985.2122.13.camel@localhost.localdomain" type="cite"><pre
wrap="">OnMon, 2011-10-31 at 18:26 -0300, Fernando Hevia wrote:
 
</pre><blockquote type="cite"><pre wrap="">[...]
I could reproduce the issue in a fresh Windows 7 install with no other apps
running other than pgAdmin v1.14.0.
>From what I could see, the execute-to-file function runs in 2 stages:
1. Rows are retrieved from DB server to RAM
2. Rows are written from RAM to file

</pre></blockquote><pre wrap="">That's right.

</pre><blockquote type="cite"><pre wrap="">The delay clearly occurs in step 2.
</pre></blockquote><pre wrap="">That's also right.

</pre><blockquote type="cite"><pre wrap="">While with small datasets (<2000) the writing to disk delay is barely
perceivable, when the number of rows is incremented (>10k) it is quite
distinctive how step 1 keeps completing in the expected time frame but step
2 takes much much longer. In any case it should be the other way around.

</pre></blockquote><pre wrap="">Nope, step 2 does a lot of work.

</pre><blockquote type="cite"><pre wrap="">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).

</pre></blockquote><pre wrap="">I don't have the same numbers:

*    1000 : 1s
*   10000 : 1s
*  200000 : 7s
* 1000000 : 18s

</pre><blockquote type="cite"><pre wrap="">The file is being written at an avg 60 KB per second, which is extremely
slow.
</pre></blockquote><pre wrap="">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
itto the encoding, if needed * writes it to the file
 

That could take some time.

I searched if there were some parts that took much longer than others,
but failed to find one.


</pre></blockquote><br /><div class="moz-signature">-- <br /></div></blockquote><small><font
face="Verdana"><small><b>BorisPezzatti<br /> Swiss Federal Research Institute WSL<br
/></b></small></font></small><tableborder="0" cellpadding="2" cellspacing="2" style="text-align: left; width:
400px;"><tbody><tr><tdstyle="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch" moz-do-not-send="true">boris.pezzatti@wsl.ch</a><br
/><aeudora="autourl" href="http://www.wsl.ch/"
moz-do-not-send="true">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/><br /><div
class="moz-signature">--<br /></div><small><font face="Verdana"><small><b>Boris Pezzatti<br /> Swiss Federal Research
InstituteWSL<br /></b></small></font></small><table border="0" cellpadding="2" cellspacing="2" style="text-align: left;
width:400px;"><tbody><tr><td style="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch">boris.pezzatti@wsl.ch</a><br /><a eudora="autourl"
href="http://www.wsl.ch/">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/> 

<br /> ... or (I promise this is the last guess)  each row is appended to the file in the the loop, and on some OS's
thereis a problem keeping open the file (so that each time the file must be opened again ...  adding a lot of extra
time).Maybe creating all the "virtual file" in a variable and storing all at once (or let say in chunks of 100 Mb)
couldsolve the problem ...<br /><br /><br /><br /><br /><br /><br /> On 11/02/2011 09:10 AM, boris pezzatti wrote:
<blockquotecite="mid:4EB0FAED.2070306@wsl.ch" type="cite"><br /> or maybe the retrieved data in RAM are somehow lazy
bound... ? <br /><br /><br /><br /> On 11/02/2011 08:58 AM, boris pezzatti wrote: <blockquote
cite="mid:4EB0F81E.70708@wsl.ch"type="cite"> Thank you Fernando for reproducing this.<br /> I suspect there must be
somepart of code in the <br /><pre wrap="">* for each row * for each column</pre> loops that result inefficient only on
somemachines or OS's (I'm using Archlinux).<br /> In fact the extra time I and Fernando get can not only be attributed
toadding commas, " and line feeds. The same file which I retrived in more than one hour (32Mb), could be saved with
openofficein 10 seconds, changing column separators, text delimiters and encoding.<br /><br /> Could it be possible
thatthe use of e.g. error catching in the loops results in poor performance on some systems? (I'm not a c++ programmer
...)<br/><br /><br /><br /><br /><br /><br /><br /> On 11/01/2011 11:23 AM, Guillaume Lelarge wrote: <blockquote
cite="mid:1320142985.2122.13.camel@localhost.localdomain"type="cite"><pre wrap="">On Mon, 2011-10-31 at 18:26 -0300,
FernandoHevia wrote:
 
</pre><blockquote type="cite"><pre wrap="">[...]
I could reproduce the issue in a fresh Windows 7 install with no other apps
running other than pgAdmin v1.14.0.
>From what I could see, the execute-to-file function runs in 2 stages:
1. Rows are retrieved from DB server to RAM
2. Rows are written from RAM to file

</pre></blockquote><pre wrap="">That's right.

</pre><blockquote type="cite"><pre wrap="">The delay clearly occurs in step 2.
</pre></blockquote><pre wrap="">That's also right.

</pre><blockquote type="cite"><pre wrap="">While with small datasets (<2000) the writing to disk delay is barely
perceivable, when the number of rows is incremented (>10k) it is quite
distinctive how step 1 keeps completing in the expected time frame but step
2 takes much much longer. In any case it should be the other way around.

</pre></blockquote><pre wrap="">Nope, step 2 does a lot of work.

</pre><blockquote type="cite"><pre wrap="">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).

</pre></blockquote><pre wrap="">I don't have the same numbers:

*    1000 : 1s
*   10000 : 1s
*  200000 : 7s
* 1000000 : 18s

</pre><blockquote type="cite"><pre wrap="">The file is being written at an avg 60 KB per second, which is extremely
slow.
</pre></blockquote><pre wrap="">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
itto the encoding, if needed * writes it to the file
 

That could take some time.

I searched if there were some parts that took much longer than others,
but failed to find one.


</pre></blockquote><br /><div class="moz-signature">-- <br /></div></blockquote></blockquote><small><font
face="Verdana"><small><b>BorisPezzatti<br /> Swiss Federal Research Institute WSL<br
/></b></small></font></small><tableborder="0" cellpadding="2" cellspacing="2" style="text-align: left; width:
400px;"><tbody><tr><tdstyle="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch" moz-do-not-send="true">boris.pezzatti@wsl.ch</a><br
/><aeudora="autourl" href="http://www.wsl.ch/"
moz-do-not-send="true">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/><br /><div
class="moz-signature">--<br /></div><small><font face="Verdana"><small><b>Boris Pezzatti<br /> Swiss Federal Research
InstituteWSL<br /></b></small></font></small><table border="0" cellpadding="2" cellspacing="2" style="text-align: left;
width:400px;"><tbody><tr><td style="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch" moz-do-not-send="true">boris.pezzatti@wsl.ch</a><br
/><aeudora="autourl" href="http://www.wsl.ch/"
moz-do-not-send="true">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/><br /><div
class="moz-signature">--<br /></div><small><font face="Verdana"><small><b>Boris Pezzatti<br /> Swiss Federal Research
InstituteWSL<br /></b></small></font></small><table border="0" cellpadding="2" cellspacing="2" style="text-align: left;
width:400px;"><tbody><tr><td style="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch">boris.pezzatti@wsl.ch</a><br /><a eudora="autourl"
href="http://www.wsl.ch/">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/> 

On Wed, 2011-11-02 at 08:58 +0100, boris pezzatti wrote:
> Thank you Fernando for reproducing this.
> I suspect there must be some part of code in the
> 
> * for each row
>    * for each column
> 
> loops that result inefficient only on some machines or OS's (I'm using 
> Archlinux).

I'm using Fedora, so we both use Linux.

> In fact the extra time I and Fernando get can not only be attributed to 
> adding commas, " and line feeds. The same file which I retrived in more 
> than one hour (32Mb), could be saved with openoffice in 10 seconds, 
> changing column separators, text delimiters and encoding.
> 

Actually, it does.

> Could it be possible that the use of e.g. error catching in the loops 
> results in poor performance on some systems? (I'm not a c++ programmer ...)
> 

Nope, I don't think so.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

On Wed, 2011-11-02 at 09:21 +0100, boris pezzatti wrote:
> ... or (I promise this is the last guess)  each row is appended to the 
> file in the the loop, and on some OS's there is a problem keeping open 
> the file (so that each time the file must be opened again ...  adding a 
> lot of extra time). Maybe creating all the "virtual file" in a variable 
> and storing all at once (or let say in chunks of 100 Mb) could solve the 
> problem ...
> 

Tried that yesterday, wasn't much faster (two seconds less, which isn't
much).


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

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.

On Thu, 2011-11-03 at 11:22 +0100, boris pezzatti wrote:
> 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?
> 

That's good to know. I tried with a boolean column, which gave me 7
seconds for 10000 rows. Which doesn't seem much, but it's actually 7
times more than without the boolean column.

It seems we keep executing the same SQL command, 10000 times (actually
as much as your number of rows). And that is weird.

I'll look into this.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

On Thu, 2011-11-03 at 12:02 +0100, Guillaume Lelarge wrote:
> On Thu, 2011-11-03 at 11:22 +0100, boris pezzatti wrote:
> > 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?
> > 
> 
> That's good to know. I tried with a boolean column, which gave me 7
> seconds for 10000 rows. Which doesn't seem much, but it's actually 7
> times more than without the boolean column.
> 
> It seems we keep executing the same SQL command, 10000 times (actually
> as much as your number of rows). And that is weird.
> 
> I'll look into this.
> 

OK, found it. We introduced some time ago a cache for type lookup. It
seems the boolean type doesn't make it to the cache. The patch is
commited. It should be available with the release of 1.14.1.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

great,
thank you a lot Guillaume!

Cheers,
Boris


On 11/03/2011 12:20 PM, Guillaume Lelarge wrote:
> On Thu, 2011-11-03 at 12:02 +0100, Guillaume Lelarge wrote:
>> On Thu, 2011-11-03 at 11:22 +0100, boris pezzatti wrote:
>>> 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?
>>>
>> That's good to know. I tried with a boolean column, which gave me 7
>> seconds for 10000 rows. Which doesn't seem much, but it's actually 7
>> times more than without the boolean column.
>>
>> It seems we keep executing the same SQL command, 10000 times (actually
>> as much as your number of rows). And that is weird.
>>
>> I'll look into this.
>>
> OK, found it. We introduced some time ago a cache for type lookup. It
> seems the boolean type doesn't make it to the cache. The patch is
> commited. It should be available with the release of 1.14.1.
>
>