PostgreSQL中的索引(二)
2020-08-04 17:37 abce 阅读(742) 评论(0) 编辑 收藏 举报在第一篇文章中，我们已经提到访问方法必须提供有关自身的信息。让我们看一下访问方法接口的结构。
属性
访问方法的所有属性都存储在«pg_am»表中（“am”代表访问方法）。我们还可以从同一个表中获取可用方法的列表：
postgres=# select * from pg_am; amname | amhandler | amtype --------+-------------+-------- btree | bthandler | i hash | hashhandler | i gist | gisthandler | i gin | ginhandler | i spgist | spghandler | i brin | brinhandler | i (6 rows) postgres=#
虽然顺序扫描可以正确地引用访问方法，但由于历史原因，它不在此列表中。
在PostgreSQL 9.5及更低版本中，每个属性都用«pg_am»表的单独字段表示。从版本9.6开始，使用特殊函数查询属性，并将其分为多个层：
·访问方法属性 - «pg_indexam_has_property»
·特定索引的属性 - «pg_index_has_property»
·索引的各列的属性 - «pg_index_column_has_property»
着眼于未来，访问方法层和索引层会分开：截至目前，基于一种访问方法的所有索引将始终具有相同的属性。
以下四个属性是访问方法的属性（通过«btree»的示例）：
postgres=# select a.*, 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 | amhandler | amtype | name | pg_indexam_has_property
--------+-----------+--------+---------------+-------------------------
btree | bthandler | i | can_order | t
btree | bthandler | i | can_unique | t
btree | bthandler | i | can_multi_col | t
btree | bthandler | i | can_exclude | t
(4 rows)
其中：
-can_order。
访问方法使我们能够在创建索引时指定值的排序顺序（目前仅适用于«btree»）。
-can_unique。
支持唯一约束和主键（仅适用于«btree»）。
-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。有些一次性返回所有结果，并且只支持位图扫描。
·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
这些属性与值的排序有关
·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»家族包括«int8_ops»、«int4_ops»和«int2_ops»类，它们的类型«bigint»、«integer»和«smallint»大小不同但意义相同:
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)
操作符族还可以包括其他操作符，用于比较不同类型的值。将操作符类进行分类是为了让计划器为不同类型值的谓词使用索引。一个操作符家族也可以包含辅助函数。
在大多数情况下，我们不需要了解操作符家族和类。通常我们只创建索引，默认情况下使用特定的操作符类。
但是，我们可以显式地指定操作符类。这是一个需要显式规范的简单例子:在与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)
系统目录(system catalog)
在本文的最后，我们提供了一个与操作符类和族直接相关的系统目录表的简化图。
系统目录使我们能够在不查看文档的情况下找到许多问题的答案。例如，某个访问方法可以操作哪些数据类型?
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)
