Skip to content

Commit ddd144b

Browse files
za-arthurza-arthur
committed
PGPRO-2412: Test bitmap index scan and index scan more accurately
1 parent 039b495 commit ddd144b

File tree

6 files changed

+702
-270
lines changed

6 files changed

+702
-270
lines changed

‎expected/orderby.out

Lines changed: 165 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -2,8 +2,82 @@ CREATE TABLE tsts (id int, t tsvector, d timestamp);
22
\copy tsts from 'data/tsts.data'
33
CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, d)
44
WITH (attach = 'd', to = 't');
5-
INSERT INTO tsts VALUES (-1, 't1 t2', '2016-05-02 02:24:22.326724');
6-
INSERT INTO tsts VALUES (-2, 't1 t2 t3', '2016-05-02 02:26:22.326724');
5+
INSERT INTO tsts VALUES (-1, 't1 t2', '2016-05-02 02:24:22.326724');
6+
INSERT INTO tsts VALUES (-2, 't1 t2 t3', '2016-05-02 02:26:22.326724');
7+
SET enable_indexscan=OFF;
8+
SET enable_indexonlyscan=OFF;
9+
SET enable_bitmapscan=OFF;
10+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
11+
id | d | ?column?
12+
-----+---------------------------------+---------------
13+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
14+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
15+
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
16+
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
17+
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
18+
(5 rows)
19+
20+
SELECT id, d, d <=| '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=| '2016-05-16 14:21:25' LIMIT 5;
21+
id | d | ?column?
22+
-----+---------------------------------+---------------
23+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
24+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
25+
252 | Thu May 12 07:21:22.326724 2016 | 370802.673276
26+
232 | Wed May 11 11:21:22.326724 2016 | 442802.673276
27+
168 | Sun May 08 19:21:22.326724 2016 | 673202.673276
28+
(5 rows)
29+
30+
SELECT id, d, d |=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d |=> '2016-05-16 14:21:25' LIMIT 5;
31+
id | d | ?column?
32+
-----+---------------------------------+---------------
33+
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
34+
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
35+
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
36+
428 | Thu May 19 15:21:22.326724 2016 | 262797.326724
37+
457 | Fri May 20 20:21:22.326724 2016 | 367197.326724
38+
(5 rows)
39+
40+
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER BY d;
41+
id | d
42+
-----+---------------------------------
43+
16 | Mon May 02 11:21:22.326724 2016
44+
39 | Tue May 03 10:21:22.326724 2016
45+
71 | Wed May 04 18:21:22.326724 2016
46+
135 | Sat May 07 10:21:22.326724 2016
47+
168 | Sun May 08 19:21:22.326724 2016
48+
232 | Wed May 11 11:21:22.326724 2016
49+
252 | Thu May 12 07:21:22.326724 2016
50+
354 | Mon May 16 13:21:22.326724 2016
51+
355 | Mon May 16 14:21:22.326724 2016
52+
(9 rows)
53+
54+
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER BY d;
55+
id | d
56+
-----+---------------------------------
57+
371 | Tue May 17 06:21:22.326724 2016
58+
406 | Wed May 18 17:21:22.326724 2016
59+
415 | Thu May 19 02:21:22.326724 2016
60+
428 | Thu May 19 15:21:22.326724 2016
61+
457 | Fri May 20 20:21:22.326724 2016
62+
458 | Fri May 20 21:21:22.326724 2016
63+
484 | Sat May 21 23:21:22.326724 2016
64+
496 | Sun May 22 11:21:22.326724 2016
65+
(8 rows)
66+
67+
-- Test bitmap index scan
68+
RESET enable_bitmapscan;
69+
SET enable_seqscan = off;
70+
EXPLAIN (costs off)
71+
SELECT count(*) FROM tsts WHERE t @@ 'wr|qh';
72+
QUERY PLAN
73+
-------------------------------------------------------------
74+
Aggregate
75+
-> Bitmap Heap Scan on tsts
76+
Recheck Cond: (t @@ '''wr'' | ''qh'''::tsquery)
77+
-> Bitmap Index Scan on tsts_idx
78+
Index Cond: (t @@ '''wr'' | ''qh'''::tsquery)
79+
(5 rows)
80+
781
SELECT count(*) FROM tsts WHERE t @@ 'wr|qh';
882
count
983
-------
@@ -40,9 +114,19 @@ SELECT count(*) FROM tsts WHERE t @@ '(eq|yt)&(wr|qh)';
40114
39
41115
(1 row)
42116

