@@ -113,9 +113,20 @@ SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
113
113
SET enable_seqscan = OFF;
114
114
SET enable_indexscan = ON;
115
115
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
+
119
130
SELECT * FROM fast_emp4000
120
131
WHERE home_base @ '(200,200),(2000,1000)'::box
121
132
ORDER BY (home_base[0])[0];
@@ -125,25 +136,69 @@ SELECT * FROM fast_emp4000
125
136
(1444,403),(1346,344)
126
137
(2 rows)
127
138
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
+
128
150
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
129
151
count
130
152
-------
131
153
2
132
154
(1 row)
133
155
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
+
134
167
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
135
168
count
136
169
-------
137
170
278
138
171
(1 row)
139
172
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
+
140
184
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
141
185
ORDER BY (poly_center(f1))[0];
142
186
f1
143
187
---------------------
144
188
((2,0),(2,4),(0,0))
145
189
(1 row)
146
190
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
+
147
202
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
148
203
ORDER BY area(f1);
149
204
f1
@@ -154,12 +209,34 @@ SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
154
209
<(100,1),115>
155
210
(4 rows)
156
211
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
+
157
223
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
158
224
count
159
225
-------
160
226
2
161
227
(1 row)
162
228
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
+
163
240
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
164
241
count
165
242
-------