目录
接口
在第一篇文章中,我们提到,访问方法必须提供自我的信息。让我们看看访问方法接口的结构。
属性
所有访问方法的属性都被存储在"pg_am"表中(“am”表示access method,访问方法)。我们同样可以从这张表中获取可用方法的列表:
postgres=# select amname from pg_am;
amname
--------
btree
hash
gist
gin
spgist
brin
(6 rows)
尽管顺序扫描可以正确地被称为访问方法,但因为一些历史原因,它不在这个列表中。
在PG 9.5版及更低的版本中,每个属性都用“pg_am”表中的一个单独的字段表示。从9.6版开始,属性需通过一些特定的函数查询,并且被分为以下几个层级:
- 访问方法的属性-“pg_indexam_has_property”(类的属性)
- 特定索引的属性-“pg_index_has_property”(对象的属性)
- 索引的各个列的属性-"pg_index_column_has_property"
访问方法层和索引层是分开的,到目前为止,基于一种访问方法的所有索引将始终具有相同的属性。
以下四种是访问方法的属性(以"btree"为例):
postgres=# 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']) p(name)
where a.amname = 'btree'
order by a.amname;
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
btree | can_order | t
btree | can_unique | t
btree | can_multi_col | t
btree | can_exclude | t
(4 rows)
(其中unnest的说明参见:postgresql中unnest使用说明与示例_那些年我们正年轻的博客-CSDN博客_postgres unnest)
can_order:当索引已建成,访问方法允许我们指定值的排序方式(目前只运用于“btree”)。
can_unique:支持唯一约束与主键(只运用于"btree")(pg的唯一索引与唯一约束在功能上基本一样,其实创建唯一约束时,pg会自动在该列上创建唯一索引,以便在插入数据时,快速检索是否与已有的值重复,但是两者均不能避免多个NULL值的存在,除非为NULL值另建唯一B树索引,参考:PG唯一索引与唯一约束.sql_岳麓丹枫001的博客-CSDN博客_pg 唯一索引)。
can_multi_col:可以在多列上建索引
can_exclude:支持排除约束EXCLUDE
以下属性与某一具体索引有关(让我们考虑一个现有的例子)
postgres=# 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);
name | pg_index_has_property
---------------+-----------------------
clusterable | t
index_scan | t
bitmap_scan | t
backward_scan | t
(4 rows)
clusterable:可以根据索引对行重新排序(使用相同名字的指令聚集CLUSTER)(该指令在物理存储上使索引列有序紧密存储)。
index_scan:支持索引扫描。尽管这一属性看起来很奇怪,但并不是所有索引都能逐个返回TID,有些会一次返回所有结果,并只支持位图扫描。(比如Bloom索引)
bitmap_scan:支持位图扫描。(所有的索引都支持位图扫描)
backward_scan:结果可以按照构建索引时指定的相反顺序返回。
最后,下面是列属性:
postgres=# 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);
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:可以在不访问表的情况下使用索引,即,支持仅索引扫描。
search_array:支持使用表达式“indexed-field IN (list_of_constants)”来检索多个值,该表达式与"indexed-field=ANY(array_of_constants)"相同。
search_nulls:支持IS NULL与IS NOT NULL条件检索。
我们已经详细讨论了一些属性。一些属性特定于某些访问方法。我们将在考虑这些特定方法时讨论这些属性。
操作符类与族
除了所述接口公开的访问方法的属性外,还需要了解访问方法接受哪些数据类型和哪些运算符的信息。为此,PostgreSQL引入了运算符类和运算符族的概念。
运算符类包含一组用于索引操作特定数据类型的最小运算符(可能还有辅助函数)。
运算符类包含在某些运算符族中(运算符族为不同的访问方法设立,而运算符类为不同的数据类型设立)。此外,如果一些运算符类具有相同的语义,那么它们可以被包含在一个公共的运算符族中。例如,“整数运算”族包括“bigint”、“integer”和“smallint”类型的“int8运算”、“int4运算”和“int2运算”类,它们的大小不同,但含义相同:
postgres=# 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'
);
opfname | opcname | opcintype
-------------+----------+-----------
integer_ops | int2_ops | smallint
integer_ops | int4_ops | integer
integer_ops | int8_ops | bigint
(3 rows)
另一个例子:“datetime_ops”族包括操纵日期(有时间和无时间)的操作符类:
postgres=# 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'
);
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)
操作符族还可以包含其他操作符,以比较不同类型的值。分组到族中使规划器能够对具有不同类型值的谓词使用索引(text类型的谓词比如LIKE a%,可以在text类型的字段上根据文本的pattern进行索引,这样规划器就能使用该索引执行LIKE谓词了,下面有举例)。族还可以包含其他辅助功能。
在大多数情况下,我们不需要了解任何有关运算符族和类的信息。通常我们只是创建一个索引,默认情况下使用某个操作符类。
但是,我们可以显式地指定运算符类。这是一个需要显式指定的简单示例:在排序规则不同于C的数据库中,常规索引不支持LIKE操作:
postgres=# show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)
postgres=# explain (costs off) select * from t where b like 'A%';
QUERY PLAN
-----------------------------
Seq Scan on t
Filter: (b ~~ 'A%'::text)
(2 rows)
我们可以通过使用操作符类“text_pattern_ops”创建一个索引来克服这个限制(注意计划中的条件是如何变化的):
postgres=# create index on t(b text_pattern_ops);
postgres=# explain (costs off) select * from t where b like 'A%';
QUERY PLAN
----------------------------------------------------------------
Bitmap Heap Scan on t
Filter: (b ~~ 'A%'::text)
-> Bitmap Index Scan on t_b_idx1
Index Cond: ((b ~>=~ 'A'::text) AND (b ~<~ 'B'::text))
(4 rows)
系统目录
在本文的最后,我们提供了系统目录中与运算符类和族直接相关的表的简化图。
不用说,所有这些表格都有详细的描述。
系统目录能让我们找到很多问题的答案,而不需要看文档。比如,某一访问方法可以操作哪些数据类型:
postgres=# select opcname, opcintype::regtype
from pg_opclass
where opcmethod = (select oid from pg_am where amname = 'btree')
order by opcintype::regtype::text;
opcname | opcintype
---------------------+-----------------------------
abstime_ops | abstime
array_ops | anyarray
enum_ops | anyenum
...
运算符类包含哪些运算符(并且因此知道,包含此类运算符的条件可以使用哪些索引访问):
postgres=# 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;
amopopr
-----------------------
<(anyarray,anyarray)
<=(anyarray,anyarray)
=(anyarray,anyarray)
>=(anyarray,anyarray)
>(anyarray,anyarray)
(5 rows)