Skip to content

Commit 8835d63

Browse files
tglsfdctglsfdc
committed
Experiment with using EXPLAIN COSTS OFF in regression tests.
This is a simple test to see whether COSTS OFF will help much with getting EXPLAIN output that's sufficiently platform-independent for use in the regression tests. The planner does have some freedom of choice in these examples (plain via bitmap indexscan), so I'm not sure what will happen.
1 parent d4382c4 commit 8835d63

File tree

2 files changed

+98
-6
lines changed

2 files changed

+98
-6
lines changed

‎src/test/regress/expected/create_index.out

Lines changed: 80 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -113,9 +113,20 @@ SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
113113
SET enable_seqscan = OFF;
114114
SET enable_indexscan = ON;
115115
SET enable_bitmapscan = ON;
116-
-- there's no easy way to check that these commands actually use
117-
-- the index, unfortunately. (EXPLAIN would work, but its output
118-
-- changes too often for me to want to put an EXPLAIN in the test...)
116+
EXPLAIN (COSTS OFF)
117+
SELECT * FROM fast_emp4000
118+
WHERE home_base @ '(200,200),(2000,1000)'::box
119+
ORDER BY (home_base[0])[0];
120+
QUERY PLAN
121+
----------------------------------------------------------------------
122+
Sort
123+
Sort Key: ((home_base[0])[0])
124+
-> Bitmap Heap Scan on fast_emp4000
125+
Recheck Cond: (home_base @ '(2000,1000),(200,200)'::box)
126+
-> Bitmap Index Scan on grect2ind
127+
Index Cond: (home_base @ '(2000,1000),(200,200)'::box)
128+
(6 rows)
129+
119130
SELECT * FROM fast_emp4000
120131
WHERE home_base @ '(200,200),(2000,1000)'::box
121132
ORDER BY (home_base[0])[0];
@@ -125,25 +136,69 @@ SELECT * FROM fast_emp4000
125136
(1444,403),(1346,344)
126137
(2 rows)
127138

139+
EXPLAIN (COSTS OFF)
140+
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
141+
QUERY PLAN
142+
-------------------------------------------------------------------
143+
Aggregate
144+
-> Bitmap Heap Scan on fast_emp4000
145+
Recheck Cond: (home_base && '(1000,1000),(0,0)'::box)
146+
-> Bitmap Index Scan on grect2ind
147+
Index Cond: (home_base && '(1000,1000),(0,0)'::box)
148+
(5 rows)
149+
128150
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
129151
count
130152
-------
131153
2
132154
(1 row)
133155

156+
EXPLAIN (COSTS OFF)
157+
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
158+
QUERY PLAN
159+
-----------------------------------------------
160+
Aggregate
161+
-> Bitmap Heap Scan on fast_emp4000
162+
Recheck Cond: (home_base IS NULL)
163+
-> Bitmap Index Scan on grect2ind
164+
Index Cond: (home_base IS NULL)
165+
(5 rows)
166+
134167
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
135168
count
136169
-------
137170
278
138171
(1 row)
139172

173+
EXPLAIN (COSTS OFF)
174+
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
175+
ORDER BY (poly_center(f1))[0];
176+
QUERY PLAN
177+
-----------------------------------------------------------
178+
Sort
179+
Sort Key: ((poly_center(f1))[0])
180+
-> Index Scan using gpolygonind on polygon_tbl
181+
Index Cond: (f1 ~ '((1,1),(2,2),(2,1))'::polygon)
182+
(4 rows)
183+
140184
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
141185
ORDER BY (poly_center(f1))[0];
142186
f1
143187
---------------------
144188
((2,0),(2,4),(0,0))
145189
(1 row)
146190

191+
EXPLAIN (COSTS OFF)
192+
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
193+
ORDER BY area(f1);
194+
QUERY PLAN
195+
--------------------------------------------------
196+
Sort
197+
Sort Key: (area(f1))
198+
-> Index Scan using gcircleind on circle_tbl
199+
Index Cond: (f1 && '<(1,-2),1>'::circle)
200+
(4 rows)
201+
147202
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
148203
ORDER BY area(f1);
149204
f1
@@ -154,12 +209,34 @@ SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
154209
<(100,1),115>
155210
(4 rows)
156211

212+
EXPLAIN (COSTS OFF)
213+
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
214+
QUERY PLAN
215+
------------------------------------------------------------------
216+
Aggregate
217+
-> Bitmap Heap Scan on gpolygon_tbl
218+
Recheck Cond: (f1 && '((1000,1000),(0,0))'::polygon)
219+
-> Bitmap Index Scan on ggpolygonind
220+
Index Cond: (f1 && '((1000,1000),(0,0))'::polygon)
221+
(5 rows)
222+
157223
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
158224
count
159225
-------
160226
2
161227
(1 row)
162228

229+
EXPLAIN (COSTS OFF)
230+
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
231+
QUERY PLAN
232+
-------------------------------------------------------------
233+
Aggregate
234+
-> Bitmap Heap Scan on gcircle_tbl
235+
Recheck Cond: (f1 && '<(500,500),500>'::circle)
236+
-> Bitmap Index Scan on ggcircleind
237+
Index Cond: (f1 && '<(500,500),500>'::circle)
238+
(5 rows)
239+
163240
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
164241
count
165242
-------

‎src/test/regress/sql/create_index.sql

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -112,25 +112,40 @@ SET enable_seqscan = OFF;
112112
SET enable_indexscan = ON;
113113
SET enable_bitmapscan = ON;
114114

115-
-- there's no easy way to check that these commands actually use
116-
-- the index, unfortunately. (EXPLAIN would work, but its output
117-
-- changes too often for me to want to put an EXPLAIN in the test...)
115+
EXPLAIN (COSTS OFF)
116+
SELECT * FROM fast_emp4000
117+
WHERE home_base @ '(200,200),(2000,1000)'::box
118+
ORDER BY (home_base[0])[0];
118119
SELECT * FROM fast_emp4000
119120
WHERE home_base @ '(200,200),(2000,1000)'::box
120121
ORDER BY (home_base[0])[0];
121122

123+
EXPLAIN (COSTS OFF)
124+
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
122125
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
123126

127+
EXPLAIN (COSTS OFF)
128+
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
124129
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
125130

131+
EXPLAIN (COSTS OFF)
132+
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
133+
ORDER BY (poly_center(f1))[0];
126134
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
127135
ORDER BY (poly_center(f1))[0];
128136

137+
EXPLAIN (COSTS OFF)
138+
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
139+
ORDER BY area(f1);
129140
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
130141
ORDER BY area(f1);
131142

143+
EXPLAIN (COSTS OFF)
144+
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
132145
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
133146

147+
EXPLAIN (COSTS OFF)
148+
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
134149
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
135150

136151
RESET enable_seqscan;

0 commit comments

Comments
 (0)