summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/btree_index.out
blob: 93ed5e8cc00ea08463cb46840c7628c37ce27e82 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
--
-- BTREE_INDEX
--
-- directory paths are passed to us in environment variables
\getenv abs_srcdir PG_ABS_SRCDIR
CREATE TABLE bt_i4_heap (
	seqno 		int4,
	random 		int4
);
CREATE TABLE bt_name_heap (
	seqno 		name,
	random 		int4
);
CREATE TABLE bt_txt_heap (
	seqno 		text,
	random 		int4
);
CREATE TABLE bt_f8_heap (
	seqno 		float8,
	random 		int4
);
\set filename :abs_srcdir '/data/desc.data'
COPY bt_i4_heap FROM :'filename';
\set filename :abs_srcdir '/data/hash.data'
COPY bt_name_heap FROM :'filename';
\set filename :abs_srcdir '/data/desc.data'
COPY bt_txt_heap FROM :'filename';
\set filename :abs_srcdir '/data/hash.data'
COPY bt_f8_heap FROM :'filename';
ANALYZE bt_i4_heap;
ANALYZE bt_name_heap;
ANALYZE bt_txt_heap;
ANALYZE bt_f8_heap;
--
-- BTREE ascending/descending cases
--
-- we load int4/text from pure descending data (each key is a new
-- low key) and name/f8 from pure ascending data (each key is a new
-- high key).  we had a bug where new low keys would sometimes be
-- "lost".
--
CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
--
-- test retrieval of min/max keys for each index
--
SELECT b.*
   FROM bt_i4_heap b
   WHERE b.seqno < 1;
 seqno |   random   
-------+------------
     0 | 1935401906
(1 row)

SELECT b.*
   FROM bt_i4_heap b
   WHERE b.seqno >= 9999;
 seqno |   random   
-------+------------
  9999 | 1227676208
(1 row)

SELECT b.*
   FROM bt_i4_heap b
   WHERE b.seqno = 4500;
 seqno |   random   
-------+------------
  4500 | 2080851358
(1 row)

SELECT b.*
   FROM bt_name_heap b
   WHERE b.seqno < '1'::name;
 seqno |   random   
-------+------------
 0     | 1935401906
(1 row)

SELECT b.*
   FROM bt_name_heap b
   WHERE b.seqno >= '9999'::name;
 seqno |   random   
-------+------------
 9999  | 1227676208
(1 row)

SELECT b.*
   FROM bt_name_heap b
   WHERE b.seqno = '4500'::name;
 seqno |   random   
-------+------------
 4500  | 2080851358
(1 row)

SELECT b.*
   FROM bt_txt_heap b
   WHERE b.seqno < '1'::text;
 seqno |   random   
-------+------------
 0     | 1935401906
(1 row)

SELECT b.*
   FROM bt_txt_heap b
   WHERE b.seqno >= '9999'::text;
 seqno |   random   
-------+------------
 9999  | 1227676208
(1 row)

SELECT b.*
   FROM bt_txt_heap b
   WHERE b.seqno = '4500'::text;
 seqno |   random   
-------+------------
 4500  | 2080851358
(1 row)

SELECT b.*
   FROM bt_f8_heap b
   WHERE b.seqno < '1'::float8;
 seqno |   random   
-------+------------
     0 | 1935401906
(1 row)

SELECT b.*
   FROM bt_f8_heap b
   WHERE b.seqno >= '9999'::float8;
 seqno |   random   
-------+------------
  9999 | 1227676208
(1 row)

SELECT b.*
   FROM bt_f8_heap b
   WHERE b.seqno = '4500'::float8;
 seqno |   random   
-------+------------
  4500 | 2080851358
(1 row)

--
-- Check correct optimization of LIKE (special index operator support)
-- for both indexscan and bitmapscan cases
--
set enable_seqscan to false;
set enable_indexscan to true;
set enable_bitmapscan to false;
explain (costs off)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc
   Index Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text))
   Filter: (proname ~~ 'RI\_FKey%del'::text)
