Skip site navigation (1) Skip section navigation (2)

Re: Fwd: [HACKERS] client performance v.s. server statistics

From: Han Zhou <zhouhan(at)gmail(dot)com>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Fwd: [HACKERS] client performance v.s. server statistics
Date: 2012-02-15 11:02:53
Message-ID: CADtzDCkjzsQjQHnva8SDdL_H4GEURyW1=oYMikY_JrGD3rZ3GQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Hi Andres,

May you missed my first post, and I paste it here again:
In our environment sequential scanning (select * from ...) for a table
with tens of thousands of record costs 1 - 2 seconds, regardless of
using ODBC driver or the "timing" result shown in psql client (which
in turn, relies on libpq). However, using EXPLAIN ANALYZE, or checking
the statistics in pg_stat_statement view, the query costs only less
than 100ms.

Has the pg_stat_statement or EXPLAIN ANALYZE included the cost of
copying tuples from shared buffers to result sets?

Best regards,
Han

On Wed, Feb 15, 2012 at 6:55 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Hi,
> On Wednesday, February 15, 2012 11:19:00 AM Zhou Han wrote:
>> I have tried unix domain socket and the performance is similar with
>> TCP socket. It is MIPS architecture so memory copy to/from kernel can
>> occupy much time, and apparently using unit domain socket has no
>> difference than TCP in terms of memory copy.
>
>> But it is still unbelievable for the ten-fold gap between the client
>> side statistic and the server side statistics. So I want to know what
>> exactly the operations are involved in the server side statistics in
>> EXPLAIN ANALYZE. May I check the code later on when I get time.
> My guess is that the time difference youre seing is actually the planning time.
> The timing shown at the end of EXPLAIN ANALYZE is just the execution, not the
> planning time. You can use "\timing on" in psql to let it display timing
> information that include planning.
>
> Whats the query?
>> For the query itself, it was just for performance comparison. There
>> are other index based queries, which are of course much faster, but
>> still result in similar ten-fold of time gap between client side and
>> server side statistics.
>>
>> I am thinking of non-kernel involved client interface, is there such
>> an option, or do I have to develop one from scratch?
> Its unlikely thats possible in a sensible amount of time. But I don't think
> thats your problem anyway.
>
> Andres



-- 
Best regards,
Han

In response to

Responses

pgsql-performance by date

Next:From: Han ZhouDate: 2012-02-15 11:33:13
Subject: Re: Fwd: [HACKERS] client performance v.s. server statistics
Previous:From: Andres FreundDate: 2012-02-15 10:55:19
Subject: Re: Fwd: [HACKERS] client performance v.s. server statistics

pgsql-hackers by date

Next:From: Kohei KaiGaiDate: 2012-02-15 11:14:49
Subject: Re: [v9.2] LEAKPROOF attribute of FUNCTION (Re: [v9.2] Fix Leaky View Problem)
Previous:From: Andres FreundDate: 2012-02-15 10:55:19
Subject: Re: Fwd: [HACKERS] client performance v.s. server statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group