1-- 2-- BTREE_INDEX 3-- test retrieval of min/max keys for each index 4-- 5SELECT b.* 6 FROM bt_i4_heap b 7 WHERE b.seqno < 1; 8 seqno | random 9-------+------------ 10 0 | 1935401906 11(1 row) 12 13SELECT b.* 14 FROM bt_i4_heap b 15 WHERE b.seqno >= 9999; 16 seqno | random 17-------+------------ 18 9999 | 1227676208 19(1 row) 20 21SELECT b.* 22 FROM bt_i4_heap b 23 WHERE b.seqno = 4500; 24 seqno | random 25-------+------------ 26 4500 | 2080851358 27(1 row) 28 29SELECT b.* 30 FROM bt_name_heap b 31 WHERE b.seqno < '1'::name; 32 seqno | random 33-------+------------ 34 0 | 1935401906 35(1 row) 36 37SELECT b.* 38 FROM bt_name_heap b 39 WHERE b.seqno >= '9999'::name; 40 seqno | random 41-------+------------ 42 9999 | 1227676208 43(1 row) 44 45SELECT b.* 46 FROM bt_name_heap b 47 WHERE b.seqno = '4500'::name; 48 seqno | random 49-------+------------ 50 4500 | 2080851358 51(1 row) 52 53SELECT b.* 54 FROM bt_txt_heap b 55 WHERE b.seqno < '1'::text; 56 seqno | random 57-------+------------ 58 0 | 1935401906 59(1 row) 60 61SELECT b.* 62 FROM bt_txt_heap b 63 WHERE b.seqno >= '9999'::text; 64 seqno | random 65-------+------------ 66 9999 | 1227676208 67(1 row) 68 69SELECT b.* 70 FROM bt_txt_heap b 71 WHERE b.seqno = '4500'::text; 72 seqno | random 73-------+------------ 74 4500 | 2080851358 75(1 row) 76 77SELECT b.* 78 FROM bt_f8_heap b 79 WHERE b.seqno < '1'::float8; 80 seqno | random 81-------+------------ 82 0 | 1935401906 83(1 row) 84 85SELECT b.* 86 FROM bt_f8_heap b 87 WHERE b.seqno >= '9999'::float8; 88 seqno | random 89-------+------------ 90 9999 | 1227676208 91(1 row) 92 93SELECT b.* 94 FROM bt_f8_heap b 95 WHERE b.seqno = '4500'::float8; 96 seqno | random 97-------+------------ 98 4500 | 2080851358 99(1 row) 100 101-- 102-- Check correct optimization of LIKE (special index operator support) 103-- for both indexscan and bitmapscan cases 104-- 105set enable_seqscan to false; 106set enable_indexscan to true; 107set enable_bitmapscan to false; 108explain (costs off) 109select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; 110 QUERY PLAN 111------------------------------------------------------------------------------ 112 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc 113 Index Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text)) 114 Filter: (proname ~~ 'RI\_FKey%del'::text) 115(3 rows) 116 117select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; 118 proname 119------------------------ 120 RI_FKey_cascade_del 121 RI_FKey_noaction_del 122 RI_FKey_restrict_del 123 RI_FKey_setdefault_del 124 RI_FKey_setnull_del 125(5 rows) 126 127explain (costs off) 128select proname from pg_proc where proname ilike '00%foo' order by 1; 129 QUERY PLAN 130-------------------------------------------------------------------- 131 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc 132 Index Cond: ((proname >= '00'::text) AND (proname < '01'::text)) 133 Filter: (proname ~~* '00%foo'::text) 134(3 rows) 135 136select proname from pg_proc where proname ilike '00%foo' order by 1; 137 proname 138--------- 139(0 rows) 140 141explain (costs off) 142select proname from pg_proc where proname ilike 'ri%foo' order by 1; 143 QUERY PLAN 144----------------------------------------------------------------- 145 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc 146 Filter: (proname ~~* 'ri%foo'::text) 147(2 rows) 148 149set enable_indexscan to false; 150set enable_bitmapscan to true; 151explain (costs off) 152select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; 153 QUERY PLAN 154------------------------------------------------------------------------------------------ 155 Sort 156 Sort Key: proname 157 -> Bitmap Heap Scan on pg_proc 158 Filter: (proname ~~ 'RI\_FKey%del'::text) 159 -> Bitmap Index Scan on pg_proc_proname_args_nsp_index 160 Index Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text)) 161(6 rows) 162 163select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; 164 proname 165------------------------ 166 RI_FKey_cascade_del 167 RI_FKey_noaction_del 168 RI_FKey_restrict_del 169 RI_FKey_setdefault_del 170 RI_FKey_setnull_del 171(5 rows) 172 173explain (costs off) 174select proname from pg_proc where proname ilike '00%foo' order by 1; 175 QUERY PLAN 176-------------------------------------------------------------------------------- 177 Sort 178 Sort Key: proname 179 -> Bitmap Heap Scan on pg_proc 180 Filter: (proname ~~* '00%foo'::text) 181 -> Bitmap Index Scan on pg_proc_proname_args_nsp_index 182 Index Cond: ((proname >= '00'::text) AND (proname < '01'::text)) 183(6 rows) 184 185select proname from pg_proc where proname ilike '00%foo' order by 1; 186 proname 187--------- 188(0 rows) 189 190explain (costs off) 191select proname from pg_proc where proname ilike 'ri%foo' order by 1; 192 QUERY PLAN 193----------------------------------------------------------------- 194 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc 195 Filter: (proname ~~* 'ri%foo'::text) 196(2 rows) 197 198reset enable_seqscan; 199reset enable_indexscan; 200reset enable_bitmapscan; 201-- Also check LIKE optimization with binary-compatible cases 202create temp table btree_bpchar (f1 text collate "C"); 203create index on btree_bpchar(f1 bpchar_ops) WITH (deduplicate_items=on); 204insert into btree_bpchar values ('foo'), ('fool'), ('bar'), ('quux'); 205-- doesn't match index: 206explain (costs off) 207select * from btree_bpchar where f1 like 'foo'; 208 QUERY PLAN 209------------------------------- 210 Seq Scan on btree_bpchar 211 Filter: (f1 ~~ 'foo'::text) 212(2 rows) 213 214select * from btree_bpchar where f1 like 'foo'; 215 f1 216----- 217 foo 218(1 row) 219 220explain (costs off) 221select * from btree_bpchar where f1 like 'foo%'; 222 QUERY PLAN 223-------------------------------- 224 Seq Scan on btree_bpchar 225 Filter: (f1 ~~ 'foo%'::text) 226(2 rows) 227 228select * from btree_bpchar where f1 like 'foo%'; 229 f1 230------ 231 foo 232 fool 233(2 rows) 234 235-- these do match the index: 236explain (costs off) 237select * from btree_bpchar where f1::bpchar like 'foo'; 238 QUERY PLAN 239---------------------------------------------------- 240 Bitmap Heap Scan on btree_bpchar 241 Filter: ((f1)::bpchar ~~ 'foo'::text) 242 -> Bitmap Index Scan on btree_bpchar_f1_idx 243 Index Cond: ((f1)::bpchar = 'foo'::bpchar) 244(4 rows) 245 246select * from btree_bpchar where f1::bpchar like 'foo'; 247 f1 248----- 249 foo 250(1 row) 251 252explain (costs off) 253select * from btree_bpchar where f1::bpchar like 'foo%'; 254 QUERY PLAN 255------------------------------------------------------------------------------------------ 256 Bitmap Heap Scan on btree_bpchar 257 Filter: ((f1)::bpchar ~~ 'foo%'::text) 258 -> Bitmap Index Scan on btree_bpchar_f1_idx 259 Index Cond: (((f1)::bpchar >= 'foo'::bpchar) AND ((f1)::bpchar < 'fop'::bpchar)) 260(4 rows) 261 262select * from btree_bpchar where f1::bpchar like 'foo%'; 263 f1 264------ 265 foo 266 fool 267(2 rows) 268 269-- get test coverage for "single value" deduplication strategy: 270insert into btree_bpchar select 'foo' from generate_series(1,1500); 271-- 272-- Perform unique checking, with and without the use of deduplication 273-- 274CREATE TABLE dedup_unique_test_table (a int) WITH (autovacuum_enabled=false); 275CREATE UNIQUE INDEX dedup_unique ON dedup_unique_test_table (a) WITH (deduplicate_items=on); 276CREATE UNIQUE INDEX plain_unique ON dedup_unique_test_table (a) WITH (deduplicate_items=off); 277-- Generate enough garbage tuples in index to ensure that even the unique index 278-- with deduplication enabled has to check multiple leaf pages during unique 279-- checking (at least with a BLCKSZ of 8192 or less) 280DO $$ 281BEGIN 282 FOR r IN 1..1350 LOOP 283 DELETE FROM dedup_unique_test_table; 284 INSERT INTO dedup_unique_test_table SELECT 1; 285 END LOOP; 286END$$; 287-- Exercise the LP_DEAD-bit-set tuple deletion code with a posting list tuple. 288-- The implementation prefers deleting existing items to merging any duplicate 289-- tuples into a posting list, so we need an explicit test to make sure we get 290-- coverage (note that this test also assumes BLCKSZ is 8192 or less): 291DROP INDEX plain_unique; 292DELETE FROM dedup_unique_test_table WHERE a = 1; 293INSERT INTO dedup_unique_test_table SELECT i FROM generate_series(0,450) i; 294-- 295-- Test B-tree fast path (cache rightmost leaf page) optimization. 296-- 297-- First create a tree that's at least three levels deep (i.e. has one level 298-- between the root and leaf levels). The text inserted is long. It won't be 299-- TOAST compressed because we use plain storage in the table. Only a few 300-- index tuples fit on each internal page, allowing us to get a tall tree with 301-- few pages. (A tall tree is required to trigger caching.) 302-- 303-- The text column must be the leading column in the index, since suffix 304-- truncation would otherwise truncate tuples on internal pages, leaving us 305-- with a short tree. 306create table btree_tall_tbl(id int4, t text); 307alter table btree_tall_tbl alter COLUMN t set storage plain; 308create index btree_tall_idx on btree_tall_tbl (t, id) with (fillfactor = 10); 309insert into btree_tall_tbl select g, repeat('x', 250) 310from generate_series(1, 130) g; 311-- 312-- Test for multilevel page deletion 313-- 314CREATE TABLE delete_test_table (a bigint, b bigint, c bigint, d bigint); 315INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,80000) i; 316ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d); 317-- Delete most entries, and vacuum, deleting internal pages and creating "fast 318-- root" 319DELETE FROM delete_test_table WHERE a < 79990; 320VACUUM delete_test_table; 321-- 322-- Test B-tree insertion with a metapage update (XLOG_BTREE_INSERT_META 323-- WAL record type). This happens when a "fast root" page is split. This 324-- also creates coverage for nbtree FSM page recycling. 325-- 326-- The vacuum above should've turned the leaf page into a fast root. We just 327-- need to insert some rows to cause the fast root page to split. 328INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i; 329-- Test unsupported btree opclass parameters 330create index on btree_tall_tbl (id int4_ops(foo=1)); 331ERROR: operator class int4_ops has no options 332-- Test case of ALTER INDEX with abuse of column names for indexes. 333-- This grammar is not officially supported, but the parser allows it. 334CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id); 335ALTER INDEX btree_tall_idx2 ALTER COLUMN id SET (n_distinct=100); 336ERROR: "btree_tall_idx2" is not a table, materialized view, or foreign table 337DROP INDEX btree_tall_idx2; 338-- Partitioned index 339CREATE TABLE btree_part (id int4) PARTITION BY RANGE (id); 340CREATE INDEX btree_part_idx ON btree_part(id); 341ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100); 342ERROR: "btree_part_idx" is not a table, materialized view, or foreign table 343DROP TABLE btree_part; 344