Re: Use CLOCK_MONOTONIC_COARSE for instr_time when available - Mailing list pgsql-hackers

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@gmail.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