X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 1EE159DCB55
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 12 Dec 2005 21:07:55 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 69972-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 12 Dec 2005 21:07:58 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id A1E699DCB34
	for <pgsql-performance@postgresql.org>;
	Mon, 12 Dec 2005 21:07:52 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jBD17q8o084716
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Mon, 12 Dec 2005 18:07:55 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jBD17q36063520;
	Mon, 12 Dec 2005 18:07:52 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jBD17p9U063519;
	Mon, 12 Dec 2005 18:07:51 -0700 (MST) (envelope-from mfuhr)
Date: Mon, 12 Dec 2005 18:07:51 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>,
	pgsql-performance@postgresql.org,
	Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
Subject: Re: How much expensive are row level statistics?
Message-ID: <20051213010751.GA63416@winnie.fuhr.org>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DDAC9@Herge.rcsinc.local>
	<2506.1134428461@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2506.1134428461@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.019 required=5 tests=[AWL=0.019]
X-Spam-Score: 0.019
X-Spam-Level: 
X-Archive-Number: 200512/307
X-Sequence-Number: 16128

On Mon, Dec 12, 2005 at 06:01:01PM -0500, Tom Lane wrote:
> IIRC, the only significant cost from enabling stats is the cost of
> transmitting the counts to the stats collector, which is a cost
> basically paid once at each transaction commit.  So short transactions
> will definitely have more overhead than longer ones.  Even for a really
> simple transaction, though, 30% seems high --- the stats code is
> designed deliberately to minimize the penalty.

Now there goes Tom with his skeptical eye again, and here comes me
saying "oops" again.  Further tests show that for this application
the killer is stats_command_string, not stats_block_level or
stats_row_level.  Here are timings for the same set of operations
(thousands of insert, update, and delete statements in one transaction)
run under various settings:

stats_command_string = off
stats_block_level = off
stats_row_level = off
time: 2:09.46

stats_command_string = off
stats_block_level = on
stats_row_level = off
time: 2:12.28

stats_command_string = off
stats_block_level = on
stats_row_level = on
time: 2:14.38

stats_command_string = on
stats_block_level = off
stats_row_level = off
time: 2:50.58

stats_command_string = on
stats_block_level = on
stats_row_level = on
time: 2:53.76

[Wanders off, swearing that he ran these tests before and saw higher
penalties for block- and row-level statistics.]

-- 
Michael Fuhr