Re: Performance PG 8.0 on dual opteron / 4GB / 3ware

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> Joost Kraaijeveld
> Sent: 07 November 2005 04:26
> To: Tom Lane
> Cc: Pgsql-Performance
> Subject: Re: [PERFORM] Performance PG 8.0 on dual opteron /
> 4GB / 3ware
>
> Hi Tom,
>
> On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote:
> > I'm confused --- where's the 82sec figure coming from, exactly?
> >From actually executing the query.
>
> >From PgAdmin:
>
> -- Executing query:
> select objectid from prototype.orders
>
> Total query runtime: 78918 ms.
> Data retrieval runtime: 188822 ms.
> 1104379 rows retrieved.
>
>
> > We've heard reports of performance issues in PgAdmin with large
> > result sets ... if you do the same query in psql, what happens?
> jkr(at)Panoramix:~/postgresql$ time psql muntdev -c "select objectid from
> prototype.orders" > output.txt
>
> real 0m5.554s
> user 0m1.121s
> sys 0m0.470s
>
>
> Now *I* am confused. What does PgAdmin do more than giving
> the query to
> the database?

Nothing - it just uses libpq's pqexec function. The speed issue in
pgAdmin is rendering the results in the grid which can be slow on some
OS's due to inefficiencies in some grid controls with large data sets.
That's why we give 2 times - the first is the query runtime on the
server, the second is data retrieval and rendering (iirc, it's been a
while).

Regards, Dave

Hi Dave,

On Mon, 2005-11-07 at 08:51 +0000, Dave Page wrote:
> > On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote:
> > > I'm confused --- where's the 82sec figure coming from, exactly?
> > >From actually executing the query.
> >
> > >From PgAdmin:
> >
> > -- Executing query:
> > select objectid from prototype.orders
> >
> > Total query runtime: 78918 ms.
> > Data retrieval runtime: 188822 ms.
> > 1104379 rows retrieved.
> >
> >
> > > We've heard reports of performance issues in PgAdmin with large
> > > result sets ... if you do the same query in psql, what happens?
> > jkr(at)Panoramix:~/postgresql$ time psql muntdev -c "select objectid from
> > prototype.orders" > output.txt
> >
> > real 0m5.554s
> > user 0m1.121s
> > sys 0m0.470s
> >
> >
> > Now *I* am confused. What does PgAdmin do more than giving
> > the query to
> > the database?
>
> Nothing - it just uses libpq's pqexec function. The speed issue in
> pgAdmin is rendering the results in the grid which can be slow on some
> OS's due to inefficiencies in some grid controls with large data sets.
> That's why we give 2 times - the first is the query runtime on the
> server, the second is data retrieval and rendering (iirc, it's been a
> while).
That is what I thought, but what could explain the difference in query
runtime (78 seconds versus 5 seconds) ?

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J(dot)Kraaijeveld(at)Askesis(dot)nl
web: www.askesis.nl

Dave Page wrote:

>>
>>
>>Now *I* am confused. What does PgAdmin do more than giving
>>the query to
>>the database?
>
>
> Nothing - it just uses libpq's pqexec function. The speed issue in
> pgAdmin is rendering the results in the grid which can be slow on some
> OS's due to inefficiencies in some grid controls with large data sets.
> That's why we give 2 times - the first is the query runtime on the
> server, the second is data retrieval and rendering (iirc, it's been a
> while).

yrnc.
Query runtime includes data transfer to the client, i.e. until libpq
returns the set, second time is retrieving data from libpq and rendering.

Regards,