上一篇blog讨论了PostgreSQL9.1的新特性之一, 即将字段类型由 varchar 转换成 text 类型时不再需要重写表了, 原文地址 https://postgres.fun/20110916165650.html ,但是有种情况例外，当这个字段被索引时，索引需要重写，下面是测试过程。
环境准备
创建测试表
1
2
3
4
5
6
7
8
|
[postgres@pgb 16386]$ psql mydb mydb
psql (9.1.0)
Type "help" for help.
mydb=> create table test_13 ;
CREATE TABLE
mydb=> insert into test_13 select generate_series(1,1000000),'aaa';
INSERT 0 1000000
|
创建索引并分析表
1
2
3
4
5
|
mydb=> create index idx_test_13_name on test_13 using btree (name);
CREATE INDEX
mydb=> analyze test_13;
ANALYZE
|
查询表，索引统计信息
1
2
3
4
5
6
7
8
9
10
11
|
mydb=> select relname,relowner,relfilenode,relpages,reltuples from pg_class where relname='test_13';
relname | relowner | relfilenode | relpages | reltuples
---------+----------+-------------+----------+-----------
test_13 | 16384 | 32793 | 4425 | 1e+06
(1 row)
mydb=> select relname,relowner,relfilenode,relpages,reltuples from pg_class where relname='idx_test_13_name';
relname | relowner | relfilenode | relpages | reltuples
------------------+----------+-------------+----------+-----------
idx_test_13_name | 16384 | 32796 | 2198 | 1e+06
(1 row)
|
修改字段类型 ( varchar –> text )
1
2
3
4
5
6
|
mydb=> /timing
Timing is on.
mydb=> alter table test_13 alter column name type text;
ALTER TABLE
Time: 9192.565 ms
|
备注：表上有100万数据，将字段类型由 varchar 修改成 text 类型时，花费了 9 秒，猜测重写了表，接着往下看。
1
2
3
4
5
6
|
mydb=> /d test_13
Table "mydb.test_13"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
|
再次查看表，索引信息
1
2
3
4
5
6
7
8
9
10
11
|
mydb=> select relname,relowner,relfilenode,relpages,reltuples from pg_class where relname='test_13';
relname | relowner | relfilenode | relpages | reltuples
---------+----------+-------------+----------+-----------
test_13 | 16384 | 32793 | 4425 | 1e+06
(1 row)
mydb=> select relname,relowner,relfilenode,relpages,reltuples from pg_class where relname='idx_test_13_name';
relname | relowner | relfilenode | relpages | reltuples
------------------+----------+-------------+----------+-----------
idx_test_13_name | 16384 | 32797 | 2198 | 1e+06
(1 row)
|
备注：索引的 relfilenode 发生了变化，由原来的 32796 变成了 32797, 说明索引被重写; 而表的 relfilenode 没有变化，依然是 32793 , 说明表数据没有被重写。接下来做进一步分析，看看将 text 类型字段转换成 varchar 会是什么情况。
修改字段类型 ( text –> varchar )
1
2
3
|
mydb=> alter table test_13 alter name type varchar(32);
ALTER TABLE
Time: 13660.555 ms
|
备注， 将字段类型由 text 改成成 varchar 时，花费了 13 秒左右，说明重写了表。
再次查看表，索引信息
1
2
3
4
5
6
7
8
9
10
11
|
mydb=> select relname,relowner,relfilenode,relpages,reltuples from pg_class where relname='test_13';
relname | relowner | relfilenode | relpages | reltuples
---------+----------+-------------+----------+-----------
test_13 | 16384 | 32802 | 4425 | 1e+06
(1 row)
mydb=> select relname,relowner,relfilenode,relpages,reltuples from pg_class where relname='idx_test_13_name';
relname | relowner | relfilenode | relpages | reltuples
------------------+----------+-------------+----------+-----------
idx_test_13_name | 16384 | 32805 | 2198 | 1e+06
(1 row)
|
备注：表的 relfilenode 发生了变化，由原来的 32793 变化成 32802, 说明表被重写了。索引的 relfilenode 也发生了变化，由原来的 32797 变化成了 32805, 说明也被重写了。
总结
- 当类型为 varchar 的字段上没有索引时，将其转换成 text 时，不需要重写表和索引。
- 当类型为 varchar 的字段上有 btree 索引时, 不需要重写表数据，但需要重写索引。
- 其它索引类型没有测试，有兴趣的朋友可以测试下，推测是和第2点情况一样。
- 当类型为 text 的字段转换成 varchar 类型时，表和索引需要重写。
原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/236432.html
