From: | boris pezzatti <boris(dot)pezzatti(at)wsl(dot)ch> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Re: very slow when writing query to file |
Date: | 2011-11-03 10:22:30 |
Message-ID: | 4EB26B66.7000403@wsl.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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
> <guillaume(at)lelarge(dot)info <mailto:guillaume(at)lelarge(dot)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(dot)pezzatti(at)wsl(dot)ch
http://www.wsl.ch <http://www.wsl.ch/>
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2011-11-03 11:02:22 | Re: very slow when writing query to file |
Previous Message | Guillaume Lelarge | 2011-11-03 08:27:28 | Re: Bug, New Column... window, data type typing |