(3 rows)

select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
        proname         
------------------------
 RI_FKey_cascade_del
 RI_FKey_noaction_del
 RI_FKey_restrict_del
 RI_FKey_setdefault_del
 RI_FKey_setnull_del
(5 rows)

explain (costs off)
select proname from pg_proc where proname ilike '00%foo' order by 1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc
   Index Cond: ((proname >= '00'::text) AND (proname < '01'::text))
   Filter: (proname ~~* '00%foo'::text)
(3 rows)

select proname from pg_proc where proname ilike '00%foo' order by 1;
 proname 
---------
(0 rows)

explain (costs off)
select proname from pg_proc where proname ilike 'ri%foo' order by 1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc
   Filter: (proname ~~* 'ri%foo'::text)
(2 rows)

set enable_indexscan to false;
set enable_bitmapscan to true;
explain (costs off)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Sort
   Sort Key: proname
   ->  Bitmap Heap Scan on pg_proc
         Filter: (proname ~~ 'RI\_FKey%del'::text)
         ->  Bitmap Index Scan on pg_proc_proname_args_nsp_index
               Index Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text))
(6 rows)

select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
        proname         
------------------------
 RI_FKey_cascade_del
 RI_FKey_noaction_del
 RI_FKey_restrict_del
 RI_FKey_setdefault_del
 RI_FKey_setnull_del
(5 rows)

explain (costs off)
select proname from pg_proc where proname ilike '00%foo' order by 1;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Sort
   Sort Key: proname
   ->  Bitmap Heap Scan on pg_proc
         Filter: (proname ~~* '00%foo'::text)
         ->  Bitmap Index Scan on pg_proc_proname_args_nsp_index
               Index Cond: ((proname >= '00'::text) AND (proname < '01'::text))
(6 rows)

select proname from pg_proc where proname ilike '00%foo' order by 1;
 proname 
---------
(0 rows)

explain (costs off)
select proname from pg_proc where proname ilike 'ri%foo' order by 1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc
   Filter: (proname ~~* 'ri%foo'::text)
(2 rows)

reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
-- Also check LIKE optimization with binary-compatible cases
create temp table btree_bpchar (f1 text collate "C");
create index on btree_bpchar(f1 bpchar_ops) WITH (deduplicate_items=on);
insert into btree_bpchar values ('foo'), ('fool'), ('bar'), ('quux');
-- doesn't match index:
explain (costs off)
select * from btree_bpchar where f1 like 'foo';
          QUERY PLAN           
-------------------------------
 Seq Scan on btree_bpchar
   Filter: (f1 ~~ 'foo'::text)
(2 rows)

select * from btree_bpchar where f1 like 'foo';
 f1  
-----
 foo
(1 row)

explain (costs off)
select * from btree_bpchar where f1 like 'foo%';
           QUERY PLAN           
--------------------------------
 Seq Scan on btree_bpchar
   Filter: (f1 ~~ 'foo%'::text)
(2 rows)

select * from btree_bpchar where f1 like 'foo%';
  f1  
------
 foo
 fool
(2 rows)

-- these do match the index:
explain (costs off)
select * from btree_bpchar where f1::bpchar like 'foo';
                     QUERY PLAN                     
----------------------------------------------------
 Bitmap Heap Scan on btree_bpchar
   Filter: ((f1)::bpchar ~~ 'foo'::text)
   ->  Bitmap Index Scan on btree_bpchar_f1_idx
         Index Cond: ((f1)::bpchar = 'foo'::bpchar)
(4 rows)

select * from btree_bpchar where f1::bpchar like 'foo';
 f1  
-----
 foo
(1 row)

explain (costs off)
select * from btree_bpchar where f1::bpchar like 'foo%';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on btree_bpchar
   Filter: ((f1)::bpchar ~~ 'foo%'::text)
   ->  Bitmap Index Scan on btree_bpchar_f1_idx
         Index Cond: (((f1)::bpchar >= 'foo'::bpchar) AND ((f1)::bpchar < 'fop'::bpchar))
