UUID vs BIGINT speed comparison in PostgreSQL

Percentage result

PG 14.6

select version();

PostgreSQL 14.6 (Debian 14.6-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

PG 14.6 UUID: 49206.630 ms (100%)

explain analyse
    select
        gen_random_uuid()
    from
        generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=16) (actual time=6071.724..48614.724 rows=25000000 loops=1)
Planning Time: 0.026 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.306 ms, Inlining 0.000 ms, Optimization 0.132 ms, Emission 1.315 ms, Total 1.753 ms"
Execution Time: 49206.630 ms

PG 14.6 UUID v1 with uuid-ossp: 145914.053 ms (296%)

create extension if not exists "uuid-ossp";

explain analyse
    select
        uuid_generate_v1()
    from
        generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=16) (actual time=2879.300..145209.272 rows=25000000 loops=1)
Planning Time: 0.023 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.292 ms, Inlining 0.000 ms, Optimization 0.109 ms, Emission 1.243 ms, Total 1.644 ms"
Execution Time: 145914.053 ms

PG 14.6 UUID v4 with uuid-ossp: 104594.026 ms (212%)

create extension if not exists "uuid-ossp";

explain analyse
    select
        uuid_generate_v4()
    from
        generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=16) (actual time=4414.126..103999.992 rows=25000000 loops=1)
Planning Time: 0.019 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.286 ms, Inlining 0.000 ms, Optimization 0.082 ms, Emission 0.908 ms, Total 1.276 ms"
Execution Time: 104594.026 ms

PG 14.6 BIGINT: 14186.833 ms (28%)

drop sequence if exists demo;

create sequence demo as bigint;

explain analyse
select
    nextval('demo')
from
    generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=8) (actual time=4573.023..13597.980 rows=25000000 loops=1)
Planning Time: 0.036 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.369 ms, Inlining 0.000 ms, Optimization 0.126 ms, Emission 1.352 ms, Total 1.846 ms"
Execution Time: 14186.833 ms

PG 15.1

select version();

PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

PG 15.1 UUID: 44215.709 ms (89%)

explain analyse
select
    gen_random_uuid()
from
    generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=16) (actual time=1597.800..43626.773 rows=25000000 loops=1)
Planning Time: 0.035 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.380 ms, Inlining 0.000 ms, Optimization 0.151 ms, Emission 1.685 ms, Total 2.216 ms"
Execution Time: 44215.709 ms

PG 15.1 UUID v1 with uuid-ossp: 150920.625 ms (306%)

create extension if not exists "uuid-ossp";

explain analyse
select
    uuid_generate_v1()
from
    generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=16) (actual time=6338.731..150149.723 rows=25000000 loops=1)
Planning Time: 0.018 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.174 ms, Inlining 0.000 ms, Optimization 0.069 ms, Emission 0.928 ms, Total 1.171 ms"
Execution Time: 150920.625 ms

PG 15.1 UUID v4 with uuid-ossp: 102157.233 ms (207%)

create extension if not exists "uuid-ossp";

explain analyse
select
    uuid_generate_v4()
from
    generate_series(1, 25000000);

Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=16) (actual time=1598.845..101564.754 rows=25000000 loops=1)
Planning Time: 0.028 ms
JIT:
  Functions: 3
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.276 ms, Inlining 0.000 ms, Optimization 0.114 ms, Emission 1.050 ms, Total 1.440 ms"
Execution Time: 102157.233 ms

PG 15.1 BIGINT: 10010.054 ms (20%)

drop sequence if exists demo;

create sequence demo as bigint;

explain analyse
select
    nextval('demo')
from
    generate_series(1, 25000000);

 Function Scan on generate_series  (cost=0.00..312500.00 rows=25000000 width=8) (actual time=1562.800..9332.415 rows=25000000 loops=1)
 Planning Time: 0.122 ms
 JIT:
   Functions: 3
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.176 ms, Inlining 0.000 ms, Optimization 0.108 ms, Emission 2.954 ms, Total 3.237 ms
 Execution Time: 10010.054 ms