diff options
| -rw-r--r-- | src/backend/rewrite/rewriteHandler.c | 2 | ||||
| -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 |
4 files changed, 243 insertions, 1168 deletions
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 7bb0537482..ac4b9fe746 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -2608,7 +2608,7 @@ QueryRewriteCTAS(Query *parsetree) coldef->inhcount = 0; coldef->is_local = true; - coldef->is_not_null = true; + coldef->is_not_null = false; coldef->raw_default = NULL; coldef->cooked_default = NULL; coldef->constraints = NIL; 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; |