Skip to content

Commit 9fd6511

Browse files
feodorfeodor
committed
make order index by addInfo
1 parent d2d0320 commit 9fd6511

File tree

11 files changed

+780
-279
lines changed

11 files changed

+780
-279
lines changed

‎Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ EXTENSION = rum
1111
DATA = rum--1.0.sql
1212
PGFILEDESC = "RUM index access method"
1313

14-
REGRESS = rum ruminv timestamp orderby
14+
REGRESS = rum ruminv timestamp orderby altorder
1515

1616
ifdef USE_PGXS
1717
PG_CONFIG = pg_config

‎TODO

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
1. with naturalOrder=true make scan the rest to be consistent with seqscan
2+
2. add leftlink to data page to privide backward scan on index (<=| op)
3+

‎expected/altorder.out

Lines changed: 205 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,205 @@
1+
CREATE TABLE atsts (id int, t tsvector, d timestamp);
2+
\copy atsts from 'data/tsts.data'
3+
CREATE INDEX atsts_idx ON atsts USING rum (t rum_tsvector_timestamp_ops, d)
4+
WITH (orderby = 'd', addto = 't', use_alternative_order='t');
5+
INSERT INTO atsts VALUES (-1, 't1 t2', '2016-05-02 02:24:22.326724');
6+
INSERT INTO atsts VALUES (-2, 't1 t2 t3', '2016-05-02 02:26:22.326724');
7+
SELECT count(*) FROM atsts WHERE t @@ 'wr|qh';
8+
count
9+
-------
10+
158
11+
(1 row)
12+
13+
SELECT count(*) FROM atsts WHERE t @@ 'wr&qh';
14+
count
15+
-------
16+
17
17+
(1 row)
18+
19+
SELECT count(*) FROM atsts WHERE t @@ 'eq&yt';
20+
count
21+
-------
22+
6
23+
(1 row)
24+
25+
SELECT count(*) FROM atsts WHERE t @@ 'eq|yt';
26+
count
27+
-------
28+
98
29+
(1 row)
30+
31+
SELECT count(*) FROM atsts WHERE t @@ '(eq&yt)|(wr&qh)';
32+
count
33+
-------
34+
23
35+
(1 row)
36+
37+
SELECT count(*) FROM atsts WHERE t @@ '(eq|yt)&(wr|qh)';
38+
count
39+
-------
40+
39
41+
(1 row)
42+
43+
SET enable_indexscan=OFF;
44+
SET enable_indexonlyscan=OFF;
45+
SET enable_bitmapscan=OFF;
46+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
47+
id | d | ?column?
48+
-----+---------------------------------+---------------
49+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
50+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
51+
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
52+
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
53+
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
54+
(5 rows)
55+
56+
SELECT id, d, d <=| '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d <=| '2016-05-16 14:21:25' LIMIT 5;
57+
id | d | ?column?
58+
-----+---------------------------------+---------------
59+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
60+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
61+
252 | Thu May 12 07:21:22.326724 2016 | 370802.673276
62+
232 | Wed May 11 11:21:22.326724 2016 | 442802.673276
63+
168 | Sun May 08 19:21:22.326724 2016 | 673202.673276
64+
(5 rows)
65+
66+
SELECT id, d, d |=> '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d |=> '2016-05-16 14:21:25' LIMIT 5;
67+
id | d | ?column?
68+
-----+---------------------------------+---------------
69+
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
70+
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
71+
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
72+
428 | Thu May 19 15:21:22.326724 2016 | 262797.326724
73+
457 | Fri May 20 20:21:22.326724 2016 | 367197.326724
74+
(5 rows)
75+
76+
RESET enable_indexscan;
77+
RESET enable_indexonlyscan;
78+
RESET enable_bitmapscan;
79+
SET enable_seqscan = off;
80+
EXPLAIN (costs off)
81+
SELECT count(*) FROM atsts WHERE t @@ 'wr|qh';
82+
QUERY PLAN
83+
-------------------------------------------------------------
84+
Aggregate
85+
-> Bitmap Heap Scan on atsts
86+
Recheck Cond: (t @@ '''wr'' | ''qh'''::tsquery)
87+
-> Bitmap Index Scan on atsts_idx
88+
Index Cond: (t @@ '''wr'' | ''qh'''::tsquery)
89+
(5 rows)
90+
91+
SELECT count(*) FROM atsts WHERE t @@ 'wr|qh';
92+
count
93+
-------
94+
158
95+
(1 row)
96+
97+
SELECT count(*) FROM atsts WHERE t @@ 'wr&qh';
98+
count
99+
-------
100+
17
101+
(1 row)
102+
103+
SELECT count(*) FROM atsts WHERE t @@ 'eq&yt';
104+
count
105+
-------
106+
6
107+
(1 row)
108+
109+
SELECT count(*) FROM atsts WHERE t @@ 'eq|yt';
110+
count
111+
-------
112+
98
113+
(1 row)
114+
115+
SELECT count(*) FROM atsts WHERE t @@ '(eq&yt)|(wr&qh)';
116+
count
117+
-------
118+
23
119+
(1 row)
120+
121+
SELECT count(*) FROM atsts WHERE t @@ '(eq|yt)&(wr|qh)';
122+
count
123+
-------
124+
39
125+
(1 row)
126+
127+
EXPLAIN (costs off)
128+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
129+
QUERY PLAN
130+
-----------------------------------------------------------------------------------
131+
Limit
132+
-> Index Scan using atsts_idx on atsts
133+
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
134+
Order By: (d <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone)
135+
(4 rows)
136+
137+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
138+
id | d | ?column?
139+
-----+---------------------------------+---------------
140+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
141+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
142+
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
143+
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
144+
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
145+
(5 rows)
146+
147+
EXPLAIN (costs off)
148+
SELECT id, d, d <=| '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d <=| '2016-05-16 14:21:25' LIMIT 5;
149+
QUERY PLAN
150+
-----------------------------------------------------------------------------------
151+
Limit
152+
-> Index Scan using atsts_idx on atsts
153+
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
154+
Order By: (d <=| 'Mon May 16 14:21:25 2016'::timestamp without time zone)
155+
(4 rows)
156+
157+
SELECT id, d, d <=| '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d <=| '2016-05-16 14:21:25' LIMIT 5;
158+
id | d | ?column?
159+
-----+---------------------------------+---------------
160+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
161+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
162+
252 | Thu May 12 07:21:22.326724 2016 | 370802.673276
163+
232 | Wed May 11 11:21:22.326724 2016 | 442802.673276
164+
168 | Sun May 08 19:21:22.326724 2016 | 673202.673276
165+
(5 rows)
166+
167+
EXPLAIN (costs off)
168+
SELECT id, d, d |=> '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d |=> '2016-05-16 14:21:25' LIMIT 5;
169+
QUERY PLAN
170+
-----------------------------------------------------------------------------------
171+
Limit
172+
-> Index Scan using atsts_idx on atsts
173+
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
174+
Order By: (d |=> 'Mon May 16 14:21:25 2016'::timestamp without time zone)
175+
(4 rows)
176+
177+
SELECT id, d, d |=> '2016-05-16 14:21:25' FROM atsts WHERE t @@ 'wr&qh' ORDER BY d |=> '2016-05-16 14:21:25' LIMIT 5;
178+
id | d | ?column?
179+
-----+---------------------------------+---------------
180+
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
181+
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
182+
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
183+
428 | Thu May 19 15:21:22.326724 2016 | 262797.326724
184+
457 | Fri May 20 20:21:22.326724 2016 | 367197.326724
185+
(5 rows)
186+
187+
EXPLAIN (costs off)
188+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM atsts ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
189+
QUERY PLAN
190+
-----------------------------------------------------------------------------------
191+
Limit
192+
-> Index Scan using atsts_idx on atsts
193+
Order By: (d <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone)
194+
(3 rows)
195+
196+
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM atsts ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
197+
id | d | ?column?
198+
-----+---------------------------------+-------------
199+
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
200+
356 | Mon May 16 15:21:22.326724 2016 | 3597.326724
201+
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
202+
357 | Mon May 16 16:21:22.326724 2016 | 7197.326724
203+
353 | Mon May 16 12:21:22.326724 2016 | 7202.673276
204+
(5 rows)
205+

0 commit comments

Comments
 (0)