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

Hi,

To be more specific, I list my calculation here:
The timing shown in psql may include: plan + execution + copying to
result set in backend (does this step exist?) + transferring data to
client via socket.

Then I want to know what's the time shown in pg_stat_statement and
EXPLAIN ANALYZE in terms of the above mentioned parts. And why are the
gap is almost 10 times (100 ms v.s. 1 second)? As a comparison,
transferring same amount of data with unix domain socket should cost
only a very small fraction of this (almost negligible), according to
my other performance tests.

And I don't think the plan time plays an important role here in
EXPLAIN ANALYZE, because the command itself costs similar time to the
"Total runtime" as shown in psql (timing on), which means the plan is
too simple to take any significant part of time in this case.

Best regards,
Han

On Wed, Feb 15, 2012 at 7:02 PM, Han Zhou <zhouhan(at)gmail(dot)com> wrote:
> 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

--
Best regards,
Han