Thread: Re: [pgsql-hackers-win32] Help with tuning this query (with explain analyze finally)

"Magnus Hagander" <mha@sollentuna.net> writes:
> There is. I beleive QueryPerformanceCounter has sub-mirosecond
> resolution.

> Can we just replace gettimeofday() with a version that's basically:

No, because it's also used for actual time-of-day calls.  It'd be
necessary to hack executor/instrument.c in particular.

            regards, tom lane

Tom Lane wrote:

>"Magnus Hagander" <mha@sollentuna.net> writes:
>
>
>>There is. I beleive QueryPerformanceCounter has sub-mirosecond
>>resolution.
>>
>>
>>Can we just replace gettimeofday() with a version that's basically:
>>
>>
>
>No, because it's also used for actual time-of-day calls.  It'd be
>necessary to hack executor/instrument.c in particular.
>
>            regards, tom lane
>
>
It seems that there are 2 possibilities. Leave gettimeofday as it is,
and then change code that calls it for deltas with a
"pg_get_high_res_delta_time()", which on most platforms is just
gettimeofday, but on win32 is a wrapper for QueryPerformanceCounter().

Or we modify the win32 gettimeofday call to something like:

gettimeofday(struct timeval *tv, struct timezone *tz)
{
  static int initialized = 0;
  static LARGE_INTEGER freq = {0};
  static LARGE_INTEGER base = {0};
  static struct time_t base_tm = {0};
  LARGE_INTEGER now = {0};
  int64_t delta_secs = 0;

  if(!initialized) {
    QueryPerformanceFrequency(&freq);
    base_tm = time(NULL); // This can be any moderately accurate time
function, maybe getlocaltime if it exists
    QueryPerformanceCounter(&base);
  }

  QueryPerformanceCounter(&now);
  delta_secs = now.QuadPart - base.QuadPart;
  tv->tv_sec = delta_secs / freq.QuadPart;
  delta_secs -= *tv.tv_sec * freq.QuadPart;
  tv->tv_usec = delta_secs * 1000000 / freq.QuadPart

  tv->tv_sec += base_tm;

   return 0;
}

John A Meinel <john@arbash-meinel.com> writes:
>>> Can we just replace gettimeofday() with a version that's basically:
>>
>> No, because it's also used for actual time-of-day calls.  It'd be
>> necessary to hack executor/instrument.c in particular.

> Or we modify the win32 gettimeofday call to something like:

That's what Magnus was talking about, but it's really no good because
it would cause Postgres' now() function to fail to track post-boot-time
changes in the system date setting.  Which I think would rightly be
considered a bug.

The EXPLAIN ANALYZE instrumentation code will really be happier with a
straight time-since-bootup counter; by using gettimeofday, it is
vulnerable to giving wrong answers if someone changes the date setting
while the EXPLAIN is running.  But there is (AFAIK) no such call among
the portable Unix syscalls.  It seems reasonable to me to #ifdef that
code to make use of QueryPerformanceCounter on Windows.  This does not
mean we want to alter the behavior of gettimeofday() where it's being
used to find out the time of day.

            regards, tom lane

Tom Lane wrote:

>John A Meinel <john@arbash-meinel.com> writes:
>
>
>>>>Can we just replace gettimeofday() with a version that's basically:
>>>>
>>>>
>>>No, because it's also used for actual time-of-day calls.  It'd be
>>>necessary to hack executor/instrument.c in particular.
>>>
>>>
>
>
>
>>Or we modify the win32 gettimeofday call to something like:
>>
>>
>
>That's what Magnus was talking about, but it's really no good because
>it would cause Postgres' now() function to fail to track post-boot-time
>changes in the system date setting.  Which I think would rightly be
>considered a bug.
>
>The EXPLAIN ANALYZE instrumentation code will really be happier with a
>straight time-since-bootup counter; by using gettimeofday, it is
>vulnerable to giving wrong answers if someone changes the date setting
>while the EXPLAIN is running.  But there is (AFAIK) no such call among
>the portable Unix syscalls.  It seems reasonable to me to #ifdef that
>code to make use of QueryPerformanceCounter on Windows.  This does not
>mean we want to alter the behavior of gettimeofday() where it's being
>used to find out the time of day.
>
>            regards, tom lane
>
>
>

What if you changed the "initialized" to

if (count & 0xFF == 0) {
  count = 1;
  // get the new time of day
}
++count;

Then we would only be wrong for 256 gettimeofday calls. I agree it isn't
great, though. And probably better to just abstract (possibly just with
#ifdef) the calls for accurate timing, from the calls that actually need
the real time.

John
=:->

John A Meinel <john@arbash-meinel.com> writes:

> Then we would only be wrong for 256 gettimeofday calls. I agree it isn't
> great, though. And probably better to just abstract (possibly just with
> #ifdef) the calls for accurate timing, from the calls that actually need
> the real time.

What would be really neato would be to use the rtdsc (sp?) or equivalent
assembly instruction where available. Most processors provide such a thing and
it would give much lower overhead and much more accurate answers.

The main problem I see with this would be on multi-processor machines.
(QueryPerformanceCounter does work properly on multi-processor machines,
right?)

--
greg