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; 杨江华 <yjhj...@gmail.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 <yjhj...@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 > > >
