\c table_name_test_db;
set dolphin.lower_case_table_names TO 0;
set dolphin.b_compatibility_mode to off;
--
-- 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);
ERROR: relation "Polygon_tbl" does not exist
CREATE INDEX gcircleind ON Circle_tbl USING gist (f1);
ERROR: relation "Circle_tbl" does not exist
INSERT INTO point_tbl(f1) VALUES (NULL);
CREATE INDEX gpointind ON point_tbl USING gist (f1);
--CREATE TABLE gPolygon_tbl AS
-- SELECT polygon(home_base) AS f1 FROM Slow_emp4000;
--INSERT INTO gPolygon_tbl VALUES ( '(1000,0,0,1000)' );
--INSERT INTO gPolygon_tbl VALUES ( '(0,1000,1000,1000)' );
--CREATE TABLE gCircle_tbl AS
-- SELECT circle(home_base) AS f1 FROM Slow_emp4000;
--CREATE INDEX ggpolygonind ON gPolygon_tbl USING gist (f1);
--CREATE INDEX ggcircleind ON gCircle_tbl USING gist (f1);
--
-- SP-GiST
--
CREATE TABLE Quad_point_tbl AS
SELECT point(unique1,unique2) AS p FROM Tenk1;
INSERT INTO Quad_point_tbl
SELECT '(333.0,400.0)'::point FROM generate_series(1,1000);
INSERT INTO Quad_point_tbl VALUES (NULL), (NULL), (NULL);
CREATE INDEX sp_quad_ind ON Quad_point_tbl USING spgist (p);
ERROR: access method "spgist" does not support row store
CREATE TABLE KD_point_tbl AS SELECT * FROM Quad_point_tbl;
CREATE INDEX SP_kd_ind ON KD_point_tbl USING spgist (p kd_point_ops);
ERROR: access method "spgist" does not support row store
CREATE TABLE Suffix_text_tbl AS
SELECT name AS t FROM Road WHERE name !~ '^[0-9]';
INSERT INTO Suffix_text_tbl
SELECT 'P0123456789abcdef' FROM generate_series(1,1000);
INSERT INTO Suffix_text_tbl VALUES ('P0123456789abcde');
INSERT INTO Suffix_text_tbl VALUES ('P0123456789abcdefF');
CREATE INDEX sp_suff_ind ON Suffix_text_tbl USING spgist (t);
ERROR: access method "spgist" does not support row store
--
-- Test GiST and SP-GiST indexes
--
-- get non-indexed results for comparison purposes
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
-------
0
(1 row)
SELECT count(*) FROM Fast_emp4000 WHERE home_base IS NULL;
count
-------
0
(1 row)
SELECT * FROM Polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
ERROR: relation "Polygon_tbl" does not exist on datanode1
LINE 1: SELECT * FROM Polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::...
^
SELECT * FROM Circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
ERROR: relation "Circle_tbl" does not exist on datanode1
LINE 1: SELECT * FROM Circle_tbl WHERE f1 && circle(point(1,-2), 1)
^
--SELECT count(*) FROM gPolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
--SELECT count(*) FROM gCircle_tbl WHERE f1 && '<(500,500),500>'::circle;
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)
SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
f1
------------
(0,0)
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(7 rows)
SELECT * FROM point_tbl WHERE f1 IS NULL;
f1
----
(1 row)
SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
f1
------------
(0,0)
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(6 rows)
SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
f1
---------
(0,0)
(-3,4)
(-10,0)
(10,10)
(4 rows)
SELECT count(*) FROM Quad_point_tbl WHERE p IS NULL;
count
-------
3
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p IS NOT NULL;
count
-------
1000
(1 row)
SELECT count(*) FROM Quad_point_tbl;
count
-------
1003
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
count
-------
1000
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
count
-------
1000
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p << '(5000, 4000)';
count
-------
1000
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p >> '(5000, 4000)';
count
-------
0
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p <^ '(5000, 4000)';
count
-------
1000
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p >^ '(5000, 4000)';
count
-------
0
(1 row)
SELECT count(*) FROM Quad_point_tbl WHERE p ~= '(4585, 365)';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdef';
count
-------
1000
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcde';
count
-------
1
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdefF';
count
-------
1
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t < 'Aztec Ct ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<~ 'Aztec Ct ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t <= 'Aztec Ct ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<=~ 'Aztec Ct ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Aztec Ct ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Worth St ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t >= 'Worth St ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>=~ 'Worth St ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t > 'Worth St ';
count
-------
0
(1 row)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>~ 'Worth St ';
count
-------
0
(1 row)
-- Now check the results from plain indexscan
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM Fast_emp4000
WHERE home_base @ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM Fast_emp4000
WHERE home_base @ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
home_base
-----------
(0 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Fast_emp4000 WHERE home_base IS NULL;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Fast_emp4000 WHERE home_base IS NULL;
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM Polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
ERROR: relation "Polygon_tbl" does not exist on datanode1
LINE 2: SELECT * FROM Polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::...
^
SELECT * FROM Polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
ERROR: relation "Polygon_tbl" does not exist on datanode1
LINE 1: SELECT * FROM Polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::...
^
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM Circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
ERROR: relation "Circle_tbl" does not exist on datanode1
LINE 2: SELECT * FROM Circle_tbl WHERE f1 && circle(point(1,-2), 1)
^
SELECT * FROM Circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
ERROR: relation "Circle_tbl" does not exist on datanode1
LINE 1: SELECT * FROM Circle_tbl WHERE f1 && circle(point(1,-2), 1)
^
--EXPLAIN (NUM_NODES OFF, NODES OFF, 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 (NUM_NODES OFF, NODES OFF, 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 (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
count
-------
3
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
count
-------
3
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
ERROR: unrecognized EXPLAIN option "num_nodes"
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 (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
count
-------
3
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
count
-------
2
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
count
-------
3
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
f1
------------
(0,0)
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(7 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM point_tbl WHERE f1 IS NULL;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM point_tbl WHERE f1 IS NULL;
f1
----
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
f1
------------
(0,0)
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(6 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
f1
---------
(0,0)
(-3,4)
(-10,0)
(10,10)
(4 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p IS NULL;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p IS NULL;
count
-------
3
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p IS NOT NULL;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p IS NOT NULL;
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl;
count
-------
1003
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p << '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p << '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p >> '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p >> '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p <^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p <^ '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p >^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p >^ '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p ~= '(4585, 365)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p ~= '(4585, 365)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p <@ box '(200,200,1000,1000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p <@ box '(200,200,1000,1000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE box '(200,200,1000,1000)' @> p;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE box '(200,200,1000,1000)' @> p;
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p << '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p << '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p >> '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p >> '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p <^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p <^ '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p >^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p >^ '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p ~= '(4585, 365)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p ~= '(4585, 365)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdef';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdef';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcde';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcde';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdefF';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdefF';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t < 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t < 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<~ 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<~ 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t <= 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t <= 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<=~ 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<=~ 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t >= 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t >= 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>=~ 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>=~ 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t > 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t > 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>~ 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>~ 'Worth St ';
count
-------
0
(1 row)
-- Now check the results from bitmap indexscan
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
f1
---------
(0,0)
(-3,4)
(-10,0)
(10,10)
(4 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p IS NULL;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p IS NULL;
count
-------
3
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p IS NOT NULL;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p IS NOT NULL;
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl;
count
-------
1003
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p << '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p << '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p >> '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p >> '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p <^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p <^ '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p >^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p >^ '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Quad_point_tbl WHERE p ~= '(4585, 365)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Quad_point_tbl WHERE p ~= '(4585, 365)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p <@ box '(200,200,1000,1000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p <@ box '(200,200,1000,1000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE box '(200,200,1000,1000)' @> p;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE box '(200,200,1000,1000)' @> p;
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p << '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p << '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p >> '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p >> '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p <^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p <^ '(5000, 4000)';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p >^ '(5000, 4000)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p >^ '(5000, 4000)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM KD_point_tbl WHERE p ~= '(4585, 365)';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM KD_point_tbl WHERE p ~= '(4585, 365)';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdef';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdef';
count
-------
1000
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcde';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcde';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdefF';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'P0123456789abcdefF';
count
-------
1
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t < 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t < 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<~ 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<~ 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t <= 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t <= 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<=~ 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~<=~ 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Aztec Ct ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Aztec Ct ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t = 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t >= 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t >= 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>=~ 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>=~ 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t > 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t > 'Worth St ';
count
-------
0
(1 row)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>~ 'Worth St ';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Suffix_text_tbl WHERE t ~>~ 'Worth St ';
count
-------
0
(1 row)
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
-- GIN over int[] and text[]
--
-- Note: GIN currently supports only bitmap scans, not plain indexscans
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
CREATE INDEX intarrayidx ON Array_index_op_test USING gin (i);
explain (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM Array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM Array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i && '{32}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i && '{17}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i = '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i @> '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i && '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i <@ '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_op_test WHERE i = '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_op_test WHERE i && '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
CREATE INDEX textarrayidx ON Array_index_op_test USING gin (t);
explain (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM Array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM Array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t = '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t @> '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t && '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t <@ '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
-- And try it with a multicolumn GIN index
DROP INDEX intarrayidx, textarrayidx;
CREATE INDEX botharrayidx ON Array_index_op_test USING gin (i, t);
SELECT * FROM Array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i && '{32}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_index_op_test WHERE t = '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_op_test WHERE i = '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM Array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
-- Test GIN index's reloptions
--
CREATE INDEX gin_relopts_test ON Array_index_op_test USING gin (i)
WITH (FASTUPDATE=on, GIN_PENDING_LIST_LIMIT=128);
\d+ "gin_relopts_test"
Index "public.gin_relopts_test"
Column | Type | Definition | Storage
--------+---------+------------+---------
i | integer | i | plain
gin, for table "public.Array_index_op_test"
Options: fastupdate=on, gin_pending_list_limit=128
--
-- HASH
--
CREATE INDEX hash_i4_index ON Hash_i4_heap USING hash (random int4_ops);
CREATE INDEX hash_name_index ON Hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON Hash_txt_heap USING hash (random text_ops);
CREATE INDEX hash_f8_index ON Hash_f8_heap USING hash (random float8_ops);
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
--
-- Test functional index
--
-- PGXC: Here replication is used to ensure correct index creation
-- when a non-shippable expression is used.
-- PGXCTODO: this should be removed once global constraints are supported
CREATE TABLE func_index_heap (f1 text, f2 text) ;
CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
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');
--
-- Same test, expressional index
--
DROP TABLE func_index_heap;
-- PGXC: Here replication is used to ensure correct index creation
-- when a non-shippable expression is used.
-- PGXCTODO: this should be removed once global constraints are supported
CREATE TABLE func_index_heap (f1 text, f2 text) ;
CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
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');
--
-- Also try building functional, expressional, and partial indexes on
-- tables that already contain data.
--
create unique index hash_f8_index_1 on Hash_f8_heap(abs(random));
create unique index hash_f8_index_2 on Hash_f8_heap((seqno + 1), random);
create unique index hash_f8_index_3 on Hash_f8_heap(random) where seqno > 1000;
--
-- Try some concurrent index builds
--
-- Unfortunately this only tests about half the code paths because there are
-- 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);
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);
-- 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.
-- test that expression indexes and partial indexes work concurrently
CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
-- here we also check that you can default the index name
CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));
-- You can't do a concurrent index build in a transaction
START TRANSACTION;
CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
COMMIT;
-- But you can do a regular index build in a transaction
START TRANSACTION;
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 | character set UTF8 collate utf8mb4_general_ci
f2 | text | character set UTF8 collate utf8mb4_general_ci
Indexes:
"concur_index2" UNIQUE, btree (f1) TABLESPACE pg_default
"concur_index3" UNIQUE, btree (f2) TABLESPACE pg_default INVALID
"concur_heap_expr_idx" btree ((f2 || f1)) TABLESPACE pg_default
"concur_index1" btree (f2, f1) TABLESPACE pg_default
"concur_index4" btree (f2) TABLESPACE pg_default WHERE f1 = 'a'::text
"concur_index5" btree (f2) TABLESPACE pg_default WHERE f1 = 'x'::text
"std_index" btree (f2) TABLESPACE pg_default
--
-- Try some concurrent index drops
--
DROP INDEX CONCURRENTLY "concur_index2"; -- works
DROP INDEX CONCURRENTLY IF EXISTS "concur_index2"; -- notice
NOTICE: index "concur_index2" does not exist, skipping
-- failures
DROP INDEX CONCURRENTLY "concur_index2", "concur_index3";
ERROR: DROP INDEX CONCURRENTLY does not support dropping multiple objects
START TRANSACTION;
DROP INDEX CONCURRENTLY "concur_index5";
ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block
ROLLBACK;
-- successes
DROP INDEX CONCURRENTLY IF EXISTS "concur_index3";
DROP INDEX CONCURRENTLY "concur_index4";
DROP INDEX CONCURRENTLY "concur_index5";
DROP INDEX CONCURRENTLY "concur_index1";
DROP INDEX CONCURRENTLY "concur_heap_expr_idx";
\d "concur_heap"
Table "public.concur_heap"
Column | Type | Modifiers
--------+------+-----------------------------------------------
f1 | text | character set UTF8 collate utf8mb4_general_ci
f2 | text | character set UTF8 collate utf8mb4_general_ci
Indexes:
"std_index" btree (f2) TABLESPACE pg_default
DROP TABLE concur_heap;
--
-- Test ADD CONSTRAINT USING INDEX
--
CREATE TABLE cwi_test( a int , b varchar(10), c char);
-- add some data so that all tests have something to work with.
INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6);
CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b);
ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx;
\d "cwi_test"
Table "public.cwi_test"
Column | Type | Modifiers
--------+-----------------------+--------------------------------------------------------
a | integer | not null
b | character varying(10) | character set UTF8 collate utf8mb4_general_ci not null
c | character(1) | character set UTF8 collate utf8mb4_general_ci
Indexes:
"cwi_uniq_idx" PRIMARY KEY, btree (a, b) TABLESPACE pg_default
CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a);
ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY
USING INDEX cwi_uniq2_idx;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "cwi_uniq2_idx" to "cwi_replaced_pkey"
\d "cwi_test"
Table "public.cwi_test"
Column | Type | Modifiers
--------+-----------------------+--------------------------------------------------------
a | integer | not null
b | character varying(10) | character set UTF8 collate utf8mb4_general_ci not null
c | character(1) | character set UTF8 collate utf8mb4_general_ci
Indexes:
"cwi_replaced_pkey" PRIMARY KEY, btree (b, a) TABLESPACE pg_default
DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it
ERROR: cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it
HINT: You can drop constraint cwi_replaced_pkey on table cwi_test instead.
DROP TABLE cwi_test;
--
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
--
SELECT unique1, unique2 INTO onek_with_null FROM Onek;
INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
count
-------
2
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
count
-------
0
(1 row)
DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
count
-------
2
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
count
-------
0
(1 row)
DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
count
-------
2
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
count
-------
0
(1 row)
DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
count
-------
2
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
count
-------
1
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
count
-------
0
(1 row)
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
count
-------
0
(1 row)
DROP INDEX onek_nulltest;
-- Check initial-positioning logic too
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2);
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
SELECT unique1, unique2 FROM onek_with_null
ORDER BY unique2 LIMIT 2;
unique1 | unique2
---------+---------
| -1
|
(2 rows)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
ORDER BY unique2 LIMIT 2;
unique1 | unique2
---------+---------
| -1
(1 row)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0
ORDER BY unique2 LIMIT 2;
unique1 | unique2
---------+---------
(0 rows)
SELECT unique1, unique2 FROM onek_with_null
ORDER BY unique2 DESC LIMIT 2;
unique1 | unique2
---------+---------
|
| -1
(2 rows)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
ORDER BY unique2 DESC LIMIT 2;
unique1 | unique2
---------+---------
| -1
(1 row)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999
ORDER BY unique2 DESC LIMIT 2;
unique1 | unique2
---------+---------
| -1
(1 row)
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
DROP TABLE onek_with_null;
--
-- Check bitmap index path planning
--
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT * FROM Tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT * FROM Tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
(0 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM Tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM Tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
count
-------
0
(1 row)
--
-- Check behavior with duplicate index column contents
--
CREATE TABLE dupindexcols AS
SELECT unique1 as id, stringu2::text as f1 FROM Tenk1;
CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops);
ANALYZE dupindexcols;
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT count(*) FROM dupindexcols
WHERE f1 > 'WA' and id < 1000 and f1 ~<~ 'YX';
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT count(*) FROM dupindexcols
WHERE f1 > 'WA' and id < 1000 and f1 ~<~ 'YX';
count
-------
0
(1 row)
--
-- Check ordering of =ANY indexqual results (bug in 9.2.0)
--
vacuum analyze Tenk1; -- ensure we get consistent plans here
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT unique1 FROM Tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT unique1 FROM Tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
unique1
---------
(0 rows)
EXPLAIN (NUM_NODES OFF, NODES OFF, COSTS OFF)
SELECT thousand, tenthous FROM Tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
ERROR: unrecognized EXPLAIN option "num_nodes"
SELECT thousand, tenthous FROM Tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
thousand | tenthous
----------+----------
(0 rows)
--test for relpages and reltuples of pg_class
create table test_table(a int, b int);
create table test_table_col(a int, b int) with (orientation=column);
insert into test_table select generate_series(1,100),generate_series(1,100);
insert into test_table_col select * from test_table;
analyze test_table;
analyze test_table_col;
select relpages>0 as relpages,reltuples>0 as reltuples from pg_class where relname='test_table';
relpages | reltuples
----------+-----------
t | t
(1 row)
create index test_table_idx1 on test_table(a,b);
select relpages>0 as relpages,reltuples>0 as reltuples from pg_class where relname='test_table';
relpages | reltuples
----------+-----------
t | t
(1 row)
analyze test_table;
select relpages>0 as relpages,reltuples>0 as reltuples from pg_class where relname='test_table_idx1';
relpages | reltuples
----------+-----------
t | t
(1 row)
select relpages>0 as relpages,reltuples>0 as reltuples from pg_class where relname='test_table_col';
relpages | reltuples
----------+-----------
t | t
(1 row)
create index test_table_col_idx1 on test_table_col(a,b);
select relpages>0 as relpages,reltuples>0 as reltuples from pg_class where relname='test_table_col';
relpages | reltuples
----------+-----------
t | t
(1 row)
analyze test_table_col;
select relpages>0 as relpages,reltuples>0 as reltuples from pg_class where relname='test_table_col_idx1';
relpages | reltuples
----------+-----------
t | t
(1 row)
--test nestloop index param path
create table t_hash_table (a int, b int);
create table t_rep_table (a int, b int) ;
create index idx_rep_table on t_rep_table(a);
explain (costs off) select /*+ rows(t_rep_table #100000) */ * from t_hash_table where t_hash_table.a in (select a from t_rep_table);
QUERY PLAN
----------------------------------------------------------
Nested Loop Semi Join
-> Seq Scan on t_hash_table
-> Index Only Scan using idx_rep_table on t_rep_table
Index Cond: (a = t_hash_table.a)
(4 rows)
explain (costs off) select /*+ rows(t_rep_table #100000) */ * from t_hash_table where '1' = '0' or t_hash_table.a in (select a from t_rep_table);
QUERY PLAN
----------------------------------------------------------
Nested Loop Semi Join
-> Seq Scan on t_hash_table
-> Index Only Scan using idx_rep_table on t_rep_table
Index Cond: (a = t_hash_table.a)
(4 rows)
drop index idx_rep_table;
drop table t_hash_table;
drop table t_rep_table;
drop index test_table_idx1;
drop index test_table_col_idx1;
drop table test_table;
drop table test_table_col;
create schema IDX1;
set search_path = 'IDX1';
create table TT1(id int);
create index idxTest1 on TT1(id);
-- should fail, as shema name is case sensitive.
reindex index idx1.idxTest1;
ERROR: schema "idx1" does not exist
alter index idx1.idxTest1 rename to idxTest2;
ERROR: schema "idx1" does not exist
drop index idx1.idxTest2;
ERROR: schema "idx1" does not exist
-- should work, as shema name is case sensitive and index name is case ignored.
reindex index IDX1.idxTest1;
alter index IDX1.idxTest1 rename to idxTest2;
drop index IDX1.idxTest2;
create index idxTest1 on TT1(id);
-- should fail.
reindex index "idxTest1";
ERROR: relation "idxTest1" does not exist
alter index "idxTest1" rename to "idxTest2";
ERROR: relation "idxTest1" does not exist
drop index "idxTest2";
ERROR: index "idxTest2" does not exist
-- should work.
reindex index idxTest1;
alter index idxTest1 rename to idxTest2;
drop index idxTest2;
create index idxTest1 on TT1(id);
-- should work.
reindex index "idxtest1";
alter index "idxtest1" rename to "idxtest2";
drop index "idxtest2";
create index idxTest1 on TT1(id);
-- should work.
reindex index idxtest1;
alter index idxtest1 rename to idxtest2;
drop index idxtest2;
create index idxTest1 on TT1(id);
-- should fail.
reindex index "IDXTEST1";
ERROR: relation "IDXTEST1" does not exist
alter index "IDXTEST1" rename to "IDXTEST2";
ERROR: relation "IDXTEST1" does not exist
drop index "IDXTEST2";
ERROR: index "IDXTEST2" does not exist
-- should work.
reindex index IDXTEST1;
alter index IDXTEST1 rename to IDXTEST2;
drop index IDXTEST2;
create index IDX1.idxTest1 on TT1(id);
-- should fail.
reindex index IDX1."idxTest1";
ERROR: relation "IDX1.idxTest1" does not exist
alter index IDX1."idxTest1" rename to "idxTest2";
ERROR: relation "IDX1.idxTest1" does not exist
drop index IDX1."idxTest2";
ERROR: index "idxTest2" does not exist
-- should work.
reindex index IDX1.idxTest1;
alter index IDX1.idxTest1 rename to idxTest2;
drop index IDX1.idxTest2;
create index IDX1.idxTest1 on TT1(id);
-- should fail.
reindex index "idxTest1";
ERROR: relation "idxTest1" does not exist
alter index "idxTest1" rename to "idxTest2";
ERROR: relation "idxTest1" does not exist
drop index "idxTest2";
ERROR: index "idxTest2" does not exist
-- should work.
reindex index idxTest1;
alter index idxTest1 rename to idxTest2;
drop index idxTest2;
create index IDX1.idxTest1 on TT1(id);
-- should work.
reindex index idxtest1;
alter index idxtest1 rename to idxtest2;
drop index idxtest2;
create index IDX1.idxTest1 on TT1(id);
-- should fail.
reindex index "IDXTEST1";
ERROR: relation "IDXTEST1" does not exist
alter index "IDXTEST1" rename to "IDXTEST2";
ERROR: relation "IDXTEST1" does not exist
drop index "IDXTEST2";
ERROR: index "IDXTEST2" does not exist
-- should work.
reindex index IDXTEST1;
alter index IDXTEST1 rename to IDXTEST2;
drop index IDXTEST2;
set dolphin.lower_case_table_names to 1;
create index idxTest1 on "TT1"(id);
-- should work.
reindex index "IDX1".idxTest1;
alter index "IDX1".idxTest1 rename to idxTest2;
drop index "IDX1".idxTest2;
create index idxTest1 on "TT1"(id);
-- should fail.
reindex index "IDX1"."IDXTEST1";
ERROR: relation "IDX1.IDXTEST1" does not exist
alter index "IDX1"."IDXTEST1" rename to "IDXTEST2";
ERROR: relation "IDX1.IDXTEST1" does not exist
drop index "IDX1"."IDXTEST2";
ERROR: index "IDXTEST2" does not exist
-- should work.
reindex index "IDX1".IDXTEST1;
alter index "IDX1".IDXTEST1 rename to IDXTEST2;
drop index "IDX1".IDXTEST2;
create index idxTest1 on "TT1"(id);
-- should work.
reindex index "IDX1".idxtest1;
alter index "IDX1".idxtest1 rename to idxtest2;
drop index "IDX1".idxtest2;
create index idxTest1 on "TT1"(id);
-- should work.
reindex index "IDX1"."idxtest1";
alter index "IDX1"."idxtest1" rename to "idxtest2";
drop index "IDX1"."idxtest2";
create index idxTest1 on "TT1"(id);
-- should fail.
reindex index "idxTest1";
ERROR: relation "idxTest1" does not exist
alter index "idxTest1" rename to "idxTest2";
ERROR: relation "idxTest1" does not exist
drop index "idxTest2";
ERROR: index "idxTest2" does not exist
-- should work.
reindex index idxTest1;
alter index idxTest1 rename to idxTest2;
drop index idxTest2;
create index idxTest1 on "TT1"(id);
-- should fail.
reindex index "IDXTEST1";
ERROR: relation "IDXTEST1" does not exist
alter index "IDXTEST1" rename to "IDXTEST2";
ERROR: relation "IDXTEST1" does not exist
drop index "IDXTEST2";
ERROR: index "IDXTEST2" does not exist
-- should work.
reindex index IDXTEST1;
alter index IDXTEST1 rename to IDXTEST2;
drop index IDXTEST2;
create index idxTest1 on "TT1"(id);
-- should work.
reindex index idxtest1;
alter index idxtest1 rename to idxtest2;
drop index idxtest2;
create index idxTest1 on "TT1"(id);
-- should work.
reindex index "idxtest1";
alter index "idxtest1" rename to "idxtest2";
drop index "idxtest2";
create index "IDX1".idxTest1 on "TT1"(id);
-- should fail.
reindex index "IDX1"."idxTest1";
ERROR: relation "IDX1.idxTest1" does not exist
alter index "IDX1"."idxTest1" rename to "idxTest2";
ERROR: relation "IDX1.idxTest1" does not exist
drop index "IDX1"."idxTest2";
ERROR: index "idxTest2" does not exist
-- should work.
reindex index "IDX1".idxTest1;
alter index "IDX1".idxTest1 rename to idxTest2;
drop index "IDX1".idxTest2;
create index "IDX1".idxTest1 on "TT1"(id);
-- should fail.
reindex index "IDX1"."IDXTEST1";
ERROR: relation "IDX1.IDXTEST1" does not exist
alter index "IDX1"."IDXTEST1" rename to "IDXTEST2";
ERROR: relation "IDX1.IDXTEST1" does not exist
drop index "IDX1"."IDXTEST2";
ERROR: index "IDXTEST2" does not exist
-- should work.
reindex index "IDX1".IDXTEST1;
alter index "IDX1".IDXTEST1 rename to IDXTEST2;
drop index "IDX1".IDXTEST2;
create index "IDX1".idxTest1 on "TT1"(id);
-- should work.
reindex index "IDX1".idxtest1;
alter index "IDX1".idxtest1 rename to idxtest2;
drop index "IDX1".idxtest2;
create index "IDX1".idxTest1 on "TT1"(id);
-- should work.
reindex index "IDX1"."idxtest1";
alter index "IDX1"."idxtest1" rename to "idxtest2";
drop index "IDX1"."idxtest2";
reset search_path;
set dolphin.lower_case_table_names to 0;
drop schema IDX1 cascade;
NOTICE: drop cascades to table "IDX1"."TT1"