pg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\npg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. It reads supported clock sources over and over again as fast as it can for a specified length of time, and then prints statistics about the observed differences in successive clock readings, as well as which clock source will be used.
\nSmaller (but not zero) differences are better, since they imply both more-precise clock hardware and less overhead to collect a clock reading. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
This tool is also helpful to determine if the track_io_timing configuration parameter is likely to produce useful results, and whether the TSC clock source (see timing_clock_source) is available and if it will be used by default.
pg_test_timing accepts the following command-line options:
\n-c cutoff--cutoff=cutoffSpecifies the cutoff percentage for the list of exact observed timing durations (that is, the changes in the system clock value from one reading to the next). The list will end once the running percentage total reaches or exceeds this value, except that the largest observed duration will always be printed. The default cutoff is 99.99.
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nThe first block of output has four columns, with rows showing a shifted-by-one log2(ns) histogram of timing durations (that is, the differences between successive clock readings). This is not the classic log2(n+1) histogram as it counts zeros separately and then switches to log2(ns) starting from value 1.
\nThe columns are:
\nThe second block of output goes into more detail, showing the exact timing differences observed. For brevity this list is cut off when the running-sum percentage exceeds the user-selectable cutoff value. However, the largest observed difference is always shown.
\nOn platforms that support the TSC clock source, additional output sections are shown for the RDTSCP instruction (used for general timing needs, such as track_io_timing) and the RDTSC instruction (used for EXPLAIN ANALYZE). At the end of the output, the TSC frequency, which may either be sourced from CPU information directly, or the alternate calibration mechanism are shown, as well as whether the TSC clock source will be used by default.
The example results below show system clock timing where 99.99% of loops took between 16 and 63 nanoseconds. In the second block, we can see that the typical loop time is 40 nanoseconds, and the readings appear to have full nanosecond precision. Following the system clock results, the TSC clock source results are shown, in the same fashion. The RDTSCP instruction shows most loops completing in 20\u201330 nanoseconds, while the RDTSC instruction is the fastest with an average loop time of 20 nanoseconds. In this example the TSC clock source will be used by default, but can be disabled by setting timing_clock_source to system.
System clock source: clock_gettime (CLOCK_MONOTONIC)\nTesting timing overhead for 3 seconds.\nAverage loop time including overhead: 44.67 ns\nHistogram of timing durations:\n <= ns % of total running % count\n 0 0.0000 0.0000 0\n 1 0.0000 0.0000 0\n 3 0.0000 0.0000 0\n 7 0.0000 0.0000 0\n 15 0.0000 0.0000 0\n 31 24.0606 24.0606 5385707\n 63 75.8342 99.8948 16974658\n 127 0.0900 99.9848 20143\n 255 0.0069 99.9917 1542\n 511 0.0014 99.9932 322\n 1023 0.0003 99.9935 68\n 2047 0.0001 99.9936 23\n 4095 0.0036 99.9972 813\n 8191 0.0018 99.9990 402\n 16383 0.0005 99.9995 120\n 32767 0.0001 99.9997 32\n 65535 0.0001 99.9998 24\n\nObserved timing durations up to 99.9900%:\n ns % of total running % count\n 29 3.6921 3.6921 826442\n 30 16.6755 20.3676 3732628\n 31 3.6930 24.0606 826637\n 40 75.7761 99.8368 16961658\n 41 0.0019 99.8387 431\n...\n 190 0.0003 99.9901 65\n...\n29657159 0.0000 100.0000 1\n\nClock source: RDTSCP\nAverage loop time including overhead: 37.32 ns\nHistogram of timing durations:\n <= ns % of total running % count\n 0 0.0000 0.0000 0\n 1 0.0000 0.0000 0\n 3 0.0000 0.0000 0\n 7 0.0000 0.0000 0\n 15 0.0000 0.0000 0\n 31 99.9499 99.9499 26782299\n 63 0.0381 99.9880 10220\n 127 0.0008 99.9889 224\n 255 0.0052 99.9941 1403\n 511 0.0013 99.9954 340\n 1023 0.0001 99.9954 17\n 2047 0.0000 99.9955 7\n 4095 0.0021 99.9976 569\n 8191 0.0013 99.9989 357\n 16383 0.0005 99.9994 128\n 32767 0.0003 99.9996 70\n 65535 0.0001 99.9997 19\n\nObserved timing durations up to 99.9900%:\n ns % of total running % count\n 20 16.9064 16.9064 4530201\n 29 41.5214 58.4279 11125972\n 30 41.5220 99.9499 11126126\n 40 0.0089 99.9587 2374\n...\n 130 0.0007 99.9902 181\n...\n18501572 0.0000 100.0000 1\n\nFast clock source: RDTSC\nAverage loop time including overhead: 27.12 ns\nHistogram of timing durations:\n <= ns % of total running % count\n 0 0.0000 0.0000 0\n 1 0.0000 0.0000 0\n 3 0.0000 0.0000 0\n 7 0.0000 0.0000 0\n 15 1.2247 1.2247 456231\n 31 98.7566 99.9813 36789785\n 63 0.0109 99.9921 4049\n 127 0.0029 99.9951 1087\n 255 0.0008 99.9959 305\n 511 0.0007 99.9966 279\n 1023 0.0000 99.9966 7\n 2047 0.0001 99.9967 22\n 4095 0.0018 99.9985 673\n 8191 0.0010 99.9995 383\n 16383 0.0003 99.9998 94\n 32767 0.0001 99.9999 38\n 65535 0.0000 99.9999 9\n\nObserved timing durations up to 99.9900%:\n ns % of total running % count\n 9 0.6316 0.6316 235290\n 10 0.5931 1.2247 220941\n 20 91.4328 92.6574 34061442\n 29 3.6427 96.3001 1357007\n 30 3.6811 99.9813 1371336\n 40 0.0089 99.9902 3325\n...\n61594291 0.0000 100.0000 1\n\nTSC frequency source: x86, cpuid 0x15\nTSC frequency in use: 2449228 kHz\nTSC frequency from calibration: 2448603 kHz\n\nTSC clock source will be used by default, unless timing_clock_source is set to 'system'.\n\n
pg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. It reads supported clock sources over and over again as fast as it can for a specified length of time, and then prints statistics about the observed differences in successive clock readings, as well as which clock source will be used.
\nSmaller (but not zero) differences are better, since they imply both more-precise clock hardware and less overhead to collect a clock reading. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
This tool is also helpful to determine if the track_io_timing configuration parameter is likely to produce useful results, and whether the TSC clock source (see timing_clock_source) is available and if it will be used by default.
pg_test_timing accepts the following command-line options:
\n-c cutoff--cutoff=cutoffSpecifies the cutoff percentage for the list of exact observed timing durations (that is, the changes in the system clock value from one reading to the next). The list will end once the running percentage total reaches or exceeds this value, except that the largest observed duration will always be printed. The default cutoff is 99.99.
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nThe first block of output has four columns, with rows showing a shifted-by-one log2(ns) histogram of timing durations (that is, the differences between successive clock readings). This is not the classic log2(n+1) histogram as it counts zeros separately and then switches to log2(ns) starting from value 1.
\nThe columns are:
\nThe second block of output goes into more detail, showing the exact timing differences observed. For brevity this list is cut off when the running-sum percentage exceeds the user-selectable cutoff value. However, the largest observed difference is always shown.
\nOn platforms that support the TSC clock source, additional output sections are shown for the RDTSCP instruction (used for general timing needs, such as track_io_timing) and the RDTSC instruction (used for EXPLAIN ANALYZE). At the end of the output, the TSC frequency, which may either be sourced from CPU information directly, or the alternate calibration mechanism are shown, as well as whether the TSC clock source will be used by default.
The example results below show system clock timing where 99.99% of loops took between 16 and 63 nanoseconds. In the second block, we can see that the typical loop time is 40 nanoseconds, and the readings appear to have full nanosecond precision. Following the system clock results, the TSC clock source results are shown, in the same fashion. The RDTSCP instruction shows most loops completing in 20\u201330 nanoseconds, while the RDTSC instruction is the fastest with an average loop time of 20 nanoseconds. In this example the TSC clock source will be used by default, but can be disabled by setting timing_clock_source to system.
System clock source: clock_gettime (CLOCK_MONOTONIC)\nTesting timing overhead for 3 seconds.\nAverage loop time including overhead: 44.67 ns\nHistogram of timing durations:\n <= ns % of total running % count\n 0 0.0000 0.0000 0\n 1 0.0000 0.0000 0\n 3 0.0000 0.0000 0\n 7 0.0000 0.0000 0\n 15 0.0000 0.0000 0\n 31 24.0606 24.0606 5385707\n 63 75.8342 99.8948 16974658\n 127 0.0900 99.9848 20143\n 255 0.0069 99.9917 1542\n 511 0.0014 99.9932 322\n 1023 0.0003 99.9935 68\n 2047 0.0001 99.9936 23\n 4095 0.0036 99.9972 813\n 8191 0.0018 99.9990 402\n 16383 0.0005 99.9995 120\n 32767 0.0001 99.9997 32\n 65535 0.0001 99.9998 24\n\nObserved timing durations up to 99.9900%:\n ns % of total running % count\n 29 3.6921 3.6921 826442\n 30 16.6755 20.3676 3732628\n 31 3.6930 24.0606 826637\n 40 75.7761 99.8368 16961658\n 41 0.0019 99.8387 431\n...\n 190 0.0003 99.9901 65\n...\n29657159 0.0000 100.0000 1\n\nClock source: RDTSCP\nAverage loop time including overhead: 37.32 ns\nHistogram of timing durations:\n <= ns % of total running % count\n 0 0.0000 0.0000 0\n 1 0.0000 0.0000 0\n 3 0.0000 0.0000 0\n 7 0.0000 0.0000 0\n 15 0.0000 0.0000 0\n 31 99.9499 99.9499 26782299\n 63 0.0381 99.9880 10220\n 127 0.0008 99.9889 224\n 255 0.0052 99.9941 1403\n 511 0.0013 99.9954 340\n 1023 0.0001 99.9954 17\n 2047 0.0000 99.9955 7\n 4095 0.0021 99.9976 569\n 8191 0.0013 99.9989 357\n 16383 0.0005 99.9994 128\n 32767 0.0003 99.9996 70\n 65535 0.0001 99.9997 19\n\nObserved timing durations up to 99.9900%:\n ns % of total running % count\n 20 16.9064 16.9064 4530201\n 29 41.5214 58.4279 11125972\n 30 41.5220 99.9499 11126126\n 40 0.0089 99.9587 2374\n...\n 130 0.0007 99.9902 181\n...\n18501572 0.0000 100.0000 1\n\nFast clock source: RDTSC\nAverage loop time including overhead: 27.12 ns\nHistogram of timing durations:\n <= ns % of total running % count\n 0 0.0000 0.0000 0\n 1 0.0000 0.0000 0\n 3 0.0000 0.0000 0\n 7 0.0000 0.0000 0\n 15 1.2247 1.2247 456231\n 31 98.7566 99.9813 36789785\n 63 0.0109 99.9921 4049\n 127 0.0029 99.9951 1087\n 255 0.0008 99.9959 305\n 511 0.0007 99.9966 279\n 1023 0.0000 99.9966 7\n 2047 0.0001 99.9967 22\n 4095 0.0018 99.9985 673\n 8191 0.0010 99.9995 383\n 16383 0.0003 99.9998 94\n 32767 0.0001 99.9999 38\n 65535 0.0000 99.9999 9\n\nObserved timing durations up to 99.9900%:\n ns % of total running % count\n 9 0.6316 0.6316 235290\n 10 0.5931 1.2247 220941\n 20 91.4328 92.6574 34061442\n 29 3.6427 96.3001 1357007\n 30 3.6811 99.9813 1371336\n 40 0.0089 99.9902 3325\n...\n61594291 0.0000 100.0000 1\n\nTSC frequency source: x86, cpuid 0x15\nTSC frequency in use: 2449228 kHz\nTSC frequency from calibration: 2448603 kHz\n\nTSC clock source will be used by default, unless timing_clock_source is set to 'system'.\n\n
pg_test_timing \u2014 measure timing overhead
\npg_test_timing [option...]
pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate EXPLAIN ANALYZE results.
pg_test_timing accepts the following command-line options:
\n-d duration--duration=durationSpecifies the test duration, in seconds. Longer durations give slightly better accuracy, and are more likely to discover problems with the system clock moving backwards. The default test duration is 3 seconds.
\n-V--versionPrint the pg_test_timing version and exit.
\n-?--helpShow help about pg_test_timing command line arguments, and exit.
\nGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-860 system using a TSC clock source shows excellent performance:
\nTesting timing overhead for 3 seconds.\nPer loop time including overhead: 35.96 ns\nHistogram of timing durations:\n < us % of total count\n 1 96.40465 80435604\n 2 3.59518 2999652\n 4 0.00015 126\n 8 0.00002 13\n 16 0.00000 2\n\n
Note that different units are used for the per loop time than the histogram. The loop can have resolution within a few nanoseconds (ns), while the individual timing calls can only resolve down to one microsecond (us).
\nWhen the query executor is running a statement using EXPLAIN ANALYZE, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program:
CREATE TABLE t AS SELECT * FROM generate_series(1,100000);\n\\timing\nSELECT COUNT(*) FROM t;\nEXPLAIN ANALYZE SELECT COUNT(*) FROM t;\n\n
The i7-860 system measured runs the count query in 9.8 ms while the EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully timed count statement take almost 70% longer. On more substantial queries, the timing overhead would be less problematic.
On some newer Linux systems, it's possible to change the clock source used to collect timing data at any time. A second example shows the slowdown possible from switching to the slower acpi_pm time source, on the same system used for the fast results above:
\n# cat /sys/devices/system/clocksource/clocksource0/available_clocksource\ntsc hpet acpi_pm\n# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource\n# pg_test_timing\nPer loop time including overhead: 722.92 ns\nHistogram of timing durations:\n < us % of total count\n 1 27.84870 1155682\n 2 72.05956 2990371\n 4 0.07810 3241\n 8 0.01357 563\n 16 0.00007 3\n\n
In this configuration, the sample EXPLAIN ANALYZE above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In this configuration, any EXPLAIN ANALYZE totals involving many timed operations would be inflated significantly by timing overhead.
FreeBSD also allows changing the time source on the fly, and it logs information about the timer selected during boot:
\n# dmesg | grep \"Timecounter\"\nTimecounter \"ACPI-fast\" frequency 3579545 Hz quality 900\nTimecounter \"i8254\" frequency 1193182 Hz quality 0\nTimecounters tick every 10.000 msec\nTimecounter \"TSC\" frequency 2531787134 Hz quality 800\n# sysctl kern.timecounter.hardware=TSC\nkern.timecounter.hardware: ACPI-fast -> TSC\n\n
Other systems may only allow setting the time source on boot. On older Linux systems the \"clock\" kernel setting is the only way to make this sort of change. And even on some more recent ones, the only option you'll see for a clock source is \"jiffies\". Jiffies are the older Linux software clock implementation, which can have good resolution when it's backed by fast enough timing hardware, as in this example:
\n$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource\njiffies\n$ dmesg | grep time.c\ntime.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.\ntime.c: Detected 2400.153 MHz processor.\n$ pg_test_timing\nTesting timing overhead for 3 seconds.\nPer timing duration including loop overhead: 97.75 ns\nHistogram of timing durations:\n < us % of total count\n 1 90.23734 27694571\n 2 9.75277 2993204\n 4 0.00981 3010\n 8 0.00007 22\n 16 0.00000 1\n 32 0.00000 1\n\n
Collecting accurate timing information is normally done on computers using hardware clocks with various levels of accuracy. With some hardware the operating systems can pass the system clock time almost directly to programs. A system clock can also be derived from a chip that simply provides timing interrupts, periodic ticks at some known time interval. In either case, operating system kernels provide a clock source that hides these details. But the accuracy of that clock source and how quickly it can return results varies based on the underlying hardware.
\nInaccurate time keeping can result in system instability. Test any change to the clock source very carefully. Operating system defaults are sometimes made to favor reliability over best accuracy. And if you are using a virtual machine, look into the recommended time sources compatible with it. Virtual hardware faces additional difficulties when emulating timers, and there are often per operating system settings suggested by vendors.
\nThe Time Stamp Counter (TSC) clock source is the most accurate one available on current generation CPUs. It's the preferred way to track the system time when it's supported by the operating system and the TSC clock is reliable. There are several ways that TSC can fail to provide an accurate timing source, making it unreliable. Older systems can have a TSC clock that varies based on the CPU temperature, making it unusable for timing. Trying to use TSC on some older multicore CPUs can give a reported time that's inconsistent among multiple cores. This can result in the time going backwards, a problem this program checks for. And even the newest systems can fail to provide accurate TSC timing with very aggressive power saving configurations.
\nNewer operating systems may check for the known TSC problems and switch to a slower, more stable clock source when they are seen. If your system supports TSC time but doesn't default to that, it may be disabled for a good reason. And some operating systems may not detect all the possible problems correctly, or will allow using TSC even in situations where it's known to be inaccurate.
\nThe High Precision Event Timer (HPET) is the preferred timer on systems where it's available and TSC is not accurate. The timer chip itself is programmable to allow up to 100 nanosecond resolution, but you may not see that much accuracy in your system clock.
\nAdvanced Configuration and Power Interface (ACPI) provides a Power Management (PM) Timer, which Linux refers to as the acpi_pm. The clock derived from acpi_pm will at best provide 300 nanosecond resolution.
\nTimers used on older PC hardware include the 8254 Programmable Interval Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt Controller (APIC) timer, and the Cyclone timer. These timers aim for millisecond resolution.
\n