=> SELECT data
FROM bt_page_items('bookings_pkey',290)
WHERE itemoffset = 2;
data
-------------------------
0f 30 43 39 41 42 31 00
(1 row)
为解码值，需编写一个临时函数。该函数并非在所有情况下或所有平台上均适用，但适用于本章示例。
=> CREATE FUNCTION data_to_text(data text)
RETURNS text
AS $$
DECLARE
raw bytea := ('\x'||replace(data,' ',''))::bytea;
pos integer := 0;
len integer;
res text := '';
BEGIN
WHILE (octet_length(raw) > pos) LOOP
len := (get_byte(raw,pos) - 3) / 2;
EXIT WHEN len <= 0;
IF pos > 0 THEN
res := res || ', ';
END IF;
res := res || (
SELECT string_agg( chr(get_byte(raw, i)),'')
FROM generate_series(pos+1,pos+len) i
);
pos := pos + len + 1;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;
=> EXPLAIN (costs off) SELECT *
FROM bookings WHERE book_ref = 'E2D725';
QUERY PLAN
---------------------------------------------
Index Scan using bookings_pkey on bookings
Index Cond: (book_ref = 'E2D725'::bpchar)
(2 rows)
=> EXPLAIN (costs off) SELECT *
FROM tickets
WHERE book_ref = '000010';
QUERY PLAN
--------------------------------------------------
Index Scan using tickets_book_ref_idx on tickets
Index Cond: (book_ref = '000010'::bpchar)
(2 rows)
=> EXPLAIN (costs off) SELECT *
FROM tickets
WHERE book_ref = '000010' AND passenger_name = 'LYUDMILA BOGDANOVA';
QUERY PLAN
---------------------------------------------------------------------
Index Scan using tickets_bref_name_idx on tickets
Index Cond: ((book_ref = '000010'::bpchar) AND (passenger_name...
(2 rows)
但若仅按乘客姓名查找，则必须遍历所有行：
=> EXPLAIN (costs off) SELECT *
FROM tickets
WHERE passenger_name = 'LYUDMILA BOGDANOVA';
QUERY PLAN
-------------------------------------------------------------
Gather
Workers Planned: 2
-> Parallel Seq Scan on tickets
Filter: (passenger_name = 'LYUDMILA BOGDANOVA'::text)
(4 rows)
=> EXPLAIN (costs off) SELECT *
FROM tickets ORDER BY passenger_name, book_ref;
QUERY PLAN
---------------------------------------------------
Index Scan using tickets_name_bref_idx on tickets
(1 row)
=> EXPLAIN (costs off) SELECT *
FROM tickets ORDER BY passenger_name DESC, book_ref DESC;
QUERY PLAN
------------------------------------------------------------
Index Scan Backward using tickets_name_bref_idx on tickets
(1 row)
=> EXPLAIN (costs off) SELECT *
FROM tickets ORDER BY passenger_name ASC, book_ref DESC;
QUERY PLAN
--------------------------------------------------------
Incremental Sort
Sort Key: passenger_name, book_ref DESC
Presorted Key: passenger_name
-> Index Scan using tickets_name_bref_idx on tickets
(4 rows)
使用索引进行排序的能力也受未定义值位置的影响。默认情况下，未定义值在排序时被视为‘大于’普通值，即在升序排序时位于树的右侧，或在降序排序时位于左侧。此顺序可通过 NULLS LAST 和 NULLS FIRST 子句进行更改。
在以下示例中，索引不满足 ORDER BY 子句，因此结果需要额外排序：
=> EXPLAIN (costs off) SELECT *
FROM tickets ORDER BY passenger_name NULLS FIRST, book_ref DESC;
QUERY PLAN
-------------------------------------------------------
Sort
Sort Key: passenger_name NULLS FIRST, book_ref DESC
-> Seq Scan on tickets
(3 rows)
但如果创建一个符合所需顺序的索引，它将被使用：
=> CREATE INDEX tickets_name_bref_idx2
ON tickets(passenger_name NULLS FIRST, book_ref DESC);
=> EXPLAIN (costs off) SELECT *
FROM tickets ORDER BY passenger_name NULLS FIRST, book_ref DESC;
QUERY PLAN
----------------------------------------------------
Index Scan using tickets_name_bref_idx2 on tickets
(1 row)
为了更好地理解此机制，创建一个新数据类型并为其设置与默认顺序不同的排序顺序是有帮助的。文档中给出了复数的示例14，但使用的是 C 语言。幸运的是，B-树操作符类也可以使用解释型语言创建，我将采用这种方式，使此示例尽可能简单（尽管显然效率低下）。
我们将创建一个用于信息单位的新复合类型：
=> CREATE TYPE capacity_units AS ENUM (
'B', 'kB', 'MB', 'GB', 'TB', 'PB'
);
=> CREATE TYPE capacity AS (
amount integer,
unit capacity_units
);
现在创建一个包含新类型列的表，并用随机值填充它：
=> CREATE TABLE test AS
SELECT ( (random()*1023)::integer, u.unit )::capacity AS cap
FROM generate_series(1,100),
unnest(enum_range(NULL::capacity_units)) AS u(unit);
默认情况下，复合类型的值按字典序排序，而在此情况下，这与自然顺序不一致：
=> SELECT * FROM test ORDER BY cap;
cap
-----------
(1,MB)
(2,kB)
(3,MB)
(7,GB)
...
(1018,TB)
(1020,B)
(1020,kB)
(1021,PB)
(600 rows)
=> CREATE FUNCTION capacity_cmp(a capacity, b capacity)
RETURNS integer
AS $$
SELECT sign(bookings.capacity_to_bytes(a) - bookings.capacity_to_bytes(b));
$$ LANGUAGE sql STRICT IMMUTABLE;
现在，借助基准函数可以轻松定义比较操作符。我特意使用了‘奇怪’的名称，以说明它们可以是任意的：
=> CREATE FUNCTION capacity_lt(a capacity, b capacity) RETURNS boolean
AS $$
BEGIN
RETURN capacity_cmp(a,b) < 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
=> CREATE OPERATOR #<# (
LEFTARG = capacity,
RIGHTARG = capacity,
FUNCTION = capacity_lt
);
其余四个操作符按类似方式定义。
=> CREATE FUNCTION capacity_le(a capacity, b capacity) RETURNS boolean
AS $$
BEGIN
RETURN capacity_cmp(a,b) <= 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
=> CREATE OPERATOR #<=# (
LEFTARG = capacity,
RIGHTARG = capacity,
FUNCTION = capacity_le
);
=> CREATE FUNCTION capacity_eq(a capacity, b capacity) RETURNS boolean
AS $$
BEGIN
RETURN capacity_cmp(a,b) = 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
=> CREATE OPERATOR #=# (
LEFTARG = capacity,
RIGHTARG = capacity,
FUNCTION = capacity_eq,
MERGES -- можно использовать в соединении слиянием
);
=> CREATE FUNCTION capacity_ge(a capacity, b capacity) RETURNS boolean
AS $$
BEGIN
RETURN capacity_cmp(a,b) >= 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
=> CREATE OPERATOR #>=# (
LEFTARG = capacity,
RIGHTARG = capacity,
FUNCTION = capacity_ge
);
=> CREATE FUNCTION capacity_gt(a capacity, b capacity) RETURNS boolean
AS $$
BEGIN
RETURN capacity_cmp(a,b) > 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
=> CREATE OPERATOR #># (
LEFTARG = capacity,
RIGHTARG = capacity,
FUNCTION = capacity_gt
);
=> CREATE OPERATOR CLASS capacity_ops
DEFAULT FOR TYPE capacity -- будет использоваться по умолчанию
USING btree AS
OPERATOR 1 #<#,
OPERATOR 2 #<=#,
OPERATOR 3 #=#,
OPERATOR 4 #>=#,
OPERATOR 5 #>#,
FUNCTION 1 capacity_cmp(capacity,capacity);
=> SELECT * FROM test ORDER BY cap;
cap
-----------
(9,B)
(30,B)
(42,B)
(47,B)
...
(999,PB)
(1007,PB)
(1015,PB)
(1021,PB)
(600 rows)
我们的操作符类在创建索引时默认使用，创建的索引将返回正确顺序的值：
=> CREATE INDEX ON test(cap);
=> SELECT * FROM test WHERE cap #<# (100,'B')::capacity ORDER BY cap;
cap
--------
(9,B)
(30,B)
(42,B)
(47,B)
(66,B)
(67,B)
(69,B)
(97,B)
(99,B)
(9 rows)
=> EXPLAIN (costs off)
SELECT * FROM test WHERE cap #<# (100,'B')::capacity ORDER BY cap;
QUERY PLAN
---------------------------------------------
Index Only Scan using test_cap_idx on test
Index Cond: (cap #<# '(100,B)'::capacity)
(2 rows)
=> EXPLAIN (costs off) SELECT *
FROM bookings ORDER BY book_ref DESC;
QUERY PLAN
-----------------------------------------------------
Index Scan Backward using bookings_pkey on bookings
(1 row)
列级属性
ORDERABLE 属性表明 B-树以有序方式存储数据，前四个属性（ASC 和 DESC，NULLS FIRST 和 NULLS LAST）定义了该列值的具体排序方式。在以下示例中，列值按升序排序，而未定义值位于末尾。
=> SELECT p.name,
pg_index_column_has_property('flights_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)
