Message-ID: <73cae1b4-75d0-4f55-b034-a1f9b7be61a2@gmail.com>
Date: Tue, 19 May 2026 15:11:36 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Sequence Access Methods, round two
From: Andrei Lepikhov <lepihov@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>,
 Xuneng Zhou <xunengzhou@gmail.com>, Chao Li <li.evan.chao@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 Kirill Reshke <reshkekirill@gmail.com>, Peter Smith <smithpb2250@gmail.com>
References: <02699E3C-CC8B-423D-B36F-0AAA9370415E@gmail.com>
 <aUpWDj1yr3osMbhO@paquier.xyz>
 <1F653C1D-A28D-4F3B-8B18-98F993F39B04@gmail.com>
 <433EABEB-0BBB-46E6-B2D2-226E9494A6D8@gmail.com>
 <aU4g6YY_zmVKhmub@paquier.xyz>
 <4317C0A5-2210-40C1-93FE-FF618E3BC0D4@gmail.com>
 <aV3v-mFzKmOPoz7g@paquier.xyz>
 <688decf8-1e14-4ebc-869f-94e9ce6b53db@gmail.com>
 <agj5RCIn0e62V1KA@paquier.xyz>
 <8e63b447-9ebb-4873-973b-437b20c49881@gmail.com>
 <agpElslHL2UQ6oxJ@paquier.xyz>
 <e10e3067-9e95-40f0-be84-de59787bf742@gmail.com>
Content-Language: en-US
In-Reply-To: <e10e3067-9e95-40f0-be84-de59787bf742@gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/73cae1b4-75d0-4f55-b034-a1f9b7be61a2%40gmail.com>
Precedence: bulk

On 18/05/2026 20:42, Andrei Lepikhov wrote:
> On 18/05/2026 00:43, Michael Paquier wrote:
> Ok. So let me just leave the idea of avoiding unnecessary cache lookups here.
Here is my benchmark results.

Test script:

DROP SEQUENCE IF EXISTS abc;
CREATE UNLOGGED SEQUENCE abc USING seqlocal;
-- Warm up
SELECT count(nextval('abc')) FROM generate_series(1, 1E6) \watch i=0 c=100
\timing on
\o result.txt
-- Benchmark
SELECT count(nextval('abc')) FROM generate_series(1, 1E6) \watch i=0 c=100

To identify thermal impact I averaged and compared results per 20 iterations in
a pack:

| Window      | Baseline | v29          |
| ----------- | -------- | ------------ |
| iter 1-20   | 236.92   | 235.28       |
| iter 21-40  | 235.77   | 236.17       |
| iter 41-60  | 238.53   | 234.64       |
| iter 61-80  | 236.62   | 234.35       |
| iter 81-100 | 237.13   | 235.24       |

This test doesn't include insertion machinery itself, so no cold cache measures
or sequence lock contention.

pgbench results on the UNLOGGED table insertion shows the following:

| Test                           | Baseline   | v29          | Δ      |
| INSERT pgbench (mean tps)      | 24,997     | 25,195       | +0.79% |

My oldish Intel-based MacBook is probably not sensitive enough to detect the
overhead. So, you can recheck the result using scripts [1] to restore the exact
test.

[1] https://github.com/danolivo/conf/tree/main/2026e-SeqAM

-- 
regards, Andrei Lepikhov,
pgEdge