1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
set enable_seqscan=off;
CREATE TABLE test_int4 (
i int4
);
INSERT INTO test_int4 VALUES (-2),(-1),(0),(1),(2),(3);
CREATE INDEX idx_int4 ON test_int4 USING rum (i);
SELECT * FROM test_int4 WHERE i<1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i<=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i>=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i;
EXPLAIN (costs off)
SELECT *, i <=> 0::int4 FROM test_int4 ORDER BY i <=> 0::int4;
SELECT *, i <=> 0::int4 FROM test_int4 ORDER BY i <=> 0::int4;
EXPLAIN (costs off)
SELECT *, i <=> 1::int4 FROM test_int4 WHERE i<1::int4 ORDER BY i <=> 1::int4;
SELECT *, i <=> 1::int4 FROM test_int4 WHERE i<1::int4 ORDER BY i <=> 1::int4;
CREATE TABLE test_int4_o AS SELECT id::int4, t FROM tsts;
CREATE INDEX test_int4_o_idx ON test_int4_o USING rum
(t rum_tsvector_addon_ops, id)
WITH (attach = 'id', to = 't');
RESET enable_seqscan;
SET enable_indexscan=OFF;
SET enable_indexonlyscan=OFF;
SET enable_bitmapscan=OFF;
SELECT id, id <=> 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_seqscan = off;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int4_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
CREATE TABLE test_int4_a AS SELECT id::int4, t FROM tsts;
CREATE INDEX test_int4_a_idx ON test_int4_a USING rum
(t rum_tsvector_addon_ops, id)
WITH (attach = 'id', to = 't', order_by_attach='t');
EXPLAIN (costs off)
SELECT count(*) FROM test_int4_a WHERE id < 400;
SELECT count(*) FROM test_int4_a WHERE id < 400;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int4_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int4_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int4_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int4_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int4_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int4_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
CREATE TABLE test_int4_h_o AS SELECT id::int4, t FROM tsts;
CREATE INDEX test_int4_h_o_idx ON test_int4_h_o USING rum
(t rum_tsvector_hash_addon_ops, id)
WITH (attach = 'id', to = 't');
RESET enable_seqscan;
SET enable_indexscan=OFF;
SET enable_indexonlyscan=OFF;
SET enable_bitmapscan=OFF;
SELECT id, id <=> 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_seqscan = off;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int4_h_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
CREATE TABLE test_int4_h_a AS SELECT id::int4, t FROM tsts;
CREATE INDEX test_int4_h_a_idx ON test_int4_h_a USING rum
(t rum_tsvector_hash_addon_ops, id)
WITH (attach = 'id', to = 't', order_by_attach='t');
EXPLAIN (costs off)
SELECT count(*) FROM test_int4_h_a WHERE id < 400;
SELECT count(*) FROM test_int4_h_a WHERE id < 400;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int4_h_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int4_h_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int4_h_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int4_h_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int4_h_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int4_h_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh' AND id <= 400 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh' AND id >= 400 ORDER BY id;
CREATE TABLE test_int4_id_t AS SELECT id::int4, t FROM tsts;
CREATE INDEX test_int4_id_t_idx ON test_int4_o USING rum
(t rum_tsvector_ops, id);
EXPLAIN (costs off)
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh' AND id <= 400::int4 ORDER BY id <=> 400::int4;
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh' AND id <= 400::int4 ORDER BY id <=> 400::int4;
|
