Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / create_index_optimizer.out
blob: f661e53f487e9ddb836b3157ed8a005f3b8e8046 [file] [log] [blame]
--
-- CREATE_INDEX
-- Create ancillary data structures (i.e. indices)
--
--
-- BTREE
--
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);
NOTICE: relation "onek_unique1" already exists, skipping
CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);
ERROR: syntax error at or near "ON"
LINE 1: CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_...
^
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);
INSERT INTO POINT_TBL(f1) VALUES (NULL);
ANALYZE POINT_TBL;
CREATE INDEX gpointind ON point_tbl USING gist (f1);
CREATE TEMP TABLE gpolygon_tbl AS
SELECT polygon(home_base) AS f1 FROM slow_emp4000;
ANALYZE gpolygon_tbl;
INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' );
INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' );
CREATE TEMP TABLE gcircle_tbl AS
SELECT circle(home_base) AS f1 FROM slow_emp4000;
ANALYZE gcircle_tbl;
CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
SET optimizer_enable_tablescan = OFF;
--
-- Test 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
-----------------------
(337,455),(240,359)
(1444,403),(1346,344)
(2 rows)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
count
-------
2
(1 row)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
count
-------
278
(1 row)
SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
f1
---------------------
((2,0),(2,4),(0,0))
(1 row)
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
f1
---------------
<(1,2),3>
<(1,3),5>
<(1,2),100>
<(100,1),115>
(4 rows)
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
count
-------
2
(1 row)
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
count
-------
2
(1 row)
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
-------
4
(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
-------
4
(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
-------
5
(1 row)
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
count
-------
1
(1 row)
--Comment out unstable test.
--SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
CREATE VIEW point_tblv AS
SELECT * FROM point_tbl
WHERE NOT f1 ~= '(1e-300, -1e-300)' AND (f1 <-> '(0,0)') != 'inf';
-- In gpdb, we intentional filter out point (1e-300, -1e-300) and `inf` every order by related queries
-- in this test case file. It is an underflow point, rank it cause randomly results( (0,0),
-- (1e-300, -1e-300) are equal).
SELECT * FROM point_tblv ORDER BY f1 <-> '0,1';
f1
------------
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(NaN,NaN)
(6 rows)
SELECT * FROM point_tbl WHERE f1 IS NULL;
f1
----
(1 row)
SELECT * FROM point_tblv WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
f1
------------
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(NaN,NaN)
(6 rows)
SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
f1
---------
(-3,4)
(-10,0)
(10,10)
(3 rows)
SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10;
f1
-------------------------------------------------
((240,359),(240,455),(337,455),(337,359))
((662,163),(662,187),(759,187),(759,163))
((1000,0),(0,1000))
((0,1000),(1000,1000))
((1346,344),(1346,403),(1444,403),(1444,344))
((278,1409),(278,1457),(369,1457),(369,1409))
((907,1156),(907,1201),(948,1201),(948,1156))
((1517,971),(1517,1043),(1594,1043),(1594,971))
((175,1820),(175,1850),(259,1850),(259,1820))
((2424,81),(2424,160),(2424,160),(2424,81))
(10 rows)
SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10;
circle_center | radius
----------------+--------
(288.5,407) | 68
(710.5,175) | 50
(323.5,1433) | 51
(927.5,1178.5) | 30
(1395,373.5) | 57
(1555.5,1007) | 53
(217,1835) | 45
(489,2421.5) | 22
(2424,120.5) | 40
(751.5,2655) | 20
(10 rows)
-- Now check the results from plain indexscan
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
EXPLAIN (COSTS OFF)
SELECT * FROM fast_emp4000
WHERE home_base <@ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
QUERY PLAN
----------------------------------------------------------------------------
Result
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((home_base[0])[0])
-> Sort
Sort Key: ((home_base[0])[0])
-> Index Scan using grect2ind on fast_emp4000
Index Cond: (home_base <@ '(2000,1000),(200,200)'::box)
Filter: (home_base <@ '(2000,1000),(200,200)'::box)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM fast_emp4000
WHERE home_base <@ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
home_base
-----------------------
(337,455),(240,359)
(1444,403),(1346,344)
(2 rows)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
QUERY PLAN
-------------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Index Scan using grect2ind on fast_emp4000
Index Cond: (home_base && '(1000,1000),(0,0)'::box)
Filter: (home_base && '(1000,1000),(0,0)'::box)
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
count
-------
2
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
QUERY PLAN
-------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Index Only Scan using grect2ind on fast_emp4000
Index Cond: (home_base IS NULL)
Optimizer: Postgres query optimizer
(6 rows)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
count
-------
278
(1 row)
EXPLAIN (COSTS OFF)
SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
QUERY PLAN
-----------------------------------------------------------------------
Result
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((poly_center(f1))[0])
-> Sort
Sort Key: ((poly_center(f1))[0])
-> Index Scan using gpolygonind on polygon_tbl
Index Cond: (f1 @> '((1,1),(2,2),(2,1))'::polygon)
Filter: (f1 @> '((1,1),(2,2),(2,1))'::polygon)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
f1
---------------------
((2,0),(2,4),(0,0))
(1 row)
EXPLAIN (COSTS OFF)
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
QUERY PLAN
--------------------------------------------------------------
Result
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: (area(f1))
-> Sort
Sort Key: (area(f1))
-> Index Scan using gcircleind on circle_tbl
Index Cond: (f1 && '<(1,-2),1>'::circle)
Filter: (f1 && '<(1,-2),1>'::circle)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
f1
---------------
<(1,2),3>
<(1,3),5>
<(1,2),100>
<(100,1),115>
(4 rows)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
QUERY PLAN
------------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Index Scan using ggpolygonind on gpolygon_tbl
Index Cond: (f1 && '((1000,1000),(0,0))'::polygon)
Filter: (f1 && '((1000,1000),(0,0))'::polygon)
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
count
-------
2
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
QUERY PLAN
-------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Index Scan using ggcircleind on gcircle_tbl
Index Cond: (f1 && '<(500,500),500>'::circle)
Filter: (f1 && '<(500,500),500>'::circle)
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
count
-------
2
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
QUERY PLAN
----------------------------------------------------------
Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl
Index Cond: (f1 <@ '(100,100),(0,0)'::box)
Filter: (f1 <@ '(100,100),(0,0)'::box)
Optimizer: Pivotal Optimizer (GPORCA)
(6 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
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl
Index Cond: (f1 <@ '(100,100),(0,0)'::box)
Filter: (f1 <@ '(100,100),(0,0)'::box)
Optimizer: Pivotal Optimizer (GPORCA)
(6 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
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl
Index Cond: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
Filter: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)
SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
count
-------
4
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
QUERY PLAN
----------------------------------------------------------
Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl
Index Cond: (f1 <@ '<(50,50),50>'::circle)
Filter: (f1 <@ '<(50,50),50>'::circle)
Optimizer: Pivotal Optimizer (GPORCA)
(6 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
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl p
Index Cond: (f1 << '(0,0)'::point)
Filter: (f1 << '(0,0)'::point)
Optimizer: Pivotal Optimizer (GPORCA)
(6 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
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl p
Index Cond: (f1 >> '(0,0)'::point)
Filter: (f1 >> '(0,0)'::point)
Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)
SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
count
-------
4
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 <<| '(0.0, 0.0)';
QUERY PLAN
-------------------------------------------------------
Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl p
Index Cond: (f1 <<| '(0,0)'::point)
Filter: (f1 <<| '(0,0)'::point)
Optimizer: GPORCA
(6 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
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl p
Index Cond: (f1 |>> '(0,0)'::point)
Filter: (f1 |>> '(0,0)'::point)
Optimizer: GPORCA
(6 rows)
SELECT count(*) FROM point_tbl p WHERE p.f1 |>> '(0.0, 0.0)';
count
-------
5
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
QUERY PLAN
-----------------------------------------------------
Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl p
Index Cond: (f1 ~= '(-5,-12)'::point)
Filter: (f1 ~= '(-5,-12)'::point)
Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
count
-------
1
(1 row)
EXPLAIN (COSTS OFF)
SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((f1 <-> '(0,1)'::point))
-> Index Only Scan using gpointind on point_tbl
Order By: (f1 <-> '(0,1)'::point)
Optimizer: Postgres query optimizer
(5 rows)
--Comment out unstable test.
--SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
EXPLAIN (COSTS OFF)
SELECT * FROM point_tblv ORDER BY f1 <-> '0,1';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((point_tbl.f1 <-> '(0,1)'::point))
-> Index Only Scan using gpointind on point_tbl
Order By: (f1 <-> '(0,1)'::point)
Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision))
Optimizer: Postgres query optimizer
(6 rows)
SELECT * FROM point_tblv ORDER BY f1 <-> '0,1';
f1
------------
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(NaN,NaN)
(6 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM point_tbl WHERE f1 IS NULL;
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Only Scan using gpointind on point_tbl
Index Cond: (f1 IS NULL)
Optimizer: Postgres query optimizer
(4 rows)
SELECT * FROM point_tbl WHERE f1 IS NULL;
f1
----
(1 row)
EXPLAIN (COSTS OFF)
SELECT * FROM point_tblv WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((point_tbl.f1 <-> '(0,1)'::point))
-> Index Only Scan using gpointind on point_tbl
Index Cond: (f1 IS NOT NULL)
Order By: (f1 <-> '(0,1)'::point)
Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision))
Optimizer: Postgres query optimizer
(7 rows)
SELECT * FROM point_tblv WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
f1
------------
(-3,4)
(-10,0)
(10,10)
(-5,-12)
(5.1,34.5)
(NaN,NaN)
(6 rows)
--Comment out unstable test.
--SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
EXPLAIN (COSTS OFF)
SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result
-> Sort
Sort Key: ((f1 <-> '(0,1)'::point))
-> Result
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl
Index Cond: (f1 <@ '(10,10),(-10,-10)'::box)
Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision) AND (f1 <@ '(10,10),(-10,-10)'::box))
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
f1
---------
(-3,4)
(-10,0)
(10,10)
(3 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((f1 <-> '(0,0)'::point))
-> Limit
-> Index Scan using ggpolygonind on gpolygon_tbl
Order By: (f1 <-> '(0,0)'::point)
Optimizer: Postgres query optimizer
(7 rows)
SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10;
f1
-------------------------------------------------
((240,359),(240,455),(337,455),(337,359))
((662,163),(662,187),(759,187),(759,163))
((1000,0),(0,1000))
((0,1000),(1000,1000))
((1346,344),(1346,403),(1444,403),(1444,344))
((278,1409),(278,1457),(369,1457),(369,1409))
((907,1156),(907,1201),(948,1201),(948,1156))
((1517,971),(1517,1043),(1594,1043),(1594,971))
((175,1820),(175,1850),(259,1850),(259,1820))
((2424,81),(2424,160),(2424,160),(2424,81))
(10 rows)
EXPLAIN (COSTS OFF)
SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10;
QUERY PLAN
---------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: ((f1 <-> '(200,300)'::point))
-> Limit
-> Index Scan using ggcircleind on gcircle_tbl
Order By: (f1 <-> '(200,300)'::point)
Optimizer: Postgres query optimizer
(7 rows)
SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10;
circle_center | radius
----------------+--------
(288.5,407) | 68
(710.5,175) | 50
(323.5,1433) | 51
(927.5,1178.5) | 30
(1395,373.5) | 57
(1555.5,1007) | 53
(217,1835) | 45
(489,2421.5) | 22
(2424,120.5) | 40
(751.5,2655) | 20
(10 rows)
EXPLAIN (COSTS OFF)
SELECT point(x,x), (SELECT f1 FROM gpolygon_tbl ORDER BY f1 <-> point(x,x) LIMIT 1) as c FROM generate_series(0,10,1) x;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Function Scan on generate_series x
SubPlan 1
-> Limit
-> Sort
Sort Key: ((gpolygon_tbl.f1 <-> point((x.x)::double precision, (x.x)::double precision)))
-> Result
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on gpolygon_tbl
Optimizer: Postgres query optimizer
(10 rows)
SELECT point(x,x), (SELECT f1 FROM gpolygon_tbl ORDER BY f1 <-> point(x,x) LIMIT 1) as c FROM generate_series(0,10,1) x;
point | c
---------+-------------------------------------------
(0,0) | ((240,359),(240,455),(337,455),(337,359))
(1,1) | ((240,359),(240,455),(337,455),(337,359))
(2,2) | ((240,359),(240,455),(337,455),(337,359))
(3,3) | ((240,359),(240,455),(337,455),(337,359))
(4,4) | ((240,359),(240,455),(337,455),(337,359))
(5,5) | ((240,359),(240,455),(337,455),(337,359))
(6,6) | ((240,359),(240,455),(337,455),(337,359))
(7,7) | ((240,359),(240,455),(337,455),(337,359))
(8,8) | ((240,359),(240,455),(337,455),(337,359))
(9,9) | ((240,359),(240,455),(337,455),(337,359))
(10,10) | ((240,359),(240,455),(337,455),(337,359))
(11 rows)
-- Now check the results from bitmap indexscan
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
EXPLAIN (COSTS OFF)
SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result
-> Sort
Sort Key: ((f1 <-> '(0,1)'::point))
-> Result
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using gpointind on point_tbl
Index Cond: (f1 <@ '(10,10),(-10,-10)'::box)
Filter: ((f1 <> '(1e-300,-1e-300)'::point) AND ((f1 <-> '(0,0)'::point) <> 'Infinity'::double precision) AND (f1 <@ '(10,10),(-10,-10)'::box))
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM point_tblv WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
f1
---------
(-3,4)
(-10,0)
(10,10)
(3 rows)
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 (costs off)
SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
QUERY PLAN
----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: seqno
-> 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[])
Optimizer: Pivotal Optimizer (GPORCA)
(9 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 && '{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}
101 | {} | {}
(4 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)
SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
16 | {14,63,85,11} | {AAAAAA66777}
17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494}
21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449}
26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
53 | {38,17} | {AAAAAAAAAAA21658}
54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406}
57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415}
58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804}
62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
91 | {78} | {AAAAAAAAAAAAA62007,AAA99043}
92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
101 | {} | {}
102 | {NULL} | {NULL}
(102 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
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
seqno | i | t
-------+--------+--------
102 | {NULL} | {NULL}
(1 row)
SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
explain (costs off)
SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: seqno
-> 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[])
Optimizer: Pivotal Optimizer (GPORCA)
(9 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 && '{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}
101 | {} | {}
(3 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)
SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
16 | {14,63,85,11} | {AAAAAA66777}
17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494}
21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449}
26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
53 | {38,17} | {AAAAAAAAAAA21658}
54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406}
57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415}
58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804}
62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
91 | {78} | {AAAAAAAAAAAAA62007,AAA99043}
92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
101 | {} | {}
102 | {NULL} | {NULL}
(102 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
-------+----+----
101 | {} | {}
(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);
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)
SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
seqno | i | t
-------+--------+--------
102 | {NULL} | {NULL}
(1 row)
SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
-- Try a GIN index with a lot of items with same key. (GIN creates a posting
-- tree when there are enough duplicates)
--
CREATE TABLE array_gin_test (a int[]);
INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g;
CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a);
SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}';
count
-------
2000
(1 row)
DROP TABLE array_gin_test;
--
-- 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 | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
i | integer | yes | 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) WITH (fillfactor=60);
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
DROP TABLE unlogged_hash_table;
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
-- Test hash index build tuplesorting. Force hash tuplesort using low
-- maintenance_work_mem setting and fillfactor:
SET maintenance_work_mem = '1MB';
CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10);
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
QUERY PLAN
-------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Index Scan using hash_tuplesort_idx on tenk1
Index Cond: (stringu1 = 'TVAAAA'::name)
Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)
SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
count
-------
14
(1 row)
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
CREATE 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: (In GPDB, the index isn't unique, so no error)
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
-- while we're here, see that the metadata looks sane
\d func_index_heap
Table "public.func_index_heap"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
f1 | text | | |
f2 | text | | |
Indexes:
"func_index_index" btree (textcat(f1, f2))
Distributed by: (f1)
\d func_index_index
Index "public.func_index_index"
Column | Type | Key? | Definition
---------+------+------+-----------------
textcat | text | yes | textcat(f1, f2)
btree, for table "public.func_index_heap"
--
-- Same test, expressional index
--
DROP TABLE func_index_heap;
CREATE TABLE func_index_heap (f1 text, f2 text);
CREATE 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: (In GPDB, the index isn't unique, so no error)
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
-- while we're here, see that the metadata looks sane
\d func_index_heap
Table "public.func_index_heap"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
f1 | text | | |
f2 | text | | |
Indexes:
"func_index_index" btree ((f1 || f2))
Distributed by: (f1)
\d func_index_index
Index "public.func_index_index"
Column | Type | Key? | Definition
--------+------+------+------------
expr | text | yes | (f1 || f2)
btree, for table "public.func_index_heap"
-- this should fail because of unsafe column type (anonymous record)
create index on func_index_heap ((f1 || f2), (row(f1, f2)));
ERROR: column "row" has pseudo-type record
--
-- Test unique index with included columns
--
CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text);
CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3);
INSERT INTO covering_index_heap VALUES(1,1,'AAA');
INSERT INTO covering_index_heap VALUES(1,2,'AAA');
-- this should fail because of unique index on f1,f2:
INSERT INTO covering_index_heap VALUES(1,2,'BBB');
ERROR: duplicate key value violates unique constraint "covering_index_index"
DETAIL: Key (f1, f2)=(1, 2) already exists.
-- and this shouldn't:
INSERT INTO covering_index_heap VALUES(1,4,'AAA');
-- Try to build index on table that already contains data
CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3);
-- Try to use existing covering index as primary key
ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX
covering_pkey;
DROP TABLE covering_index_heap;
--
-- Also try building functional, expressional, and partial indexes on
-- tables that already contain data.
--
create index hash_f8_index_1 on hash_f8_heap(abs(random));
create index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
create 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.
--
-- Concurrent index building is not currently supported in Cloudberry.
CREATE TABLE concur_heap (f1 text, f2 text, dk text) distributed by (dk);
-- empty table
CREATE INDEX concur_index1 ON concur_heap(f2,f1);
CREATE INDEX IF NOT EXISTS concur_index1 ON concur_heap(f2,f1);
NOTICE: relation "concur_index1" already exists, skipping
INSERT INTO concur_heap VALUES ('a','b', '1');
INSERT INTO concur_heap VALUES ('b','b', '1');
INSERT INTO concur_heap VALUES ('c','c', '2');
INSERT INTO concur_heap VALUES ('d','d', '3');
-- unique index
CREATE UNIQUE INDEX concur_index2 ON concur_heap(dk, f1);
CREATE UNIQUE INDEX IF NOT EXISTS concur_index2 ON concur_heap(dk, f1);
NOTICE: relation "concur_index2" already exists, skipping
-- check if constraint is set up properly to be enforced
INSERT INTO concur_heap VALUES ('b','x', '1');
ERROR: duplicate key value violates unique constraint "concur_index2" (seg0 127.0.1.1:7002 pid=3254)
DETAIL: Key (dk, f1)=(1, b) already exists.
-- check if constraint is enforced properly at build time
CREATE UNIQUE INDEX concur_index3 ON concur_heap(dk, f2);
ERROR: could not create unique index "concur_index3"
DETAIL: Key (dk, f2)=(1, b) is duplicated.
-- test that expression indexes and partial indexes work concurrently
CREATE INDEX concur_index4 on concur_heap(f2) WHERE f1='a';
CREATE INDEX concur_index5 on concur_heap(f2) WHERE f1='x';
-- here we also check that you can default the index name
CREATE INDEX on concur_heap((f2||f1));
-- 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
COMMIT;
-- test where predicate is able to do a transactional update during
-- a concurrent build before switching pg_index state flags.
CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE
LANGUAGE plpgsql AS $$
BEGIN
EXECUTE 'SELECT txid_current()';
RETURN true;
END; $$;
CREATE INDEX concur_index8 ON concur_heap (f1)
WHERE predicate_stable();
DROP INDEX concur_index8;
DROP FUNCTION predicate_stable();
-- But you can do a regular index build in a transaction
BEGIN;
CREATE INDEX std_index on concur_heap(f2);
COMMIT;
-- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX
VACUUM FULL concur_heap;
REINDEX TABLE concur_heap;
DELETE FROM concur_heap WHERE f1 = 'b';
NOTICE: One or more columns in the following table(s) do not have statistics: concur_heap
HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics.
VACUUM FULL concur_heap;
\d concur_heap
Table "public.concur_heap"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
f1 | text | | |
f2 | text | | |
dk | text | | |
Indexes:
"concur_heap_expr_idx" btree ((f2 || f1))
"concur_index1" btree (f2, f1)
"concur_index2" UNIQUE, btree (dk, f1)
"concur_index4" btree (f2) WHERE f1 = 'a'::text
"concur_index5" btree (f2) WHERE f1 = 'x'::text
"std_index" btree (f2)
Distributed by: (dk)
REINDEX TABLE concur_heap;
\d concur_heap
Table "public.concur_heap"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
f1 | text | | |
f2 | text | | |
dk | text | | |
Indexes:
"concur_heap_expr_idx" btree ((f2 || f1))
"concur_index1" btree (f2, f1)
"concur_index2" UNIQUE, btree (dk, f1)
"concur_index4" btree (f2) WHERE f1 = 'a'::text
"concur_index5" btree (f2) WHERE f1 = 'x'::text
"std_index" btree (f2)
Distributed by: (dk)
-- Temporary tables with concurrent builds and on-commit actions
-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored.
-- PRESERVE ROWS, the default.
CREATE TEMP TABLE concur_temp (f1 int, f2 text)
ON COMMIT PRESERVE ROWS;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
CREATE INDEX concur_temp_ind ON concur_temp(f1);
DROP INDEX concur_temp_ind;
DROP TABLE concur_temp;
-- ON COMMIT DROP
BEGIN;
CREATE TEMP TABLE concur_temp (f1 int, f2 text)
ON COMMIT DROP;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
-- Fails when running in a transaction.
CREATE INDEX concur_temp_ind ON concur_temp(f1);
COMMIT;
-- ON COMMIT DELETE ROWS
CREATE TEMP TABLE concur_temp (f1 int, f2 text)
ON COMMIT DELETE ROWS;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
CREATE INDEX concur_temp_ind ON concur_temp(f1);
DROP INDEX concur_temp_ind;
DROP TABLE concur_temp;
--
-- Try some concurrent index drops
-- Cloudberry: The functionality of these tests is replicated in gp_index
--
DROP INDEX "concur_index2"; -- works
DROP INDEX IF EXISTS "concur_index2"; -- notice
NOTICE: index "concur_index2" does not exist, skipping
-- failures
DROP INDEX "concur_index2", "concur_index3";
ERROR: index "concur_index2" does not exist
BEGIN;
DROP INDEX "concur_index5";
ROLLBACK;
-- successes
DROP INDEX IF EXISTS "concur_index3";
NOTICE: index "concur_index3" does not exist, skipping
DROP INDEX "concur_index4";
DROP INDEX "concur_index5";
DROP INDEX "concur_index1";
DROP INDEX "concur_heap_expr_idx";
\d concur_heap
Table "public.concur_heap"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
f1 | text | | |
f2 | text | | |
dk | text | | |
Indexes:
"std_index" btree (f2)
Distributed by: (dk)
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 | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
a | integer | | not null |
b | character varying(10) | | not null |
c | character(1) | | |
Indexes:
"cwi_uniq_idx" PRIMARY KEY, btree (a, b)
Distributed by: (a)
\d cwi_uniq_idx
Index "public.cwi_uniq_idx"
Column | Type | Key? | Definition
--------+-----------------------+------+------------
a | integer | yes | a
b | character varying(10) | yes | b
primary key, btree, for table "public.cwi_test"
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 | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
a | integer | | not null |
b | character varying(10) | | not null |
c | character(1) | | |
Indexes:
"cwi_replaced_pkey" PRIMARY KEY, btree (b, a)
Distributed by: (a)
\d cwi_replaced_pkey
Index "public.cwi_replaced_pkey"
Column | Type | Key? | Definition
--------+-----------------------+------+------------
b | character varying(10) | yes | b
a | integer | yes | a
primary key, btree, for table "public.cwi_test"
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.
-- Check that non-default index options are rejected
CREATE UNIQUE INDEX cwi_uniq3_idx ON cwi_test(a desc);
ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx; -- fail
ERROR: index "cwi_uniq3_idx" column number 1 does not have default sorting behavior
LINE 1: ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx;
^
DETAIL: Cannot create a primary key or unique constraint using such an index.
CREATE UNIQUE INDEX cwi_uniq4_idx ON cwi_test(b collate "POSIX", a);
ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx; -- fail
ERROR: index "cwi_uniq4_idx" column number 1 does not have default sorting behavior
LINE 1: ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx;
^
DETAIL: Cannot create a primary key or unique constraint using such an index.
DROP TABLE cwi_test;
-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables
CREATE TABLE cwi_test(a int) PARTITION BY hash (a);
create unique index on cwi_test (a);
alter table cwi_test add primary key using index cwi_test_a_idx ;
ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables
DROP TABLE cwi_test;
--
-- Check handling of indexes on system columns
--
CREATE TABLE syscol_table (a INT);
-- System columns cannot be indexed
CREATE INDEX ON syscolcol_table (ctid);
ERROR: relation "syscolcol_table" does not exist
-- nor used in expressions
CREATE INDEX ON syscol_table ((ctid >= '(1000,0)'));
ERROR: index creation on system columns is not supported
-- nor used in predicates
CREATE INDEX ON syscol_table (a) WHERE ctid >= '(1000,0)';
ERROR: index creation on system columns is not supported
DROP TABLE syscol_table;
--
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
--
CREATE TABLE onek_with_null AS SELECT unique1, unique2 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
-------
1000
(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
-------
499
(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
-------
1000
(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
-------
499
(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
-------
1000
(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
-------
499
(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
-------
1000
(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
-------
499
(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);
ERROR: UNIQUE index must contain all columns in the table's distribution key
DETAIL: Distribution key column "unique1" is not included in the constraint.
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
147 | 0
(2 rows)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
ORDER BY unique2 LIMIT 2;
unique1 | unique2
---------+---------
| -1
147 | 0
(2 rows)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0
ORDER BY unique2 LIMIT 2;
unique1 | unique2
---------+---------
147 | 0
931 | 1
(2 rows)
SELECT unique1, unique2 FROM onek_with_null
ORDER BY unique2 DESC LIMIT 2;
unique1 | unique2
---------+---------
|
278 | 999
(2 rows)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
ORDER BY unique2 DESC LIMIT 2;
unique1 | unique2
---------+---------
278 | 999
0 | 998
(2 rows)
SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999
ORDER BY unique2 DESC LIMIT 2;
unique1 | unique2
---------+---------
0 | 998
744 | 997
(2 rows)
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
DROP TABLE onek_with_null;
--
-- Check bitmap index path planning
--
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
QUERY PLAN
-----------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using tenk1_thous_tenthous on tenk1
Index Cond: (thousand = 42)
Filter: ((tenthous = 1) OR (tenthous = 3) OR (tenthous = 42))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
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
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
QUERY PLAN
------------------------------------------------------------
Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using tenk1_hundred on tenk1
Index Cond: (hundred = 42)
Filter: ((thousand = 42) OR (thousand = 99))
Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
count
-------
10
(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 (COSTS OFF)
SELECT count(*) FROM dupindexcols
WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
QUERY PLAN
----------------------------------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Bitmap Heap Scan on dupindexcols
Recheck Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))
-> Bitmap Index Scan on dupindexcols_i
Index Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))
Optimizer: Postgres query optimizer
(8 rows)
SELECT count(*) FROM dupindexcols
WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
count
-------
97
(1 row)
--
-- Check ordering of =ANY indexqual results (bug in 9.2.0)
--
vacuum tenk1; -- ensure we get consistent plans here
explain (costs off)
SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
QUERY PLAN
-------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: unique1
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
unique1
---------
1
7
42
(3 rows)
explain (costs off)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: thousand
-> Sort
Sort Key: thousand
-> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: (thousand < 2)
Filter: (tenthous = ANY ('{1001,3000}'::integer[]))
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
thousand | tenthous
----------+----------
0 | 3000
1 | 1001
(2 rows)
SET enable_indexonlyscan = OFF;
explain (costs off)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: thousand
-> Sort
Sort Key: thousand
-> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: (thousand < 2)
Filter: (tenthous = ANY ('{1001,3000}'::integer[]))
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
thousand | tenthous
----------+----------
0 | 3000
1 | 1001
(2 rows)
RESET enable_indexonlyscan;
--
-- Check elimination of constant-NULL subexpressions
--
explain (costs off)
select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 1) AND (tenthous = 1001))
Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)
--
-- Check matching of boolean index columns to WHERE conditions and sort keys
--
create temp table boolindex (b bool, i int, unique(b, i), junk float);
explain (costs off)
select * from boolindex order by b, i limit 10;
QUERY PLAN
-------------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: b, i
-> Index Scan using boolindex_b_i_key on boolindex
Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)
-- In PostgreSQL, this uses an Index Scan. In GPDB, the logic to deduce
-- which path keys are useful for ordering miss this case. That's because
-- we build a path key to also represent the DISTRIBUTED BY key, which is 'b'
-- for this table, and the index is deemed not useful for that path key.
-- We can live with that. But to cover the same codepaths as in upstream,
-- run the test again with different distribution key so that index scans
-- are used.
explain (costs off)
select * from boolindex where b order by i limit 10;
QUERY PLAN
------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i
-> Sort
Sort Key: i
-> Index Scan using boolindex_b_i_key on boolindex
Index Cond: (b = true)
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
-- Repeate with different distribution key
alter table boolindex set distributed by (i);
explain (costs off)
select * from boolindex where b order by i limit 10;
QUERY PLAN
-------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i
-> Sort
Sort Key: i
-> Index Scan using boolindex_b_i_key on boolindex
Index Cond: (b = true)
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
explain (costs off)
select * from boolindex where b = true order by i desc limit 10;
QUERY PLAN
----------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i
-> Sort
Sort Key: i DESC
-> Index Scan using boolindex_b_i_key on boolindex
Index Cond: (b = true)
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
explain (costs off)
select * from boolindex where not b order by i limit 10;
QUERY PLAN
-------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i
-> Sort
Sort Key: i
-> Index Scan using boolindex_b_i_key on boolindex
Index Cond: (b = false)
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
explain (costs off)
select * from boolindex where b is true order by i desc limit 10;
QUERY PLAN
----------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i
-> Limit
-> Index Scan Backward using boolindex_b_i_key on boolindex
Index Cond: (b = true)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off)
select * from boolindex where b is false order by i desc limit 10;
QUERY PLAN
----------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i
-> Limit
-> Index Scan Backward using boolindex_b_i_key on boolindex
Index Cond: (b = false)
Optimizer: Postgres query optimizer
(7 rows)
--
-- REINDEX (VERBOSE)
--
CREATE TABLE reindex_verbose(id integer primary key);
\set VERBOSITY terse \\ -- suppress machine-dependent details
REINDEX (VERBOSE) TABLE reindex_verbose;
INFO: index "reindex_verbose_pkey" was reindexed
INFO: index "reindex_verbose_pkey" was reindexed (seg0 127.0.1.1:7002 pid=3296159)
INFO: index "reindex_verbose_pkey" was reindexed (seg1 127.0.1.1:7003 pid=3296160)
INFO: index "reindex_verbose_pkey" was reindexed (seg2 127.0.1.1:7004 pid=3296161)
\set VERBOSITY default
DROP TABLE reindex_verbose;
--
-- REINDEX CONCURRENTLY
--
CREATE TABLE concur_reindex_tab (c1 int);
-- REINDEX
REINDEX TABLE concur_reindex_tab; -- notice
NOTICE: table "concur_reindex_tab" has no indexes to reindex
REINDEX TABLE concur_reindex_tab; -- notice
NOTICE: table "concur_reindex_tab" has no indexes to reindex
ALTER TABLE concur_reindex_tab ADD COLUMN c2 text; -- add toast index
-- Normal index with integer column
CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab(c1);
-- Normal index with text column
CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab(c2);
-- UNIQUE index with expression
CREATE UNIQUE INDEX concur_reindex_ind3 ON concur_reindex_tab(c1, abs(c1));
-- Duplicate column names
CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab(c1, c1, c2);
-- Create table for check on foreign key dependence switch with indexes swapped
ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
CREATE TABLE concur_reindex_tab2 (c1 int REFERENCES concur_reindex_tab);
INSERT INTO concur_reindex_tab VALUES (1, 'a');
INSERT INTO concur_reindex_tab VALUES (2, 'a');
-- Reindex concurrently of exclusion constraint currently not supported
-- start_ignore
/*
* CREATE TABLE concur_reindex_tab3 (c1 int, c2 int4range, EXCLUDE USING gist (c2 WITH &&));
*
* -- GPDB: The above fails, because the exclusion constraint doesn't include the
* -- distribution key. But this works.
* CREATE TABLE concur_reindex_tab3 (c1 int, c2 int4range,
* distkey int4range DEFAULT 'empty',
* EXCLUDE USING gist (c2 WITH &&, distkey WITH =))
* DISTRIBUTED BY (distkey);
*
* INSERT INTO concur_reindex_tab3 VALUES (3, '[1,2]');
* REINDEX INDEX concur_reindex_tab3_c2_excl; -- error
* REINDEX TABLE concur_reindex_tab3; -- succeeds with warning
* INSERT INTO concur_reindex_tab3 VALUES (4, '[2,4]');
*/
-- end_ignore
-- Check materialized views
CREATE MATERIALIZED VIEW concur_reindex_matview AS SELECT * FROM concur_reindex_tab;
-- Dependency lookup before and after the follow-up REINDEX commands.
-- These should remain consistent.
SELECT pg_describe_object(classid, objid, objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
deptype
FROM pg_depend
WHERE classid = 'pg_class'::regclass AND
objid in ('concur_reindex_tab'::regclass,
'concur_reindex_ind1'::regclass,
'concur_reindex_ind2'::regclass,
'concur_reindex_ind3'::regclass,
'concur_reindex_ind4'::regclass,
'concur_reindex_matview'::regclass)
ORDER BY 1, 2;
obj | objref | deptype
------------------------------------------+------------------------------------------------------------+---------
index concur_reindex_ind1 | constraint concur_reindex_ind1 on table concur_reindex_tab | i
index concur_reindex_ind2 | column c2 of table concur_reindex_tab | a
index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
index concur_reindex_ind4 | column c1 of table concur_reindex_tab | a
index concur_reindex_ind4 | column c2 of table concur_reindex_tab | a
materialized view concur_reindex_matview | schema public | n
table concur_reindex_tab | schema public | n
(8 rows)
REINDEX INDEX concur_reindex_ind1;
REINDEX TABLE concur_reindex_tab;
REINDEX TABLE concur_reindex_matview;
SELECT pg_describe_object(classid, objid, objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
deptype
FROM pg_depend
WHERE classid = 'pg_class'::regclass AND
objid in ('concur_reindex_tab'::regclass,
'concur_reindex_ind1'::regclass,
'concur_reindex_ind2'::regclass,
'concur_reindex_ind3'::regclass,
'concur_reindex_ind4'::regclass,
'concur_reindex_matview'::regclass)
ORDER BY 1, 2;
obj | objref | deptype
------------------------------------------+------------------------------------------------------------+---------
index concur_reindex_ind1 | constraint concur_reindex_ind1 on table concur_reindex_tab | i
index concur_reindex_ind2 | column c2 of table concur_reindex_tab | a
index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
index concur_reindex_ind4 | column c1 of table concur_reindex_tab | a
index concur_reindex_ind4 | column c2 of table concur_reindex_tab | a
materialized view concur_reindex_matview | schema public | n
table concur_reindex_tab | schema public | n
(8 rows)
-- Check that comments are preserved
CREATE TABLE testcomment (i int);
CREATE INDEX testcomment_idx1 ON testcomment (i);
COMMENT ON INDEX testcomment_idx1 IS 'test comment';
SELECT obj_description('testcomment_idx1'::regclass, 'pg_class');
obj_description
-----------------
test comment
(1 row)
REINDEX TABLE testcomment;
SELECT obj_description('testcomment_idx1'::regclass, 'pg_class');
obj_description
-----------------
test comment
(1 row)
REINDEX TABLE testcomment ;
SELECT obj_description('testcomment_idx1'::regclass, 'pg_class');
obj_description
-----------------
test comment
(1 row)
DROP TABLE testcomment;
-- Check that indisclustered updates are preserved
CREATE TABLE concur_clustered(i int);
CREATE INDEX concur_clustered_i_idx ON concur_clustered(i);
ALTER TABLE concur_clustered CLUSTER ON concur_clustered_i_idx;
REINDEX TABLE concur_clustered;
SELECT indexrelid::regclass, indisclustered FROM pg_index
WHERE indrelid = 'concur_clustered'::regclass;
indexrelid | indisclustered
------------------------+----------------
concur_clustered_i_idx | t
(1 row)
DROP TABLE concur_clustered;
-- Check that indisreplident updates are preserved.
CREATE TABLE concur_replident(i int NOT NULL);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE UNIQUE INDEX concur_replident_i_idx ON concur_replident(i);
ALTER TABLE concur_replident REPLICA IDENTITY
USING INDEX concur_replident_i_idx;
SELECT indexrelid::regclass, indisreplident FROM pg_index
WHERE indrelid = 'concur_replident'::regclass;
indexrelid | indisreplident
------------------------+----------------
concur_replident_i_idx | t
(1 row)
REINDEX TABLE concur_replident;
SELECT indexrelid::regclass, indisreplident FROM pg_index
WHERE indrelid = 'concur_replident'::regclass;
indexrelid | indisreplident
------------------------+----------------
concur_replident_i_idx | t
(1 row)
DROP TABLE concur_replident;
-- Check that opclass parameters are preserved
CREATE TABLE concur_appclass_tab(i tsvector, j tsvector, k tsvector);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry.
CREATE INDEX concur_appclass_ind on concur_appclass_tab
USING gist (i tsvector_ops (siglen='1000'), j tsvector_ops (siglen='500'));
CREATE INDEX concur_appclass_ind_2 on concur_appclass_tab
USING gist (k tsvector_ops (siglen='300'), j tsvector_ops);
REINDEX TABLE concur_appclass_tab;
\d concur_appclass_tab
Table "public.concur_appclass_tab"
Column | Type | Collation | Nullable | Default
--------+----------+-----------+----------+---------
i | tsvector | | |
j | tsvector | | |
k | tsvector | | |
Indexes:
"concur_appclass_ind" gist (i tsvector_ops (siglen='1000'), j tsvector_ops (siglen='500'))
"concur_appclass_ind_2" gist (k tsvector_ops (siglen='300'), j)
Distributed randomly
DROP TABLE concur_appclass_tab;
-- Partitions
-- Create some partitioned tables
CREATE TABLE concur_reindex_part (c1 int, c2 int) PARTITION BY RANGE (c1);
CREATE TABLE concur_reindex_part_0 PARTITION OF concur_reindex_part
FOR VALUES FROM (0) TO (10) PARTITION BY list (c2);
NOTICE: table has parent, setting distribution columns to match parent table
CREATE TABLE concur_reindex_part_0_1 PARTITION OF concur_reindex_part_0
FOR VALUES IN (1);
NOTICE: table has parent, setting distribution columns to match parent table
CREATE TABLE concur_reindex_part_0_2 PARTITION OF concur_reindex_part_0
FOR VALUES IN (2);
NOTICE: table has parent, setting distribution columns to match parent table
-- This partitioned table will have no partitions.
CREATE TABLE concur_reindex_part_10 PARTITION OF concur_reindex_part
FOR VALUES FROM (10) TO (20) PARTITION BY list (c2);
NOTICE: table has parent, setting distribution columns to match parent table
-- Create some partitioned indexes
CREATE INDEX concur_reindex_part_index ON ONLY concur_reindex_part (c1);
CREATE INDEX concur_reindex_part_index_0 ON ONLY concur_reindex_part_0 (c1);
ALTER INDEX concur_reindex_part_index ATTACH PARTITION concur_reindex_part_index_0;
-- This partitioned index will have no partitions.
CREATE INDEX concur_reindex_part_index_10 ON ONLY concur_reindex_part_10 (c1);
ALTER INDEX concur_reindex_part_index ATTACH PARTITION concur_reindex_part_index_10;
CREATE INDEX concur_reindex_part_index_0_1 ON ONLY concur_reindex_part_0_1 (c1);
ALTER INDEX concur_reindex_part_index_0 ATTACH PARTITION concur_reindex_part_index_0_1;
CREATE INDEX concur_reindex_part_index_0_2 ON ONLY concur_reindex_part_0_2 (c1);
ALTER INDEX concur_reindex_part_index_0 ATTACH PARTITION concur_reindex_part_index_0_2;
SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
ORDER BY relid, level;
relid | parentrelid | level
-------------------------------+-----------------------------+-------
concur_reindex_part_index | | 0
concur_reindex_part_index_0 | concur_reindex_part_index | 1
concur_reindex_part_index_10 | concur_reindex_part_index | 1
concur_reindex_part_index_0_1 | concur_reindex_part_index_0 | 2
concur_reindex_part_index_0_2 | concur_reindex_part_index_0 | 2
(5 rows)
SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
ORDER BY relid, level;
relid | parentrelid | level
-------------------------------+-----------------------------+-------
concur_reindex_part_index | | 0
concur_reindex_part_index_0 | concur_reindex_part_index | 1
concur_reindex_part_index_10 | concur_reindex_part_index | 1
concur_reindex_part_index_0_1 | concur_reindex_part_index_0 | 2
concur_reindex_part_index_0_2 | concur_reindex_part_index_0 | 2
(5 rows)
-- REINDEX should preserve dependencies of partition tree.
SELECT pg_describe_object(classid, objid, objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
deptype
FROM pg_depend
WHERE classid = 'pg_class'::regclass AND
objid in ('concur_reindex_part'::regclass,
'concur_reindex_part_0'::regclass,
'concur_reindex_part_0_1'::regclass,
'concur_reindex_part_0_2'::regclass,
'concur_reindex_part_index'::regclass,
'concur_reindex_part_index_0'::regclass,
'concur_reindex_part_index_0_1'::regclass,
'concur_reindex_part_index_0_2'::regclass)
ORDER BY 1, 2;
obj | objref | deptype
------------------------------------------+--------------------------------------------+---------
column c1 of table concur_reindex_part | table concur_reindex_part | i
column c2 of table concur_reindex_part_0 | table concur_reindex_part_0 | i
index concur_reindex_part_index | column c1 of table concur_reindex_part | a
index concur_reindex_part_index_0 | column c1 of table concur_reindex_part_0 | a
index concur_reindex_part_index_0 | index concur_reindex_part_index | P
index concur_reindex_part_index_0 | table concur_reindex_part_0 | S
index concur_reindex_part_index_0_1 | column c1 of table concur_reindex_part_0_1 | a
index concur_reindex_part_index_0_1 | index concur_reindex_part_index_0 | P
index concur_reindex_part_index_0_1 | table concur_reindex_part_0_1 | S
index concur_reindex_part_index_0_2 | column c1 of table concur_reindex_part_0_2 | a
index concur_reindex_part_index_0_2 | index concur_reindex_part_index_0 | P
index concur_reindex_part_index_0_2 | table concur_reindex_part_0_2 | S
table concur_reindex_part | schema public | n
table concur_reindex_part_0 | schema public | n
table concur_reindex_part_0 | table concur_reindex_part | a
table concur_reindex_part_0_1 | schema public | n
table concur_reindex_part_0_1 | table concur_reindex_part_0 | a
table concur_reindex_part_0_2 | schema public | n
table concur_reindex_part_0_2 | table concur_reindex_part_0 | a
(19 rows)
REINDEX INDEX concur_reindex_part_index_0_1;
REINDEX INDEX concur_reindex_part_index_0_2;
SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
ORDER BY relid, level;
relid | parentrelid | level
-------------------------------+-----------------------------+-------
concur_reindex_part_index | | 0
concur_reindex_part_index_0 | concur_reindex_part_index | 1
concur_reindex_part_index_10 | concur_reindex_part_index | 1
concur_reindex_part_index_0_1 | concur_reindex_part_index_0 | 2
concur_reindex_part_index_0_2 | concur_reindex_part_index_0 | 2
(5 rows)
REINDEX TABLE concur_reindex_part_0_1;
REINDEX TABLE concur_reindex_part_0_2;
SELECT pg_describe_object(classid, objid, objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
deptype
FROM pg_depend
WHERE classid = 'pg_class'::regclass AND
objid in ('concur_reindex_part'::regclass,
'concur_reindex_part_0'::regclass,
'concur_reindex_part_0_1'::regclass,
'concur_reindex_part_0_2'::regclass,
'concur_reindex_part_index'::regclass,
'concur_reindex_part_index_0'::regclass,
'concur_reindex_part_index_0_1'::regclass,
'concur_reindex_part_index_0_2'::regclass)
ORDER BY 1, 2;
obj | objref | deptype
------------------------------------------+--------------------------------------------+---------
column c1 of table concur_reindex_part | table concur_reindex_part | i
column c2 of table concur_reindex_part_0 | table concur_reindex_part_0 | i
index concur_reindex_part_index | column c1 of table concur_reindex_part | a
index concur_reindex_part_index_0 | column c1 of table concur_reindex_part_0 | a
index concur_reindex_part_index_0 | index concur_reindex_part_index | P
index concur_reindex_part_index_0 | table concur_reindex_part_0 | S
index concur_reindex_part_index_0_1 | column c1 of table concur_reindex_part_0_1 | a
index concur_reindex_part_index_0_1 | index concur_reindex_part_index_0 | P
index concur_reindex_part_index_0_1 | table concur_reindex_part_0_1 | S
index concur_reindex_part_index_0_2 | column c1 of table concur_reindex_part_0_2 | a
index concur_reindex_part_index_0_2 | index concur_reindex_part_index_0 | P
index concur_reindex_part_index_0_2 | table concur_reindex_part_0_2 | S
table concur_reindex_part | schema public | n
table concur_reindex_part_0 | schema public | n
table concur_reindex_part_0 | table concur_reindex_part | a
table concur_reindex_part_0_1 | schema public | n
table concur_reindex_part_0_1 | table concur_reindex_part_0 | a
table concur_reindex_part_0_2 | schema public | n
table concur_reindex_part_0_2 | table concur_reindex_part_0 | a
(19 rows)
SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
ORDER BY relid, level;
relid | parentrelid | level
-------------------------------+-----------------------------+-------
concur_reindex_part_index | | 0
concur_reindex_part_index_0 | concur_reindex_part_index | 1
concur_reindex_part_index_10 | concur_reindex_part_index | 1
concur_reindex_part_index_0_1 | concur_reindex_part_index_0 | 2
concur_reindex_part_index_0_2 | concur_reindex_part_index_0 | 2
(5 rows)
-- REINDEX for partitioned indexes
-- REINDEX TABLE fails for partitioned indexes
-- Top-most parent index
REINDEX TABLE concur_reindex_part_index; -- error
ERROR: "concur_reindex_part_index" is not a table, directory table or materialized view
REINDEX TABLE concur_reindex_part_index; -- error
ERROR: "concur_reindex_part_index" is not a table, directory table or materialized view
-- Partitioned index with no leaves
REINDEX TABLE concur_reindex_part_index_10; -- error
ERROR: "concur_reindex_part_index_10" is not a table, directory table or materialized view
REINDEX TABLE concur_reindex_part_index_10; -- error
ERROR: "concur_reindex_part_index_10" is not a table, directory table or materialized view
-- Cannot run in a transaction block
BEGIN;
REINDEX INDEX concur_reindex_part_index;
ERROR: REINDEX INDEX cannot run inside a transaction block
CONTEXT: while reindexing partitioned index "public.concur_reindex_part_index"
ROLLBACK;
-- Helper functions to track changes of relfilenodes in a partition tree.
-- Create a table tracking the relfilenode state.
CREATE OR REPLACE FUNCTION create_relfilenode_part(relname text, indname text)
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
CREATE TABLE %I AS
SELECT oid, relname, relfilenode, relkind, reltoastrelid
FROM pg_class
WHERE oid IN
(SELECT relid FROM pg_partition_tree(''%I''));',
relname, indname);
END
$func$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION compare_relfilenode_part(tabname text)
RETURNS TABLE (relname name, relkind "char", state text) AS
$func$
BEGIN
RETURN QUERY EXECUTE
format(
'SELECT b.relname,
b.relkind,
CASE WHEN a.relfilenode = b.relfilenode THEN ''relfilenode is unchanged''
ELSE ''relfilenode has changed'' END
-- Do not join with OID here as CONCURRENTLY changes it.
FROM %I b JOIN pg_class a ON b.relname = a.relname
ORDER BY 1;', tabname);
END
$func$ LANGUAGE plpgsql;
-- Check that expected relfilenodes are changed, non-concurrent case.
SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'oid' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create_relfilenode_part
-------------------------
(1 row)
REINDEX INDEX concur_reindex_part_index;
SELECT * FROM compare_relfilenode_part('reindex_index_status');
relname | relkind | state
-------------------------------+---------+--------------------------
concur_reindex_part_index | I | relfilenode is unchanged
concur_reindex_part_index_0 | I | relfilenode is unchanged
concur_reindex_part_index_0_1 | i | relfilenode has changed
concur_reindex_part_index_0_2 | i | relfilenode has changed
concur_reindex_part_index_10 | I | relfilenode is unchanged
(5 rows)
DROP TABLE reindex_index_status;
-- concurrent case.
SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'oid' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create_relfilenode_part
-------------------------
(1 row)
REINDEX INDEX concur_reindex_part_index;
SELECT * FROM compare_relfilenode_part('reindex_index_status');
relname | relkind | state
-------------------------------+---------+--------------------------
concur_reindex_part_index | I | relfilenode is unchanged
concur_reindex_part_index_0 | I | relfilenode is unchanged
concur_reindex_part_index_0_1 | i | relfilenode has changed
concur_reindex_part_index_0_2 | i | relfilenode has changed
concur_reindex_part_index_10 | I | relfilenode is unchanged
(5 rows)
DROP TABLE reindex_index_status;
-- REINDEX for partitioned tables
-- REINDEX INDEX fails for partitioned tables
-- Top-most parent
REINDEX INDEX concur_reindex_part; -- error
ERROR: "concur_reindex_part" is not an index
REINDEX INDEX concur_reindex_part; -- error
ERROR: "concur_reindex_part" is not an index
-- Partitioned with no leaves
REINDEX INDEX concur_reindex_part_10; -- error
ERROR: "concur_reindex_part_10" is not an index
REINDEX INDEX concur_reindex_part_10; -- error
ERROR: "concur_reindex_part_10" is not an index
-- Cannot run in a transaction block
BEGIN;
REINDEX TABLE concur_reindex_part;
ERROR: REINDEX TABLE cannot run inside a transaction block
CONTEXT: while reindexing partitioned table "public.concur_reindex_part"
ROLLBACK;
-- Check that expected relfilenodes are changed, non-concurrent case.
-- Note that the partition tree changes of the *indexes* need to be checked.
SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'oid' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create_relfilenode_part
-------------------------
(1 row)
REINDEX TABLE concur_reindex_part;
SELECT * FROM compare_relfilenode_part('reindex_index_status');
relname | relkind | state
-------------------------------+---------+--------------------------
concur_reindex_part_index | I | relfilenode is unchanged
concur_reindex_part_index_0 | I | relfilenode is unchanged
concur_reindex_part_index_0_1 | i | relfilenode has changed
concur_reindex_part_index_0_2 | i | relfilenode has changed
concur_reindex_part_index_10 | I | relfilenode is unchanged
(5 rows)
DROP TABLE reindex_index_status;
-- concurrent case.
SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'oid' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create_relfilenode_part
-------------------------
(1 row)
REINDEX TABLE concur_reindex_part;
SELECT * FROM compare_relfilenode_part('reindex_index_status');
relname | relkind | state
-------------------------------+---------+--------------------------
concur_reindex_part_index | I | relfilenode is unchanged
concur_reindex_part_index_0 | I | relfilenode is unchanged
concur_reindex_part_index_0_1 | i | relfilenode has changed
concur_reindex_part_index_0_2 | i | relfilenode has changed
concur_reindex_part_index_10 | I | relfilenode is unchanged
(5 rows)
DROP TABLE reindex_index_status;
DROP FUNCTION create_relfilenode_part;
DROP FUNCTION compare_relfilenode_part;
-- Cleanup of partition tree used for REINDEX test.
DROP TABLE concur_reindex_part;
-- Check errors
-- Cannot run inside a transaction block
BEGIN;
REINDEX TABLE concur_reindex_tab;
COMMIT;
-- In upstream, pg cann't reindex system catalogs concurrently,
-- we keep the `CONCURRENTLY` to make the following commands fail,
-- so these commands will not cause deadlock with test create_view,
-- like `drop schema xxx cascade;`.
-- See more details at https://code.hashdata.xyz/cloudberry/cbdb/-/issues/54
REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
ERROR: cannot reindex system catalogs concurrently
REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
ERROR: cannot reindex system catalogs concurrently
-- These are the toast table and index of pg_authid.
REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
ERROR: cannot reindex system catalogs concurrently
REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
ERROR: cannot reindex system catalogs concurrently
REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
ERROR: cannot reindex system catalogs concurrently
-- Warns about catalog relations
REINDEX SCHEMA CONCURRENTLY pg_catalog;
WARNING: cannot reindex system catalogs concurrently, skipping all
-- Check the relation status, there should not be invalid indexes
\d concur_reindex_tab
Table "public.concur_reindex_tab"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | not null |
c2 | text | | |
Indexes:
"concur_reindex_ind1" PRIMARY KEY, btree (c1)
"concur_reindex_ind2" btree (c2)
"concur_reindex_ind3" UNIQUE, btree (c1, abs(c1))
"concur_reindex_ind4" btree (c1, c1, c2)
Referenced by:
TABLE "concur_reindex_tab2" CONSTRAINT "concur_reindex_tab2_c1_fkey" FOREIGN KEY (c1) REFERENCES concur_reindex_tab(c1)
Distributed by: (c1)
DROP MATERIALIZED VIEW concur_reindex_matview;
DROP TABLE concur_reindex_tab, concur_reindex_tab2;
-- Check handling of invalid indexes
CREATE TABLE concur_reindex_tab4 (c1 int);
INSERT INTO concur_reindex_tab4 VALUES (1), (1), (2);
-- This trick creates an invalid index.
CREATE UNIQUE INDEX concur_reindex_ind5 ON concur_reindex_tab4 (c1);
ERROR: could not create unique index "concur_reindex_ind5"
DETAIL: Key (c1)=(1) is duplicated.
-- Reindexing concurrently this index fails with the same failure.
-- The extra index created is itself invalid, and can be dropped.
--REINDEX INDEX concur_reindex_ind5;
\d concur_reindex_tab4
Table "public.concur_reindex_tab4"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | |
Distributed by: (c1)
DROP INDEX concur_reindex_ind5_ccnew;
ERROR: index "concur_reindex_ind5_ccnew" does not exist
-- This makes the previous failure go away, so the index can become valid.
DELETE FROM concur_reindex_tab4 WHERE c1 = 1;
-- The invalid index is not processed when running REINDEX TABLE.
REINDEX TABLE concur_reindex_tab4;
NOTICE: table "concur_reindex_tab4" has no indexes to reindex
\d concur_reindex_tab4
Table "public.concur_reindex_tab4"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | |
Distributed by: (c1)
-- But it is fixed with REINDEX INDEX.
--REINDEX INDEX concur_reindex_ind5;
\d concur_reindex_tab4
Table "public.concur_reindex_tab4"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | |
Distributed by: (c1)
DROP TABLE concur_reindex_tab4;
-- Check handling of indexes with expressions and predicates. The
-- definitions of the rebuilt indexes should match the original
-- definitions.
CREATE TABLE concur_exprs_tab (c1 int , c2 boolean);
INSERT INTO concur_exprs_tab (c1, c2) VALUES (1369652450, FALSE),
(414515746, TRUE),
(897778963, FALSE);
CREATE UNIQUE INDEX concur_exprs_index_expr
ON concur_exprs_tab (c1, (c1::text COLLATE "C"));
CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
WHERE (c1::text > 500000000::text COLLATE "C");
CREATE UNIQUE INDEX concur_exprs_index_pred_2
ON concur_exprs_tab (c1, (1 / c1))
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
ALTER INDEX concur_exprs_index_expr ALTER COLUMN 2 SET STATISTICS 100;
ANALYZE concur_exprs_tab;
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
'concur_exprs_index_expr'::regclass,
'concur_exprs_index_pred'::regclass,
'concur_exprs_index_pred_2'::regclass)
GROUP BY starelid ORDER BY starelid::regclass::text;
starelid | count
-------------------------+-------
concur_exprs_index_expr | 1
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
pg_get_indexdef
-------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_expr ON public.concur_exprs_tab USING btree (c1, ((c1)::text) COLLATE "C")
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
pg_get_indexdef
----------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_pred ON public.concur_exprs_tab USING btree (c1) WHERE ((c1)::text > ((500000000)::text COLLATE "C"))
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
pg_get_indexdef
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_pred_2 ON public.concur_exprs_tab USING btree (c1, ((1 / c1))) WHERE ('-H'::text >= ((c2)::text COLLATE "C"))
(1 row)
REINDEX TABLE concur_exprs_tab;
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
pg_get_indexdef
-------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_expr ON public.concur_exprs_tab USING btree (c1, ((c1)::text) COLLATE "C")
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
pg_get_indexdef
----------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_pred ON public.concur_exprs_tab USING btree (c1) WHERE ((c1)::text > ((500000000)::text COLLATE "C"))
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
pg_get_indexdef
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_pred_2 ON public.concur_exprs_tab USING btree (c1, ((1 / c1))) WHERE ('-H'::text >= ((c2)::text COLLATE "C"))
(1 row)
-- ALTER TABLE recreates the indexes, which should keep their collations.
ALTER TABLE concur_exprs_tab ALTER c2 TYPE TEXT;
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
pg_get_indexdef
-------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_expr ON public.concur_exprs_tab USING btree (c1, ((c1)::text) COLLATE "C")
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
pg_get_indexdef
----------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_pred ON public.concur_exprs_tab USING btree (c1) WHERE ((c1)::text > ((500000000)::text COLLATE "C"))
(1 row)
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
pg_get_indexdef
----------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX concur_exprs_index_pred_2 ON public.concur_exprs_tab USING btree (c1, ((1 / c1))) WHERE ('-H'::text >= (c2 COLLATE "C"))
(1 row)
-- Statistics should remain intact.
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
'concur_exprs_index_expr'::regclass,
'concur_exprs_index_pred'::regclass,
'concur_exprs_index_pred_2'::regclass)
GROUP BY starelid ORDER BY starelid::regclass::text;
starelid | count
-------------------------+-------
concur_exprs_index_expr | 1
(1 row)
-- attstattarget should remain intact
SELECT attrelid::regclass, attnum, attstattarget
FROM pg_attribute WHERE attrelid IN (
'concur_exprs_index_expr'::regclass,
'concur_exprs_index_pred'::regclass,
'concur_exprs_index_pred_2'::regclass)
ORDER BY attrelid::regclass::text, attnum;
attrelid | attnum | attstattarget
---------------------------+--------+---------------
concur_exprs_index_expr | 1 | -1
concur_exprs_index_expr | 2 | 100
concur_exprs_index_pred | 1 | -1
concur_exprs_index_pred_2 | 1 | -1
concur_exprs_index_pred_2 | 2 | -1
(5 rows)
DROP TABLE concur_exprs_tab;
-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.
-- ON COMMIT PRESERVE ROWS, the default.
CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text)
ON COMMIT PRESERVE ROWS;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar');
CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2);
REINDEX TABLE concur_temp_tab_1;
REINDEX INDEX concur_temp_ind_1;
-- Still fails in transaction blocks
BEGIN;
REINDEX INDEX concur_temp_ind_1;
COMMIT;
-- ON COMMIT DELETE ROWS
CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text)
ON COMMIT DELETE ROWS;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2);
REINDEX TABLE concur_temp_tab_2;
REINDEX INDEX concur_temp_ind_2;
-- ON COMMIT DROP
BEGIN;
CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text)
ON COMMIT PRESERVE ROWS;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar');
CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2);
-- Fails when running in a transaction
REINDEX INDEX concur_temp_ind_3;
COMMIT;
-- REINDEX SCHEMA processes all temporary relations
CREATE TABLE reindex_temp_before AS
SELECT oid, relname, relfilenode, relkind, reltoastrelid
FROM pg_class
WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'oid' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset
REINDEX SCHEMA :temp_schema_name;
SELECT b.relname,
b.relkind,
CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
ELSE 'relfilenode has changed' END
FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid
ORDER BY 1;
relname | relkind | case
-------------------+---------+-------------------------
concur_temp_ind_1 | i | relfilenode has changed
concur_temp_ind_2 | i | relfilenode has changed
(2 rows)
DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before;
--
-- REINDEX SCHEMA
--
REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist
ERROR: schema "schema_to_reindex" does not exist
CREATE SCHEMA schema_to_reindex;
SET search_path = 'schema_to_reindex';
CREATE TABLE table1(col1 SERIAL PRIMARY KEY);
INSERT INTO table1 SELECT generate_series(1,400);
CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL);
INSERT INTO table2 SELECT generate_series(1,400), 'abc';
CREATE INDEX ON table2(col2);
CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2;
CREATE INDEX ON matview(col1);
CREATE VIEW view AS SELECT col2 FROM table2;
CREATE TABLE reindex_before AS
SELECT oid, relname, relfilenode, relkind, reltoastrelid
FROM pg_class
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
INSERT INTO reindex_before
SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid
FROM pg_class WHERE oid IN
(SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0);
INSERT INTO reindex_before
SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
FROM pg_class where oid in
(select indexrelid from pg_index where indrelid in
(select reltoastrelid from reindex_before where reltoastrelid > 0));
REINDEX SCHEMA schema_to_reindex;
CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
FROM pg_class
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
SELECT b.relname,
b.relkind,
CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
ELSE 'relfilenode has changed' END
FROM reindex_before b JOIN pg_class a ON b.oid = a.oid
ORDER BY 1;
relname | relkind | case
----------------------+---------+--------------------------
matview | m | relfilenode is unchanged
matview_col1_idx | i | relfilenode has changed
pg_toast_TABLE | t | relfilenode is unchanged
pg_toast_TABLE_index | i | relfilenode has changed
table1 | r | relfilenode is unchanged
table1_col1_seq | S | relfilenode is unchanged
table1_pkey | i | relfilenode has changed
table2 | r | relfilenode is unchanged
table2_col1_seq | S | relfilenode is unchanged
table2_col2_idx | i | relfilenode has changed
table2_pkey | i | relfilenode has changed
view | v | relfilenode is unchanged
(12 rows)
REINDEX SCHEMA schema_to_reindex;
BEGIN;
REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction
ERROR: REINDEX SCHEMA cannot run inside a transaction block
END;
-- concurrently
REINDEX SCHEMA schema_to_reindex;
-- Failure for unauthorized user
CREATE ROLE regress_reindexuser NOLOGIN;
NOTICE: resource queue required -- using default resource queue "pg_default"
SET SESSION ROLE regress_reindexuser;
REINDEX SCHEMA schema_to_reindex;
ERROR: must be owner of schema schema_to_reindex
-- Permission failures with toast tables and indexes (pg_authid here)
RESET ROLE;
GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
SET SESSION ROLE regress_reindexuser;
REINDEX TABLE pg_toast.pg_toast_1260;
ERROR: must be owner of table pg_toast_1260
REINDEX INDEX pg_toast.pg_toast_1260_index;
ERROR: must be owner of index pg_toast_1260_index
-- Clean up
RESET ROLE;
REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser;
DROP ROLE regress_reindexuser;
DROP SCHEMA schema_to_reindex CASCADE;
NOTICE: drop cascades to 6 other objects
DETAIL: drop cascades to table table1
drop cascades to table table2
drop cascades to materialized view matview
drop cascades to view view
drop cascades to table reindex_before
drop cascades to table reindex_after