diff options
Diffstat (limited to 'src/test/regress/sql')
| -rw-r--r-- | src/test/regress/sql/create_index.sql | 290 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 32 |
2 files changed, 321 insertions, 1 deletions
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 |