PostgreSQL的B-tree索引用法詳解

demo=# select * from aircrafts;
 aircraft_code |  model  | range
---------------+---------------------+-------
 773   | Boeing 777-300  | 11100
 763   | Boeing 767-300  | 7900
 SU9   | Sukhoi SuperJet-100 | 3000
 320   | Airbus A320-200  | 5700
 321   | Airbus A321-200  | 5600
 319   | Airbus A319-100  | 6700
 733   | Boeing 737-300  | 4200
 CN1   | Cessna 208 Caravan | 1200
 CR2   | Bombardier CRJ-200 | 2700
(9 rows)
demo=# create index on aircrafts(range);
demo=# set enable_seqscan = off;

demo=# explain(costs off) select * from aircrafts where range = 3000;
     QUERY PLAN      
---------------------------------------------------
 Index Scan using aircrafts_range_idx on aircrafts
 Index Cond: (range = 3000)
(2 rows)

demo=# explain(costs off) select * from aircrafts where range  3000;
     QUERY PLAN     
---------------------------------------------------
 Index Scan using aircrafts_range_idx on aircrafts
 Index Cond: (range  3000)
(2 rows)

demo=# explain(costs off) select * from aircrafts
where range between 3000 and 5000;
      QUERY PLAN      
-----------------------------------------------------
 Index Scan using aircrafts_range_idx on aircrafts
 Index Cond: ((range >= 3000) AND (range = 5000))
(2 rows)

demo=# create index on aircrafts(range desc);

demo=# create view aircrafts_v as
select model,
  case
   when range  4000 then 1
   when range  10000 then 2
   else 3
  end as class
from aircrafts; 
 
demo=# select * from aircrafts_v;
  model  | class
---------------------+-------
 Boeing 777-300  |  3
 Boeing 767-300  |  2
 Sukhoi SuperJet-100 |  1
 Airbus A320-200  |  2
 Airbus A321-200  |  2
 Airbus A319-100  |  2
 Boeing 737-300  |  2
 Cessna 208 Caravan |  1
 Bombardier CRJ-200 |  1
(9 rows)

demo=# create index on aircrafts(
 (case when range  4000 then 1 when range  10000 then 2 else 3 end),
 model);

demo=# select class, model from aircrafts_v order by class, model;
 class |  model  
-------+---------------------
  1 | Bombardier CRJ-200
  1 | Cessna 208 Caravan
  1 | Sukhoi SuperJet-100
  2 | Airbus A319-100
  2 | Airbus A320-200
  2 | Airbus A321-200
  2 | Boeing 737-300
  2 | Boeing 767-300
  3 | Boeing 777-300
(9 rows) 
 
demo=# explain(costs off)
select class, model from aircrafts_v order by class, model;
      QUERY PLAN      
--------------------------------------------------------
 Index Scan using aircrafts_case_model_idx on aircrafts
(1 row)

demo=# select class, model from aircrafts_v order by class desc, model desc;
 class |  model  
-------+---------------------
  3 | Boeing 777-300
  2 | Boeing 767-300
  2 | Boeing 737-300
  2 | Airbus A321-200
  2 | Airbus A320-200
  2 | Airbus A319-100
  1 | Sukhoi SuperJet-100
  1 | Cessna 208 Caravan
  1 | Bombardier CRJ-200
(9 rows)
demo=# explain(costs off)
select class, model from aircrafts_v order by class desc, model desc;
       QUERY PLAN       
-----------------------------------------------------------------
 Index Scan BACKWARD using aircrafts_case_model_idx on aircrafts
(1 row)

demo=# explain(costs off)
select class, model from aircrafts_v order by class ASC, model DESC;
     QUERY PLAN     
-------------------------------------------------
 Sort
 Sort Key: (CASE ... END), aircrafts.model DESC
 -> Seq Scan on aircrafts
(3 rows)

demo=# create index aircrafts_case_asc_model_desc_idx on aircrafts(
 (case
 when range  4000 then 1
 when range  10000 then 2
 else 3
 end) ASC,
 model DESC); 
 
demo=# explain(costs off)
select class, model from aircrafts_v order by class ASC, model DESC;
       QUERY PLAN       
-----------------------------------------------------------------
 Index Scan using aircrafts_case_asc_model_desc_idx on aircrafts
(1 row)

demo=# create index on aircrafts(
 model,
 (case when range  4000 then 1 when range  10000 then 2 else 3 end));

