Re: Use CLOCK_MONOTONIC_COARSE for instr_time when available

This reflects the correct insertion of 100 million rows instead of 10
million.

-- Create table and insert 100 million rows

CREATE TABLE t1(a int);

INSERT INTO t1 SELECT * FROM generate_series(1, 100000000);

-- close parallel

SET max_parallel_workers_per_gather = 0;

SET max_parallel_workers = 0;

-- Run the query and check execution time

EXPLAIN ANALYZE SELECT COUNT(*) FROM t1;

杨江华 <yjhjstz(at)gmail(dot)com> 于2025年3月26日周三 11:14写道：

> Dear PostgreSQL Hackers,
>
> This patch modifies the instr_time.h header to prefer
> CLOCK_MONOTONIC_COARSE when available. By using CLOCK_MONOTONIC_COARSE,
> we can leverage a lower resolution(4ms) but faster alternative for timing
> operations, which reduces the overhead of frequent timestamp retrievals.
> This change is expected to provide performance improvements, especially in
> scenarios with frequent timing operations.
>
>
> *Key Changes:*
>
> • *CLOCK_MONOTONIC_COARSE* is used when available, offering faster
> performance with slightly reduced precision.
>
> • For macOS, *CLOCK_MONOTONIC_RAW* remains the preferred choice due to
> its higher resolution.
>
> • *CLOCK_MONOTONIC* is used as a fallback when neither of the above
> options is available.
>
>
> *Performance Improvements:*
>
>
> In testing with a workload that performs a COUNT(*) operation on a table
> containing 100 million rows, we observed a noticeable performance
> improvement after applying this patch.
>
>
> *SQL to Reproduce:*
>
> -- Create table and insert 10 million rows
> CREATE TABLE t1(a int);
> INSERT INTO t1
> SELECT * FROM generate_series(1, 10000000);
>
> -- Close parallel
> SET max_parallel_workers_per_gather = 0;
> SET max_parallel_workers = 0;
>
> -- Run the query and check execution time
> EXPLAIN ANALYZE SELECT COUNT(*) FROM t1;
>
> SELECT COUNT(*) FROM t1;
>
> *Before the Patch:*
>
> • *EXPLAIN ANALYZE Execution Time*: 4914 ms
>
> • *Perf Results*:
>
> • 33.97% of time spent in [vdso] __vdso_clock_gettime
>
> • 5.28% in heapgettup_pagemode
>
> • 4.44% in InstrStopNode
>
>
> *After the Patch:*
>
> • *EXPLAIN ANALYZE Execution Time*: 3114 ms (down from 4914 ms)
>
> • *Perf Results*:
>
> • 12.45% of time spent in ExecInterpExpr
>
> • 9.18% in [vdso] __vdso_clock_gettime
>
> • 6.92% in ExecScan
>
> • Reduced usage of clock_gettime, leading to more efficient execution.
>
>
> The execution time of EXPLAIN ANALYZE SELECT COUNT(*) FROM t1; after the
> patch is much closer to the actual time of SELECT COUNT(*) FROM t1;,
> which means the overhead of timing operations has been significantly
> reduced.
>
>
> This change provides around a *20-30%* reduction in execution time for
> the tested query.
>
>
> *Patch Details:*
>
> From 91d61b8c9a60f0e19b73e03c1a0e46d2dc64573d Mon Sep 17 00:00:00 2001
> From: Jianghua Yang <yjhjstz(at)gmail(dot)com>
> Date: Thu, 27 Mar 2025 01:58:58 +0800
> Subject: [PATCH] Use CLOCK_MONOTONIC_COARSE for instr_time when available
>
> This patch modifies `instr_time.h` to prefer `CLOCK_MONOTONIC_COARSE`
> when available. `CLOCK_MONOTONIC_COARSE` provides a lower resolution
> but faster alternative for timing operations, which can reduce the
> overhead of frequent timestamp retrievals.
>
> On macOS, `CLOCK_MONOTONIC_RAW` remains the preferred choice when
> available, as it provides high-resolution timestamps. Otherwise,
> `CLOCK_MONOTONIC` is used as a fallback.
>
> Author: Jianghua Yang
> ---
> src/include/portability/instr_time.h | 4 +++-
> 1 file changed, 3 insertions(+), 1 deletion(-)
>
> I believe this change will result in better performance for many
> PostgreSQL users, especially those with high-frequency timing operations. I
> look forward to your feedback on this patch.
>
>
> Best regards,
>
> Jianghua Yang
>
>
>