diff options
| author | Abbas | 2012-04-30 06:11:21 +0000 |
|---|---|---|
| committer | Abbas | 2012-04-30 06:11:21 +0000 |
| commit | 63bfc97691117d2bc738ec318b971d7d7ccada7a (patch) | |
| tree | 6fbcea29d16dc6db92cb170765ade171d3889a4a /src/test | |
| parent | 99448b33fd6e5d462cd506732fc1fca1724be08a (diff) | |
Fix the test case create_index. The test case was failing because of the following reasons
1. There was a problem in CTAS. While doing the create table step QueryRewriteCTAS
was wrongly assuming that the new table to be created has to be created with
NOT NULL constraint for all columns, where as in PG the table created as a
result of CTAS has no such constraint added by default,
even if the table from which we are selecting has any such constraint.
2. We added an id column to the polygon_tbl. This change had an impact on
a couple of test cases here too.
3. In XC, EXPLAIN show the plan of coordinator only, whereas in this test case
there seems to be a need to show the plans of data node. Added a feature
request 3522479 for this and changed the expected output file for now.
4. Removed alternate expected output file.
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/create_index.out | 390 | ||||
| -rw-r--r-- | src/test/regress/expected/create_index_1.out | 953 | ||||
| -rw-r--r-- | src/test/regress/sql/create_index.sql | 66 |
3 files changed, 242 insertions, 1167 deletions
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index c78d9ee1e8..e3f931b4a9 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -53,6 +53,7 @@ CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); CREATE INDEX gcircleind ON circle_tbl USING gist (f1); INSERT INTO POINT_TBL(f1) VALUES (NULL); CREATE INDEX gpointind ON point_tbl USING gist (f1); +SET enforce_two_phase_commit TO off; CREATE TEMP TABLE gpolygon_tbl AS SELECT polygon(home_base) AS f1 FROM slow_emp4000; INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); @@ -88,9 +89,9 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 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)) + id | f1 +----+--------------------- + 1 | ((2,0),(2,4),(0,0)) (1 row) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) @@ -210,19 +211,21 @@ SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0 SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box ORDER BY (home_base[0])[0]; - QUERY PLAN ----------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- Sort - Sort Key: ((home_base[0])[0]) - -> Bitmap Heap Scan on fast_emp4000 - Recheck Cond: (home_base @ '(2000,1000),(200,200)'::box) - -> Bitmap Index Scan on grect2ind - Index Cond: (home_base @ '(2000,1000),(200,200)'::box) -(6 rows) + Output: home_base, ((home_base[0])[0]) + Sort Key: ((fast_emp4000.home_base[0])[0]) + -> Result + Output: home_base, (home_base[0])[0] + -> Data Node Scan on fast_emp4000 + Output: home_base + Remote query: SELECT home_base FROM ONLY fast_emp4000 WHERE (home_base @ '(2000,1000),(200,200)'::box) +(8 rows) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box @@ -233,16 +236,18 @@ SELECT * FROM fast_emp4000 (1444,403),(1346,344) (2 rows) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate - -> Bitmap Heap Scan on fast_emp4000 - Recheck Cond: (home_base && '(1000,1000),(0,0)'::box) - -> Bitmap Index Scan on grect2ind - Index Cond: (home_base && '(1000,1000),(0,0)'::box) -(5 rows) + Output: pg_catalog.count(*) + -> Materialize + Output: (count(*)) + -> Data Node Scan on "__REMOTE_GROUP_QUERY__" + Output: count(*) + Remote query: SELECT count(*) FROM (SELECT home_base FROM ONLY fast_emp4000 WHERE (home_base && '(1000,1000),(0,0)'::box)) group_1 +(7 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; count @@ -250,16 +255,18 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 2 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; - QUERY PLAN ------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------- Aggregate - -> Bitmap Heap Scan on fast_emp4000 - Recheck Cond: (home_base IS NULL) - -> Bitmap Index Scan on grect2ind - Index Cond: (home_base IS NULL) -(5 rows) + Output: pg_catalog.count(*) + -> Materialize + Output: (count(*)) + -> Data Node Scan on "__REMOTE_GROUP_QUERY__" + Output: count(*) + Remote query: SELECT count(*) FROM (SELECT home_base FROM ONLY fast_emp4000 WHERE (home_base IS NULL)) group_1 +(7 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; count @@ -267,34 +274,42 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 278 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon ORDER BY (poly_center(f1))[0]; - QUERY PLAN ------------------------------------------------------------ + 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) + Output: id, f1, ((poly_center(f1))[0]) + Sort Key: ((poly_center(polygon_tbl.f1))[0]) + -> Result + Output: id, f1, (poly_center(f1))[0] + -> Data Node Scan on polygon_tbl + Output: id, f1 + Remote query: SELECT id, f1 FROM ONLY polygon_tbl WHERE (f1 ~ '((1,1),(2,2),(2,1))'::polygon) +(8 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)) + id | f1 +----+--------------------- + 1 | ((2,0),(2,4),(0,0)) (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); - QUERY PLAN --------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------- Sort - Sort Key: (area(f1)) - -> Index Scan using gcircleind on circle_tbl - Index Cond: (f1 && '<(1,-2),1>'::circle) -(4 rows) + Output: f1, (area(f1)) + Sort Key: (area(circle_tbl.f1)) + -> Result + Output: f1, area(f1) + -> Data Node Scan on circle_tbl + Output: f1 + Remote query: SELECT f1 FROM ONLY circle_tbl WHERE (f1 && '<(1,-2),1>'::circle) +(8 rows) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); @@ -306,16 +321,18 @@ SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) <(100,1),115> (4 rows) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------- Aggregate - -> Bitmap Heap Scan on gpolygon_tbl - Recheck Cond: (f1 && '((1000,1000),(0,0))'::polygon) - -> Bitmap Index Scan on ggpolygonind - Index Cond: (f1 && '((1000,1000),(0,0))'::polygon) -(5 rows) + Output: pg_catalog.count(*) + -> Materialize + Output: (count(*)) + -> Data Node Scan on "__REMOTE_GROUP_QUERY__" + Output: count(*) + Remote query: SELECT count(*) FROM (SELECT f1 FROM ONLY gpolygon_tbl WHERE (f1 && '((1000,1000),(0,0))'::polygon)) group_1 +(7 rows) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; count @@ -323,16 +340,18 @@ SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 2 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------- Aggregate - -> Bitmap Heap Scan on gcircle_tbl - Recheck Cond: (f1 && '<(500,500),500>'::circle) - -> Bitmap Index Scan on ggcircleind - Index Cond: (f1 && '<(500,500),500>'::circle) -(5 rows) + Output: pg_catalog.count(*) + -> Materialize + Output: (count(*)) + -> Data Node Scan on "__REMOTE_GROUP_QUERY__" + Output: count(*) + Remote query: SELECT count(*) FROM (SELECT f1 FROM ONLY gcircle_tbl WHERE (f1 && '<(500,500),500>'::circle)) group_1 +(7 rows) SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; count @@ -340,13 +359,13 @@ SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 2 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; - QUERY PLAN ----------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl - Index Cond: (f1 <@ '(100,100),(0,0)'::box) + QUERY PLAN +---------------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl WHERE (f1 <@ '(100,100),(0,0)'::box) (3 rows) SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; @@ -355,13 +374,13 @@ SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 3 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; - QUERY PLAN ----------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl - Index Cond: ('(100,100),(0,0)'::box @> f1) + QUERY PLAN +---------------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl WHERE ('(100,100),(0,0)'::box @> f1) (3 rows) SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; @@ -370,13 +389,13 @@ SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 3 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; - QUERY PLAN ----------------------------------------------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl - Index Cond: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon) + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl WHERE (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon) (3 rows) SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; @@ -385,13 +404,13 @@ SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50, 3 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; - QUERY PLAN ----------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl - Index Cond: (f1 <@ '<(50,50),50>'::circle) + QUERY PLAN +---------------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl WHERE (f1 <@ '<(50,50),50>'::circle) (3 rows) SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; @@ -400,13 +419,13 @@ SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 1 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; - QUERY PLAN -------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl p - Index Cond: (f1 << '(0,0)'::point) + QUERY PLAN +---------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl p WHERE (f1 << '(0,0)'::point) (3 rows) SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; @@ -415,13 +434,13 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 3 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; - QUERY PLAN -------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl p - Index Cond: (f1 >> '(0,0)'::point) + QUERY PLAN +---------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl p WHERE (f1 >> '(0,0)'::point) (3 rows) SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; @@ -430,13 +449,13 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 2 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; - QUERY PLAN -------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl p - Index Cond: (f1 <^ '(0,0)'::point) + QUERY PLAN +---------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl p WHERE (f1 <^ '(0,0)'::point) (3 rows) SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; @@ -445,13 +464,13 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 1 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; - QUERY PLAN -------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl p - Index Cond: (f1 >^ '(0,0)'::point) + QUERY PLAN +---------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl p WHERE (f1 >^ '(0,0)'::point) (3 rows) SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; @@ -460,13 +479,13 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 3 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; - QUERY PLAN -------------------------------------------------- - Aggregate - -> Index Scan using gpointind on point_tbl p - Index Cond: (f1 ~= '(-5,-12)'::point) + QUERY PLAN +------------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: count(*) + Remote query: SELECT count(*) AS count FROM point_tbl p WHERE (f1 ~= '(-5,-12)'::point) (3 rows) SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; @@ -475,13 +494,14 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 1 (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; - QUERY PLAN ------------------------------------------ - Index Scan using gpointind on point_tbl - Order By: (f1 <-> '(0,1)'::point) -(2 rows) + QUERY PLAN +--------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: point_tbl.f1, (point_tbl.f1 <-> '(0,1)'::point) + Remote query: SELECT f1 FROM point_tbl ORDER BY (f1 <-> '(0,1)'::point) +(3 rows) SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; f1 @@ -495,13 +515,14 @@ SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; (7 rows) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 IS NULL; - QUERY PLAN ------------------------------------------ - Index Scan using gpointind on point_tbl - Index Cond: (f1 IS NULL) -(2 rows) + QUERY PLAN +------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: point_tbl.f1 + Remote query: SELECT f1 FROM point_tbl WHERE (f1 IS NULL) +(3 rows) SELECT * FROM point_tbl WHERE f1 IS NULL; f1 @@ -509,13 +530,13 @@ SELECT * FROM point_tbl WHERE f1 IS NULL; (1 row) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; - QUERY PLAN ------------------------------------------ - Index Scan using gpointind on point_tbl - Index Cond: (f1 IS NOT NULL) - Order By: (f1 <-> '(0,1)'::point) + QUERY PLAN +-------------------------------------------------------------------------------------------------- + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: point_tbl.f1, (point_tbl.f1 <-> '(0,1)'::point) + Remote query: SELECT f1 FROM point_tbl WHERE (f1 IS NOT NULL) ORDER BY (f1 <-> '(0,1)'::point) (3 rows) SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; @@ -529,13 +550,13 @@ SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; (5.1,34.5) (6 rows) -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; - QUERY PLAN ------------------------------------------------- - Index Scan using gpointind on point_tbl - Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) - Order By: (f1 <-> '(0,1)'::point) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: point_tbl.f1, (point_tbl.f1 <-> '(0,1)'::point) + Remote query: SELECT f1 FROM point_tbl WHERE (f1 <@ '(10,10),(-10,-10)'::box) ORDER BY (f1 <-> '(0,1)'::point) (3 rows) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; @@ -550,17 +571,14 @@ SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0 SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; - QUERY PLAN ------------------------------------------------------------- - Sort - Sort Key: ((f1 <-> '(0,1)'::point)) - -> Bitmap Heap Scan on point_tbl - Recheck Cond: (f1 <@ '(10,10),(-10,-10)'::box) - -> Bitmap Index Scan on gpointind - Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) -(6 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Data Node Scan on "__REMOTE_FQS_QUERY__" + Output: point_tbl.f1, (point_tbl.f1 <-> '(0,1)'::point) + Remote query: SELECT f1 FROM point_tbl WHERE (f1 <@ '(10,10),(-10,-10)'::box) ORDER BY (f1 <-> '(0,1)'::point) +(3 rows) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; f1 @@ -583,17 +601,19 @@ SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); -explain (costs off) +explain (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------- Sort - Sort Key: seqno - -> Bitmap Heap Scan on array_index_op_test - Recheck Cond: (i @> '{32}'::integer[]) - -> Bitmap Index Scan on intarrayidx - Index Cond: (i @> '{32}'::integer[]) -(6 rows) + Output: seqno, i, t + Sort Key: array_index_op_test.seqno + -> Result + Output: seqno, i, t + -> Data Node Scan on array_index_op_test + Output: seqno, i, t + Remote query: SELECT seqno, i, t FROM ONLY array_index_op_test WHERE (i @> '{32}'::integer[]) +(8 rows) SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; seqno | i | t @@ -829,17 +849,19 @@ SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; (1 row) CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); -explain (costs off) +explain (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------- Sort - Sort Key: seqno - -> Bitmap Heap Scan on array_index_op_test - Recheck Cond: (t @> '{AAAAAAAA72908}'::text[]) - -> Bitmap Index Scan on textarrayidx - Index Cond: (t @> '{AAAAAAAA72908}'::text[]) -(6 rows) + Output: seqno, i, t + Sort Key: array_index_op_test.seqno + -> Result + Output: seqno, i, t + -> Data Node Scan on array_index_op_test + Output: seqno, i, t + Remote query: SELECT seqno, i, t FROM ONLY array_index_op_test WHERE (t @> '{AAAAAAAA72908}'::text[]) +(8 rows) SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; seqno | i | t @@ -1125,13 +1147,12 @@ CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops); -- CREATE TABLE func_index_heap (f1 text, f2 text); CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. INSERT INTO func_index_heap VALUES('ABC','DEF'); INSERT INTO func_index_heap VALUES('AB','CDEFG'); INSERT INTO func_index_heap VALUES('QWE','RTY'); -- this should fail because of unique index: INSERT INTO func_index_heap VALUES('ABCD', 'EF'); -ERROR: duplicate key value violates unique constraint "func_index_index" -DETAIL: Key (textcat(f1, f2))=(ABCDEF) already exists. -- but this shouldn't: INSERT INTO func_index_heap VALUES('QWERTY'); -- @@ -1140,13 +1161,12 @@ INSERT INTO func_index_heap VALUES('QWERTY'); DROP TABLE func_index_heap; CREATE TABLE func_index_heap (f1 text, f2 text); CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. INSERT INTO func_index_heap VALUES('ABC','DEF'); INSERT INTO func_index_heap VALUES('AB','CDEFG'); INSERT INTO func_index_heap VALUES('QWE','RTY'); -- this should fail because of unique index: INSERT INTO func_index_heap VALUES('ABCD', 'EF'); -ERROR: duplicate key value violates unique constraint "func_index_index" -DETAIL: Key ((f1 || f2))=(ABCDEF) already exists. -- but this shouldn't: INSERT INTO func_index_heap VALUES('QWERTY'); -- @@ -1154,8 +1174,11 @@ INSERT INTO func_index_heap VALUES('QWERTY'); -- tables that already contain data. -- create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. -- -- Try some concurrent index builds -- @@ -1164,27 +1187,36 @@ create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; CREATE TABLE concur_heap (f1 text, f2 text); -- empty table CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported INSERT INTO concur_heap VALUES ('a','b'); INSERT INTO concur_heap VALUES ('b','b'); -- unique index CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported -- check if constraint is set up properly to be enforced INSERT INTO concur_heap VALUES ('b','x'); -ERROR: duplicate key value violates unique constraint "concur_index2" -DETAIL: Key (f1)=(b) already exists. -- check if constraint is enforced properly at build time CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); -ERROR: could not create unique index "concur_index3" -DETAIL: Key (f2)=(b) is duplicated. +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported -- test that expression indexes and partial indexes work concurrently CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported -- here we also check that you can default the index name CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported -- You can't do a concurrent index build in a transaction BEGIN; CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); -ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block +ERROR: PGXC does not support concurrent INDEX yet +DETAIL: The feature is not currently supported COMMIT; -- But you can do a regular index build in a transaction BEGIN; @@ -1200,12 +1232,6 @@ Table "public.concur_heap" f1 | text | f2 | text | Indexes: - "concur_index2" UNIQUE, btree (f1) - "concur_index3" UNIQUE, btree (f2) INVALID - "concur_heap_expr_idx" btree ((f2 || f1)) - "concur_index1" btree (f2, f1) - "concur_index4" btree (f2) WHERE f1 = 'a'::text - "concur_index5" btree (f2) WHERE f1 = 'x'::text "std_index" btree (f2) DROP TABLE concur_heap; diff --git a/src/test/regress/expected/create_index_1.out b/src/test/regress/expected/create_index_1.out deleted file mode 100644 index 67d3939e2e..0000000000 --- a/src/test/regress/expected/create_index_1.out +++ /dev/null @@ -1,953 +0,0 @@ --- --- CREATE_INDEX --- Create ancillary data structures (i.e. indices) --- --- --- BTREE --- -CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops); -CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops); -CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops); -CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops); -CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops); -CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops); -CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops); -CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous); -CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops); -CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops); -CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops); -CREATE INDEX rix ON road USING btree (name text_ops); -CREATE INDEX iix ON ihighway USING btree (name text_ops); -CREATE INDEX six ON shighway USING btree (name text_ops); --- test comments -COMMENT ON INDEX six_wrong IS 'bad index'; -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) - where unique1 < 20 or unique1 > 980; -CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops) - where stringu1 < 'B'; -CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops) - where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K'; --- --- GiST (rtree-equivalent opclasses only) --- -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); -CREATE INDEX gpointind ON point_tbl USING gist (f1); -CREATE TEMP TABLE gpolygon_tbl AS - SELECT polygon(home_base) AS f1 FROM slow_emp4000; -ERROR: INTO clause not yet supported -INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); -ERROR: relation "gpolygon_tbl" does not exist -LINE 1: INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); - ^ -INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' ); -ERROR: relation "gpolygon_tbl" does not exist -LINE 1: INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' ); - ^ -CREATE TEMP TABLE gcircle_tbl AS - SELECT circle(home_base) AS f1 FROM slow_emp4000; -ERROR: INTO clause not yet supported -CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1); -ERROR: relation "gpolygon_tbl" does not exist -CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1); -ERROR: relation "gcircle_tbl" does not exist -SET enable_seqscan = ON; -SET enable_indexscan = OFF; -SET enable_bitmapscan = OFF; -SELECT * FROM fast_emp4000 - WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY (home_base[0])[0]; - home_base ------------ -(0 rows) - -SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; - count -------- - 1 -(1 row) - -SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; - count -------- - 138 -(1 row) - -SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - id | f1 -----+--------------------- - 1 | ((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; -ERROR: relation "gpolygon_tbl" does not exist -LINE 1: SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,... - ^ -SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; -ERROR: relation "gcircle_tbl" does not exist -LINE 1: SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500... - ^ -SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; - count -------- - 3 -(1 row) - -SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; - count -------- - 3 -(1 row) - -SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; - count -------- - 3 -(1 row) - -SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; - count -------- - 1 -(1 row) - -SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; - count -------- - 3 -(1 row) - -SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; - count -------- - 2 -(1 row) - -SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; - count -------- - 1 -(1 row) - -SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; - count -------- - 3 -(1 row) - -SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; - count -------- - 1 -(1 row) - -SET enable_seqscan = OFF; -SET enable_indexscan = ON; -SET enable_bitmapscan = ON; ---EXPLAIN (COSTS OFF) ---SELECT * FROM fast_emp4000 --- WHERE home_base @ '(200,200),(2000,1000)'::box --- ORDER BY (home_base[0])[0]; -SELECT * FROM fast_emp4000 - WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY (home_base[0])[0]; - home_base ------------ -(0 rows) - ---EXPLAIN (COSTS OFF) ---SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; -SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; - count -------- - 1 -(1 row) - ---EXPLAIN (COSTS OFF) ---SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; -SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; - count -------- - 138 -(1 row) - ---EXPLAIN (COSTS OFF) ---SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon --- ORDER BY (poly_center(f1))[0]; -SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - id | f1 -----+--------------------- - 1 | ((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); -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; -ERROR: relation "gpolygon_tbl" does not exist -LINE 2: SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,... - ^ -SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; -ERROR: relation "gpolygon_tbl" does not exist -LINE 1: SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,... - ^ -EXPLAIN (COSTS OFF) -SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; -ERROR: relation "gcircle_tbl" does not exist -LINE 2: SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500... - ^ -SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; -ERROR: relation "gcircle_tbl" does not exist -LINE 1: SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500... - ^ -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; - count -------- - 3 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; - count -------- - 3 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; - count -------- - 3 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; - count -------- - 1 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; - count -------- - 3 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; - count -------- - 2 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; - count -------- - 1 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; - count -------- - 3 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Materialize - -> Data Node Scan (Node Count [1]) -(3 rows) - -SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; - count -------- - 1 -(1 row) - -RESET enable_seqscan; -RESET enable_indexscan; -RESET enable_bitmapscan; --- --- GIN over int[] and text[] --- -SET enable_seqscan = OFF; -SET enable_indexscan = ON; -SET enable_bitmapscan = OFF; -CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); -SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 53 | {38,17} | {AAAAAAAAAAA21658} - 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} -(8 rows) - -SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 53 | {38,17} | {AAAAAAAAAAA21658} - 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} -(8 rows) - -SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} -(3 rows) - -SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 53 | {38,17} | {AAAAAAAAAAA21658} - 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(11 rows) - -SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; - seqno | i | t --------+---------------+---------------------------------------------------------------------------------------------------------------------------- - 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} -(3 rows) - -SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno; - seqno | i | t --------+---------+----------------------------------------------------------------------------------------------------------------- - 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} -(1 row) - -CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); -SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; - seqno | i | t --------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- - 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} - 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} - 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} - 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} -(4 rows) - -SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; - seqno | i | t --------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- - 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} - 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} - 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} - 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} -(4 rows) - -SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; - seqno | i | t --------+------------------+-------------------------------------------------------------------- - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} - 96 | {23,97,43} | {AAAAAAAAAA646,A87088} -(3 rows) - -SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; - seqno | i | t --------+------------------+-------------------------------------------------------------------- - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} - 96 | {23,97,43} | {AAAAAAAAAA646,A87088} -(3 rows) - -SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; - seqno | i | t --------+------+-------------------------------------------------------------------- - 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} -(1 row) - -SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; - seqno | i | t --------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} - 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} - 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} - 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} - 96 | {23,97,43} | {AAAAAAAAAA646,A87088} -(6 rows) - -SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; - seqno | i | t --------+--------------------+----------------------------------------------------------------------------------------------------------- - 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} - 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} -(2 rows) - -SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno; - seqno | i | t --------+------------+------------------------ - 96 | {23,97,43} | {AAAAAAAAAA646,A87088} -(1 row) - --- Repeat some of the above tests but exercising bitmapscans instead -SET enable_indexscan = OFF; -SET enable_bitmapscan = ON; -SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 53 | {38,17} | {AAAAAAAAAAA21658} - 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} -(8 rows) - -SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 53 | {38,17} | {AAAAAAAAAAA21658} - 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} -(8 rows) - -SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} -(3 rows) - -SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} - 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 53 | {38,17} | {AAAAAAAAAAA21658} - 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(11 rows) - -SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; - seqno | i | t --------+---------------+---------------------------------------------------------------------------------------------------------------------------- - 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} -(3 rows) - -SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno; - seqno | i | t --------+---------+----------------------------------------------------------------------------------------------------------------- - 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} -(1 row) - --- And try it with a multicolumn GIN index -DROP INDEX intarrayidx, textarrayidx; -CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t); -SET enable_seqscan = OFF; -SET enable_indexscan = ON; -SET enable_bitmapscan = OFF; -SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------- - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} - 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} - 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} - 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} - 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(7 rows) - -SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------- - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} - 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} - 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} - 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} - 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(7 rows) - -SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+-----------------------------+------------------------------------------------------------------------------ - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(1 row) - -SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+-----------------------------+------------------------------------------------------------------------------ - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(1 row) - -SET enable_indexscan = OFF; -SET enable_bitmapscan = ON; -SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; - seqno | i | t --------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ - 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} - 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} - 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} - 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} - 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(6 rows) - -SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------- - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} - 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} - 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} - 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} - 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(7 rows) - -SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------- - 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} - 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} - 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} - 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} - 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} - 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(7 rows) - -SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+-----------------------------+------------------------------------------------------------------------------ - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(1 row) - -SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; - seqno | i | t --------+-----------------------------+------------------------------------------------------------------------------ - 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} -(1 row) - -RESET enable_seqscan; -RESET enable_indexscan; -RESET enable_bitmapscan; --- --- 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); --- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); --- --- Test functional index --- -CREATE TABLE func_index_heap (f1 text, f2 text); -CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); -ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. -INSERT INTO func_index_heap VALUES('ABC','DEF'); -INSERT INTO func_index_heap VALUES('AB','CDEFG'); -INSERT INTO func_index_heap VALUES('QWE','RTY'); --- this should fail because of unique index: -INSERT INTO func_index_heap VALUES('ABCD', 'EF'); --- but this shouldn't: -INSERT INTO func_index_heap VALUES('QWERTY'); --- --- Same test, expressional index --- -DROP TABLE func_index_heap; -CREATE TABLE func_index_heap (f1 text, f2 text); -CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); -ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. -INSERT INTO func_index_heap VALUES('ABC','DEF'); -INSERT INTO func_index_heap VALUES('AB','CDEFG'); -INSERT INTO func_index_heap VALUES('QWE','RTY'); --- this should fail because of unique index: -INSERT INTO func_index_heap VALUES('ABCD', 'EF'); --- but this shouldn't: -INSERT INTO func_index_heap VALUES('QWERTY'); --- --- 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)); -ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. -create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); -ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. -create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; -ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. --- --- Try some concurrent index builds --- --- Unfortunately this only tests about half the code paths because there are --- no concurrent updates happening to the table at the same time. -CREATE TABLE concur_heap (f1 text, f2 text); --- empty table -CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported -INSERT INTO concur_heap VALUES ('a','b'); -INSERT INTO concur_heap VALUES ('b','b'); --- unique index -CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported --- check if constraint is set up properly to be enforced -INSERT INTO concur_heap VALUES ('b','x'); --- check if constraint is enforced properly at build time -CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported --- test that expression indexes and partial indexes work concurrently -CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported -CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported --- here we also check that you can default the index name -CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported --- You can't do a concurrent index build in a transaction -BEGIN; -CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); -ERROR: PGXC does not support concurrent INDEX yet -DETAIL: The feature is not currently supported -COMMIT; --- But you can do a regular index build in a transaction -BEGIN; -CREATE INDEX std_index on concur_heap(f2); -COMMIT; --- check to make sure that the failed indexes were cleaned up properly and the --- successful indexes are created properly. Notably that they do NOT have the --- "invalid" flag set. -\d concur_heap -Table "public.concur_heap" - Column | Type | Modifiers ---------+------+----------- - f1 | text | - f2 | text | -Indexes: - "std_index" btree (f2) - -DROP TABLE concur_heap; --- --- Tests for IS NULL/IS NOT NULL with b-tree indexes --- -SELECT unique1, unique2 INTO onek_with_null FROM onek; -ERROR: INTO clause not yet supported -INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL); -ERROR: relation "onek_with_null" does not exist -LINE 1: INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -... - ^ -CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1); -ERROR: relation "onek_with_null" does not exist -SET enable_seqscan = OFF; -SET enable_indexscan = ON; -SET enable_bitmapscan = ON; -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NUL... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -DROP INDEX onek_nulltest; -ERROR: index "onek_nulltest" does not exist -CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1); -ERROR: relation "onek_with_null" does not exist -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NUL... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -DROP INDEX onek_nulltest; -ERROR: index "onek_nulltest" does not exist -CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1); -ERROR: relation "onek_with_null" does not exist -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NUL... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -DROP INDEX onek_nulltest; -ERROR: index "onek_nulltest" does not exist -CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1); -ERROR: relation "onek_with_null" does not exist -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NUL... - ^ -SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; -ERROR: relation "onek_with_null" does not exist -LINE 1: SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AN... - ^ -RESET enable_seqscan; -RESET enable_indexscan; -RESET enable_bitmapscan; - -DROP TABLE onek_with_null; -ERROR: table "onek_with_null" does not exist diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index eeab112246..d73c9af41f 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -80,6 +80,8 @@ INSERT INTO POINT_TBL(f1) VALUES (NULL); CREATE INDEX gpointind ON point_tbl USING gist (f1); +SET enforce_two_phase_commit TO off; + CREATE TEMP TABLE gpolygon_tbl AS SELECT polygon(home_base) AS f1 FROM slow_emp4000; INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); @@ -146,91 +148,91 @@ SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; ---EXPLAIN (COSTS OFF) ---SELECT * FROM fast_emp4000 --- WHERE home_base @ '(200,200),(2000,1000)'::box --- ORDER BY (home_base[0])[0]; +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) +SELECT * FROM fast_emp4000 + WHERE home_base @ '(200,200),(2000,1000)'::box + ORDER BY (home_base[0])[0]; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box ORDER BY (home_base[0])[0]; ---EXPLAIN (COSTS OFF) ---SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) +SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; ---EXPLAIN (COSTS OFF) ---SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) +SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; ---EXPLAIN (COSTS OFF) ---SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon --- ORDER BY (poly_center(f1))[0]; +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) +SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon + ORDER BY (poly_center(f1))[0]; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon ORDER BY (poly_center(f1))[0]; ---EXPLAIN (COSTS OFF) ---SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) --- ORDER BY area(f1); +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) +SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) + ORDER BY area(f1); SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 IS NULL; SELECT * FROM point_tbl WHERE f1 IS NULL; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; @@ -238,7 +240,7 @@ SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; -EXPLAIN (COSTS OFF) +EXPLAIN (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; @@ -258,7 +260,7 @@ SET enable_bitmapscan = ON; CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); -explain (costs off) +explain (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; @@ -280,7 +282,7 @@ SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); -explain (costs off) +explain (num_nodes off, nodes off, verbose on, COSTS OFF) SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; |