• PostgreSQL主键索引膨胀的重建方法


    普通的索引膨胀处理比较简单,主键的索引膨胀也不复杂,只是在新旧索引交替时有一些小处理。本试验在primary key上通过CONCURRENTLY建立第二索引来解决索引膨胀问题,适用9.3、9.4,其他版本使用前请实际测试。

    创建测试表

                                        Table "swrd.mytbl"
     Column |            Type             |                     Modifiers
    --------+-----------------------------+----------------------------------------------------
     id     | integer                     | not null default nextval('mytbl_id_seq'::regclass)
     val    | timestamp without time zone | default now()
    Indexes:
        "mytbl_pkey" PRIMARY KEY, btree (id)
    

    生成测试数据

    生成测试数据步骤略,这里为了清楚看到测试的情况,生成10000000条。

    swrd=# SELECT COUNT(*) FROM mytbl;
      count
    ----------
     10000000
    (1 row)
    

    创建第二索引

    在id上创建第二索引,记得使用CONCURRENTLY参数

    swrd=# CREATE UNIQUE INDEX CONCURRENTLY ON mytbl USING btree(id);
    CREATE INDEX
    

    可以看到id字段上同时有两个索引mytbl_pkey和mytbl_id_idx

    swrd=# SELECT schemaname,relname,indexrelname,pg_relation_size(indexrelid) AS index_size,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname IN (SELECT indexname FROM pg_indexes WHERE schemaname = 'swrd' AND tablename = 'mytbl');
     schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch
    ------------+---------+--------------+------------+----------+--------------+---------------
     swrd       | mytbl   | mytbl_pkey   |  224632832 |        0 |            0 |             0
     swrd       | mytbl   | mytbl_id_idx |  224641024 |        0 |            0 |             0
    (2 rows)
    

    替换索引

    开启事务删除主键索引同时将第二索引更新为主键的约束

    swrd=# begin;
    BEGIN
    swrd=# ALTER TABLE mytbl DROP CONSTRAINT mytbl_pkey;
    ALTER TABLE
    swrd=# ALTER TABLE mytbl ADD CONSTRAINT mytbl_id_idx PRIMARY KEY USING INDEX mytbl_id_idx;
    ALTER TABLE
    swrd=# END;
    COMMIT
    

    检查测试表的索引,可见现在只有第二索引了

    swrd=# SELECT schemaname,relname,indexrelname,pg_relation_size(indexrelid) AS index_size,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname IN (SELECT indexname FROM pg_indexes WHERE schemaname = 'swrd' AND tablename = 'mytbl');
     schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch
    ------------+---------+--------------+------------+----------+--------------+---------------
     swrd       | mytbl   | mytbl_id_idx |  224641024 |        0 |            0 |             0
    (1 row)
    

    检查表的定义

    检查表定义,可以看到与最初建表时是一样的

    swrd=# d+ mytbl
                                                            Table "swrd.mytbl"
     Column |            Type             |                     Modifiers                      | Storage | Stats target | Description
    --------+-----------------------------+----------------------------------------------------+---------+--------------+-------------
     id     | integer                     | not null default nextval('mytbl_id_seq'::regclass) | plain   |              |
     val    | timestamp without time zone | default now()                                      | plain   |              |
    Indexes:
        "mytbl_id_idx" PRIMARY KEY, btree (id)
    

    参考:PostgreSQL主键索引膨胀的重建方法

  • 相关阅读:
    linux 时间同步
    sublime3 install python3
    Postfix的工作原理
    MySQL启动报错
    Socket server
    自定义静态网卡配置
    Nginx 404 500
    Pymysql
    Gitlab smtp 设置
    Windows 文件自动同步共享工具
  • 原文地址:https://www.cnblogs.com/xiaotengyi/p/6365952.html
Copyright © 2020-2023  润新知