=> 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)
常见的创建索引命令如下所示：
CREATEINDEXONaircrafts_data(model,range);
但这只是完整形式的简写：
CREATEINDEXONaircrafts_dataUSINGbtree-- метод доступа по умолчанию
(modeltext_ops,-- класс операторов по умолчанию для text
rangeint4_ops-- класс операторов по умолчанию для integer
);
运算符类 text_pattern_ops 可以克服对 ~~ 运算符（对应 LIKE 语法）支持的限制。在非 C 排序规则的数据库中，该运算符无法使用普通文本列索引：
=> SELECT datcollate FROM pg_database
WHERE datname = current_database();
datcollate
-------------
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)
=> 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)
=> 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)
=> 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)
=> 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)
与返回布尔值的普通索引操作符不同，排序操作符返回一个实数——即一个参数到另一个参数的“距离”。索引支持在 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)
该属性定义索引结构是否能够恢复被索引的值。这并非总是可行；例如，某些索引可能存储的不是实际值，而是其哈希值。在这种情况下，CAN INCLUDE 属性也无法生效。
SEARCH ARRAY 支持对数组中多个值的搜索。
这种需求不仅出现在直接使用数组时。例如，查询规划器会将 IN (список) 结构转换为数组搜索：（另见：554；编辑器添加的链接）
=> 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)
如果索引方法不支持此类操作符，则执行计划时需要多次迭代来查找单个值（这可能影响效率）。
SEARCH NULLS 支持对 IS NULL 和 IS NOT NULL 条件的搜索。
是否应索引空值？这使得可以使用索引处理 IS [NOT] NULL 条件，也可作为无任何表条件时的覆盖索引（因为此时索引必须返回表中所有行的数据，包括空值）。但若不包含空值，索引可能更紧凑。 NULL
=> CREATE INDEX ON flights(actual_arrival)
WHERE actual_arrival IS NOT NULL;
CREATE INDEX
=> 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)