43-
SET enable_indexscan=OFF;
44-
SET enable_indexonlyscan=OFF;
45-
SET enable_bitmapscan=OFF;
117+
EXPLAIN (costs off)
118+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
119+
QUERY PLAN
120+
-------------------------------------------------------------------------------------
121+
Limit
122+
-> Sort
123+
Sort Key: ((d <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone))
124+
-> Bitmap Heap Scan on tsts
125+
Recheck Cond: (t @@ '''wr'' & ''qh'''::tsquery)
126+
-> Bitmap Index Scan on tsts_idx
127+
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
128+
(7 rows)
129+
46130
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
47131
id | d | ?column?
48132
-----+---------------------------------+---------------
@@ -53,6 +137,19 @@ SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY
53137
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
54138
(5 rows)
55139

140+
EXPLAIN (costs off)
141+
SELECT id, d, d <=| '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=| '2016-05-16 14:21:25' LIMIT 5;
142+
QUERY PLAN
143+
-------------------------------------------------------------------------------------
144+
Limit
145+
-> Sort
146+
Sort Key: ((d <=| 'Mon May 16 14:21:25 2016'::timestamp without time zone))
147+
-> Bitmap Heap Scan on tsts
148+
Recheck Cond: (t @@ '''wr'' & ''qh'''::tsquery)
149+
-> Bitmap Index Scan on tsts_idx
150+
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
151+
(7 rows)
152+
56153
SELECT id, d, d <=| '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=| '2016-05-16 14:21:25' LIMIT 5;
57154
id | d | ?column?
58155
-----+---------------------------------+---------------
@@ -63,6 +160,19 @@ SELECT id, d, d <=| '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY
63160
168 | Sun May 08 19:21:22.326724 2016 | 673202.673276
64161
(5 rows)
65162

163+
EXPLAIN (costs off)
164+
SELECT id, d, d |=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d |=> '2016-05-16 14:21:25' LIMIT 5;
165+
QUERY PLAN
166+
-------------------------------------------------------------------------------------
167+
Limit
168+
-> Sort
169+
Sort Key: ((d |=> 'Mon May 16 14:21:25 2016'::timestamp without time zone))
170+
-> Bitmap Heap Scan on tsts
171+
Recheck Cond: (t @@ '''wr'' & ''qh'''::tsquery)
172+
-> Bitmap Index Scan on tsts_idx
173+
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
174+
(7 rows)
175+
66176
SELECT id, d, d |=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d |=> '2016-05-16 14:21:25' LIMIT 5;
67177
id | d | ?column?
68178
-----+---------------------------------+---------------
@@ -73,6 +183,37 @@ SELECT id, d, d |=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY
73183
457 | Fri May 20 20:21:22.326724 2016 | 367197.326724
74184
(5 rows)
75185

186+
EXPLAIN (costs off)
187+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
188+
QUERY PLAN
189+
-----------------------------------------------------------------------------------
190+
Limit
191+
-> Index Scan using tsts_idx on tsts
192+
Order By: (d <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone)
193+
(3 rows)
194+
195+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
196+
id | d | ?column?
197+
-----+---------------------------------+-------------
198+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
199+
356 | Mon May 16 15:21:22.326724 2016 | 3597.326724
200+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
201+
357 | Mon May 16 16:21:22.326724 2016 | 7197.326724
202+
353 | Mon May 16 12:21:22.326724 2016 | 7202.673276
203+
(5 rows)
204+
205+
EXPLAIN (costs off)
206+
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER BY d;
207+
QUERY PLAN
208+
----------------------------------------------------------------------------------------------------------------------------------
209+
Sort
210+
Sort Key: d
211+
-> Bitmap Heap Scan on tsts
212+
Recheck Cond: ((t @@ '''wr'' & ''qh'''::tsquery) AND (d <= 'Mon May 16 14:21:25 2016'::timestamp without time zone))
213+
-> Bitmap Index Scan on tsts_idx
214+
Index Cond: ((t @@ '''wr'' & ''qh'''::tsquery) AND (d <= 'Mon May 16 14:21:25 2016'::timestamp without time zone))
215+
(6 rows)
216+
76217
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER BY d;
77218
id | d
78219
-----+---------------------------------
@@ -87,6 +228,18 @@ SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER
87228
355 | Mon May 16 14:21:22.326724 2016
88229
(9 rows)
89230

