Subject: Re: How much expensive are row level statistics?
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Fuhr <mike@fuhr.org>,
	Merlin Moncure <merlin.moncure@rcsonline.com>,
	pgsql-performance@postgresql.org,
	Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
In-Reply-To: <18416.1134691599@sss.pgh.pa.us>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DDAC9@Herge.rcsinc.local>
	<2506.1134428461@sss.pgh.pa.us>
	<20051213010751.GA63416@winnie.fuhr.org>
	<1667.1134444045@sss.pgh.pa.us>
	<20051215234448.GA47382@winnie.fuhr.org>
	<18416.1134691599@sss.pgh.pa.us>
Content-Type: text/plain
Date: Fri, 16 Dec 2005 13:17:25 +0000
Message-Id: <1134739045.2964.15.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2005-12-15 at 19:06 -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Does the backend support, or could it be easily modified to support,
> > a mechanism that would post the command string after a configurable
> > amount of time had expired, and then continue processing the query?
> 
> Not really, unless you want to add the overhead of setting a timer
> interrupt for every query.  Which is sort of counterproductive when
> the motivation is to reduce overhead ...
> 
> (It might be more or less free if you have statement_timeout set, since
> there would be a setitimer call anyway.  But I don't think that's the
> norm.)

We could do the deferred send fairly easily. You need only set a timer
when stats_command_string = on, so we'd only do that when requested by
the admin. Overall, that would be a cheaper way of doing it than now.

However, I'm more inclined to the idea of a set of functions that allow
an administrator to retrieve the full SQL text executing in a backend,
with an option to return an EXPLAIN of the currently executing plan.
Right now, stats only gives you the first 1000 chars, so you're always
stuck if its a big query. Plus we don't yet have a way of getting the
exact  EXPLAIN of a running query (you can get close, but it could
differ).

Pull is better than push. Asking specific backends what they're doing
when you need to know will be efficient; asking them to send their
command strings, all of the time, deferred or not will always be more
wasteful. Plus if you forgot to turn on stats_command_string before
execution, then you've no way of knowing anyhow.

Best Regards, Simon Riggs