aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/create_index.out83
-rw-r--r--src/test/regress/sql/create_index.sql21
2 files changed, 98 insertions, 6 deletions
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e43ee6aa53d..7b37cbf40bc 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -113,9 +113,20 @@ SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
--- there's no easy way to check that these commands actually use
--- the index, unfortunately. (EXPLAIN would work, but its output
--- changes too often for me to want to put an EXPLAIN in the test...)
+EXPLAIN (COSTS OFF)
+SELECT * FROM fast_emp4000
+ WHERE home_base @ '(200,200),(2000,1000)'::box
+ ORDER BY (home_base[0])[0];
+ 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)
+
SELECT * FROM fast_emp4000
WHERE home_base @ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
@@ -125,18 +136,51 @@ SELECT * FROM fast_emp4000
(1444,403),(1346,344)
(2 rows)
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
+ 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)
+
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
+-----------------------------------------------
+ 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)
+
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
+-----------------------------------------------------------
+ Sort
+ Sort Key: ((poly_center(f1))[0])
+ -> Index Scan using gpolygonind on polygon_tbl
+ Index Cond: (f1 ~ '((1,1),(2,2),(2,1))'::polygon)
+(4 rows)
+
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
f1
@@ -144,6 +188,17 @@ SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
((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
+--------------------------------------------------
+ Sort
+ Sort Key: (area(f1))
+ -> Index Scan using gcircleind on circle_tbl
+ Index Cond: (f1 && '<(1,-2),1>'::circle)
+(4 rows)
+
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
f1
@@ -154,12 +209,34 @@ SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
<(100,1),115>
(4 rows)
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
+ 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)
+
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
+-------------------------------------------------------------
+ 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)
+
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
count
-------
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 9638b23312e..66ab0f9f02f 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -112,25 +112,40 @@ SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
--- there's no easy way to check that these commands actually use
--- the index, unfortunately. (EXPLAIN would work, but its output
--- changes too often for me to want to put an EXPLAIN in the test...)
+EXPLAIN (COSTS OFF)
+SELECT * FROM fast_emp4000
+ WHERE home_base @ '(200,200),(2000,1000)'::box
+ ORDER BY (home_base[0])[0];
SELECT * FROM fast_emp4000
WHERE home_base @ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
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;
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
+EXPLAIN (COSTS OFF)
+SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
+ ORDER BY (poly_center(f1))[0];
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
ORDER BY (poly_center(f1))[0];
+EXPLAIN (COSTS OFF)
+SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
+ ORDER BY area(f1);
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
ORDER BY area(f1);
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
RESET enable_seqscan;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 13:22:42 +0000