Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Monday, March 07, 2005 10:39 AM > To: John A Meinel > Cc: Magnus Hagander; Ken Egervari; pgsql-performance@postgresql.org; > [EMAIL PROTECTED] > Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning > this query > (with explain analyze finally) > > [...] > 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. There is always clock(). It's mandated by ANSI C, but my docs say that POSIX requires CLOCKS_PER_SEC == 100 regardless of actual timer resolution, which seems a little brain-dead to me. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
John A Meinel <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
"Magnus Hagander" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
> > Do we need actual high precision time, or do we just need > to be able > > to get high precision differences? Getting the differences > is fairly > > easy, but if you need to "sync up" any drif then it becomes > a bit more > > difficult. > > You're right, we only care about differences not absolute > time. If there's something like a microseconds-since-bootup > counter, it would be fine. There is. I beleive QueryPerformanceCounter has sub-mirosecond resolution. Can we just replace gettimeofday() with a version that's basically: if (never_run_before) GetSystemTime() and get current timer for baseline. now = baseline + current timer - baseline timer; return now; Or do we need to make changes at the points where the function is actually called? //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > Do we need actual high precision time, or do we just need to be able to > get high precision differences? Getting the differences is fairly easy, > but if you need to "sync up" any drif then it becomes a bit more > difficult. You're right, we only care about differences not absolute time. If there's something like a microseconds-since-bootup counter, it would be fine. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
> >> 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?) Yes. The gettimeofday() implementation (in src/backend/port/gettimeofday.c). Actually, in reality you don't even get millisecond resolution it seems (after some reading up). More along the line of 10-millisecond-resolution. See for example http://msdn.microsoft.com/msdnmag/issues/04/03/HighResolutionTimer/. > 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? There are, see link above. But it's definitly not easy. I don't think we can just take the complete code from their exmaple (due to licensing). We could go with the "middle way", but it has a couple of pitfalls. Do we need actual high precision time, or do we just need to be able to get high precision differences? Getting the differences is fairly easy, but if you need to "sync up" any drif then it becomes a bit more difficult. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
