注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.1: Converting a INDEXED varchar column to text still requires rewrite a index  

2011-09-23 10:53:16|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


          上一篇blog讨论了PostgreSQL9.1的新特性之一, 即将字段类型由 varchar 转换成 text 类型时不再
    需要重写表了, 原文地址 http://francs3.blog.163.com/blog/static/40576727201181645650382/,但是
    有种情况例外,当这个字段被索引时,索引需要重写,下面是测试过程。
   

--1 创建测试表
[postgres@pgb 16386]$ psql mydb mydb
psql (9.1.0)
Type "help" for help.

mydb=> create table test_13 (id integer,name varchar(32));
CREATE TABLE

mydb=> insert into test_13 select generate_series(1,1000000),'aaa';
INSERT 0 1000000


--2 创建索引并分析表
mydb=> create index idx_test_13_name on test_13 using btree (name);
CREATE INDEX

mydb=> analyze test_13;
ANALYZE


--3 查询表,索引统计信息
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)

 
--4 修改字段类型 ( varchar --> text )
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 秒,猜测重写了表,
              接着往下看。

mydb=> \d test_13
     Table "mydb.test_13"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 name   | text    |


--5 再次查看表,索引信息
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 会是什么情况。
      
 
--6  修改字段类型 ( text --> varchar )   
mydb=> alter table test_13 alter name type varchar(32);
ALTER TABLE
Time: 13660.555 ms
  
    备注, 将字段类型由 text 改成成 varchar 时,花费了 13 秒左右,说明重写了表。


-7 再次查看表,索引信息
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)
(1 row)

    备注: 表的 relfilenode 发生了变化,由原来的 32793 变化成 32802, 说明表被重写了;
                索引的 relfilenode 也发生了变化,由原来的 32797 变化成了 32805, 说明也被
                重写了。

 
--8 总结
       1 当类型为 varchar 的字段上没有索引时,将其转换成 text 时,不需要重写表和索引;
       2 当类型为 varchar 的字段上有 btree 索引时, 不需要重写表数据,但需要重写索引;
       3 其它索引类型没有测试,有兴趣的朋友可以测试下,推测是和第2点情况一样;
       4 当类型为 text 的字段转换成 varchar 类型时,表和索引需要重写;
     

  评论这张
 
阅读(26045)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016