(4 rows)

select * from btree_bpchar where f1::bpchar like 'foo%';
  f1  
------
 foo
 fool
(2 rows)

-- get test coverage for "single value" deduplication strategy:
insert into btree_bpchar select 'foo' from generate_series(1,1500);
--
-- Perform unique checking, with and without the use of deduplication
--
CREATE TABLE dedup_unique_test_table (a int) WITH (autovacuum_enabled=false);
CREATE UNIQUE INDEX dedup_unique ON dedup_unique_test_table (a) WITH (deduplicate_items=on);
CREATE UNIQUE INDEX plain_unique ON dedup_unique_test_table (a) WITH (deduplicate_items=off);
-- Generate enough garbage tuples in index to ensure that even the unique index
-- with deduplication enabled has to check multiple leaf pages during unique
-- checking (at least with a BLCKSZ of 8192 or less)
DO $$
BEGIN
    FOR r IN 1..1350 LOOP
        DELETE FROM dedup_unique_test_table;
        INSERT INTO dedup_unique_test_table SELECT 1;
    END LOOP;
END$$;
-- Exercise the LP_DEAD-bit-set tuple deletion code with a posting list tuple.
-- The implementation prefers deleting existing items to merging any duplicate
-- tuples into a posting list, so we need an explicit test to make sure we get
-- coverage (note that this test also assumes BLCKSZ is 8192 or less):
DROP INDEX plain_unique;
DELETE FROM dedup_unique_test_table WHERE a = 1;
INSERT INTO dedup_unique_test_table SELECT i FROM generate_series(0,450) i;
--
-- Test B-tree fast path (cache rightmost leaf page) optimization.
--
-- First create a tree that's at least three levels deep (i.e. has one level
-- between the root and leaf levels). The text inserted is long.  It won't be
-- TOAST compressed because we use plain storage in the table.  Only a few
-- index tuples fit on each internal page, allowing us to get a tall tree with
-- few pages.  (A tall tree is required to trigger caching.)
--
-- The text column must be the leading column in the index, since suffix
-- truncation would otherwise truncate tuples on internal pages, leaving us
-- with a short tree.
create table btree_tall_tbl(id int4, t text);
alter table btree_tall_tbl alter COLUMN t set storage plain;
create index btree_tall_idx on btree_tall_tbl (t, id) with (fillfactor = 10);
insert into btree_tall_tbl select g, repeat('x', 250)
from generate_series(1, 130) g;
--
-- Test for multilevel page deletion
--
CREATE TABLE delete_test_table (a bigint, b bigint, c bigint, d bigint);
INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,80000) i;
ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d);
-- Delete most entries, and vacuum, deleting internal pages and creating "fast
-- root"
DELETE FROM delete_test_table WHERE a < 79990;
VACUUM delete_test_table;
--
-- Test B-tree insertion with a metapage update (XLOG_BTREE_INSERT_META
-- WAL record type). This happens when a "fast root" page is split.  This
-- also creates coverage for nbtree FSM page recycling.
--
-- The vacuum above should've turned the leaf page into a fast root. We just
-- need to insert some rows to cause the fast root page to split.
INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
ERROR:  operator class int4_ops has no options
-- Test case of ALTER INDEX with abuse of column names for indexes.
-- This grammar is not officially supported, but the parser allows it.
CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id);
ALTER INDEX btree_tall_idx2 ALTER COLUMN id SET (n_distinct=100);
ERROR:  ALTER action ALTER COLUMN ... SET cannot be performed on relation "btree_tall_idx2"
DETAIL:  This operation is not supported for indexes.
DROP INDEX btree_tall_idx2;
-- Partitioned index
CREATE TABLE btree_part (id int4) PARTITION BY RANGE (id);
CREATE INDEX btree_part_idx ON btree_part(id);
ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100);
ERROR:  ALTER action ALTER COLUMN ... SET cannot be performed on relation "btree_part_idx"
DETAIL:  This operation is not supported for partitioned indexes.
DROP TABLE btree_part;