Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 2047EA5CB37
	for <pgsql-performance@postgresql.org>;
	Wed, 15 Feb 2012 07:36:18 -0400 (AST)
Received: from mail.anarazel.de ([217.115.131.40])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andres@anarazel.de>) id 1Rxd9k-0003WR-VO
	for pgsql-performance@postgresql.org; Wed, 15 Feb 2012 11:36:18 +0000
Received: from intern.anarazel.de (p57A9EC32.dip.t-dialin.net [87.169.236.50])
	(Authenticated sender: andres@anarazel.de)
	by mail.anarazel.de (Postfix) with ESMTPA id 9E1CA448004;
	Wed, 15 Feb 2012 12:36:02 +0100 (CET)
From: Andres Freund <andres@anarazel.de>
To: Han Zhou <zhouhan@gmail.com>
Subject: Re: Fwd: [HACKERS] client performance v.s. server statistics
Date: Wed, 15 Feb 2012 12:36:01 +0100
User-Agent: KMail/1.13.7 (Linux/3.1.0-1-amd64; KDE/4.6.5; x86_64; ; )
Cc: pgsql-performance@postgresql.org
References: 
 <CADtzDCkR8O5OJ5bsVs8Lmr3yoi=uuA8GbO-QuNDsnrX9--WubA@mail.gmail.com>
	<CADtzDCkjzsQjQHnva8SDdL_H4GEURyW1=oYMikY_JrGD3rZ3GQ@mail.gmail.com>
	<CADtzDCn3eHDFtrMZqQ1ss7vy78P0TqFQtU+JWmqPre8Db=4Kfw@mail.gmail.com>
In-Reply-To: 
 <CADtzDCn3eHDFtrMZqQ1ss7vy78P0TqFQtU+JWmqPre8Db=4Kfw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201202151236.01839.andres@anarazel.de>
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201202/118
X-Sequence-Number: 46290

On Wednesday, February 15, 2012 12:33:13 PM Han Zhou wrote:
> 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.
Correct.

> 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.
Yea, you proved my quick theory wrong.

> 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.
Sounds like that.

It would be interesting to see the time difference between:
COPY (SELECT * FROM blub) TO '/tmp/somefile';
COPY (SELECT * FROM blub) TO '/tmp/somefile' BINARY;
EXPLAIN ANALYZE SELECT * FROM blub;

Andres