PostgreSQL中的索引(二)

2020-08-04 17:37 abce 阅读(824) 评论(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)