Re: A costing analysis tool

On Fri, Oct 14, 2005 at 03:34:43PM -0500, Kevin Grittner wrote:
> Of course, if running with EXPLAIN ANALYZE significantly
> distorts the run time, the whole effort is doomed at the outset.
> Can you quantify the distortion you mention?  Do you know

To do the calculations for EXPLAIN ANALYZE, PostgreSQL will call
gettimeofday() once (or possibly twice) for every iteration of every
node in the execution plan. This is usually (but not always) a kernel
call so if there are a lot of rows being processed compared with the
amount of other calculations happening, the results are distorted.

This is unfortunate because EXPLAIN ANALYZE is an immensly useful tool,
as far as it goes. I've pondered if some kind of userspace timing
mechanism could be introduced (possibly using builtin CPU cycle
counters) to reduce the cost. It does, however, remain a cost.

Given that you can see how many times gettimeday() was called, you may
be able to correct the error. I havn't tried that though.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.