Measure Timing Overhead with pg_test_timing

Before you trust a single number out of EXPLAIN ANALYZE, run pg_test_timing and find out how much the measuring itself costs on this host. On a noisy virtual machine that overhead can be the whole story.

You will finish with a measured baseline for this Linux host, a way to repeat it for a longer sample, and a safe comparison between an ordinary query and its EXPLAIN ANALYZE form. Allow about fifteen minutes. The examples use PostgreSQL 16.15, installed here as Ubuntu package postgresql-client-16 16.15-0ubuntu0.24.04.1.

You need a shell and the PostgreSQL client package that provides pg_test_timing. A database connection is optional for the first half and only needed for the query comparison. Nothing here changes PostgreSQL configuration, the kernel clock source or persistent data.

1. Confirm the installed program

Check the binary and its package version first. These are ordinary, read-only commands, no elevated privileges required:

$ command -v pg_test_timing
/usr/lib/postgresql/16/bin/pg_test_timing
$ dpkg-query -W -f='${Package} ${Version}\n' postgresql-client-16
postgresql-client-16 16.15-0ubuntu0.24.04.1
$ /usr/lib/postgresql/16/bin/pg_test_timing --version
pg_test_timing (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

Your PATH might find it somewhere else on a different install. What matters is that you have confirmed it is the PostgreSQL utility you meant to test, and that you write the version down alongside whatever you measure.

$ pg_test_timing --help
Usage: pg_test_timing [-d DURATION]

2. Run the three-second baseline

Run it with no options for the documented default of three seconds:

$ pg_test_timing
Testing timing overhead for 3 seconds.
Per loop time including overhead: 17.63 ns
Histogram of timing durations:
  < us   % of total      count
     1     98.27049   167208666
     2      1.72543     2935842
     4      0.00133        2268

These numbers move with CPU load, virtualisation, power management and clock source, so treat this as a sample, not a pass mark. The command measures how much work it takes to collect timing data, and along the way checks that the system clock never runs backwards.

Read it in two parts: the per-loop figure is nanoseconds including loop overhead, and the histogram groups individual calls by microsecond bucket. A healthy result usually has over 90 per cent of calls under one microsecond and average overhead below 100 nanoseconds. Useful signals, not a guarantee about what any particular query will cost.

3. Repeat with a longer sample

-d or --duration asks for more seconds. Ten is a reasonable repeatable sample when the first result looks jumpy:

$ pg_test_timing --duration=10
Testing timing overhead for 10 seconds.
Per loop time including overhead: 18.02 ns
Histogram of timing durations:
  < us   % of total      count
     1     98.1       550000000
     2      1.8        10000000
     4      0.1          500000

Those counts are illustrative; your host will print its own numbers. Check the duration in the first line and read the real histogram rather than copying expected figures into a report. A longer run tightens the estimate a little and gives the clock check more chances to spot a backwards step, at the cost of more CPU time while it runs.

Do not run several copies at once, they will compete for CPU and muddy the result. If the per-loop figure jumps around between otherwise quiet runs, check host load and VM scheduling before you go anywhere near a PostgreSQL setting.

4. Compare a query with EXPLAIN Analyse

EXPLAIN ANALYZE actually runs the statement while timing every plan node, and that timing itself is not free. A generated read-only set is a clean way to compare, since it never creates or drops a table:

$ psql -X -d YOUR_DATABASE
YOUR_DATABASE=> \timing on
Timing is on.
YOUR_DATABASE=> SELECT count(*) FROM generate_series(1, 100000);
 count
--------
 100000
(1 row)

Time: 6.4 ms
YOUR_DATABASE=> EXPLAIN ANALYZE SELECT count(*) FROM generate_series(1, 100000);
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1250.00..1250.01 rows=1 width=8) (actual time=... rows=1 loops=1)
 ...
 Planning Time: ... ms
 Execution Time: ... ms
(... rows)

Time: ... ms

Both plan text and timings are specific to your host. What you actually want to know is whether the analysed run takes noticeably longer than the plain one. Repeat each query a few times if the answer matters, accounting for cache state and background load, and never subtract one noisy pair of timings and present it as a universal per-row cost.

This session is temporary and the statement read-only, so there is nothing to undo. Exit with \q:

YOUR_DATABASE=> \q

5. Investigate a poor result without changing the clock

A large per-loop value, or a histogram with plenty of calls in the multi-microsecond range, makes timing-heavy plans less trustworthy. Rerun the test after cutting host load and check whether the result actually settles. You can inspect the clock source without writing to it:

$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource
$ cat /sys/devices/system/clocksource/clocksource0/current_clocksource

These files may not exist in a container or on a non-Linux system. That is an environment limit, not a licence to invent a number. If the first command lists several sources, the second names the one actually in use.

6. Handle common failures

If the shell says the command is missing, find the package-owned binary and run it by full path:

$ dpkg -S pg_test_timing
$ ls -l /usr/lib/postgresql/16/bin/pg_test_timing

If -d is rejected, check pg_test_timing --help and --version first, you may be running a different major version than the one documented here. Keep the duration a plain positive number, never a shell expression the program was not expecting.

If repeated tests disagree with each other, stop changing settings and capture the conditions instead: CPU load, host type, active clock source, PostgreSQL version. One run cannot tell timer overhead apart from scheduling noise. Elevated privileges are not normally needed for pg_test_timing or the read-only clock-source checks; only reach for sudo if your host's permissions genuinely require it, and never to write a clock-source setting during a diagnostic.