本系列的内容主要翻译自Postgresql官方博客, 为了便于理解,对于其中部分涉及到的知识,我在查阅相关资料的基础上做了补充。

原文: Indexes in PostgreSQL — 2

系列文章索引

接口

第一篇文章中,我们提到了访问方法必须提供关于它自身的信息。这一篇我们来看一下访问方法接口的内部。

属性

访问方法的所有属性都存储在pg_am表中。我们可以从这个表中列出所有的可用的访问方法

1
select amname from pg_am;
1
2
3
4
5
6
7
8
9
10
 amname 
--------
heap
btree
hash
gist
gin
spgist
brin
(7 rows)

尽管顺序扫描也可以称为是一种访问方法,但是由于历史原因,不在这个表里。

在PostgreSQL 9.5和以前的版本中,每一个属性都是pg_table中一个单独的字段。从9.6版本开始,这些属性需要用一些特殊的函数来查询,而且分为了几个层面。

  • 访问方法级别属性-“pg_indexam_has_property”
  • 特定索引级别的属性-“pg_index_has_property”
  • 索引的列级别的属性-“pg_index_column_has_property”

访问方法层和索引层被分开,是为了着眼于未来,而截至到现在,基于同一个访问方法的所有索引都具有相同的属性。

索引方法属性

下面五个属性是索引方法的属性(以btree为例)

1
2
3
4
5
select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
from pg_am a,
unnest(array['can_order','can_unique','can_multi_col','can_exclude','can_include']) p(name)
where a.amname = 'btree'
order by a.amname;
1
2
3
4
5
6
7
8
 amname |     name      | pg_indexam_has_property 
--------+---------------+-------------------------
btree | can_order | t
btree | can_unique | t
btree | can_multi_col | t
btree | can_exclude | t
btree | can_include | t
(5 rows)
  • can_order 访问方法允许我们在创建索引的时候,为索引指定排序顺序。(目前仅btree支持)

  • can_unique 支持唯一约束和主键。 (目前仅btree支持)

  • can_multi_col 一个索引可以建立在多个列上

  • can_execude 支持排除约束

  • can_include 创建索引的时候可以使用INCLUDE条件

索引属性

下列是索引的属性(以第一篇文章建立的索引作为例子)

1
2
3
4
select p.name, pg_index_has_property('t_a_idx'::regclass,p.name)
from unnest(array[
'clusterable','index_scan','bitmap_scan','backward_scan'
]) p(name);
1
2
3
4
5
6
7
     name      | pg_index_has_property 
---------------+-----------------------
clusterable | t
index_scan | t
bitmap_scan | t
backward_scan | t
(4 rows)
  • clusterable 可以根据索引对记录行重新排序(使用CLUSTER)
  • index_scan 支持索引扫描。虽然这个属性看起来多余,但是不是所有的索引都可以一个接一个返回TID的。有一些一次返回所有的结果,有一些只支持位图扫描
  • bitmap_scan 支持位图扫描
  • backward_scan 结果可以根据在创建索引的时候相反的顺序返回

CLUSTER会对指定的表根据索引信息重新进行物理排序(即磁盘上的存储顺序)。CLUSTER是一次性操作,即在进行CLUSTER操作以后,对之后更新的数据不会重新进行物理上的排序。

在第一篇文章中讲过查看物理排序和逻辑排序相关性的命令

1
select attname, correlation from pg_stats where tablename = 't';
1
2
3
4
5
6
 attname | correlation 
---------+-------------
c | 0.9896601
a | -0.02042865
b | -0.38292253
(3 rows)

由于t表中的顺序是随机插入的,因此字段a的相关性非常的低,几乎没有相关性。但是如果我们对表t用索引t_a_idx执行一下CLUSER命令

1
2
3
cluster t using t_a_idx;

analyze t;

再次查看相关性

1
select attname, correlation from pg_stats where tablename = 't';
1
2
3
4
5
6
 attname | correlation 
---------+-------------
c | 0.97945035
a | 1
b | 0.01093776
(3 rows)

可以看到,a的相关性变成了1

索引的列的属性

最后是列的属性

1
2
3
4
5
6
select p.name,
pg_index_column_has_property('t_a_idx'::regclass,1,p.name)
from unnest(array[
'asc','desc','nulls_first','nulls_last','orderable','distance_orderable',
'returnable','search_array','search_nulls'
]) p(name);
1
2
3
4
5
6
7
8
9
10
11
12
        name        | pg_index_column_has_property
--------------------+------------------------------
asc | t
desc | f
nulls_first | f
nulls_last | t
orderable | t
distance_orderable | f
returnable | t
search_array | t
search_nulls | t
(9 rows)
  • asc, desc, nulls_first, nulls_last, orderable 这些属性和排序相关(这个将在btree一节讨论)
  • distance_orderable 是否可以通过“距离”操作符来按顺序扫描列(目前仅适用于Gist和RUM索引)
  • returnable 可以在不获取表结构的情况下使用索引,这意味着支持index-only扫描,即覆盖索引
  • search_array 支持用«indexed-field IN (list_of_constants)»或者«indexed-field = ANY(array_of_constants)»这样的表达式来搜索几个指定值
  • search_nulls 可以根据is null或者is not null条件来搜索。

我们讨论了一些关于属性的细节。某些属性特定于指定的索引方法。我们将会在讲到这些访问方法的时候来讨论这些属性。上面的内容可以在官方文档-函数信息中找到对应的描述

操作符类和家族

除了所描述的访问方法公开的属性以外,还需要知道访问方法能够支持哪些数据类型和哪些运算符。为此,PostgreSQL引入了操作符类和操作符族的概念。

