diff options
Diffstat (limited to 'src/test/regress/expected/create_index.out')
| -rw-r--r-- | src/test/regress/expected/create_index.out | 161 |
1 files changed, 45 insertions, 116 deletions
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 53c8e830ce..1cc475abb0 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2,6 +2,8 @@ -- CREATE_INDEX -- Create ancillary data structures (i.e. indices) -- +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR -- -- BTREE -- @@ -31,18 +33,6 @@ ERROR: relation "six_wrong" does not exist COMMENT ON INDEX six IS 'good index'; COMMENT ON INDEX six IS NULL; -- --- 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); --- -- BTREE partial indices -- CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops) @@ -54,9 +44,20 @@ CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops) -- -- GiST (rtree-equivalent opclasses only) -- +CREATE TABLE slow_emp4000 ( + home_base box +); +CREATE TABLE fast_emp4000 ( + home_base box +); +\set filename :abs_srcdir '/data/rect.data' +COPY slow_emp4000 FROM :'filename'; +INSERT INTO fast_emp4000 SELECT * FROM slow_emp4000; +ANALYZE slow_emp4000; +ANALYZE fast_emp4000; CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base); -CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); -CREATE INDEX gcircleind ON circle_tbl USING gist (f1); +-- we want to work with a point_tbl that includes a null +CREATE TEMP TABLE point_tbl AS SELECT * FROM public.point_tbl; INSERT INTO POINT_TBL(f1) VALUES (NULL); CREATE INDEX gpointind ON point_tbl USING gist (f1); CREATE TEMP TABLE gpolygon_tbl AS @@ -95,23 +96,6 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 278 (1 row) -SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - f1 ---------------------- - ((2,0),(2,4),(0,0)) -(1 row) - -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); - f1 ---------------- - <(1,2),3> - <(1,3),5> - <(1,2),100> - <(100,1),115> -(4 rows) - SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; count ------- @@ -311,45 +295,6 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; (1 row) EXPLAIN (COSTS OFF) -SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - QUERY PLAN ------------------------------------------------------------- - Sort - Sort Key: ((poly_center(f1))[0]) - -> Index Scan using gpolygonind on polygon_tbl - Index Cond: (f1 @> '((1,1),(2,2),(2,1))'::polygon) -(4 rows) - -SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - f1 ---------------------- - ((2,0),(2,4),(0,0)) -(1 row) - -EXPLAIN (COSTS OFF) -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); - QUERY PLAN --------------------------------------------------- - Sort - Sort Key: (area(f1)) - -> Index Scan using gcircleind on circle_tbl - Index Cond: (f1 && '<(1,-2),1>'::circle) -(4 rows) - -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); - f1 ---------------- - <(1,2),3> - <(1,3),5> - <(1,2),100> - <(100,1),115> -(4 rows) - -EXPLAIN (COSTS OFF) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; QUERY PLAN ------------------------------------------------------------ @@ -677,6 +622,36 @@ RESET enable_bitmapscan; -- -- Note: GIN currently supports only bitmap scans, not plain indexscans -- +CREATE TABLE array_index_op_test ( + seqno int4, + i int4[], + t text[] +); +\set filename :abs_srcdir '/data/array.data' +COPY array_index_op_test FROM :'filename'; +ANALYZE array_index_op_test; +SELECT * FROM array_index_op_test WHERE i = '{NULL}' ORDER BY seqno; + seqno | i | t +-------+--------+-------- + 102 | {NULL} | {NULL} +(1 row) + +SELECT * FROM array_index_op_test WHERE i @> '{NULL}' ORDER BY seqno; + seqno | i | t +-------+---+--- +(0 rows) + +SELECT * FROM array_index_op_test WHERE i && '{NULL}' ORDER BY seqno; + seqno | i | t +-------+---+--- +(0 rows) + +SELECT * FROM array_index_op_test WHERE i <@ '{NULL}' ORDER BY seqno; + seqno | i | t +-------+----+---- + 101 | {} | {} +(1 row) + SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; @@ -904,28 +879,6 @@ SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno; 101 | {} | {} (1 row) -SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; - seqno | i | t --------+--------+-------- - 102 | {NULL} | {NULL} -(1 row) - -SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; - seqno | i | t --------+---+--- -(0 rows) - -SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; - seqno | i | t --------+---+--- -(0 rows) - -SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; - seqno | i | t --------+----+---- - 101 | {} | {} -(1 row) - CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); explain (costs off) SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; @@ -1195,18 +1148,6 @@ SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 101 | {} | {} (1 row) -SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; - seqno | i | t --------+--------+-------- - 102 | {NULL} | {NULL} -(1 row) - -SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; - seqno | i | t --------+----+---- - 101 | {} | {} -(1 row) - RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; @@ -1240,10 +1181,6 @@ Options: fastupdate=on, gin_pending_list_limit=128 -- -- HASH -- -CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); -CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); -CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); -CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60); CREATE UNLOGGED TABLE unlogged_hash_table (id int4); CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); DROP TABLE unlogged_hash_table; @@ -1418,13 +1355,6 @@ ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING I covering_pkey; DROP TABLE covering_index_heap; -- --- Also try building functional, expressional, and partial indexes on --- tables that already contain data. --- -create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); -create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); -create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; --- -- Try some concurrent index builds -- -- Unfortunately this only tests about half the code paths because there are @@ -1949,7 +1879,6 @@ SELECT count(*) FROM dupindexcols -- -- Check ordering of =ANY indexqual results (bug in 9.2.0) -- -vacuum tenk1; -- ensure we get consistent plans here explain (costs off) SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) |