From: Martijn van Oosterhout <kleptog@svana.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Kelly Burkhart <kelly.burkhart@gmail.com>
Cc: Evgeny Gridasov <eugrid@fpm.kubsu.ru>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] EXPLAIN ANALYZE on 8.2
Date: Fri, 15 Dec 2006 16:48:45 +0100
Message-ID: <20061215154845.GL958@svana.org> (raw)
In-Reply-To: <5822.1166194617@sss.pgh.pa.us>
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>
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?
>
> > Yeah, it reduced the number of calls as the count got larger. It broke
> > somewhere, though I don't quite remember why.
>
> 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,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.
Attachments:
signature.asc (application/pgp-signature, 188B) download
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: kleptog@svana.org, tgl@sss.pgh.pa.us, simon@2ndquadrant.com, kelly.burkhart@gmail.com, eugrid@fpm.kubsu.ru, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] EXPLAIN ANALYZE on 8.2
In-Reply-To: <20061215154845.GL958@svana.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