Re: Help with tuning this query (with explain analyze finally)

From: Tom Lane
Subject: Re: Help with tuning this query (with explain analyze finally)
Date: ,
Msg-id: 14444.1109802588@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari")
List: pgsql-performance

Tree view

Help with tuning this query  ("Ken Egervari", )
 Re: Help with tuning this query  (Richard Huxton, )
  Re: Help with tuning this query  (Bricklen Anderson, )
   Re: Help with tuning this query  (Bruce Momjian, )
 Re: Help with tuning this query  (John Arbash Meinel, )
 Re: Help with tuning this query  ("Ken Egervari", )
  Re: Help with tuning this query  (John Arbash Meinel, )
 Re: Help with tuning this query  (Ragnar Hafstað, )
 Re: Help with tuning this query  ("Ken Egervari", )
  Re: Help with tuning this query  (Ragnar Hafstað, )
 Re: Help with tuning this query  ("Ken Egervari", )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Tom Lane, )
   Re: Help with tuning this query (with explain analyze finally)  (John A Meinel, )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Tom Lane, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
 Re: Help with tuning this query (more musings)  ("Ken Egervari", )
  Re: Help with tuning this query (more musings)  (John A Meinel, )
 Re: Help with tuning this query (Some musings)  ("Ken Egervari", )
 Re: Help with tuning this query  (Mark Kirkwood, )
 Re: Help with tuning this query  (Josh Berkus, )
 Re: Help with tuning this query (more musings)  ("Ken Egervari", )
  Re: Help with tuning this query (more musings)  (Richard Huxton, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Richard Huxton, )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken", )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
   Re: Help with tuning this query (with explain analyze finally)  (John A Meinel, )

"Ken Egervari" <> writes:
>> What platform is this on?  It seems very strange/fishy that all the
>> actual-time values are exact integral milliseconds.

> My machine is WinXP professional, athon xp 2100, but I get similar results
> on my Intel P4 3.0Ghz as well (which is also running WinXP).  Why do you
> ask?

Well, what it suggests is that gettimeofday() is only returning a result
good to the nearest millisecond.  (Win32 hackers, does that sound right?)

If so, I'd have to take the EXPLAIN ANALYZE results with a big grain of
salt, because what it's trying to do is add up a lot of
mostly-sub-millisecond intervals.  What would essentially happen is that
whichever plan node had control at a particular millisecond boundary
would get charged for the whole preceding millisecond, and any other
nodes (which might have actually eaten most of the millisecond) would
get charged nothing.

Over a sufficiently long query run, the errors would average out, but
this wasn't that long --- 312 milliseconds, so in essence we are trying
to estimate the query's behavior from only 312 samples of where it was
at the millisecond boundaries.  I don't trust profiles based on less
than a few thousand samples ...

Most modern machines seem to have clocks that can count elapsed time
down to near the microsecond level.  Anyone know if it's possible to get
such numbers out of Windows, or are we stuck with milliseconds?

            regards, tom lane


pgsql-performance by date:

From: "Ken Egervari"
Date:
Subject: Re: Help with tuning this query (Some musings)
From: Mark Kirkwood
Date:
Subject: Re: Help with tuning this query