Re: Use CLOCK_MONOTONIC_COARSE for instr_time when available

*I agree, so this patch only affects explain analyze.*

*1. This change to CLOCK_MONOTONIC_COARSE only affects EXPLAIN ANALYZE and
does not impact other modules.*

The patch introduces optional support for CLOCK_MONOTONIC_COARSE specifically
within the INSTR_TIMEinstrumentation framework. The modifications are
guarded by the compile-time macro USE_CLOCK_MONOTONIC_COARSE, and are only
used when gathering timing data for performance instrumentation. Given that
INSTR_TIME is mainly used in EXPLAIN ANALYZE, and there are no changes to
runtime or planner logic, this patch ensures that only diagnostic outputs
are affected—leaving core execution paths and other modules untouched.

------------------------------

*2. With this modification, EXPLAIN ANALYZE produces timing results that
are closer to real-world wall-clock time, making performance debugging more
accurate.*

By using CLOCK_MONOTONIC_COARSE, which has lower overhead compared to
CLOCK_MONOTONIC, the patch improves the efficiency of timing
collection in EXPLAIN
ANALYZE. While it may slightly reduce precision, the resulting measurements
more closely reflect actual elapsed time observed by users, especially in
performance-sensitive environments. This makes EXPLAIN ANALYZE outputs more
reliable and helpful for developers diagnosing query performance
bottlenecks.

--- origin version

explain analyze select count(*) from t1;
Thu 27 Mar 2025 01:31:20 AM CST
(every 1s)

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1852876.63..1852876.64 rows=1 width=8) (actual
time=4914.037..4914.038 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..1570796.90 rows=112831890 width=0)
(actual time=0.039..3072.303 rows=100000000 loops=1)
Planning Time: 0.132 ms
Execution Time: 4914.072 ms
(4 rows)

Time: 4914.676 ms (00:04.915)

--- apply patch

postgres=# explain analyze select count(*) from t1;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1692478.40..1692478.41 rows=1 width=8) (actual
time=3116.164..3116.164 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..1442478.32 rows=100000032 width=0)
(actual time=0.000..2416.127 rows=100000000 loops=1)
Planning Time: 0.000 ms
Execution Time: 3116.164 ms
(4 rows)

Time: 3114.059 ms (00:03.114)
postgres=# select count(*) from t1;
count
-----------
100000000
(1 row)

Time: 2086.130 ms (00:02.086)

Andres Freund <andres(at)anarazel(dot)de> 于2025年3月27日周四 07:19写道：

> Hi,
>
> On 2025-03-26 23:09:42 -0400, Tom Lane wrote:
> > =?UTF-8?B?5p2o5rGf5Y2O?= <yjhjstz(at)gmail(dot)com> writes:
> > > This patch modifies the instr_time.h header to prefer
> CLOCK_MONOTONIC_COARSE
> > > when available.
> >
> > As far as I know, our usage of instr_time really needs the highest
> > resolution available, because we are usually trying to measure pretty
> > short intervals. You say that this patch reduces execution time,
> > and I imagine that's true ... but I wonder if it doesn't do so at
> > the cost of totally destroying the reliability of the output numbers.
>
> The reason, on x86, the timestamp querying has a somewhat high overhead is
> that the "accurate" "read the tsc" instruction serves as a barrier for
> out-of-order execution. With modern highly out-of-order execution that
> means
> we'll wait for all scheduled instructions to finish before determining the
> current time, multiple times for each tuple. That of course slows things
> down
> substantially.
>
> There's a patch to use the version of rdtsc that does *not* have barrier
> semantics:
>
> https://postgr.es/m/CAP53PkzO2KpscD-tgFW_V-4WS%2BvkniH4-B00eM-e0bsBF-xUxg%40mail.gmail.com
>
> Greetings,
>
> Andres Freund
>