Re: That EXPLAIN ANALYZE patch still needs work

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Larry Rosenman <ler(at)lerctr(dot)org>, "'Alvaro Herrera'" <alvherre(at)commandprompt(dot)com>, "'Martijn van Oosterhout'" <kleptog(at)svana(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: That EXPLAIN ANALYZE patch still needs work
Date: 2006-06-09 19:40:53
Message-ID: 87hd2uglru.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > To avoid user confusion it would reasonable to print out a line at the bottom:
>
> > Explain analyze profiling overhead removed: xxx ms
>
> > That also gives the user feedback on how precise their explain analyze results
> > are. If they see that the overhead being removed is as large as the timing
> > remaining then they can realize that the results aren't especially precise. On
> > the other hand if they see that the overhead being removed is small then they
> > can be pretty confident in the precision of the results.
>
> I would prefer to leave the numbers unmanipulated, because frankly I'd
> have no confidence in the correction. Perhaps we could put a note at the
> bottom about "Estimated total profiling overhead: xxx ms".

Well the whole reason the overhead is deceptive is that some nodes call
gettimeofday many more times proportionally to their running time than others.
It makes users suspect cpu-intensive nodes that process many records when the
real problem lies in a i/o-intensive node that processes few records. Just
showing the total overhead will give the user the impression that time is
distributed evenly throughout the nodes proportionally to their total time
which doesn't help correct the distortion.

There seem to be two types of overhead going on. There's the amount of time
spent in gettimeofday itself which is pretty consistent. Then there's other
effects that cause normal operation itself to take longer (or potentially even
less long).

Postgres may as well remove the consistent gettimeofday overhead it can
identify and measure even if there's more it can't characterize perfectly.

--
greg

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-06-09 19:47:00 Re: List schema contents
Previous Message Jim C. Nasby 2006-06-09 19:33:57 List schema contents