src/test/regress/expected/btree_index.out

   1 --
   2 -- BTREE_INDEX
   3 -- test retrieval of min/max keys for each index
   4 --
   5 SELECT b.*
   6    FROM bt_i4_heap b
   7    WHERE b.seqno < 1;
   8  seqno |   random
   9 -------+------------
  10      0 | 1935401906
  11 (1 row)
  12
  13 SELECT b.*
  14    FROM bt_i4_heap b
  15    WHERE b.seqno >= 9999;
  16  seqno |   random
  17 -------+------------
  18   9999 | 1227676208
  19 (1 row)
  20
  21 SELECT b.*
  22    FROM bt_i4_heap b
  23    WHERE b.seqno = 4500;
  24  seqno |   random
  25 -------+------------
  26   4500 | 2080851358
  27 (1 row)
  28
  29 SELECT b.*
  30    FROM bt_name_heap b
  31    WHERE b.seqno < '1'::name;
  32  seqno |   random
  33 -------+------------
  34  0     | 1935401906
  35 (1 row)
  36
  37 SELECT b.*
  38    FROM bt_name_heap b
  39    WHERE b.seqno >= '9999'::name;
  40  seqno |   random
  41 -------+------------
  42  9999  | 1227676208
  43 (1 row)
  44
  45 SELECT b.*
  46    FROM bt_name_heap b
  47    WHERE b.seqno = '4500'::name;
  48  seqno |   random
  49 -------+------------
  50  4500  | 2080851358
  51 (1 row)
  52
  53 SELECT b.*
  54    FROM bt_txt_heap b
  55    WHERE b.seqno < '1'::text;
  56  seqno |   random
  57 -------+------------
  58  0     | 1935401906
  59 (1 row)
  60
  61 SELECT b.*
  62    FROM bt_txt_heap b
  63    WHERE b.seqno >= '9999'::text;
  64  seqno |   random
  65 -------+------------
  66  9999  | 1227676208
  67 (1 row)
  68
  69 SELECT b.*
  70    FROM bt_txt_heap b
  71    WHERE b.seqno = '4500'::text;
  72  seqno |   random
  73 -------+------------
  74  4500  | 2080851358
  75 (1 row)
  76
  77 SELECT b.*
  78    FROM bt_f8_heap b
  79    WHERE b.seqno < '1'::float8;
  80  seqno |   random
  81 -------+------------
  82      0 | 1935401906
  83 (1 row)
  84
  85 SELECT b.*
  86    FROM bt_f8_heap b
  87    WHERE b.seqno >= '9999'::float8;
  88  seqno |   random
  89 -------+------------
  90   9999 | 1227676208
  91 (1 row)
  92
  93 SELECT 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 --
 105 set enable_seqscan to false;
 106 set enable_indexscan to true;
 107 set enable_bitmapscan to false;
 108 explain (costs off)
 109 select 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
 117 select 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
 127 explain (costs off)
 128 select 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
 136 select proname from pg_proc where proname ilike '00%foo' order by 1;
 137  proname
 138 ---------
 139 (0 rows)
 140
 141 explain (costs off)
 142 select 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
 149 set enable_indexscan to false;
 150 set enable_bitmapscan to true;
 151 explain (costs off)
 152 select 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
 163 select 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
 173 explain (costs off)
 174 select 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
 185 select proname from pg_proc where proname ilike '00%foo' order by 1;
 186  proname
 187 ---------
 188 (0 rows)
 189
 190 explain (costs off)
 191 select 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
 198 reset enable_seqscan;
 199 reset enable_indexscan;
 200 reset enable_bitmapscan;
 201 -- Also check LIKE optimization with binary-compatible cases
 202 create temp table btree_bpchar (f1 text collate "C");
 203 create index on btree_bpchar(f1 bpchar_ops) WITH (deduplicate_items=on);
 204 insert into btree_bpchar values ('foo'), ('fool'), ('bar'), ('quux');
 205 -- doesn't match index:
 206 explain (costs off)
 207 select * 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
 214 select * from btree_bpchar where f1 like 'foo';
 215  f1
 216 -----
 217  foo
 218 (1 row)
 219
 220 explain (costs off)
 221 select * 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
 228 select * from btree_bpchar where f1 like 'foo%';
 229   f1
 230 ------
 231  foo
 232  fool
 233 (2 rows)
 234
 235 -- these do match the index:
 236 explain (costs off)
 237 select * 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
 246 select * from btree_bpchar where f1::bpchar like 'foo';
 247  f1
 248 -----
 249  foo
 250 (1 row)
 251
 252 explain (costs off)
 253 select * 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
 262 select * 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:
 270 insert into btree_bpchar select 'foo' from generate_series(1,1500);
 271 --
 272 -- Perform unique checking, with and without the use of deduplication
 273 --
 274 CREATE TABLE dedup_unique_test_table (a int) WITH (autovacuum_enabled=false);
 275 CREATE UNIQUE INDEX dedup_unique ON dedup_unique_test_table (a) WITH (deduplicate_items=on);
 276 CREATE 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)
 280 DO $$
 281 BEGIN
 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;
 286 END$$;
 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):
 291 DROP INDEX plain_unique;
 292 DELETE FROM dedup_unique_test_table WHERE a = 1;
 293 INSERT 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.
 306 create table btree_tall_tbl(id int4, t text);
 307 alter table btree_tall_tbl alter COLUMN t set storage plain;
 308 create index btree_tall_idx on btree_tall_tbl (t, id) with (fillfactor = 10);
 309 insert into btree_tall_tbl select g, repeat('x', 250)
 310 from generate_series(1, 130) g;
 311 --
 312 -- Test for multilevel page deletion
 313 --
 314 CREATE TABLE delete_test_table (a bigint, b bigint, c bigint, d bigint);
 315 INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,80000) i;
 316 ALTER 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"
 319 DELETE FROM delete_test_table WHERE a < 79990;
 320 VACUUM 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.
 328 INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
 329 -- Test unsupported btree opclass parameters
 330 create index on btree_tall_tbl (id int4_ops(foo=1));
 331 ERROR:  operator class int4_ops has no options