diff options
Diffstat (limited to 'src/test/regress/expected/btree_index.out')
| -rw-r--r-- | src/test/regress/expected/btree_index.out | 389 |
1 files changed, 389 insertions, 0 deletions
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out new file mode 100644 index 0000000..93ed5e8 --- /dev/null +++ b/src/test/regress/expected/btree_index.out @@ -0,0 +1,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; |