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

最新推荐文章于 2023-08-07 00:54:45 发布

数据库人生

最新推荐文章于 2023-08-07 00:54:45 发布

阅读量1.6k

点赞数 1

本文链接：https://blog.csdn.net/ctypyb2002/article/details/108974788

版权

postgresql opti table index 同时被 2 个专栏收录

83 篇文章 3 订阅

订阅专栏

postgresql basic

57 篇文章 5 订阅

订阅专栏

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=# create table tmp_t0(c0 bigint);
peiybdb=# \d+
                                 List of relations
 Schema |    Name    |   Type   |  Owner   | Persistence |    Size    | Description 
--------+------------+----------+----------+-------------+------------+-------------
 public | seq_a1     | sequence | postgres | permanent   | 8192 bytes | 
 public | seq_a100   | sequence | postgres | permanent   | 8192 bytes | 
 public | seq_a1000  | sequence | postgres | permanent   | 8192 bytes | 
 public | seq_a10000 | sequence | postgres | permanent   | 8192 bytes | 
 public | tmp_t0     | table    | postgres | permanent   | 0 bytes    | 
(5 rows)

peiybdb=# \timing

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大了后反而慢了。