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

Commit9fd6511

Browse files
committed
make order index by addInfo
1 parentd2d0320 commit9fd6511

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
ifdefUSE_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)
[8]ページ先頭
©2009-2025 Movatter.jp