1--
2-- BTREE_INDEX
3-- test retrieval of min/max keys for each index
4--
5
6SELECT b.*
7   FROM bt_i4_heap b
8   WHERE b.seqno < 1;
9
10SELECT b.*
11   FROM bt_i4_heap b
12   WHERE b.seqno >= 9999;
13
14SELECT b.*
15   FROM bt_i4_heap b
16   WHERE b.seqno = 4500;
17
18SELECT b.*
19   FROM bt_name_heap b
20   WHERE b.seqno < '1'::name;
21
22SELECT b.*
23   FROM bt_name_heap b
24   WHERE b.seqno >= '9999'::name;
25
26SELECT b.*
27   FROM bt_name_heap b
28   WHERE b.seqno = '4500'::name;
29
30SELECT b.*
31   FROM bt_txt_heap b
32   WHERE b.seqno < '1'::text;
33
34SELECT b.*
35   FROM bt_txt_heap b
36   WHERE b.seqno >= '9999'::text;
37
38SELECT b.*
39   FROM bt_txt_heap b
40   WHERE b.seqno = '4500'::text;
41
42SELECT b.*
43   FROM bt_f8_heap b
44   WHERE b.seqno < '1'::float8;
45
46SELECT b.*
47   FROM bt_f8_heap b
48   WHERE b.seqno >= '9999'::float8;
49
50SELECT b.*
51   FROM bt_f8_heap b
52   WHERE b.seqno = '4500'::float8;
53
54--
55-- Check correct optimization of LIKE (special index operator support)
56-- for both indexscan and bitmapscan cases
57--
58
59set enable_seqscan to false;
60set enable_indexscan to true;
61set enable_bitmapscan to false;
62select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
63
64set enable_indexscan to false;
65set enable_bitmapscan to true;
66select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
67
68--
69-- Test B-tree page deletion. In particular, deleting a non-leaf page.
70--
71
72-- First create a tree that's at least four levels deep. The text inserted
73-- is long and poorly compressible. That way only a few index tuples fit on
74-- each page, allowing us to get a tall tree with fewer pages.
75create table btree_tall_tbl(id int4, t text);
76create index btree_tall_idx on btree_tall_tbl (id, t) with (fillfactor = 10);
77insert into btree_tall_tbl
78  select g, g::text || '_' ||
79          (select string_agg(md5(i::text), '_') from generate_series(1, 50) i)
80from generate_series(1, 100) g;
81
82-- Delete most entries, and vacuum. This causes page deletions.
83delete from btree_tall_tbl where id < 950;
84vacuum btree_tall_tbl;
85
86--
87-- Test B-tree insertion with a metapage update (XLOG_BTREE_INSERT_META
88-- WAL record type). This happens when a "fast root" page is split.
89--
90
91-- The vacuum above should've turned the leaf page into a fast root. We just
92-- need to insert some rows to cause the fast root page to split.
93insert into btree_tall_tbl (id, t)
94  select g, repeat('x', 100) from generate_series(1, 500) g;
95