demo=# create index on flights(actual_arrival);
demo=# explain(costs off) select * from flights where actual_arrival is null;
      QUERY PLAN      
-------------------------------------------------------
 Bitmap Heap Scan on flights
 Recheck Cond: (actual_arrival IS NULL)
 -> Bitmap Index Scan on flights_actual_arrival_idx
   Index Cond: (actual_arrival IS NULL)
(4 rows)

demo=# explain(costs off)
select * from flights order by actual_arrival NULLS LAST;
      QUERY PLAN      
--------------------------------------------------------
 Index Scan using flights_actual_arrival_idx on flights
(1 row)

demo=# explain(costs off)
select * from flights order by actual_arrival NULLS FIRST;
    QUERY PLAN    
----------------------------------------
 Sort
 Sort Key: actual_arrival NULLS FIRST
 -> Seq Scan on flights
(3 rows)

demo=# create index flights_nulls_first_idx on flights(actual_arrival NULLS FIRST);
demo=# explain(costs off)
select * from flights order by actual_arrival NULLS FIRST;
      QUERY PLAN      
-----------------------------------------------------
 Index Scan using flights_nulls_first_idx on flights
(1 row)

demo=# \pset null NULL
demo=# select null  42;
 ?column?
----------
 NULL
(1 row)

 amname |  name  | pg_indexam_has_property
--------+---------------+-------------------------
 btree | can_order  | t
 btree | can_unique | t
 btree | can_multi_col | t
 btree | can_exclude | t

  name  | pg_index_has_property
---------------+-----------------------
 clusterable | t
 index_scan | t
 bitmap_scan | t
 backward_scan | t

  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

demo=# explain(costs off)
select * from aircrafts where aircraft_code in ('733','763','773');
       QUERY PLAN       
-----------------------------------------------------------------
 Index Scan using aircrafts_pkey on aircrafts
 Index Cond: (aircraft_code = ANY ('{733,763,773}'::bpchar[]))
(2 rows)

demo=# \d bookings
    Table "bookings.bookings"
 Column |   Type   | Modifiers
--------------+--------------------------+-----------
 book_ref  | character(6)    | not null
 book_date | timestamp with time zone | not null
 total_amount | numeric(10,2)   | not null
Indexes:
 "bookings_pkey" PRIMARY KEY, btree (book_ref)
Referenced by:
TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

demo=# create unique index bookings_pkey2 on bookings(book_ref) INCLUDE (book_date);

demo=# begin;
demo=# alter table bookings drop constraint bookings_pkey cascade;
demo=# alter table bookings add primary key using index bookings_pkey2;
demo=# alter table tickets add foreign key (book_ref) references bookings (book_ref);
demo=# commit;

demo=# \d bookings
    Table "bookings.bookings"
 Column |   Type   | Modifiers
--------------+--------------------------+-----------
 book_ref  | character(6)    | not null
 book_date | timestamp with time zone | not null
 total_amount | numeric(10,2)   | not null
Indexes:
 "bookings_pkey2" PRIMARY KEY, btree (book_ref) INCLUDE (book_date)
Referenced by:
TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

demo=# explain(costs off)
select book_ref, book_date from bookings where book_ref = '059FC4';
     QUERY PLAN     
--------------------------------------------------
 Index Only Scan using bookings_pkey2 on bookings
 Index Cond: (book_ref = '059FC4'::bpchar)
(2 rows)

postgres=# select amop.amopopr::regoperator as opfamily_operator,
   amop.amopstrategy
from  pg_am am,
   pg_opfamily opf,
   pg_amop amop
where opf.opfmethod = am.oid
and  amop.amopfamily = opf.oid
and  am.amname = 'btree'
and  opf.opfname = 'bool_ops'
order by amopstrategy;
 opfamily_operator | amopstrategy
---------------------+--------------
 (boolean,boolean) |   1
 =(boolean,boolean) |   2
 =(boolean,boolean) |   3
 >=(boolean,boolean) |   4
 >(boolean,boolean) |   5
(5 rows)

postgres=# select amop.amopopr::regoperator as opfamily_operator
from  pg_am am,
   pg_opfamily opf,
   pg_amop amop
where opf.opfmethod = am.oid
and  amop.amopfamily = opf.oid
and  am.amname = 'btree'
and  opf.opfname = 'integer_ops'
and  amop.amopstrategy = 1
order by opfamily_operator;
 pfamily_operator 
