thread-index: Acci9KkyEWvgm34nQcm1VI3cCvZauw==
Subject: Re: [HACKERS] EXPLAIN ANALYZE on 8.2
Content-Class: urn:content-classes:message
Importance: normal
From: "Simon Riggs" <simon@2ndquadrant.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Peter Eisentraut" <peter_e@gmx.net>, <pgsql-hackers@postgresql.org>,
	"Kelly Burkhart" <kelly.burkhart@gmail.com>,
	"Evgeny Gridasov" <eugrid@fpm.kubsu.ru>, <pgsql-performance@postgresql.org>
In-Reply-To: <9227.1166198245@sss.pgh.pa.us>
References: <20061214163230.d46f0138.eugrid@fpm.kubsu.ru>
	<29450.1166141131@sss.pgh.pa.us>
	<1166178488.3882.56.camel@silverbirch.site>
	<200612151406.57851.peter_e@gmx.net>
	<9227.1166198245@sss.pgh.pa.us>
Content-Type: text/plain;
	charset="iso-8859-1"
Date: Mon, 18 Dec 2006 22:33:23 +0000
Message-ID: <1166481204.3654.174.camel@silverbirch.site>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2006-12-15 at 10:57 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Am Freitag, 15. Dezember 2006 11:28 schrieb Simon Riggs:
> >> Until we work out a better solution we can fix this in two ways:
> >> 
> >> 1. EXPLAIN ANALYZE [ [ WITH | WITHOUT ] TIME STATISTICS ] ...
> >> 2. enable_analyze_timer = off | on (default) (USERSET)
> 
> > The second one is enough in my mind.
> 
> I don't see any point in either one.  If you're not going to collect
> timing data then the only useful info EXPLAIN ANALYZE could provide is
> knowledge of which rowcount estimates are badly off ... and to get that,
> you have to wait for the query to finish, which may well be impractical
> even without the gettimeofday overhead. 

On a different part of this thread, you say:

On Fri, 2006-12-15 at 09:56 -0500, Tom Lane wrote:

> 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. 

It doesn't make sense to me to claim that the timing is so important
that we cannot do without it, at the same time as saying it isn't even
approximately true that is highly variable.

>  We had discussed upthread the
> idea of having an option to issue a NOTICE as soon as any actual
> rowcount exceeds the estimate by some-configurable-percentage, and that
> seems to me to be a much more useful response to the problem of
> "E.A. takes too long" than removing gettimeofday.

> One thing that's not too clear to me though is how the NOTICE would
> identify the node at which the rowcount was exceeded...

We'd have to output the whole EXPLAIN as a NOTICE for it to make any
sense. If we can't do without the timings, then half an EXPLAIN would be
even worse.

We'd need to take account of non-linear nodes. Hash nodes react badly
beyond a certain point, HashAgg even worse. Sort performs poorly after
the end of memory, as does Materialize. Other nodes are more linear so
would need a different percentage. I don't like the sound of a whole
gaggle of GUCs to describe that. Any ideas?

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com