postgresql 数据库 sequence 的 cache 1、 cache 100、 cache 1000、cache 10000 的对比

迈不过友情╰ 2022-12-13 01:38 290阅读 0赞

os: centos 7.8.2003  
db: postgresql 13.0

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.8.2003 (Core)
    # 
    # su - postgres
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# create sequence #

$ psql
    psql (13.0)
    Type "help" for help.
    
    postgres=# \c peiybdb
    
    peiybdb=# CREATE SEQUENCE public.seq_a1
    	INCREMENT BY 1
    	MINVALUE 1
    	MAXVALUE 9223372036854775807
    	START 1
    	CACHE 1
    	NO CYCLE;
    
    peiybdb=# CREATE SEQUENCE public.seq_a100
    	INCREMENT BY 1
    	MINVALUE 1
    	MAXVALUE 9223372036854775807
    	START 1
    	CACHE 100
    	NO CYCLE;
    	
    peiybdb=# CREATE SEQUENCE public.seq_a1000
    	INCREMENT BY 1
    	MINVALUE 1
    	MAXVALUE 9223372036854775807
    	START 1
    	CACHE 1000
    	NO CYCLE;
    	
    peiybdb=# CREATE SEQUENCE public.seq_a10000
    	INCREMENT BY 1
    	MINVALUE 1
    	MAXVALUE 9223372036854775807
    	START 1
    	CACHE 10000
    	NO CYCLE;

peiybdb=# select * from pg_sequence;
     seqrelid | seqtypid | seqstart | seqincrement |       seqmax        | seqmin | seqcache | seqcycle 
    ----------+----------+----------+--------------+---------------------+--------+----------+----------
        32787 |       20 |        1 |            1 | 9223372036854775807 |      1 |        1 | f
        32789 |       20 |        1 |            1 | 9223372036854775807 |      1 |      100 | f
        32791 |       20 |        1 |            1 | 9223372036854775807 |      1 |     1000 | f
        32793 |       20 |        1 |            1 | 9223372036854775807 |      1 |    10000 | f
    (4 rows)
    
    peiybdb=# select * from pg_sequences;
     schemaname | sequencename | sequenceowner | data_type | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
    ------------+--------------+---------------+-----------+-------------+-----------+---------------------+--------------+-------+------------+------------
     public     | seq_a1       | postgres      | bigint    |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
     public     | seq_a100     | postgres      | bigint    |           1 |         1 | 9223372036854775807 |            1 | f     |        100 |           
     public     | seq_a1000    | postgres      | bigint    |           1 |         1 | 9223372036854775807 |            1 | f     |       1000 |           
     public     | seq_a10000   | postgres      | bigint    |           1 |         1 | 9223372036854775807 |            1 | f     |      10000 |           
    (4 rows)

# 对比 #

peiybdb=# insert into tmp_t0 select nextval('seq_a1') from generate_series(1,1000000);
    INSERT 0 1000000
    Time: 3131.510 ms (00:03.132)
    peiybdb=# 
    peiybdb=# 
    peiybdb=# insert into tmp_t0 select nextval('seq_a100') from generate_series(1,1000000);
    INSERT 0 1000000
    Time: 2851.756 ms (00:02.852)
    peiybdb=# 
    peiybdb=# 
    peiybdb=# insert into tmp_t0 select nextval('seq_a1000') from generate_series(1,1000000);
    INSERT 0 1000000
    Time: 3545.061 ms (00:03.545)
    peiybdb=# 
    peiybdb=# 
    peiybdb=# insert into tmp_t0 select nextval('seq_a10000') from generate_series(1,1000000);
    INSERT 0 1000000
    Time: 3976.891 ms (00:03.977)

看上去 cache 100 的时间最短，cache大了后反而慢了。