diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/create_index.out | 1068 | ||||
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 56 | ||||
| -rw-r--r-- | src/test/regress/expected/sanity_check.out | 5 | ||||
| -rw-r--r-- | src/test/regress/output/misc.source | 5 | ||||
| -rw-r--r-- | src/test/regress/sql/create_index.sql | 290 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 32 |
6 files changed, 1416 insertions, 40 deletions
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index bdd1f4ec78..86cee2de94 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -61,6 +61,26 @@ CREATE TEMP TABLE gcircle_tbl AS SELECT circle(home_base) AS f1 FROM slow_emp4000; CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1); CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1); +-- +-- SP-GiST +-- +CREATE TABLE quad_point_tbl AS + SELECT point(unique1,unique2) AS p FROM tenk1; +INSERT INTO quad_point_tbl + SELECT '(333.0,400.0)'::point FROM generate_series(1,1000); +CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p); +CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl; +CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops); +CREATE TABLE suffix_text_tbl AS + SELECT name AS t FROM road; +INSERT INTO suffix_text_tbl + SELECT '0123456789abcdef' FROM generate_series(1,1000); +INSERT INTO suffix_text_tbl VALUES ('0123456789abcde'); +INSERT INTO suffix_text_tbl VALUES ('0123456789abcdefF'); +CREATE INDEX sp_suff_ind ON suffix_text_tbl USING spgist (t); +-- +-- Test GiST and SP-GiST indexes +-- -- get non-indexed results for comparison purposes SET enable_seqscan = ON; SET enable_indexscan = OFF; @@ -207,22 +227,141 @@ SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0 (10,10) (4 rows) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + count +------- + 5000 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + count +------- + 5999 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + count +------- + 1000 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + count +------- + 1705 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + count +------- + 1705 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + count +------- + 1706 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + count +------- + 1706 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + count +------- + 1 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + count +------- + 2 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + count +------- + 50 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + count +------- + 50 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + count +------- + 48 +(1 row) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + count +------- + 48 +(1 row) + +-- Now check the results from plain indexscan SET enable_seqscan = OFF; SET enable_indexscan = ON; -SET enable_bitmapscan = 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 ----------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Sort Sort Key: ((home_base[0])[0]) - -> Bitmap Heap Scan on fast_emp4000 - Recheck Cond: (home_base @ '(2000,1000),(200,200)'::box) - -> Bitmap Index Scan on grect2ind - Index Cond: (home_base @ '(2000,1000),(200,200)'::box) -(6 rows) + -> Index Scan using grect2ind on fast_emp4000 + Index Cond: (home_base @ '(2000,1000),(200,200)'::box) +(4 rows) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box @@ -235,14 +374,12 @@ SELECT * FROM fast_emp4000 EXPLAIN (COSTS OFF) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Aggregate - -> Bitmap Heap Scan on fast_emp4000 - Recheck Cond: (home_base && '(1000,1000),(0,0)'::box) - -> Bitmap Index Scan on grect2ind - Index Cond: (home_base && '(1000,1000),(0,0)'::box) -(5 rows) + -> Index Scan using grect2ind on fast_emp4000 + Index Cond: (home_base && '(1000,1000),(0,0)'::box) +(3 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; count @@ -252,14 +389,12 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; EXPLAIN (COSTS OFF) SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; - QUERY PLAN ------------------------------------------------ + QUERY PLAN +-------------------------------------------------- Aggregate - -> Bitmap Heap Scan on fast_emp4000 - Recheck Cond: (home_base IS NULL) - -> Bitmap Index Scan on grect2ind - Index Cond: (home_base IS NULL) -(5 rows) + -> Index Scan using grect2ind on fast_emp4000 + Index Cond: (home_base IS NULL) +(3 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; count @@ -308,14 +443,12 @@ SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) EXPLAIN (COSTS OFF) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Aggregate - -> Bitmap Heap Scan on gpolygon_tbl - Recheck Cond: (f1 && '((1000,1000),(0,0))'::polygon) - -> Bitmap Index Scan on ggpolygonind - Index Cond: (f1 && '((1000,1000),(0,0))'::polygon) -(5 rows) + -> Index Scan using ggpolygonind on gpolygon_tbl + Index Cond: (f1 && '((1000,1000),(0,0))'::polygon) +(3 rows) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; count @@ -325,14 +458,12 @@ SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; EXPLAIN (COSTS OFF) SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------- Aggregate - -> Bitmap Heap Scan on gcircle_tbl - Recheck Cond: (f1 && '<(500,500),500>'::circle) - -> Bitmap Index Scan on ggcircleind - Index Cond: (f1 && '<(500,500),500>'::circle) -(5 rows) + -> Index Scan using ggcircleind on gcircle_tbl + Index Cond: (f1 && '<(500,500),500>'::circle) +(3 rows) SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; count @@ -547,6 +678,412 @@ SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0 (10,10) (4 rows) +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: ('(1000,1000),(200,200)'::box @> p) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p << '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p >> '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p <^ '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p >^ '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Index Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p ~= '(4585,365)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: ('(1000,1000),(200,200)'::box @> p) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p << '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p >> '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p <^ '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p >^ '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +-------------------------------------------------- + Aggregate + -> Index Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p ~= '(4585,365)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t = '0123456789abcdef'::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + count +------- + 1000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t = '0123456789abcde'::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t = '0123456789abcdefF'::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t < 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + count +------- + 1705 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t ~<~ 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + count +------- + 1705 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t <= 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + count +------- + 1706 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t ~<=~ 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + count +------- + 1706 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t = 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t = 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + count +------- + 2 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t >= 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t ~>=~ 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t > 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + count +------- + 48 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + -> Index Scan using sp_suff_ind on suffix_text_tbl + Index Cond: (t ~>~ 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + count +------- + 48 +(1 row) + +-- Now check the results from bitmap indexscan SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; @@ -571,6 +1108,465 @@ SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0 (10,10) (4 rows) +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: ('(1000,1000),(200,200)'::box @> p) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: ('(1000,1000),(200,200)'::box @> p) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p << '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p << '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p >> '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p >> '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p <^ '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p <^ '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p >^ '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p >^ '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p ~= '(4585,365)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p ~= '(4585,365)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: ('(1000,1000),(200,200)'::box @> p) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: ('(1000,1000),(200,200)'::box @> p) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p << '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p << '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p >> '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p >> '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p <^ '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p <^ '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p >^ '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p >^ '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p ~= '(4585,365)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p ~= '(4585,365)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t = '0123456789abcdef'::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t = '0123456789abcdef'::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + count +------- + 1000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t = '0123456789abcde'::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t = '0123456789abcde'::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t = '0123456789abcdefF'::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t = '0123456789abcdefF'::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t < 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t < 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + count +------- + 1705 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t ~<~ 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t ~<~ 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + count +------- + 1705 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + QUERY PLAN +----------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t <= 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t <= 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + count +------- + 1706 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t ~<=~ 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t ~<=~ 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + count +------- + 1706 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t = 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t = 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t = 'Worth St '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t = 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + count +------- + 2 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + QUERY PLAN +----------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t >= 'Worth St '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t >= 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t ~>=~ 'Worth St '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t ~>=~ 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t > 'Worth St '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t > 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + count +------- + 48 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on suffix_text_tbl + Recheck Cond: (t ~>~ 'Worth St '::text) + -> Bitmap Index Scan on sp_suff_ind + Index Cond: (t ~>~ 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + count +------- + 48 +(1 row) + RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index a0ffd77e0e..8e4004ed31 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1053,7 +1053,22 @@ ORDER BY 1, 2, 3; 2742 | 2 | @@@ 2742 | 3 | <@ 2742 | 4 | = -(43 rows) + 4000 | 1 | << + 4000 | 1 | ~<~ + 4000 | 2 | ~<=~ + 4000 | 3 | = + 4000 | 4 | ~>=~ + 4000 | 5 | >> + 4000 | 5 | ~>~ + 4000 | 6 | ~= + 4000 | 8 | <@ + 4000 | 10 | <^ + 4000 | 11 | < + 4000 | 11 | >^ + 4000 | 12 | <= + 4000 | 14 | >= + 4000 | 15 | > +(58 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing @@ -1077,6 +1092,24 @@ WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2 ---------+----------- (0 rows) +-- Check that each operator listed in pg_amop has an associated opclass, +-- that is one whose opcintype matches oprleft (possibly by coercion). +-- Otherwise the operator is useless because it cannot be matched to an index. +-- (In principle it could be useful to list such operators in multiple-datatype +-- btree opfamilies, but in practice you'd expect there to be an opclass for +-- every datatype the family knows about.) +SELECT p1.amopfamily, p1.amopstrategy, p1.amopopr +FROM pg_amop AS p1 +WHERE NOT EXISTS(SELECT 1 FROM pg_opclass AS p2 + WHERE p2.opcfamily = p1.amopfamily + AND binary_coercible(p2.opcintype, p1.amoplefttype)); + amopfamily | amopstrategy | amopopr +------------+--------------+--------- + 1029 | 27 | 433 + 1029 | 47 | 757 + 1029 | 67 | 759 +(3 rows) + -- Operators that are primary members of opclasses must be immutable (else -- it suggests that the index ordering isn't fixed). Operators that are -- cross-type members need only be stable, since they are just shorthands @@ -1297,6 +1330,27 @@ ORDER BY 1; 2226 | 1 | hashint4 | cid_ops (6 rows) +-- We can also check SP-GiST carefully, since the support routine signatures +-- are independent of the datatype being indexed. +SELECT p1.amprocfamily, p1.amprocnum, + p2.oid, p2.proname, + p3.opfname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3 +WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'spgist') + AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND + (CASE WHEN amprocnum = 1 OR amprocnum = 2 OR amprocnum = 3 OR amprocnum = 4 + THEN prorettype != 'void'::regtype OR proretset OR pronargs != 2 + OR proargtypes[0] != 'internal'::regtype + OR proargtypes[1] != 'internal'::regtype + WHEN amprocnum = 5 + THEN prorettype != 'bool'::regtype OR proretset OR pronargs != 2 + OR proargtypes[0] != 'internal'::regtype + OR proargtypes[1] != 'internal'::regtype + ELSE true END); + amprocfamily | amprocnum | oid | proname | opfname +--------------+-----------+-----+---------+--------- +(0 rows) + -- Support routines that are primary members of opfamilies must be immutable -- (else it suggests that the index ordering isn't fixed). But cross-type -- members need only be stable, since they are just shorthands diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index cb468e58b9..9cae9d8bf1 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -63,6 +63,7 @@ SELECT relname, relhasindex int8_tbl | f interval_tbl | f iportaltest | f + kd_point_tbl | t log_table | f lseg_tbl | f main_table | f @@ -134,6 +135,7 @@ SELECT relname, relhasindex pg_user_mapping | t point_tbl | t polygon_tbl | t + quad_point_tbl | t ramp | f real_city | f reltime_tbl | f @@ -149,6 +151,7 @@ SELECT relname, relhasindex sql_sizing_profiles | f stud_emp | f student | f + suffix_text_tbl | t tenk1 | t tenk2 | t test_range_excl | t @@ -161,7 +164,7 @@ SELECT relname, relhasindex timetz_tbl | f tinterval_tbl | f varchar_tbl | f -(150 rows) +(153 rows) -- -- another sanity check: every system catalog that has OIDs should have diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source index 45bc926407..b57c5546de 100644 --- a/src/test/regress/output/misc.source +++ b/src/test/regress/output/misc.source @@ -636,6 +636,7 @@ SELECT user_relns() AS user_relns int8_tbl interval_tbl iportaltest + kd_point_tbl log_table lseg_tbl main_table @@ -657,6 +658,7 @@ SELECT user_relns() AS user_relns person point_tbl polygon_tbl + quad_point_tbl ramp random_tbl real_city @@ -668,6 +670,7 @@ SELECT user_relns() AS user_relns stud_emp student subselect_tbl + suffix_text_tbl tenk1 tenk2 test_range_excl @@ -682,7 +685,7 @@ SELECT user_relns() AS user_relns toyemp varchar_tbl xacttest -(104 rows) +(107 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); name diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 85cf23ccb8..babde51d2c 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -92,6 +92,36 @@ CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1); CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1); +-- +-- SP-GiST +-- + +CREATE TABLE quad_point_tbl AS + SELECT point(unique1,unique2) AS p FROM tenk1; + +INSERT INTO quad_point_tbl + SELECT '(333.0,400.0)'::point FROM generate_series(1,1000); + +CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p); + +CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl; + +CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops); + +CREATE TABLE suffix_text_tbl AS + SELECT name AS t FROM road; + +INSERT INTO suffix_text_tbl + SELECT '0123456789abcdef' FROM generate_series(1,1000); +INSERT INTO suffix_text_tbl VALUES ('0123456789abcde'); +INSERT INTO suffix_text_tbl VALUES ('0123456789abcdefF'); + +CREATE INDEX sp_suff_ind ON suffix_text_tbl USING spgist (t); + +-- +-- Test GiST and SP-GiST indexes +-- + -- get non-indexed results for comparison purposes SET enable_seqscan = ON; @@ -142,9 +172,50 @@ SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + +-- Now check the results from plain indexscan SET enable_seqscan = OFF; SET enable_indexscan = ON; -SET enable_bitmapscan = ON; +SET enable_bitmapscan = OFF; EXPLAIN (COSTS OFF) SELECT * FROM fast_emp4000 @@ -234,6 +305,115 @@ EXPLAIN (COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + +-- Now check the results from bitmap indexscan SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; @@ -242,6 +422,114 @@ EXPLAIN (COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; +SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; +SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdef'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcde'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; +SELECT count(*) FROM suffix_text_tbl WHERE t = '0123456789abcdefF'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t < 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~<~ 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t <= 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Aztec Ct '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t = 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t >= 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~>=~ 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t > 'Worth St '; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; +SELECT count(*) FROM suffix_text_tbl WHERE t ~>~ 'Worth St '; + RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 6a79ea180c..e29148fd5b 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -831,6 +831,19 @@ WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2 WHERE p2.amopfamily = p1.opcfamily AND binary_coercible(p1.opcintype, p2.amoplefttype)); +-- Check that each operator listed in pg_amop has an associated opclass, +-- that is one whose opcintype matches oprleft (possibly by coercion). +-- Otherwise the operator is useless because it cannot be matched to an index. +-- (In principle it could be useful to list such operators in multiple-datatype +-- btree opfamilies, but in practice you'd expect there to be an opclass for +-- every datatype the family knows about.) + +SELECT p1.amopfamily, p1.amopstrategy, p1.amopopr +FROM pg_amop AS p1 +WHERE NOT EXISTS(SELECT 1 FROM pg_opclass AS p2 + WHERE p2.opcfamily = p1.amopfamily + AND binary_coercible(p2.opcintype, p1.amoplefttype)); + -- Operators that are primary members of opclasses must be immutable (else -- it suggests that the index ordering isn't fixed). Operators that are -- cross-type members need only be stable, since they are just shorthands @@ -1018,6 +1031,25 @@ WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') OR amproclefttype != amprocrighttype) ORDER BY 1; +-- We can also check SP-GiST carefully, since the support routine signatures +-- are independent of the datatype being indexed. + +SELECT p1.amprocfamily, p1.amprocnum, + p2.oid, p2.proname, + p3.opfname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3 +WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'spgist') + AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND + (CASE WHEN amprocnum = 1 OR amprocnum = 2 OR amprocnum = 3 OR amprocnum = 4 + THEN prorettype != 'void'::regtype OR proretset OR pronargs != 2 + OR proargtypes[0] != 'internal'::regtype + OR proargtypes[1] != 'internal'::regtype + WHEN amprocnum = 5 + THEN prorettype != 'bool'::regtype OR proretset OR pronargs != 2 + OR proargtypes[0] != 'internal'::regtype + OR proargtypes[1] != 'internal'::regtype + ELSE true END); + -- Support routines that are primary members of opfamilies must be immutable -- (else it suggests that the index ordering isn't fixed). But cross-type -- members need only be stable, since they are just shorthands |