=> SELECT opcname, opcdefault
FROM pg_am am
JOIN pg_opclass opc ON opcmethod = am.oid
WHERE amname = 'btree'
AND opcintype = 'text'::regtype;
opcname | opcdefault
−−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−
text_ops | t
varchar_ops | f
text_pattern_ops | f
varchar_pattern_ops | f
(4 rows)
一个典型的索引创建命令如下所示：
CREATE INDEX ON aircrafts(model, range);
但这只是一种简写，它可以展开为以下语法：
CREATE INDEX ON aircrafts
USING btree -- the default access method
(
model text_ops, -- the default operator class for text
range int4_ops -- the default operator class for integer
);
text_pattern_ops 操作符类旨在解决对 ‘~~’ 操作符 (对应于 LIKE 操作符) 的支持限制。在使用非 C 排序规则的数据库中，这个操作符不能使用文本字段上的常规索引。
=> SHOW lc_collate;
lc_collate
−−−−−−−−−−−−−
en_US.UTF−8
(1 row)
=> CREATE INDEX ON tickets(passenger_name);
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE passenger_name LIKE 'ELENA%';
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on tickets
Filter: (passenger_name ~~ 'ELENA%'::text)
(2 rows)
使用 text_pattern_ops 操作符类的索引行为有所不同：
=> CREATE INDEX tickets_passenger_name_pattern_idx
ON tickets(passenger_name text_pattern_ops);
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE passenger_name LIKE 'ELENA%';
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Bitmap Heap Scan on tickets
Filter: (passenger_name ~~ 'ELENA%'::text)
−> Bitmap Index Scan on tickets_passenger_name_pattern_idx
Index Cond: ((passenger_name ~>=~ 'ELENA'::text) AND
(passenger_name ~<~ 'ELENB'::text))
(5 rows)
注意 Index Cond 中过滤表达式的变化。搜索现在仅使用 % 之前的模板前缀，而 false-positive 会在基于 Filter 条件的重新检查期间被过滤掉。btree 访问方法的操作符类没有提供用于比较模板的操作符，而使用 B 树的唯一方法是使用比较操作符重写此条件。text_pattern_ops 类的操作符不考虑排序规则，这让我们有机会使用等价条件代替。5
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE 'ELENA BELOVA' = passenger_name;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using tickets_passenger_name_idx on tickets
Index Cond: (passenger_name = 'ELENA BELOVA'::text)
(2 rows)
请注意 Index Cond 条件中参数的位置：在执行阶段，索引字段必须在左侧。当参数被置换时，操作符被一个可交换的操作符替换；在此例中，它是相同的操作符，因为等值关系是可交换的。
在接下来的查询中，由于条件中的列名被函数调用替换，因此技术上不可能使用常规索引：
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE initcap(passenger_name) = 'Elena Belova';
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on tickets
Filter: (initcap(passenger_name) = 'Elena Belova'::text)
(2 rows)
=> SELECT opcname, opcintype::regtype
FROM pg_am am
JOIN pg_opfamily opf ON opfmethod = am.oid
JOIN pg_opclass opc ON opcfamily = opf.oid
WHERE amname = 'btree'
AND opfname = 'integer_ops';
opcname | opcintype
−−−−−−−−−−+−−−−−−−−−−−
int2_ops | smallint
int4_ops | integer
int8_ops | bigint
(3 rows)
datetime_ops 族包含用于处理日期的操作符类：
=> SELECT opcname, opcintype::regtype
FROM pg_am am
JOIN pg_opfamily opf ON opfmethod = am.oid
JOIN pg_opclass opc ON opcfamily = opf.oid
WHERE amname = 'btree'
AND opfname = 'datetime_ops';
opcname | opcintype
−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
date_ops | date
timestamptz_ops | timestamp with time zone
timestamp_ops | timestamp without time zone
(3 rows)
虽然每个操作符类支持单一数据类型，但一个族可以包含用于不同数据类型的操作符类：
=> SELECT opcname, amopopr::regoperator
FROM pg_am am
JOIN pg_opfamily opf ON opfmethod = am.oid
JOIN pg_opclass opc ON opcfamily = opf.oid
JOIN pg_amop amop ON amopfamily = opcfamily
WHERE amname = 'btree'
AND opfname = 'integer_ops'
AND amoplefttype = 'integer'::regtype
AND amopstrategy = 1
ORDER BY opcname;
opcname | amopopr
−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−
int2_ops | <(integer,bigint)
int2_ops | <(integer,smallint)
int2_ops | <(integer,integer)
int4_ops | <(integer,bigint)
int4_ops | <(integer,smallint)
int4_ops | <(integer,integer)
int8_ops | <(integer,bigint)
int8_ops | <(integer,smallint)
int8_ops | <(integer,integer)
(9 rows)
=> EXPLAIN (costs off)
SELECT * FROM seats ORDER BY seat_no;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−
Sort
Sort Key: seat_no
−> Seq Scan on seats
(3 rows)
但是如果有支持该属性的索引，那么数据可以立即按照所需顺序返回：
=> EXPLAIN (costs off)
SELECT * FROM seats ORDER BY aircraft_code;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using seats_pkey on seats
(1 row)
=> EXPLAIN (costs off)
SELECT * FROM ticket_flights
WHERE ticket_no = '0005432001355'
AND flight_id = 51618;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using ticket_flights_pkey on ticket_flights
Index Cond: ((ticket_no = '0005432001355'::bpchar) AND
(flight_id = 51618))
(3 rows)
通常情况下，即使过滤条件只涉及其某些列，多列索引也可以加速搜索。对于 B 树，如果过滤条件涵盖了索引声明中最先出现的相关列，搜索将会很高效：
=> EXPLAIN (costs off)
SELECT *
FROM ticket_flights
WHERE ticket_no = '0005432001355';
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using ticket_flights_pkey on ticket_flights
Index Cond: (ticket_no = '0005432001355'::bpchar)
(2 rows)
=> CREATE UNIQUE INDEX ON flights(flight_id) INCLUDE (status);
=> EXPLAIN (costs off)
SELECT status FROM flights
WHERE flight_id = 51618;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Only Scan using flights_flight_id_status_idx on flights
Index Cond: (flight_id = 51618)
(2 rows)
19.3.2 索引级属性
以下是与索引相关的属性 (显示现有索引)：
=> SELECT p.name, pg_index_has_property('seats_pkey', 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：根据索引扫描返回的 ID 顺序物理移动堆元组的能力。此属性显示是否支持 CLUSTER 命令。
INDEX SCAN：支持索引扫描。这个属性意味着访问方法可以逐个返回 TIDs。尽管看起来奇怪，但有些索引不提供这个功能。
=> SELECT p.name,
pg_index_column_has_property('seats_pkey', 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：用于排序列值。
这些属性定义了列值应按升序还是降序存储，以及空值是应该出现在常规值之前还是之后。所有这些属性仅适用于 B 树。
与返回逻辑值的常规索引操作符不同，排序操作符返回了一个实数，表示从一个参数到另一个参数的"距离"。索引支持查询的 ORDER BY 子句中指定的此类操作符。
例如，排序操作符 <-> 可以找到距指定点最近的机场：
=> CREATE INDEX ON airports_data USING gist(coordinates);
=> EXPLAIN (costs off)
SELECT * FROM airports
ORDER BY coordinates <-> point (43.578,57.593)
LIMIT 3;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Limit
−> Index Scan using airports_data_coordinates_idx on airpo...
Order By: (coordinates <−> '(43.578,57.593)'::point)
(3 rows)
RETURNABLE：无需访问表即可返回数据的能力 (支持仅索引扫描)。
这个属性定义了索引结构是否允许检索索引值。这并不总是可能的：例如，某些索引可能存储哈希码而不是实际的值。在这种情况下，CAN INCLUDE 属性也将不可用。
SEARCH ARRAY：支持在数组中搜索多个元素。
数组的显式使用并不是唯一可能需要的情况。例如，规划器将 IN (list) 表达式转换为数组扫描：
=> EXPLAIN (costs off)
SELECT * FROM bookings
WHERE book_ref IN ('C7C821', 'A5D060', 'DDE1BB');
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using bookings_pkey on bookings
Index Cond: (book_ref = ANY
('{C7C821,A5D060,DDE1BB}'::bpchar[]))
(3 rows)
=> CREATE INDEX ON flights(actual_arrival)
WHERE actual_arrival IS NOT NULL;
=> EXPLAIN (costs off)
SELECT * FROM flights
WHERE actual_arrival = '2017-06-13 10:33:00+03';
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using flights_actual_arrival_idx on flights
Index Cond: (actual_arrival = '2017−06−13 10:33:00+03'::ti...
(2 rows)
