Re: A costing analysis tool

On Sat, Oct 15, 2005 at 04:04:52PM +0200, Martijn van Oosterhout wrote:
> 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.

DTrace (http://www.opensolaris.org/os/community/dtrace/) is another
possibility, althought AFAIK it's only available on OpenSolaris right
now. But I've also heard that BSD guys are pretty stoked about it, so it
might become a standard across multiple OSes.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461