Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id D9CC59FA38F;
	Fri, 15 Dec 2006 11:49:07 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 60159-10; Fri, 15 Dec 2006 11:48:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svana.org (calyx.svana.org [125.62.87.134])
	by postgresql.org (Postfix) with ESMTP id 45F0D9FA178;
	Fri, 15 Dec 2006 11:48:57 -0400 (AST)
Received: from kleptog by svana.org with local (Exim 4.50)
	id 1GvFIj-0002vV-Ag; Sat, 16 Dec 2006 02:48:45 +1100
Date: Fri, 15 Dec 2006 16:48:45 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>,
	Kelly Burkhart <kelly.burkhart@gmail.com>,
	Evgeny Gridasov <eugrid@fpm.kubsu.ru>,
	pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] EXPLAIN ANALYZE on 8.2
Message-ID: <20061215154845.GL958@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <21146.1166112702@sss.pgh.pa.us>
	<20061214192920.c75385d6.eugrid@fpm.kubsu.ru>
	<21516.1166114475@sss.pgh.pa.us>
	<fa1e4ce70612141543o8866d7fg417eca88ce9b1344@mail.gmail.com>
	<29450.1166141131@sss.pgh.pa.us>
	<1166178488.3882.56.camel@silverbirch.site>
	<20061215105008.GD958@svana.org>
	<1166185247.3882.69.camel@silverbirch.site>
	<20061215123052.GJ958@svana.org> <5822.1166194617@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="Cy+5HEalSgyXkpVS"
Content-Disposition: inline
In-Reply-To: <5822.1166194617@sss.pgh.pa.us>
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
User-Agent: Mutt/1.5.9i
X-SA-Exim-Connect-IP: <locally generated>
X-SA-Exim-Mail-From: kleptog@svana.org
X-SA-Exim-Scanned: No (on svana.org); SAEximRunCond expanded to false
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/412
X-Sequence-Number: 22468


--Cy+5HEalSgyXkpVS
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Dec 15, 2006 at 09:56:57AM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Fri, Dec 15, 2006 at 12:20:46PM +0000, Simon Riggs wrote:
> >> Maybe sampling every 10 rows will bring things down to an acceptable
> >> level (after the first N). You tried less than 10 didn't you?
>=20
> > Yeah, it reduced the number of calls as the count got larger. It broke
> > somewhere, though I don't quite remember why.
>=20
> The fundamental problem with it was the assumption that different
> executions of a plan node will have the same timing.  That's not true,
> in fact not even approximately true.  IIRC the patch did realize
> that first-time-through is not a predictor for the rest, but some of
> our plan nodes have enormous variance even after the first time.
> I think the worst case is batched hash joins.

It didn't assume that because that's obviously bogus. It assumed the
durations would be spread as a normal distribution. Which meant that
over time the average of the measured iterations would approch the
actual average. It tried to take enough measurements to try and keep
expected error small, but it's statistics, you can only say "this will
give the right answer >95% of the time".

You are correct though, the error was caused by unexpectedly large
variations, or more likely, an unexpected distribution curve.
Statistically, we took enough samples to not be affected significantly
by large variations. Even if it looked more like a gamma distribution
it should not have been as far off as it was.

Looking at alternative approaches, like sampling with a timer, you end
up with the same problem: sometimes the calculations will fail and
produce something strange. The simplest example being than a 100Hz
timer is not going to produce any useful information for queries in the
millisecond range. A higher frequency timer than that is not going to
be available portably.

You could probably throw more effort into refining the statistics behind
it, but at some point we're going to have to draw a line and say: it's
going to be wrong X% of the time, deal with it. If we're not willing to
say that, then there's no point going ahead with any statistical
approach.

Have a nice day,
--=20
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to l=
itigate.

--Cy+5HEalSgyXkpVS
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFgsPdIB7bNG8LQkwRAmCGAJ9fnRavZoU/Sl+VbeZrXO3iHMt7YQCfdUFS
7//BprwXg5xXXBUlKYjSXG8=
=LBY5
-----END PGP SIGNATURE-----

--Cy+5HEalSgyXkpVS--