os: centos 7.6
db: postgresql 10

版本

# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core) 
# 
# yum list installed |grep -i postgres
postgresql10.x86_64                10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-contrib.x86_64        10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-devel.x86_64          10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-docs.x86_64           10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-libs.x86_64           10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg10  
postgresql10-plperl.x86_64         10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-plpython.x86_64       10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-plpython3.x86_64      10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-pltcl.x86_64          10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-server.x86_64         10.18-1PGDG.rhel7                   @pgdg10  
postgresql10-tcl.x86_64            2.7.5-1.rhel7                       @pgdg10  
postgresql10-test.x86_64           10.18-1PGDG.rhel7                   @pgdg10# su - postgres
Last login: Mon Jan 17 22:25:57 CST 2022 on pts/1-bash-4.2$ 
-bash-4.2$ psql
psql (10.18)
Type "help" for help.postgres=#

测试

postgres=# create table tmp_t0(c0 bigint primary key ,c1 varchar(100) ,c2 varchar(100) ,c3 varchar(100)
)
;postgres=# insert into tmp_t0
select id,md5(id::varchar),case when id=1 then md5(id::varchar) else null end,case when id=1 then md5(id::varchar) else null endfrom generate_series(1,1000000) as id;postgres=# select * from tmp_t0 limit 10;c0 |                c1                |                c2                |                c3                
----+----------------------------------+----------------------------------+----------------------------------1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b2 | c81e728d9d4c2f636f067f89cc14862c |                                  | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |                                  | 4 | a87ff679a2f3e71d9181a67b7542122c |                                  | 5 | e4da3b7fbbce2345d7772b0674a318d5 |                                  | 6 | 1679091c5a880faf6fb5e6087eb1b2dc |                                  | 7 | 8f14e45fceea167a5a36dedd4bea2543 |                                  | 8 | c9f0f895fb98ab9159f51fd0297e236d |                                  | 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |                                  | 10 | d3d9446802a44259755d38e6d163e820 |                                  | 
(10 rows)

创建索引,部分索引

postgres=# create index concurrently idx_tmp_t0_c1 on tmp_t0(c1);create index concurrently idx_tmp_t0_c2 on tmp_t0(c2);create index concurrently idx_tmp_t0_c3 on tmp_t0(c3) where c3 is not null ;postgres=# select pg_size_pretty(pg_relation_size('tmp_t0')) as tab_size,pg_size_pretty(pg_relation_size('idx_tmp_t0_c1')) as c1_size,pg_size_pretty(pg_relation_size('idx_tmp_t0_c2')) as c2_size,pg_size_pretty(pg_relation_size('idx_tmp_t0_c3')) as c3_size  ;tab_size | c1_size | c2_size | c3_size 
----------+---------+---------+---------73 MB    | 56 MB   | 21 MB   | 16 kB
(1 row)			  

为什么 idx_tmp_t0_c2 那么大？

参考：