postgresql 13 对于重复值的 index 优化

深碍√TFBOYSˉ_ 2023-02-19 05:28 6阅读 0赞

os: centos 7.4.1708  
db: postgresql 13beta1  
postgresql 12.3

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # su - postgres
    Last login: Fri Jun 19 10:46:55 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                      version                                                   
    ------------------------------------------------------------------------------------------------------------
     PostgreSQL 13beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# 创建索引 #

# su - postgres
    $ psql
    
    postgres=# create table tmp_t0(
    id   int8,
    name varchar(100),
    memo varchar(100)
    );
    
    postgres=# insert into tmp_t0 
    select '1',
           md5('1'::varchar),
           md5(id::varchar)	   
      from generate_series(1,1000000) as id;
    
    postgres=# create index idx_tmp_t0_x1 on tmp_t0(name);
    create index idx_tmp_t0_x2 on tmp_t0(memo);
    
    postgres=# select * from tmp_t0 limit 10;
     id |               name               |               memo               
    ----+----------------------------------+----------------------------------
      1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b
      1 | c4ca4238a0b923820dcc509a6f75849b | c81e728d9d4c2f636f067f89cc14862c
      1 | c4ca4238a0b923820dcc509a6f75849b | eccbc87e4b5ce2fe28308fd9f2a7baf3
      1 | c4ca4238a0b923820dcc509a6f75849b | a87ff679a2f3e71d9181a67b7542122c
      1 | c4ca4238a0b923820dcc509a6f75849b | e4da3b7fbbce2345d7772b0674a318d5
      1 | c4ca4238a0b923820dcc509a6f75849b | 1679091c5a880faf6fb5e6087eb1b2dc
      1 | c4ca4238a0b923820dcc509a6f75849b | 8f14e45fceea167a5a36dedd4bea2543
      1 | c4ca4238a0b923820dcc509a6f75849b | c9f0f895fb98ab9159f51fd0297e236d
      1 | c4ca4238a0b923820dcc509a6f75849b | 45c48cce2e2d7fbdea1afc51c7c6ad26
      1 | c4ca4238a0b923820dcc509a6f75849b | d3d9446802a44259755d38e6d163e820
    (10 rows)

idx\_tmp\_t0\_x1 为值重复率很高的索引  
idx\_tmp\_t0\_x2 为值重复率很低的索引

pg12 的两个索引大小

postgres=# select pg_size_pretty(pg_relation_size('public.idx_tmp_t0_x1')),pg_size_pretty(pg_relation_size('public.idx_tmp_t0_x2'));
     pg_size_pretty | pg_size_pretty 
    ----------------+----------------
     56 MB          | 56 MB
    (1 row)

pg13 的两个索引大小

pg12 idx\_tmp\_t0\_x1的大小为 56 MB  
pg13 idx\_tmp\_t0\_x1的大小为 7152 KB

可以看到 pg13 针对有很多重复值的表列创建索引，大小的优化效果还是很明显。

参考: