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

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@anarazel.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