summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/create_index.out136
-rw-r--r--src/test/regress/expected/opr_sanity.out29
-rw-r--r--src/test/regress/sql/create_index.sql36
-rw-r--r--src/test/regress/sql/opr_sanity.sql26
4 files changed, 202 insertions, 25 deletions
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 27d5e848e5..1d1470a25d 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -51,6 +51,7 @@ CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
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);
CREATE INDEX gpointind ON point_tbl USING gist (f1);
CREATE TEMP TABLE gpolygon_tbl AS
SELECT polygon(home_base) AS f1 FROM slow_emp4000;
@@ -60,6 +61,7 @@ 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);
+-- get non-indexed results for comparison purposes
SET enable_seqscan = ON;
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF;
@@ -167,6 +169,44 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
1
(1 row)
+SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
+ f1
+------------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+ (-5,-12)
+ (5.1,34.5)
+
+(7 rows)
+
+SELECT * FROM point_tbl WHERE f1 IS NULL;
+ f1
+----
+
+(1 row)
+
+SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
+ f1
+------------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+ (-5,-12)
+ (5.1,34.5)
+(6 rows)
+
+SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
+ f1
+---------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+(4 rows)
+
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
@@ -435,6 +475,102 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
1
(1 row)
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using gpointind on point_tbl
+ Order By: (f1 <-> '(0,1)'::point)
+(2 rows)
+
+SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
+ f1
+------------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+ (-5,-12)
+ (5.1,34.5)
+
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl WHERE f1 IS NULL;
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using gpointind on point_tbl
+ Index Cond: (f1 IS NULL)
+(2 rows)
+
+SELECT * FROM point_tbl WHERE f1 IS NULL;
+ f1
+----
+
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using gpointind on point_tbl
+ Index Cond: (f1 IS NOT NULL)
+ Order By: (f1 <-> '(0,1)'::point)
+(3 rows)
+
+SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
+ f1
+------------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+ (-5,-12)
+ (5.1,34.5)
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gpointind on point_tbl
+ Index Cond: (f1 <@ '(10,10),(-10,-10)'::box)
+ Order By: (f1 <-> '(0,1)'::point)
+(3 rows)
+
+SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
+ f1
+---------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+(4 rows)
+
+SET enable_seqscan = OFF;
+SET enable_indexscan = OFF;
+SET enable_bitmapscan = ON;
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: ((f1 <-> '(0,1)'::point))
+ -> Bitmap Heap Scan on point_tbl
+ Recheck Cond: (f1 <@ '(10,10),(-10,-10)'::box)
+ -> Bitmap Index Scan on gpointind
+ Index Cond: (f1 <@ '(10,10),(-10,-10)'::box)
+(6 rows)
+
+SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
+ f1
+---------
+ (0,0)
+ (-3,4)
+ (-10,0)
+ (10,10)
+(4 rows)
+
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 2176ea47da..f87cfb8009 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -991,6 +991,7 @@ ORDER BY 1, 2, 3;
783 | 12 | |&>
783 | 13 | ~
783 | 14 | @
+ 783 | 15 | <->
783 | 27 | @>
783 | 28 | <@
783 | 47 | @>
@@ -1003,7 +1004,7 @@ ORDER BY 1, 2, 3;
2742 | 2 | @@@
2742 | 3 | <@
2742 | 4 | =
-(39 rows)
+(40 rows)
-- Check that all opclass search operators have selectivity estimators.
-- This is not absolutely required, but it seems a reasonable thing
@@ -1136,11 +1137,11 @@ WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
-- Detect missing pg_amproc entries: should have as many support functions
-- as AM expects for each datatype combination supported by the opfamily.
--- GIN is a special case because it has an optional support function.
+-- GIST/GIN are special cases because each has an optional support function.
SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
- p1.amname <> 'gin' AND
+ p1.amname <> 'gist' AND p1.amname <> 'gin' AND
p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
WHERE p4.amprocfamily = p2.oid AND
p4.amproclefttype = p3.amproclefttype AND
@@ -1149,26 +1150,27 @@ WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
--------+---------+----------------+-----------------
(0 rows)
--- Similar check for GIN, allowing one optional proc
+-- Similar check for GIST/GIN, allowing one optional proc
SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
- p1.amname = 'gin' AND
- p1.amsupport - 1 > (SELECT count(*) FROM pg_amproc AS p4
- WHERE p4.amprocfamily = p2.oid AND
- p4.amproclefttype = p3.amproclefttype AND
- p4.amprocrighttype = p3.amprocrighttype);
+ (p1.amname = 'gist' OR p1.amname = 'gin') AND
+ (SELECT count(*) FROM pg_amproc AS p4
+ WHERE p4.amprocfamily = p2.oid AND
+ p4.amproclefttype = p3.amproclefttype AND
+ p4.amprocrighttype = p3.amprocrighttype)
+ NOT IN (p1.amsupport, p1.amsupport - 1);
amname | opfname | amproclefttype | amprocrighttype
--------+---------+----------------+-----------------
(0 rows)
-- Also, check if there are any pg_opclass entries that don't seem to have
--- pg_amproc support. Again, GIN has to be checked separately.
+-- pg_amproc support. Again, GIST/GIN have to be checked specially.
SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
amproclefttype = amprocrighttype AND amproclefttype = opcintype
-WHERE am.amname <> 'gin'
+WHERE am.amname <> 'gist' AND am.amname <> 'gin'
GROUP BY amname, amsupport, opcname, amprocfamily
HAVING count(*) != amsupport OR amprocfamily IS NULL;
amname | opcname | count
@@ -1179,9 +1181,10 @@ SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
amproclefttype = amprocrighttype AND amproclefttype = opcintype
-WHERE am.amname = 'gin'
+WHERE am.amname = 'gist' OR am.amname = 'gin'
GROUP BY amname, amsupport, opcname, amprocfamily
-HAVING count(*) < amsupport - 1 OR amprocfamily IS NULL;
+HAVING (count(*) != amsupport AND count(*) != amsupport - 1)
+ OR amprocfamily IS NULL;
amname | opcname | count
--------+---------+-------
(0 rows)
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index abf222de8e..043f433eb0 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -76,6 +76,8 @@ CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1);
CREATE INDEX gcircleind ON circle_tbl USING gist (f1);
+INSERT INTO POINT_TBL(f1) VALUES (NULL);
+
CREATE INDEX gpointind ON point_tbl USING gist (f1);
CREATE TEMP TABLE gpolygon_tbl AS
@@ -90,6 +92,8 @@ CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
+-- get non-indexed results for comparison purposes
+
SET enable_seqscan = ON;
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF;
@@ -130,6 +134,14 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
+SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
+
+SELECT * FROM point_tbl WHERE f1 IS NULL;
+
+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';
+
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
@@ -206,6 +218,30 @@ EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
+SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl WHERE f1 IS NULL;
+SELECT * FROM point_tbl WHERE f1 IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
+SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
+
+EXPLAIN (COSTS OFF)
+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';
+
+SET enable_seqscan = OFF;
+SET enable_indexscan = OFF;
+SET enable_bitmapscan = ON;
+
+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';
+
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 1a023a088e..8da76ff3de 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -888,36 +888,37 @@ WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
-- Detect missing pg_amproc entries: should have as many support functions
-- as AM expects for each datatype combination supported by the opfamily.
--- GIN is a special case because it has an optional support function.
+-- GIST/GIN are special cases because each has an optional support function.
SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
- p1.amname <> 'gin' AND
+ p1.amname <> 'gist' AND p1.amname <> 'gin' AND
p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
WHERE p4.amprocfamily = p2.oid AND
p4.amproclefttype = p3.amproclefttype AND
p4.amprocrighttype = p3.amprocrighttype);
--- Similar check for GIN, allowing one optional proc
+-- Similar check for GIST/GIN, allowing one optional proc
SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
- p1.amname = 'gin' AND
- p1.amsupport - 1 > (SELECT count(*) FROM pg_amproc AS p4
- WHERE p4.amprocfamily = p2.oid AND
- p4.amproclefttype = p3.amproclefttype AND
- p4.amprocrighttype = p3.amprocrighttype);
+ (p1.amname = 'gist' OR p1.amname = 'gin') AND
+ (SELECT count(*) FROM pg_amproc AS p4
+ WHERE p4.amprocfamily = p2.oid AND
+ p4.amproclefttype = p3.amproclefttype AND
+ p4.amprocrighttype = p3.amprocrighttype)
+ NOT IN (p1.amsupport, p1.amsupport - 1);
-- Also, check if there are any pg_opclass entries that don't seem to have
--- pg_amproc support. Again, GIN has to be checked separately.
+-- pg_amproc support. Again, GIST/GIN have to be checked specially.
SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
amproclefttype = amprocrighttype AND amproclefttype = opcintype
-WHERE am.amname <> 'gin'
+WHERE am.amname <> 'gist' AND am.amname <> 'gin'
GROUP BY amname, amsupport, opcname, amprocfamily
HAVING count(*) != amsupport OR amprocfamily IS NULL;
@@ -925,9 +926,10 @@ SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
amproclefttype = amprocrighttype AND amproclefttype = opcintype
-WHERE am.amname = 'gin'
+WHERE am.amname = 'gist' OR am.amname = 'gin'
GROUP BY amname, amsupport, opcname, amprocfamily
-HAVING count(*) < amsupport - 1 OR amprocfamily IS NULL;
+HAVING (count(*) != amsupport AND count(*) != amsupport - 1)
+ OR amprocfamily IS NULL;
-- Unfortunately, we can't check the amproc link very well because the
-- signature of the function may be different for different support routines
View Lorry Controller Status