Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8835d63

Browse files
committed
Experiment with using EXPLAIN COSTS OFF in regression tests.
This is a simple test to see whether COSTS OFF will help much with gettingEXPLAIN output that's sufficiently platform-independent for use in theregression tests. The planner does have some freedom of choice in theseexamples (plain via bitmap indexscan), so I'm not sure what will happen.
1 parentd4382c4 commit8835d63

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+
SELECTcount(*)FROM fast_emp4000WHERE home_base &&'(1000,1000,0,0)'::box;
122125
SELECTcount(*)FROM fast_emp4000WHERE home_base &&'(1000,1000,0,0)'::box;
123126

127+
EXPLAIN (COSTS OFF)
128+
SELECTcount(*)FROM fast_emp4000WHERE home_base ISNULL;
124129
SELECTcount(*)FROM fast_emp4000WHERE home_base ISNULL;
125130

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

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

143+
EXPLAIN (COSTS OFF)
144+
SELECTcount(*)FROM gpolygon_tblWHERE f1 &&'(1000,1000,0,0)'::polygon;
132145
SELECTcount(*)FROM gpolygon_tblWHERE f1 &&'(1000,1000,0,0)'::polygon;
133146

147+
EXPLAIN (COSTS OFF)
148+
SELECTcount(*)FROM gcircle_tblWHERE f1 &&'<(500,500),500>'::circle;
134149
SELECTcount(*)FROM gcircle_tblWHERE f1 &&'<(500,500),500>'::circle;
135150

136151
RESET enable_seqscan;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp