From: Michael Fuhr <mike@fuhr.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org, Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
Subject: Re: How much expensive are row level statistics?
Date: Mon, 12 Dec 2005 18:07:51 -0700
Message-ID: <20051213010751.GA63416@winnie.fuhr.org> (raw)
In-Reply-To: <2506.1134428461@sss.pgh.pa.us>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DDAC9@Herge.rcsinc.local>
<2506.1134428461@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com, carlosbenkendorf@yahoo.com.br
Subject: Re: How much expensive are row level statistics?
In-Reply-To: <20051213010751.GA63416@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox