PostgreSql 重建索引的操作

PostgreSql数据库的重建索引时通过REINDEX命令来实现的，如reindexindex_name；

其语法是：

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ];

下面解释下说明情况下需要：

1、当由于软件bug或者硬件原因导致的索引不再可用，索引的数据不再可用；

2、当索引包含许多空的或者近似于空的页，这个在b-tree索引会发生。Reindex会腾出空间释放哪些无用的页(页就是存放数据的一个单位，类似于block)。

3、PostgreSql数据库系统修改了存储参数，需要重建不然就会失效(如修改了fillfactor参数)；

4、创建并发索引时失败，遗留了一个失效的索引。这样的索引不会被使用，但重构后能用。一个索引的重构不能并发的执行。

下面介绍下重构索引命令的参数：

1、INDEX 重构指定的索引；

2、TABLE 重构指定表的所有索引，包括下级TOAST表；

3、DATABASE重构指定数据库的所有索引，系统共享索引也会被执行。需要注意的是这个级别的重构不能再一个事务块中执行。

4、SYSTEM 重构这个系统的索引包含当前的数据库。共享系统中的索引页是被包含的，但是用户自己的表是不处理的，同样也不能在一个事务块中执行。

5、Name 按照不同级别索引的名称。

6、FORCE 已经被废除即使写了也是被忽略的。

示例：

REINDEX INDEX my_index;
REINDEX TABLE my_table;
REINDEX DATABASE broken_db;

另外需要注意的是：

1、重建索引不同的级别的重构需要不同的权限，比如table那么就需要有这个表的权限即需要有操作索引的权限，如超级用户postgres拥有这个权限。

2、重构索引的目的是为了当索引的数据不可信时，即对于成本的计算会出现偏差较大，无益于优化器得到最优的执行计划以至于性能优化失败。

3、重构索引类似于先删除所有再创建一个索引，但是索引的条目是重新开始的。重构时当前索引是不能写的，因为此时有排他锁。

4、在8,1版本之前REINDEX DATABASE 只包含系统索引，并不是期望的所有指定数据库的索引。7.4版本之前REINDEX TABLE不会自动执行下级TOAST tables。

关于TOAST tables的含义：

TOAST直接翻译的话就是切片面包(slicedbread)的意思，全称是The Oversized-Attribute Storage Technique，

为什么会有OVERSIZED-ATTRIBUTE呢？原因很简单，因为在PostgreSQL,一条记录不能跨PAGE存储，

跨越PAGE的话必须使用TOAST（即unaligned,与原表分开存储）存储。

TOAST表不能独立创建，只有当普通表包含了main,extended或external存储格式的字段时，系统会自动创建一个和普通表关联的TOAST表。

当一条记录(tuple)在存储时(如果压缩的话算压缩后的大小)大于TOAST_TUPLE_THRESHOLD(通常是2kB)这个值时，会存储到TOAST表。

而此时在普通表的该字段处包含了一个指向TOAST的tableoid和chunk_id的数据，从而能够找到该字段的记录。

补充：pg删除索引遇到的坑

正常我们在删除索引的时候会报如下错误，

然后只需要设置索引路径就ok

