PostgreSQL具有一组功能丰富的索引功能,还有很多文章解释语法、用法,和价值的指数。在这篇文章中,我将编写基本的和有用的查询的状态数据库索引。人们开发数据库和一段时间后,当有需求要做软件的体系结构的变化,他们忘记做前面的索引的清理。这个方法创建了一个烂摊子,有时减缓因为太多的索引数据库。每当我们做一个更新或插入,该指数将随着更新实际的表,因此有必要进行清理。
有一个wiki页面,有一些相关查询PostgreSQL索引维护。
在写作之前查询我想介绍pg_index目录表。表包含的信息索引。这是基本的目录表,所有基于索引视图使用相同的表。
1 -有时你需要多少索引表。这个查询会显示模式限定表名和其索引名称。
|
1
2
3
4
5
6
7
8
9
10
|
db
=
#选择CONCAT (n.nspname”。”,c.relname)表,
我
.relname
作为
index_name
从
pg
_类
c
加入
pg
_指数
x
在
c
.oid
=
x
.indrelid
加入
pg
_类
我
在
我
.oid
=
x
.indexrelid
左
加入
pg
_名称空间
n
在
n
.oid
=
c
.relnamespace
在哪里
c
.relkind
=
任何
(
数组
(
“r”
,
“t”
]
)
和
c
.relname
就像
“pgbench_accounts”
;
表
|
指数
_的名字
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
公共
.pgbench_accounts
|
pgbench_accounts
_pkey
公共
.pgbench_accounts
|
pgbench_accounts_index
(
2
行
)
|
2 -我们都知道,索引是一个性能特性,但随着,它还可以用于保证唯一性。但确保唯一性,我们需要一个单独的类型的索引称为唯一索引。检查是否一个索引是独一无二的,pg_index上有一个专栏叫“indisunique”标识的唯一性索引。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
选择
我
.relname
作为
index_name
,
indisunique
is_unique
从
pg
_类
c
加入
pg
_指数
x
在
c
.oid
=
x
.indrelid
加入
pg
_类
我
在
我
.oid
=
x
.indexrelid
左
加入
pg
_名称空间
n
在
n
.oid
=
c
.relnamespace
在哪里
c
.relkind
=
任何
(
数组
(
“r”
,
“t”
]
)
和
c
.relname
就像
“pgbench_accounts”
;
指数
_的名字
|
是
_独特的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - - - - -
pgbench_accounts
_pkey
|
t
pgbench_accounts_index
|
f
(
2
行
)
|
3 -有一个非常简单的办法PostgreSQL指数的大小。这是一个查询列出PostgreSQL的大小。
|
1
2
3
4
5
|
选择
pg_size_pretty
(
pg_relation_size
(
“pgbench_accounts_index”
)
)
;
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
132年
MB
(
1
行
)
|
4 -这里是一个列表的索引与总表的大小和规模指数,这是非常有用的比较你的表的大小相应的索引。很好知道的大小表,索引和表的总大小。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
选择
CONCAT
(
n
.nspname
,
“。”
,
c
.relname
)
作为
表
,
我
.relname
作为
index_name
,
pg_size_pretty
(
pg_relation_size
(
x
.indrelid
)
)
作为
table_size
,
pg_size_pretty
(
pg_relation_size
(
x
.indexrelid
)
)
作为
index_size
,
pg_size_pretty
(
pg_total_relation_size
(
x
.indrelid
)
)
作为
total_size
从
pg
_类
c
加入
pg
_指数
x
在
c
.oid
=
x
.indrelid
加入
pg
_类
我
在
我
.oid
=
x
.indexrelid
左
加入
pg
_名称空间
n
在
n
.oid
=
c
.relnamespace
在哪里
c
.relkind
=
任何
(
数组
(
“r”
,
“t”
]
)
和
n
.oid
不
在
(
99年
,
11
,
12375年
)
;
表
|
指数
_的名字
|
table_size
|
index_size
|
总
_大小
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - -
公共
.pgbench_tellers
|
pgbench_tellers_pkey
|
88年
kB
|
64年
kB
|
152年
kB
公共
.pgbench_accounts
|
pgbench_accounts
_pkey
|
2561年
MB
|
428年
MB
|
3122年
MB
公共
.pgbench_accounts
|
pgbench_accounts_index
|
2561年
MB
|
132年
MB
|
3122年
MB
公共
.pgbench_branches
|
pgbench_branches
_pkey
|
8192年
字节
|
16
kB
|
24
kB
(
4
行
)
|
pg_relation_size:函数的大小关系。它是用来表/索引的大小。
pg_total_relation_size:这是一个特殊函数,它的总大小表连同其所有索引。
5 -索引的查询。这个查询将显示创建索引的查询。
|
1
2
3
4
5
6
7
8
|
选择
pg_get_indexdef
(
indexrelid
)
作为
index_query
从
pg_index
在哪里
indrelid
=
“pgbench_accounts”
::
regclass
;
index_query
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
创建
独特的
指数
pgbench_accounts_pkey
在
公共
.pgbench_accounts
使用
btree
(
援助
)
创建
指数
pgbench_accounts_index
在
公共
.pgbench_accounts
使用
btree
(
报价
)
创建
指数
pgbench_accounts_index_dup
在
公共
.pgbench_accounts
使用
btree
(
报价
)
(
3
行
)
|
6 -以防你的指数就会败坏或臃肿,您需要构建索引。同时,你不想阻止操作表,这同时重建索引命令是你的选择。
|
1
2
|
重建索引
指数
同时
idx
;
重建索引
|
7 -PostgreSQL有许多像BTree指数方法,散列,林,要点,杜松子酒。有时我们想创建一些特定的索引列但无法这样做。PostgreSQL的局限性,一些指标无法创建一些数据类型和运算符,这是有道理的。例如,哈希索引只能用于相等操作符。这里是一个列表的查询来获得支持的数据类型为特定索引。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
选择
amname
,
opfname
从
pg_opfamily
,
pg_am
在哪里
opfmethod
=
pg_am
.oid
和
amname
=
“来”
;
amname
|
opfname
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
btree
|
array_ops
btree
|
bit_ops
btree
|
bool
_运维
…
|
8 -这个查询会发现未使用索引。如果index_scans是0或接近0,那么您可以将这些索引。但是要小心,也许这些索引是唯一的目的。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
选择
年代
.relname
作为
table_name
,
indexrelname
作为
index_name
,
我
.indisunique
,
idx_scan
作为
index_scans
从
pg_catalog
.pg_stat_user_indexes
年代
,
pg
_指数
我
在哪里
我
.indexrelid
=
年代
.indexrelid
;
table_name
|
index_name
|
indisunique
|
index_scans
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
pgbench_branches
|
pgbench_branches_pkey
|
t
|
0
pgbench_tellers
|
pgbench_tellers_pkey
|
t
|
0
pgbench_accounts
|
pgbench_accounts_pkey
|
t
|
0
(
3
行
)
|
9 -查询用于查找一个重复的索引。在这个例子中,pgbench_accounts有两个相同的索引。不需要使用一个不同的名称有多个相同的索引表。正如我们已经讨论过的那样,在更新/插入的情况下,所有的索引更新以及实际的表,这损害了性能。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
|
选择
indrelid
::
regclass
table_name
,
丙氨酸
.attname
column_name
,
amname
index_method
从
pg
_指数
我
,
pg
_类
c
,
pg
_opclass
o
,
pg
_我
一个
,
pg_attribute
丙氨酸
在哪里
o
.oid
=
所有
(
indclass
)
和
丙氨酸
.attnum
=
任何
(
我
.indkey
)
和
一个
.oid
=
o
.opcmethod
和
丙氨酸
.attrelid
=
c
.oid
和
c
.oid
=
我
.indrelid
集团
通过
table_name
,
丙氨酸
.attname
,
indclass
,
amname
,
indkey
有
数
(
*
)
>
1
;
table_name
|
column_name
|
index_method
- - -
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
喷火
|
一个
|
btree
(
1
行
)
|
结论
PostgreSQL目录表来存储索引信息,因此,我们可以写出尽可能多的查询我们所需要的。这个博客显示一些基本的查询和展示了如何使用目录表编写查询。
我们的白皮书”为什么选择PostgreSQL ?”看着PostgreSQL的特点和优点,提出了一些实际的用法示例。我们还研究如何对企业有用PostgreSQL从Oracle迁移。
