Re: pg_test_timing tool for EXPLAIN ANALYZE overhead

On 02/22/2012 11:10 AM, Jay Levitt wrote:
> N.B.: Windows has at least two clock APIs, timeGetTime and
> QueryPerformanceCounters (and probably more, these days). They rely on
> different hardware clocks, and can get out of sync with each other;
> meanwhile, QueryPerformanceCounters can get out of sync with itself on
> (older?) multi-CPU boards.

The PostgreSQL wrapper in src/include/portability/instr_time.h uses
QueryPerformanceCounter and QueryPerformanceFrequency in a way that the
result can be used similarly to how deltas in UNIX dates are returned.

As far as I've been able to tell, there aren't any issues unique to
Windows there. Multiple cores can have their TSC results get out of
sync on Windows for the same reason they do on Linux systems, and
there's also the same frequency/temperature issues.

Newer versions of Windows can use TSC, older versions only use HPET or
ACPI on older versions, and there's some ability to force bad TSC units
to use ACPI instead:

http://blogs.msdn.com/b/psssql/archive/2010/08/18/how-it-works-timer-outputs-in-sql-server-2008-r2-invariant-tsc.aspx
http://blogs.msdn.com/b/psssql/archive/2010/08/18/how-it-works-timer-outputs-in-sql-server-2008-r2-invariant-tsc.aspx

There is a lot of questionable behavior if you try to use the better
timers in Windows XP; check out the obnoxious foot note about XP SP3 at
http://en.wikipedia.org/wiki/High_Precision_Event_Timer for example.

Since there's little you can do about--it's not even possible to figure
out which timer is being used easily--I didn't see any easy ways to
document what Windows does here, in a way that helped anyone. For the
most part, you get the best timer Windows has a driver for with
QueryPerformanceCounter, and that's what the PostgreSQL code uses. The
information I gave about how newer systems can have more accurate timing
is still accurate. Maybe it would be useful to add something pointing
out that newer Windows versions tend to support faster timers, too.
That's something I assume people would guess from what I already wrote,
it may be worth stating explicitly though.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com