set search_path=bi_dpa;

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • Postgresql 查询表引用或被引用的外键操作

    Postgresql 查询表引用或被引用的外键操作

    今天更新两个SQL.是用来查询PG中,主表被子表引用的外键,或子表引用了哪个主表的主键. 废话不多说,直接上实验! CentOS 7 + PG 10 创建两个实验表,test01为主表,test02为子表,test02引用test01中的id列. test=# create table test01( test(# id int primary key, test(# col1 varchar(20) test(# ); CREATE TABLE test=# create table test

  • postgresql 索引之 hash的使用详解

    postgresql 索引之 hash的使用详解

    os: ubuntu 16.04 postgresql: 9.6.8 ip 规划 192.168.56.102 node2 postgresql help create index postgres=# \h create index Command: CREATE INDEX Description: define a new index Syntax: CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON

  • PostgreSQL之INDEX 索引详解

    之前总结了PostgreSQL的序列相关知识,今天总结下索引. 我们都知道,数据库索引最主要的作用是可以提高检索数据的速度,但是索引也不是越多越好.因为索引会增加数据库的存储空间,查询数据是要花较多的时间. 1.创建索引 SQL语句如下: CREATE INDEX idx_commodity ON commodity //表名 USING btree //用B树实现 (commodity_id); //作用的具体列 2.删除索引 DROP index idx_commodity; 3.增加索引的

  • PostgreSQL Sequence序列的使用详解

    PostgreSQL是一种关系型数据库,和Oracle.MySQL一样被广泛使用.平时工作主要使用的是PostgreSQL,所以有必要对其相关知识做一下总结和掌握,先总结下序列. 一. Sequence序列 Sequence是一种自动增加的数字序列,一般作为行或者表的唯一标识,用作代理主键. 1.Sequence的创建 例子:创建一个seq_commodity,最小值为1,最大值为9223372036854775807,从1开始,增量的步长为1,缓存为1的循环排序Sequence. SQL语句如

  • PostgreSql 的hash_code函数的用法说明

    PostgreSql 的hash_code函数的用法说明

    PostgreSql 实现的hash_code 函数与java hash_code方法一致 CREATE FUNCTION hash_code(text) RETURNS integer LANGUAGE plpgsql AS $$ DECLARE i integer := 0; DECLARE h bigint := 0; BEGIN FOR i IN 1..length($1) LOOP h = (h * 31 + ascii(substring($1, i, 1))) & 42949672

  • PostgreSql 重建索引的操作

    PostgreSql 重建索引的操作

    PostgreSql数据库的重建索引时通过REINDEX命令来实现的,如reindexindex_name: 其语法是: REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]; 下面解释下说明情况下需要: 1.当由于软件bug或者硬件原因导致的索引不再可用,索引的数据不再可用: 2.当索引包含许多空的或者近似于空的页,这个在b-tree索引会发生.Reindex会腾出空间释放哪些无用的页(页就是存放数据的一个单位,类似于bloc

  • Oracle关于重建索引争论的总结

    索引重建是一个争论不休被不断热烈讨论的议题.当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20% 这2种情形下需要重建索引.近来Oracle也提出了一些与之相反的观点,就是强烈建议不要定期重建索引.本文是参考了1525787.1并进行相应描述. 1.重建索引的理由 a.Oracle的B树索引随着时间的推移变得不平衡(误解)     b.索引碎片在不断增加     c.索引不断增

  • SQLServer2005重建索引前后对比分析

    在做维护项目的时,我们经常会遇到索引维护的问题,通过语句,我们就可以判断某个表的索引是否需要重建. 执行一下语句:先分析表的索引 分析表的索引建立情况:DBCC showcontig('Table') DBCC SHOWCONTIG 正在扫描 'Table'' 表... 表: 'Table'' (53575229):索引 ID: 1,数据库 ID: 14 已执行 TABLE 级别的扫描. - 扫描页数................................: 228 - 扫描区数....

  • Oracle重建索引Shell脚本、SQL脚本分享

    索引是提高数据库查询性能的有力武器.没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难.然而索引在使用的过程中,尤其是在批量的DML的情形下会产生相应的碎片,以及B树高度会发生相应变化,因此可以对这些变化较大的索引进行重构以提高性能.N久以前Oracle建议我们定期重建那些高度为4,已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引.但Oracle现在强烈建议不要定期重建索引.具体可以参考文章:Oracle 重建索引的必要性.尽管如此重建索引还是有必要的,只是不

  • SQL SEVER数据库重建索引的方法

    一.查询思路 1.想要判断数据库查询缓慢的问题,可以使用如下语句,可以列出查询语句的平均时间,总时间,所用的CPU时间等信息 SELECT creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logic

  • 分析Mysql表读写、索引等操作的sql语句效率优化问题

    上次我们说到mysql的一些sql查询方面的优化,包括查看explain执行计划,分析索引等等.今天我们分享一些 分析mysql表读写.索引等等操作的sql语句. 闲话不多说,直接上代码: 反映表的读写压力 SELECT file_name AS file, count_read, sum_number_of_bytes_read AS total_read, count_write, sum_number_of_bytes_write AS total_written, (sum_number

  • django修改models重建数据库的操作

    第二次修改models.py以后再次 python manage.py makemigrations 提示如下 You are trying to add a non-nullable field 'address' to xc_users without a default; we can't do that (the database needs something to populate existing rows). Please select a fix: 1) Provide a o

  • PostgreSQL的upsert实例操作(insert on conflict do)

    PostgreSQL的upsert实例操作(insert on conflict do)

    建表语句: DROP TABLE IF EXISTS "goods"; CREATE TABLE "goods" ( "store_cd" int4 NOT NULL, "good_cd" varchar(50) COLLATE "pg_catalog"."default" NOT NULL, "name" varchar(255) COLLATE "pg_

  • SQL SERVER数据库重建索引的方法

    一.查询思路 1.想要判断数据库查询缓慢的问题,可以使用如下语句,可以列出查询语句的平均时间,总时间,所用的CPU时间等信息 SELECT creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logic

  • ElasticSearch学习之Es索引Api操作

    目录 Es索引Api操作 创建索引 索引别名 添加索引别名 查询索引别名 删除索引别名 mapping settings 查询索引 查询当前集群全部索引 查询单个索引 修改索引 删除索引 结束语 Es索引Api操作 在操作之前,先给大家简单的说下索引,在es中,默认所有数据都会建立索引,我们可以类比它是数据库中的库,这里需要提示的是所有的索引index都必须要小写. 创建索引 语法: PUT /${idnexName} 运行: PUT /test 输出: { "acknowledged"

随机推荐