Measure PostgreSQL Timing Overhead Before Trusting EXPLAIN Analyse

You will finish with a measured timing-overhead 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. The examples use PostgreSQL 16.15, installed here as Ubuntu package postgresql-client-16 16.15-0ubuntu0.24.04.1.

Allow about fifteen minutes. You need a shell and the PostgreSQL client package that provides pg_test_timing. A database connection is optional for the first half and required only for the query comparison. The guide reads timing information and runs a read-only query; it does not change PostgreSQL configuration, the kernel clock source or persistent database data.

1. Confirm the installed program

Start by checking the binary and package version. These are ordinary, read-only commands and do not need elevated privileges:

$ 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 command may be found through a different PATH entry on another installation. The important checks are that it is the PostgreSQL utility you intend to test and that you record its version beside the measurements.

Checkpoint: ask the installed binary for its supported options. In PostgreSQL 16, the useful controls are the duration, version and help switches:

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

2. Run the three-second baseline

Run the command with no options for the documented default duration 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

The exact numbers vary with CPU load, virtualisation, power management and the clock source, so treat this as a sample rather than a fixed pass value. The command measures how much work is involved in collecting timing data and also checks that the system time does not move backwards.

Read the output in two parts. The per-loop figure is in nanoseconds and includes loop overhead. The histogram groups individual timing calls by microsecond buckets. A healthy result normally has more than 90 per cent of calls below one microsecond, with average loop overhead below 100 nanoseconds. Those are useful indications, not a promise that every query will incur exactly that cost.

Checkpoint: save the host, PostgreSQL version, date, workload conditions and the complete output if you are comparing machines. Do not compare a quiet bare-metal run with a busy virtual machine and call the difference a PostgreSQL regression.

3. Repeat with a longer sample

Use -d or --duration to request a longer test in seconds. Ten seconds is a reasonable repeatable sample when the first result looks noisy:

$ 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

The counts above are illustrative: your host will produce different values. Verify the duration from the first line and inspect the real histogram rather than copying expected numbers into a report. A longer run improves the estimate slightly and gives the clock check more opportunity to find a backwards step, but it consumes CPU time while it runs.

Do not start several copies at once. They compete for CPU and make the result harder to interpret. If the per-loop figure changes sharply between quiet runs, check host load and virtual machine scheduling before changing PostgreSQL.

4. Compare a query with EXPLAIN Analyse

EXPLAIN ANALYZE executes the statement while timing its plan nodes. That timing is useful, but it adds overhead. Use a read-only generated set for a quick comparison, so this example does not create or remove 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

The plan text and timings are host-specific. The useful comparison is whether the analysed execution takes noticeably longer than the ordinary execution for the same statement. Repeat each query several times if you need a decision, and account for cache state and background load. Do not subtract one pair of noisy timings and present the result as a universal per-row cost.

This is a temporary database session and the statement is read-only. Exit with \q; there is no database change to undo:

YOUR_DATABASE=> \q

5. Investigate a poor result without changing the clock

A large per-loop value or a histogram with many calls taking several microseconds can make timing-heavy plans less representative. First rerun the test after reducing host load and record whether the result is stable. Check the machine's current clock-source information without writing to it:

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

The files may be unavailable in a container or on a non-Linux system. That is an environment limitation, not a reason to invent a value. If the first command lists several sources, the second shows the active one.

Changing current_clocksource is a privileged, system-wide operation and can affect timing correctness. Do not run an example such as echo ... > /sys/devices/system/clocksource/clocksource0/current_clocksource on a production host. The PostgreSQL manual warns that a slower or unsuitable source can inflate EXPLAIN ANALYZE results, while a supposedly faster source can be unreliable on particular hardware or virtual machines. Investigate vendor and kernel guidance, schedule a maintenance window and define a recovery path before considering any clock-source change. Usually the safe action here is to document the measurement and leave the operating-system default alone.

6. Handle common failures

If the shell says the command is missing, locate the package-owned binary and run it by its 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. You may be invoking a different PostgreSQL major version than the one documented here. Keep the duration as a positive, practical number and do not pass a shell expression where the program expects a number.

If repeated tests disagree, stop changing settings and capture the conditions first: CPU load, host type, active clock source and PostgreSQL version. A single run cannot distinguish timer overhead from scheduling noise. Elevated privileges are not normally required for pg_test_timing or the read-only clock-source checks; use sudo only if your host's permissions explicitly require it, and do not use it to write a clock-source setting during this diagnostic.