索引的一个核心特性数据库管理系统(DBMS)。PostgreSQL索引有非常悠久的历史,有一组丰富的索引功能。PostgreSQL b -树、散列、杜松子酒、要点和布林索引。因为发展指标仍在继续,PostgreSQL 13提供了一些增强功能。
我们可以把整体的改进分为两类:
- 改变对用户是透明的。不会有任何明显的变化,但他们会得到好处后自动升级,可能经过重建索引。不需要应用程序改变。
- 第二组的改进要求用户显式地使用新的特性。
介绍了两种类型的改进在PostgreSQL版本13。有时很难提取信息<一个href="https://www.postgresql.org/docs/13/release-13.html">释放笔记和理解为最终用户,甚至说服一个终端用户,所以这个博客包含示例的改进在PostgreSQL 13。
1。重复数据删除的b -树索引(<一个href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0d861bbb702f8aa05c2a4e3f1650e7e8df8c8c27">1]
非唯一/主要的b -树索引通常包含许多重复的值。副本的存储起着至关重要的作用,尤其是在b -树,它允许聚合操作数或通过使用这些索引。然而,一个聪明的包装方式可以消除存储实际的重复值仅维护参考。
重复数据删除技术消除了冗余的过程从树上/重复的值。这种“重复数据删除”过程减少了索引的存储要求,每个键只存储一次,节省空间。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
postgres
=
#创建表foo (id整数,名称文本);
postgres
=
#插入foo值(generate_series(1000000),“价值”);
插入
0
1000000
postgres
=
#选择pg_size_pretty (pg_relation_size (“foo”));
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
42
MB
(
1
行
)
postgres
=
#上创建索引idx foo(名称);
postgres
=
#选择pg_size_pretty (pg_relation_size (' idx '));
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
21
MB
(
1
行
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
postgres
=
#创建表foo (id整数,名称文本);
postgres
=
#插入foo值(generate_series(1000000),“价值”);
插入
0
1000000
postgres
=
#选择pg_size_pretty (pg_relation_size (“foo”));
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
42
MB
(
1
行
)
postgres
=
#上创建索引idx foo(名称);
postgres
=
#选择pg_size_pretty (pg_relation_size (' idx '));
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
6792年
kB
(
1
行
)
|
(N投票选出:这只是一个例子显示减少的大小,不是一个基准。你可能会得到不同的数字。]
介绍了一个新的指数参数叫做deduplicate_items可以在创建索引时指定。该参数用于启用/禁用重复数据删除。“上”在默认情况下,这意味着重复数据删除的好处可以没有明确做出任何改变。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
postgres
=
#上创建索引idx1 foo(名称)(deduplicate_items =);
创建
指数
postgres
=
#选择pg_size_pretty (pg_relation_size (' idx '));
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
6792年
kB
(
1
行
)
postgres
=
#选择pg_size_pretty (pg_relation_size (' idx1 '));
pg_size
_漂亮的
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
21
MB
(
1
行
)
|
注意:如果你升级从旧版本使用PostgreSQLpg_upgrade,所有索引需要重建索引结果重复数据删除的好处,不管你是哪个版本升级。
2。允许要点(<一个href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c085e1c1cb4e29637552f5d250d45ad0cb83e5cf">2.1]和SP-GiST [<一个href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c085e1c1cb4e29637552f5d250d45ad0cb83e5cf">2.2]索引查找框/点距离
深入发展的要点指数是一个模板索引在任何类型的数据,支持任何查找数据。默认情况下,它支持一个宽范围的运营商。在PostgreSQL 13中,t他的新补丁增加了对失踪”的支持< - >(框,点)”运营商要点box_ops排序操作符。
让我们考虑一个表的例子和一个盒子,一个点,一个圆。
|
1
|
postgres
=
#创建表foo (b盒子,p点,c圈);
|
插入数据表:
|
1
2
3
4
5
|
postgres
=
#插入foo
选择
盒子
(
点
(
0.05
*
我
,
0.05
*
我
)
,
点
(
0.05
*
我
,
0.05
*
我
)
)
,
点
(
0.05
*
我
,
0.05
*
我
)
,
圆
(
点
(
0.05
*
我
,
0.05
*
我
)
,
1。0
)
从
generate_series
(
0
,
10000年
)
作为
我
;
|
分析表:
|
1
|
postgres
=
#真空分析foo;
|
创建一个要点索引框字段:
|
1
2
|
postgres
=
#使用要点创建指数idx foo (b);
创建
指数
|
现在我们有一个查询来检查所有的盒子中包含另一个框区域但点按距离排序。
|
1
2
3
4
5
6
7
|
postgres
=
#解释(成本)从foo选择b, b < @盒(点(5,5),点(6,6)),b < - >点(5.2,5.91);
查询
计划
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
指数
只有
扫描
使用
idx
在
喷火
指数
气孔导度
:
(
b
<
@
”(6,6),(5)
::
盒子
)
订单
通过
:
(
b
<
- >
“(5.2,5.91)”
::
点
)
(
3
行
)
|
请注意命令操作符使用在上面查询“b < - >点(5.2,5.91);”。这是不可能的在前面的PostgreSQL版本。
3所示。允许<一个href="https://www.postgresql.org/docs/13/gin.html">杜松子酒索引来更有效地处理不限制<一个href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4b754d6c16e16cc1a1adf12ab0f48603069a0efd">3]
这是一个性能增强使用杜松子酒索引的查询。现在的杜松子酒指数将在处理更高效的否定的限制。这种改善避免杜松子酒的完整扫描索引。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
24
25
26
|
postgres
=
#创建表t_gin_test_tbl(我int4 [], j int4 []);
postgres
=
# t_gin_test_tbl上创建索引使用杜松子酒(i, j);
postgres
=
# NSERT成t_gin_test_tbl
值
(
零
,
零
)
,
(
“{}”
,
零
)
,
(
“{1}”
,
零
)
,
(
“{1,2}”
,
零
)
,
(
零
,
“{}”
)
,
(
零
,
{10}”
)
,
(
“{1,2}”
,
{10}”
)
,
(
“{2}”
,
{10}”
)
,
(
“{1,3}”
,
“{}”
)
,
(
“{1}”
,
{10}”
)
;
postgres
=
#设置ENABLE_SEQSCAN =;
postgres
=
#解释(成本)
选择
*
从
t_gin_test_tbl
在哪里
数组
(
0
]
<
@
我
;
查询
计划
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
位图
堆
扫描
在
t_gin_test
_资源描述
重新检查
气孔导度
:
(
“{0}”
::
整数
(
]
<
@
我
)
- >
位图
指数
扫描
在
t_gin_test_tbl_i_j
_idx
指数
气孔导度
:
(
我
@
>
“{0}”
::
整数
(
]
)
(
4
行
)
|
4所示。索引操作符类参数(<一个href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=911e70207703799605f5a0e8aad9f06cff067c63">4]
PostgreSQL有多种索引访问方法,如(1)主旨,(2)杜松子酒,(3)SP-GiST,(4)布林。在创建索引时已经有一个选项来指定操作符类。操作符类包含用于该指数的比较函数。通常当我们创建一个索引不指定操作符类,默认使用操作符类,大部分时间它就足够了。但在某些情况下需要有多个有意义的行为,因此我们需要指定操作符类。
这些opclasses定义键的表示和操作。随着它还定义了支持的搜索策略。添加一些用户端决定opclass opclass_parameter介绍。新语法创建索引添加到指定操作符类选项。
|
1
2
|
创建
(
独特的
]
指数
(
同时
]
(
(
如果
不
存在
]
的名字
]
在
(
只有
]
table_name
(
使用
方法
]
(
{
column_name
|
(
表达式
)
}
(
核对
排序
]
(
opclass
(
(
opclass_parameter
=
价值
(
,
。
。
。
]
)
]
]
(
ASC
|
DESC
]
(
零位
{
第一个
|
去年
}
]
(
,
。
。
。
]
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
postgres
=
#创建表int_arr (int id val int []);
创建
表
postgres
=
# int_arr上创建索引使用要点(val gist__intbig_ops (siglen = 32));
创建
指数
postgres
=
# \ d int_arr
表
“public.int_arr”
列
|
类型
|
排序
|
可以为空
|
默认的
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
- - -
- - -
- - -
- - -
- - -
+
- - -
- - -
- - -
- - -
- - - - - -
id
|
整数
|
|
|
瓦尔
|
整数
(
]
|
|
|
索引
:
“int_arr_val_idx”
要点
(
瓦尔
gist__intbig_ops
(
siglen
=
“32”
)
)
|
5。依据签名长度
允许创建索引指定要点签名长度和最大数量的整数范围。现在用户可以指定依据指标参数。
|
1
|
创建
指数
的名字
在
表
使用
要点
(
列
(
{
默认的
|
tsvector
_运维
}
(
siglen
=
数量
)
]
)
;
|
6。防止使用非默认排序的索引<一个href="https://www.postgresql.org/docs/13/sql-altertable.html">添加作为一个表的独特或主键约束(<一个href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fbbf68094c5ff3d513969d072126c92932e484da">5]
现在必须匹配索引和列排序的索引的独特性与列的独特性。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
postgres
=
#创建表foo (col varchar (255));
创建
表
postgres
=
#创建唯一索引idx foo (col desc);
创建
指数
postgres
=
# ALTER TABLE foo添加约束使用索引idx unique_idx独特;
2020年
- - - - - -
09年
- - - - - -
07年
01
:
33
:
55.971
PKT
(
11083年
]
错误
:
指数
“idx”
列
数量
1
做
不
有
默认的
排序
行为
在
字符
21
2020年
- - - - - -
09年
- - - - - -
07年
01
:
33
:
55.971
PKT
(
11083年
]
细节
:
不能
创建
一个
主
关键
或
独特的
约束
使用
这样的
一个
指数
。
2020年
- - - - - -
09年
- - - - - -
07年
01
:
33
:
55.971
PKT
(
11083年
]
声明
:
改变
表
喷火
添加
约束
unique_idx
独特的
使用
指数
idx
;
错误
:
指数
“idx”
列
数量
1
做
不
有
默认的
排序
行为
行
1
:
改变
表
喷火
添加
约束
unique_idx
独特的
使用
指数
。
。
。
^
细节
:
不能
创建
一个
主
关键
或
独特的
约束
使用
这样的
一个
指数
。
|
* (目前这是在默认情况下,但最终决定将GA]
注意:所有的信息是基于PostgreSQL 13β。
