接上一篇
目录
"btree_gist"扩展
让我们把问题复杂化。我们扩大了我们卑微的生意，我们打算出租多间小屋：
postgres=# alter table reservations add house_no integer default 1;我们需要改变排他约束，以便将门牌号考虑在内。但是，GiST不支持整数的相等运算：
postgres=# alter table reservations drop constraint reservations_during_excl;
postgres=# alter table reservations add exclude using gist(during with &&, house_no with =);
ERROR: data type integer has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.在这种情况下，“btree_gist”扩展将有所帮助，它增加了对B树固有操作的gist支持。GiST最终可以支持任何运算符，那么我们为什么不让它支持“大于”、“小于”和“相等”运算符呢？
postgres=# create extension btree_gist;
postgres=# alter table reservations add exclude using gist(during with &&, house_no with =);现在，我们仍然无法在（和上一篇文章中提到的）同一日期预订第一个房子：
postgres=# insert into reservations(during, house_no) values ('[2017-05-15, 2017-06-15)', 1);
ERROR: conflicting key value violates exclusion constraint "reservations_during_house_no_excl"但是，我们可以保留第二个（房子的预订记录）：
postgres=# insert into reservations(during, house_no) values ('[2017-05-15, 2017-06-15)', 2);但请注意，尽管GiST可以以某种方式支持“大于”、“小于”和“相等”运算符（就是通过btree-gist扩展），但B-tree仍能更好地实现这一点。因此，只有在本质上需要GiST索引时，才值得使用这种技术，就像我们的示例中那样。
用于全文搜索的RD树
关于全文搜索
让我们从简单介绍PostgreSQL全文搜索开始（如果你知道，可以跳过本节）。
全文搜索的任务是从文档集中选择与搜索查询匹配的文档。（如果有许多匹配的文档，找到最佳匹配很重要，但在此我们将不做任何说明。）
出于搜索目的，文档被转换为特殊类型“tsvector”，其中包含词素及其在文档中的位置。词素是转换成适合搜索形式的单词。例如，单词通常会转换为小写，并且会切断可变结尾（就是一些单词后的s/ty/tion/ed等）：
postgres=# select to_tsvector('There was a crooked man, and he walked a crooked mile');
to_tsvector
-----------------------------------------
'crook':4,10 'man':5 'mile':11 'walk':8
(1 row)我们还可以看到，一些单词（称为停止词）被完全删除（“there”、“was”、“a”、“and”、“he”），因为它们可能出现得太频繁，以至于搜索它们时没有意义。所有这些转换当然都可以配置，但这是另一回事。
搜索查询用另一种类型表示：“tsquery”。大致来说，一个查询由一个或几个连接词连接而成：“and”&，“or”|，“not”！。我们也可以用括号来说明操作的优先级。
postgres=# select to_tsquery('man & (walking | running)');
to_tsquery
----------------------------
'man' & ( 'walk' | 'run' )
(1 row)只有一个匹配运算符@@用于全文搜索。
postgres=# select to_tsvector('There was a crooked man, and he walked a crooked mile') @@ to_tsquery('man & (walking | running)');
?column?
----------
t
(1 row)
postgres=# select to_tsvector('There was a crooked man, and he walked a crooked mile') @@ to_tsquery('man & (going | running)');
?column?
----------
f
(1 row)这些信息现在就足够了。在下一篇以GIN索引为特色的文章中，我们将深入探讨全文搜索。
RD-trees
对于快速全文搜索，首先，表需要存储“tsvector”类型的列（以避免每次搜索时执行代价高昂的转换），其次，必须在此列上建立索引。GiST是一种可能的访问方法。
postgres=# create table ts(doc text, doc_tsv tsvector);
postgres=# create index on ts using gist(doc_tsv);
postgres=# insert into ts(doc) values
('Can a sheet slitter slit sheets?'),
('How many sheets could a sheet slitter slit?'),
('I slit a sheet, a sheet I slit.'),
('Upon a slitted sheet I sit.'),
('Whoever slit the sheets is a good sheet slitter.'),
('I am a sheet slitter.'),
('I slit sheets.'),
('I am the sleekest sheet slitter that ever slit sheets.'),
('She slits the sheet she sits on.');
postgres=# update ts set doc_tsv = to_tsvector(doc);当然，将最后一步（将文档转换为“tsvector”）委托给触发器是很方便的。
postgres=# select * from ts;
-[ RECORD 1 ]----------------------------------------------------
doc | Can a sheet slitter slit sheets?
doc_tsv | 'sheet':3,6 'slit':5 'slitter':4
-[ RECORD 2 ]----------------------------------------------------
doc | How many sheets could a sheet slitter slit?
doc_tsv | 'could':4 'mani':2 'sheet':3,6 'slit':8 'slitter':7
-[ RECORD 3 ]----------------------------------------------------
doc | I slit a sheet, a sheet I slit.
doc_tsv | 'sheet':4,6 'slit':2,8
-[ RECORD 4 ]----------------------------------------------------
doc | Upon a slitted sheet I sit.
doc_tsv | 'sheet':4 'sit':6 'slit':3 'upon':1
-[ RECORD 5 ]----------------------------------------------------
doc | Whoever slit the sheets is a good sheet slitter.
doc_tsv | 'good':7 'sheet':4,8 'slit':2 'slitter':9 'whoever':1
-[ RECORD 6 ]----------------------------------------------------
doc | I am a sheet slitter.
doc_tsv | 'sheet':4 'slitter':5
-[ RECORD 7 ]----------------------------------------------------
doc | I slit sheets.
doc_tsv | 'sheet':3 'slit':2
-[ RECORD 8 ]----------------------------------------------------
doc | I am the sleekest sheet slitter that ever slit sheets.
doc_tsv | 'ever':8 'sheet':5,10 'sleekest':4 'slit':9 'slitter':6
-[ RECORD 9 ]----------------------------------------------------
doc | She slits the sheet she sits on.
doc_tsv | 'sheet':4 'sit':6 'slit':2索引应该如何构造？直接使用R-tree不是一个选项，因为不清楚如何定义文档的“边框”。但我们可以对这种方法进行一些修改，即所谓的RD树（RD代表“俄罗斯玩偶”）。在这种情况下，集合被理解为一组词素，但一般来说，集合可以是任意的。
RD树的一个想法是用一个边界集替换一个矩形框，即一个包含子集合所有元素的集合。
一个重要的问题是如何在索引行中表示集合。最简单的方法就是枚举集合中的所有元素。这可能如下所示：
例如，为了按条件 doc_tsv@@to_tsquery('sit')访问，我们只能下降到包含“sit”词素的节点：
这种说法有明显的问题。文档中的词素数量可能相当大，因此索引行的大小会很大，并且会进入TOAST，这使得索引的效率大大降低。即使每个文档几乎没有唯一的词素，集合的并集也可能非常大：根越高，索引行越大。
这样的表示有时会被使用，但用于其他数据类型。全文搜索使用了另一种更紧凑的解决方案——所谓的签名树。它的想法对所有与布隆过滤器打交道的人来说都很熟悉。
每个词素都可以用它的签名来表示：一个特定长度的位字符串，其中除一位以外的所有位都为零。该位的位置由词素的哈希函数的值决定（我们在前面讨论了哈希函数的内部结构）。
文档签名是所有文档词素签名的位或。
让我们假设以下词素的特征：
could 1000000
ever 0001000
good 0000010
mani 0000100
sheet 0000100
sleekest 0100000
sit 0010000
slit 0001000
slitter 0000001
upon 0000010
whoever 0010000文件的签名如下：
Can a sheet slitter slit sheets? 0001101
How many sheets could a sheet slitter slit? 1001101
I slit a sheet, a sheet I slit. 0001100
Upon a slitted sheet I sit. 0011110
Whoever slit the sheets is a good sheet slitter. 0011111
I am a sheet slitter. 0000101
I slit sheets. 0001100
I am the sleekest sheet slitter that ever slit sheets. 0101101
She slits the sheet she sits on. 0011100索引树可以表示为：
这种方法的优点是显而易见的：索引行大小相等，而且这样的索引很紧凑。但缺点也很明显：精确性被牺牲在紧凑性上。
让我们考虑同样的条件 doc_tsv@@ to_tsquery(“sit”)。让我们以与文档相同的方式计算搜索查询的签名：在本例中为0010000。一致性函数必须返回其签名至少包含查询签名中一位的所有子节点：
与上图相比：我们可以看到多了一个叶节点变为黄色（因为哈希函数有冲突，不同的词素可能对应相同的签名），这意味着在搜索过程中会出现误报，并通过过多的节点。在这里，我们选择了“where”词素，不幸的是，其签名与“sit”词素的签名相同。重要的是，模式中不能出现假阴性，也就是说，我们确保不会错过所需的值。
此外，不同的文档也可能会得到相同的签名：在我们的示例中，文档“我撕开了一张纸，一张纸我撕开了”和“我撕开了几张纸”（两者的签名都是0001100）。如果叶索引行不存储“tsvector”的值，索引本身将给出误报。当然，在这种情况下，该方法将要求索引引擎使用表重查结果，这样用户就不会看到这些误报。但搜索效率可能会受到影响。
实际上，在当前的实现中，签名的大小是124字节，而不是图中的7位，因此与示例中相比，出现上述问题的可能性要小得多。但实际上，更多的文档也被编入了索引。为了以某种方式减少索引方法的误报次数，实现变得有点棘手：被索引的“tsvector”存储在叶索引行中，但前提是它的大小不大（略小于页面的1/16，对于8-KB页面，大约为0.5KB）。
示例
为了了解索引是如何在实际数据上工作的，我们来看看“pgsql-hacker”电子邮件的存档。示例中使用的版本包含356125条消息，其中包含发送日期、主题、作者和文本：
fts=# select * from mail_messages order by sent limit 1;
-[ RECORD 1 ]--------------------------------------------------------
id | 1572389
parent_id | 1562808
sent | 1997-06-24 11:31:09
subject | Re: [HACKERS] Array bug is still there....
author | "Thomas G. Lockhart" <Thomas.Lockhart@jpl.nasa.gov>
body_plain | Andrew Martin wrote: +
| > Just run the regression tests on 6.1 and as I suspected the array bug +
| > is still there. The regression test passes because the expected output+
| > has been fixed to the *wrong* output. +
| +
| OK, I think I understand the current array behavior, which is apparently+
| different than the behavior for v1.0x. +
...添加并填充“tsvector”类型的列，并建立索引。在这里，我们将在to_tsvector函数参数中加入三个值（主题、作者和消息文本），以表明文档不必是一个字段，而是可以由完全不同的任意部分组成。
fts=# alter table mail_messages add column tsv tsvector;
fts=# update mail_messages
set tsv = to_tsvector(subject||' '||author||' '||body_plain);
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
...
UPDATE 356125
fts=# create index on mail_messages using gist(tsv); 正如我们所看到的，由于尺寸太大，一些单词被删除了。但索引最终会被创建，并且可以支持搜索查询：
fts=# explain (analyze, costs off)
select * from mail_messages where tsv @@ to_tsquery('magic & value');
QUERY PLAN
----------------------------------------------------------
Index Scan using mail_messages_tsv_idx on mail_messages
(actual time=0.998..416.335 rows=898 loops=1)
Index Cond: (tsv @@ to_tsquery('magic & value'::text))
Rows Removed by Index Recheck: 7859
Planning time: 0.203 ms
Execution time: 416.492 ms
(5 rows)我们可以看到，除了匹配条件的898行，access方法移除了7859多行，这些行是通过重新检查表筛选出来的。这表明了精度损失对效率的负面影响。
内部构件
为了分析索引的内容，我们将再次使用“gevel”扩展：
fts=# select level, a
from gist_print('mail_messages_tsv_idx') as t(level int, valid bool, a gtsvector)
where a is not null;
level | a
-------+-------------------------------
1 | 992 true bits, 0 false bits
2 | 988 true bits, 4 false bits
3 | 573 true bits, 419 false bits
4 | 65 unique words
4 | 107 unique words
4 | 64 unique words
4 | 42 unique words
...存储在索引行中的专用类型“gtsvector”的值实际上是签名，可能还包括源“tsvector”。如果向量可用，则输出包含词素（唯一词）的数量，否则包含签名中的真位和假位的数量。
很明显，在根节点中，签名退化为“所有都是1”，也就是说，一个索引级别变得完全无用（还有一个几乎无用，只有四个0位）。
属性
让我们看看GiST访问方法的属性：
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
gist | can_order | f
gist | can_unique | f
gist | can_multi_col | t
gist | can_exclude | t不支持值排序（因为该索引组织索引项的依据不是排序，也没有定义关键字的排序方法，区别于以text_pattern使用B树构建的索引）和唯一约束（不同关键字的签名可能会重复而且被分别索引）。正如我们所看到的，索引可以建立在多个列上，并用于排除约束。
以下索引级别的特性可用：
name | pg_index_has_property
---------------+-----------------------
clusterable | t
index_scan | t
bitmap_scan | t
backward_scan | f（此处的可聚集应该是将索引聚集存储，而不涉及对文本的排序吧，只有定义了排序的数据类型，使用聚集命令时，才会先排序？）
最有趣的特性是列层。某些属性独立于运算符类：
name | pg_index_column_has_property
--------------------+------------------------------
asc | f
desc | f
nulls_first | f
nulls_last | f
orderable | f
search_array | f
search_nulls | t（不支持排序；索引不能用于搜索数组；支持空值。）
但剩下的两个属性“distance_orderable”和“returnable”的值将取决于所使用的运算符类。例如，对于点我们将获得：
name | pg_index_column_has_property
--------------------+------------------------------
distance_orderable | t
returnable | t第一个属性表示距离操作符可用于搜索最近的邻居。第二个告诉我们索引可以用于仅索引扫描。虽然叶索引行存储的是矩形而不是点，但访问方法可以返回所需的内容。
以下是区间的属性：
name | pg_index_column_has_property
--------------------+------------------------------
distance_orderable | f
returnable | t对于区间，距离函数未定义，因此无法搜索最近的邻居。
对于全文搜索，我们得到：
name | pg_index_column_has_property
--------------------+------------------------------
distance_orderable | f
returnable | f由于叶行只能包含签名而不包含数据本身，因此对仅索引扫描的支持已丢失。然而，这是一个小损失，因为没有人对类型“tsvector”的值感兴趣：该值用于选择行，而需要显示的是源文本，但索引中仍然缺少。（就是显示关键词是无意义的，我们需要的是这个关键词集合指向的源文本，而源文本也不在索引中）
其他数据类型
最后，除了已经讨论过的几何类型（以点为例）、区间和全文搜索类型之外，我们还将提到GiST访问方法目前支持的其他一些类型。
在标准类型中，“inet”类型是IP地址类型。其余的都是通过扩展添加的：
- cube为多维cube提供“cube”数据类型。对于这种类型，就像平面中的几何类型一样，GiST操作符类被定义为：R树，支持最邻近搜索。
- seg为有边框的区间提供“seg”数据类型，其边界指定为一定精度，并为该数据类型添加GiST索引支持（R树）。
- intarray扩展了整数数组的功能，并为它们添加了GiST支持。实现了两个操作符类：“gist_int_ops”（索引行中键完整表示的RD树）和“gist_bigint_ops”（签名RD树）。第一类可用于小型阵列，第二类可用于大型阵列。（这个和上文说的全文搜索类型很像，只是将词素改为了整数）
- ltree为树状结构添加了“ltree”数据类型，并为该数据类型提供了GiST支持（RD树）。
- pg_trgm添加了一个专门的操作符类“gist_trgm_ops”，用于在全文搜索中使用三角图。但这将与GIN索引一起进一步讨论。
2902
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
