64.1. 索引的基本 API 结构#
每个索引访问方法都由pg_am
系统目录中的一行描述。pg_am
条目为索引访问方法指定一个名称和一个处理函数。可以使用CREATE ACCESS METHOD和DROP ACCESS METHODSQL 命令创建和删除这些条目。
索引访问方法处理程序函数必须声明为接受一个类型为internal
的参数,并返回伪类型index_am_handler
。该参数是一个虚拟值,它仅仅用于防止处理程序函数直接从 SQL 命令中调用。该函数的结果必须是类型为IndexAmRoutine
的 palloc'd 结构,其中包含核心代码使用索引访问方法所需了解的所有内容。IndexAmRoutine
结构,也称为访问方法的API 结构,包括指定访问方法的各种固定属性的字段,例如它是否支持多列索引。更重要的是,它包含对访问方法的支持函数的指针,这些函数执行访问索引的所有实际工作。这些支持函数是普通的 C 函数,在 SQL 级别不可见或不可调用。支持函数在第 64.2 节中进行描述。
结构IndexAmRoutine
因此定义
typedef struct IndexAmRoutine
{
NodeTag type;
/*
* Total number of strategies (operators) by which we can traverse/search
* this AM. Zero if AM does not have a fixed set of strategy assignments.
*/
uint16 amstrategies;
/* total number of support functions that this AM uses */
uint16 amsupport;
/* opclass options support function number or 0 */
uint16 amoptsprocnum;
/* does AM support ORDER BY indexed column's value? */
bool amcanorder;
/* does AM support ORDER BY result of an operator on indexed column? */
bool amcanorderbyop;
/* does AM support backward scanning? */
bool amcanbackward;
/* does AM support UNIQUE indexes? */
bool amcanunique;
/* does AM support multi-column indexes? */
bool amcanmulticol;
/* does AM require scans to have a constraint on the first index column? */
bool amoptionalkey;
/* does AM handle ScalarArrayOpExpr quals? */
bool amsearcharray;
/* does AM handle IS NULL/IS NOT NULL quals? */
bool amsearchnulls;
/* can index storage data type differ from column data type? */
bool amstorage;
/* can an index of this type be clustered on? */
bool amclusterable;
/* does AM handle predicate locks? */
bool ampredlocks;
/* does AM support parallel scan? */
bool amcanparallel;
/* does AM support columns included with clause INCLUDE? */
bool amcaninclude;
/* does AM use maintenance_work_mem? */
bool amusemaintenanceworkmem;
/* does AM summarize tuples, with at least all tuples in the block
* summarized in one summary */
bool amsummarizing;
/* OR of parallel vacuum flags */
uint8 amparallelvacuumoptions;
/* type of data stored in index, or InvalidOid if variable */
Oid amkeytype;
/* interface functions */
ambuild_function ambuild;
ambuildempty_function ambuildempty;
aminsert_function aminsert;
ambulkdelete_function ambulkdelete;
amvacuumcleanup_function amvacuumcleanup;
amcanreturn_function amcanreturn; /* can be NULL */
amcostestimate_function amcostestimate;
amoptions_function amoptions;
amproperty_function amproperty; /* can be NULL */
ambuildphasename_function ambuildphasename; /* can be NULL */
amvalidate_function amvalidate;
amadjustmembers_function amadjustmembers; /* can be NULL */
ambeginscan_function ambeginscan;
amrescan_function amrescan;
amgettuple_function amgettuple; /* can be NULL */
amgetbitmap_function amgetbitmap; /* can be NULL */
amendscan_function amendscan;
ammarkpos_function ammarkpos; /* can be NULL */
amrestrpos_function amrestrpos; /* can be NULL */
/* interface functions to support parallel index scans */
amestimateparallelscan_function amestimateparallelscan; /* can be NULL */
aminitparallelscan_function aminitparallelscan; /* can be NULL */
amparallelrescan_function amparallelrescan; /* can be NULL */
} IndexAmRoutine;
为了有用,索引访问方法还必须在pg_opfamily
、pg_opclass
、pg_amop
和pg_amproc
中定义一个或多个操作符族和操作符类。这些条目允许规划器确定可以使用此访问方法的索引的查询限定条件的类型。操作符族和类在第 38.16 节中进行描述,这是阅读本章的先决条件材料。
单个索引由一个pg_class
条目定义,该条目将其描述为物理关系,外加一个pg_index
条目,该条目显示索引的逻辑内容,即它拥有的索引列集和这些列的语义,由关联的操作符类捕获。索引列(键值)可以是基础表的简单列,也可以是表行的表达式。索引访问方法通常对索引键值来自何处不感兴趣(它始终被传递预先计算的键值),但它会对pg_index
中的操作符类信息非常感兴趣。这两个目录条目都可以作为Relation
数据结构的一部分进行访问,该数据结构传递给索引上的所有操作。
Some of the flag fields ofIndexAmRoutine
have nonobvious implications. The requirements ofamcanunique
are discussed inSection 64.5. Theamcanmulticol
flag asserts that the access method supports multi-key-column indexes, whileamoptionalkey
asserts that it allows scans where no indexable restriction clause is given for the first index column. Whenamcanmulticol
is false,amoptionalkey
essentially says whether the access method supports full-index scans without any restriction clause. Access methods that support multiple index columnsmustsupport scans that omit restrictions on any or all of the columns after the first; however they are permitted to require some restriction to appear for the first index column, and this is signaled by settingamoptionalkey
false. One reason that an index AM might setamoptionalkey
false is if it doesn't index null values. Since most indexable operators are strict and hence cannot return true for null inputs, it is at first sight attractive to not store index entries for null values: they could never be returned by an index scan anyway. However, this argument fails when an index scan has no restriction clause for a given index column. In practice this means that indexes that haveamoptionalkey
true must index nulls, since the planner might decide to use such an index with no scan keys at all. A related restriction is that an index access method that supports multiple index columnsmustsupport indexing null values in columns after the first, because the planner will assume the index can be used for queries that do not restrict these columns. For example, consider an index on (a,b) and a query withWHERE a = 4
. The system will assume the index can be used to scan for rows witha = 4
, which is wrong if the index omits rows whereb
is null. It is, however, OK to omit rows where the first indexed column is null. An index access method that does index nulls may also setamsearchnulls
, indicating that it supportsIS NULL
andIS NOT NULL
clauses as search conditions.
amcaninclude
标志指示访问方法是否支持“included”列,即它可以存储(无需处理)除键列之外的其他列。前一段的要求仅适用于键列。特别是,amcanmulticol
=false
和amcaninclude
=true
的组合是有意义的:这意味着只能有一个键列,但也可以有包含的列。此外,必须允许包含的列为 null,与amoptionalkey
无关。
amsummarizing
标志指示访问方法是否总结索引元组,总结粒度至少为每个块。不指向单个元组而是指向块范围的访问方法(如BRIN)可能允许HOT优化继续。这并不适用于索引谓词中引用的属性,对该属性的更新始终会禁用HOT。