231+
EXPLAIN (costs off)
232+
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER BY d;
233+
QUERY PLAN
234+
----------------------------------------------------------------------------------------------------------------------------------
235+
Sort
236+
Sort Key: d
237+
-> Bitmap Heap Scan on tsts
238+
Recheck Cond: ((t @@ '''wr'' & ''qh'''::tsquery) AND (d >= 'Mon May 16 14:21:25 2016'::timestamp without time zone))
239+
-> Bitmap Index Scan on tsts_idx
240+
Index Cond: ((t @@ '''wr'' & ''qh'''::tsquery) AND (d >= 'Mon May 16 14:21:25 2016'::timestamp without time zone))
241+
(6 rows)
242+
90243
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER BY d;
91244
id | d
92245
-----+---------------------------------
@@ -100,20 +253,18 @@ SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER
100253
496 | Sun May 22 11:21:22.326724 2016
101254
(8 rows)
102255

256+
-- Test index scan
103257
RESET enable_indexscan;
104258
RESET enable_indexonlyscan;
105-
RESET enable_bitmapscan;
106-
SET enable_seqscan = off;
259+
SET enable_bitmapscan=OFF;
107260
EXPLAIN (costs off)
108261
SELECT count(*) FROM tsts WHERE t @@ 'wr|qh';
109-
QUERY PLAN
110-
-------------------------------------------------------------
262+
QUERY PLAN
263+
-------------------------------------------------------
111264
Aggregate
112-
-> Bitmap Heap Scan on tsts
113-
Recheck Cond: (t @@ '''wr'' | ''qh'''::tsquery)
114-
-> Bitmap Index Scan on tsts_idx
115-
Index Cond: (t @@ '''wr'' | ''qh'''::tsquery)
116-
(5 rows)
265+
-> Index Scan using tsts_idx on tsts
266+
Index Cond: (t @@ '''wr'' | ''qh'''::tsquery)
267+
(3 rows)
117268

118269
SELECT count(*) FROM tsts WHERE t @@ 'wr|qh';
119270
count
@@ -277,54 +428,6 @@ SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER
277428
496 | Sun May 22 11:21:22.326724 2016
278429
(8 rows)
279430

280-
SET enable_bitmapscan=OFF;
281-
EXPLAIN (costs off)
282-
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER BY d;
283-
QUERY PLAN
284-
----------------------------------------------------------------------------------------------------------------------------
285-
Sort
286-
Sort Key: d
287-
-> Index Scan using tsts_idx on tsts
288-
Index Cond: ((t @@ '''wr'' & ''qh'''::tsquery) AND (d <= 'Mon May 16 14:21:25 2016'::timestamp without time zone))
289-
(4 rows)
290-
291-
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER BY d;
292-
id | d
293-
-----+---------------------------------
294-
16 | Mon May 02 11:21:22.326724 2016
295-
39 | Tue May 03 10:21:22.326724 2016
296-
71 | Wed May 04 18:21:22.326724 2016
297-
135 | Sat May 07 10:21:22.326724 2016
298-
168 | Sun May 08 19:21:22.326724 2016
299-
232 | Wed May 11 11:21:22.326724 2016
300-
252 | Thu May 12 07:21:22.326724 2016
301-
354 | Mon May 16 13:21:22.326724 2016
302-
355 | Mon May 16 14:21:22.326724 2016
303-
(9 rows)
304-
305-
EXPLAIN (costs off)
306-
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER BY d;
307-
QUERY PLAN
308-
----------------------------------------------------------------------------------------------------------------------------
309-
Sort
310-
Sort Key: d
311-
-> Index Scan using tsts_idx on tsts
312-
Index Cond: ((t @@ '''wr'' & ''qh'''::tsquery) AND (d >= 'Mon May 16 14:21:25 2016'::timestamp without time zone))
313-
(4 rows)
314-
315-
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER BY d;
316-
id | d
317-
-----+---------------------------------
318-
371 | Tue May 17 06:21:22.326724 2016
319-
406 | Wed May 18 17:21:22.326724 2016
320-
415 | Thu May 19 02:21:22.326724 2016
321-
428 | Thu May 19 15:21:22.326724 2016
322-
457 | Fri May 20 20:21:22.326724 2016
323-
458 | Fri May 20 21:21:22.326724 2016
324-
484 | Sat May 21 23:21:22.326724 2016
325-
496 | Sun May 22 11:21:22.326724 2016
326-
(8 rows)
327-
328431
SELECT id, d FROM tsts WHERE t @@ 'wr&qh' AND d <= '2016-05-16 14:21:25' ORDER BY d ASC LIMIT 3;
329432
id | d
330433
----+---------------------------------

0 commit comments

Comments
 (0)