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 96-- 97-- Test vacuum_cleanup_index_scale_factor 98-- 99 100-- Simple create 101create table btree_test(a int); 102create index btree_idx1 on btree_test(a) with (vacuum_cleanup_index_scale_factor = 40.0); 103select reloptions from pg_class WHERE oid = 'btree_idx1'::regclass; 104 105-- Fail while setting improper values 106create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = -10.0); 107create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = 100.0); 108create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = 'string'); 109create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = true); 110 111-- Simple ALTER INDEX 112alter index btree_idx1 set (vacuum_cleanup_index_scale_factor = 70.0); 113select reloptions from pg_class WHERE oid = 'btree_idx1'::regclass; 114