一个操作符类包含了一组最小的运算符(可能还有辅助函数),用于索引操作特定的数据类型。

操作符类可以被包括在操作符族中。而且,一个常见的操作符类通常包含几个拥有同样语义的操作符类。例如,”integer_ops”族包含了bigint数据类型的”int8_ops”、integer数据类型的”int4_ops”和smallint数据类型的”int2_ops”,这些数据类型具有不同的大小,但是有相同的意义。

1
2
3
4
5
6
7
select opfname, opcname, opcintype::regtype
from pg_opclass opc, pg_opfamily opf
where opf.opfname = 'integer_ops'
and opc.opcfamily = opf.oid
and opf.opfmethod = (
select oid from pg_am where amname = 'btree'
);
1
2
3
4
5
6
   opfname   | opcname  | opcintype 
-------------+----------+-----------
integer_ops | int2_ops | smallint
integer_ops | int4_ops | integer
integer_ops | int8_ops | bigint
(3 rows)

另外一个例子,”datetime_ops”族中包含了几个操作日期的操作符类。

1
2
3
4
5
6
7
select opfname, opcname, opcintype::regtype
from pg_opclass opc, pg_opfamily opf
where opf.opfname = 'datetime_ops'
and opc.opcfamily = opf.oid
and opf.opfmethod = (
select oid from pg_am where amname = 'btree'
);
1
2
3
4
5
6
   opfname    |     opcname     |          opcintype          
--------------+-----------------+-----------------------------
datetime_ops | date_ops | date
datetime_ops | timestamptz_ops | timestamp with time zone
datetime_ops | timestamp_ops | timestamp without time zone
(3 rows)

一个操作符族可以包含一些额外的操作符来比较不同类型的值。操作符类组成成一个族可以让计划器为那些不同数据类型的谓词来使用索引。操作符族也可以包含一些其它的辅助方法。

在大多数情况下,我们不需要知道关于操作符族和操作符类的信息。通常我们都是创建索引,然后默认使用某一个操作符类。

然而我们可以显式的指定操作符类。这是一个说明什么时候需要明确指定操作符类的例子:在排序规则(COLLATE)和C不同的数据库中,一个常规的索引不支持LIKE操作符。

COLLATE 简单的理解就是可排序数据类型的排序规则,目前内置的可排序数据类型只有text、varchar、char,具体可以点击查看官方文档

1
show lc_collate;
1
2
3
4
lc_collate 
------------
en_US.utf8
(1 row)

从上面结果中可以看出默认排序规则是en_US.utf8,不是C。 这时候我们在t(b)上创建索引,然后尝试使用LIKE查询

1
2
3
4
5
create index on t(b);

analyze t;

explain (costs off) select * from t where b like 'A%';
1
2
3
4
5
         QUERY PLAN          
-----------------------------
Seq Scan on t
Filter: (b ~~ 'A%'::text)
(2 rows)

如果我们在创建索引的时候显式指定操作符类是text_pattern_ops,如下面所示

1
2
3
4
5
create index on t(b text_pattern_ops);

analyze t;

explain (costs off) select * from t where b like 'A%';
1
2
3
4
5
6
7
                           QUERY PLAN                           
----------------------------------------------------------------
Bitmap Heap Scan on t
Filter: (b ~~ 'A%'::text)
-> Bitmap Index Scan on t_b_idx4
Index Cond: ((b ~>=~ 'A'::text) AND (b ~<~ 'B'::text))
(4 rows)

可以看到这时候LIKE查询也走了索引。

在讲述后面的内容之前,我们先来验证另外一件事情,即排序规则是C的情况下,LIKE查询是否走索引

1
2
3
4
5
6
7
8
9
10
11
12
13
--- 在字段b的后面加了collate "C"来指定b使用C排序
create table t3(a integer, b text collate "C", c boolean);

insert into t3(a, b, c)
select s.id, chr((32 + random() * 94)::integer), random() < 0.01
from generate_series(1, 100000) as s(id)
order by random();

create index on t3(b);

analyze t3;

explain (costs off) select * from t3 where b like 'A%';
1
2
3
4
5
6
                      QUERY PLAN                      
------------------------------------------------------
Index Scan using t3_b_idx on t3
Index Cond: ((b >= 'A'::text) AND (b < 'B'::text))
Filter: (b ~~ 'A%'::text)
(3 rows)

从查询计划中可以看出,b like ‘A%’被转换为了 b >= ‘A’::text 和 b < ‘B’::text,这一点不难理解,想象一下英文词典上的排序,以A开头的单词是不是排在了A的后面(>=A)和B的前面(<B)

系统目录

在这篇文章的结尾,我们提供了系统目录中和运算符类以及运算符族直接相关的表的简化图。

这里有上述表中的全部具体描述

系统目录能够让我们在不查看文档的情况下找到许多问题的答案。例如,某种访问方法可以操作哪些数据类型

1
2
3
4
select opcname, opcintype::regtype
from pg_opclass
where opcmethod = (select oid from pg_am where amname = 'btree')
order by opcintype::regtype::text;
1
2
3
4
5
6
       opcname       |          opcintype          
---------------------+-----------------------------
abstime_ops | abstime
array_ops | anyarray
enum_ops | anyenum
...

一个操作符类包含哪些操作符

1
2
3
4
5
6
7
8
select amop.amopopr::regoperator
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'array_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'btree'
and amop.amoplefttype = opc.opcintype;
1
2
3
4
5
6
7
8
        amopopr        
-----------------------
<(anyarray,anyarray)
<=(anyarray,anyarray)
=(anyarray,anyarray)
>=(anyarray,anyarray)
>(anyarray,anyarray)
(5 rows)