----------------------
 (integer,bigint)
 (smallint,smallint)
 (integer,integer)
 (bigint,bigint)
 (bigint,integer)
 (smallint,integer)
 (integer,smallint)
 (smallint,bigint)
 (bigint,smallint)
(9 rows)

postgres=# create type complex as (re float, im float);

postgres=# create table numbers(x complex);
postgres=# insert into numbers values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0));

postgres=# select * from numbers order by x;
 x 
--------
 (0,10)
 (1,1)
 (1,3)
(3 rows)

postgres=# create function modulus(a complex) returns float as $$
 select sqrt(a.re*a.re + a.im*a.im);
$$ immutable language sql;
 
 
//此時(shí)，使用整個(gè)函數(shù)系統(tǒng)的定義5種操作符：
postgres=# create function complex_lt(a complex, b complex) returns boolean as $$
 select modulus(a)  modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_le(a complex, b complex) returns boolean as $$
 select modulus(a) = modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_eq(a complex, b complex) returns boolean as $$
 select modulus(a) = modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_ge(a complex, b complex) returns boolean as $$
 select modulus(a) >= modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_gt(a complex, b complex) returns boolean as $$
 select modulus(a) > modulus(b);
$$ immutable language sql;

postgres=# create operator ##(leftarg=complex, rightarg=complex, procedure=complex_lt);
postgres=# create operator #=#(leftarg=complex, rightarg=complex, procedure=complex_le);
postgres=# create operator #=#(leftarg=complex, rightarg=complex, procedure=complex_eq);
postgres=# create operator #>=#(leftarg=complex, rightarg=complex, procedure=complex_ge);
postgres=# create operator #>#(leftarg=complex, rightarg=complex, procedure=complex_gt);

postgres=# select (1.0,1.0)::complex ## (1.0,3.0)::complex;
 ?column?
----------
 t
(1 row)

postgres=# create function complex_cmp(a complex, b complex) returns integer as $$
 select case when modulus(a)  modulus(b) then -1
    when modulus(a) > modulus(b) then 1
    else 0
   end;
$$ language sql;

postgres=# create operator class complex_ops
default for type complex
using btree as
 operator 1 ##,
 operator 2 #=#,
 operator 3 #=#,
 operator 4 #>=#,
 operator 5 #>#,
function 1 complex_cmp(complex,complex);
 
//排序結(jié)果：
postgres=# select * from numbers order by x;
 x 
--------
 (1,1)
 (1,3)
 (0,10)
(3 rows)
 
//可以使用此查詢(xún)獲取支持的函數(shù)：
 
postgres=# select amp.amprocnum,
  amp.amproc,
  amp.amproclefttype::regtype,
  amp.amprocrighttype::regtype
from pg_opfamily opf,
  pg_am am,
  pg_amproc amp
where opf.opfname = 'complex_ops'
and opf.opfmethod = am.oid
and am.amname = 'btree'
and amp.amprocfamily = opf.oid;
 amprocnum | amproc | amproclefttype | amprocrighttype
-----------+-------------+----------------+-----------------
   1 | complex_cmp | complex  | complex
(1 row)

demo=# create extension pageinspect;

demo=# select * from bt_metap('ticket_flights_pkey');
 magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
 340322 |  2 | 164 |  2 |  164 |   2
(1 row)

demo=# select type, live_items, dead_items, avg_item_size, page_size, free_size
from bt_page_stats('ticket_flights_pkey',164);
 type | live_items | dead_items | avg_item_size | page_size | free_size
------+------------+------------+---------------+-----------+-----------
 r |   33 |   0 |   31 |  8192 |  6984
(1 row)

demo=# select itemoffset, ctid, itemlen, left(data,56) as data
from bt_page_items('ticket_flights_pkey',164) limit 5;
 itemoffset | ctid | itemlen |       data       
------------+---------+---------+----------------------------------------------------------
   1 | (3,1) |  8 |
   2 | (163,1) |  32 | 1d 30 30 30 35 34 33 32 33 30 35 37 37 31 00 00 ff 5f 00
   3 | (323,1) |  32 | 1d 30 30 30 35 34 33 32 34 32 33 36 36 32 00 00 4f 78 00
   4 | (482,1) |  32 | 1d 30 30 30 35 34 33 32 35 33 30 38 39 33 00 00 4d 1e 00
   5 | (641,1) |  32 | 1d 30 30 30 35 34 33 32 36 35 35 37 38 35 00 00 2b 09 00
(5 rows)