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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
/*
* ------------------------------------
* NOTE: This test behaves differenly
* ------------------------------------
*
* int8.out - test output for 64-bit systems and
* int8_1.out - test output for 32-bit systems.
*
* Since c01743aa486 and 161320b4b96 (>=18) EXPLAIN output was changed,
* now it shows whether nodes are disabled.
*
* int8_2.out - test output for 32-bit systems (>=18)
*
*/
set enable_seqscan=off;
CREATE TABLE test_int8 (
i int8
);
INSERT INTO test_int8 VALUES (-2),(-1),(0),(1),(2),(3);
CREATE INDEX idx_int8 ON test_int8 USING rum (i);
SELECT * FROM test_int8 WHERE i<1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i<=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i>=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i;
EXPLAIN (costs off)
SELECT *, i <=> 0::int8 FROM test_int8 ORDER BY i <=> 0::int8;
SELECT *, i <=> 0::int8 FROM test_int8 ORDER BY i <=> 0::int8;
EXPLAIN (costs off)
SELECT *, i <=> 1::int8 FROM test_int8 WHERE i<1::int8 ORDER BY i <=> 1::int8;
SELECT *, i <=> 1::int8 FROM test_int8 WHERE i<1::int8 ORDER BY i <=> 1::int8;
CREATE TABLE test_int8_o AS SELECT id::int8, t FROM tsts;
CREATE INDEX test_int8_o_idx ON test_int8_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_int8_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id FROM test_int8_o WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
SELECT id FROM test_int8_o WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_seqscan = off;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int8_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int8_o WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
SELECT id FROM test_int8_o WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int8_o WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
SELECT id FROM test_int8_o WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
CREATE TABLE test_int8_a AS SELECT id::int8, t FROM tsts;
CREATE INDEX test_int8_a_idx ON test_int8_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_int8_a WHERE id < 400::int8;
SELECT count(*) FROM test_int8_a WHERE id < 400::int8;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int8_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int8_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int8_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int8_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int8_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int8_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int8_a WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
SELECT id FROM test_int8_a WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int8_a WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
SELECT id FROM test_int8_a WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
CREATE TABLE test_int8_h_o AS SELECT id::int8, t FROM tsts;
CREATE INDEX test_int8_h_o_idx ON test_int8_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_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id FROM test_int8_h_o WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
SELECT id FROM test_int8_h_o WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_seqscan = off;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int8_h_o WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int8_h_o WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
SELECT id FROM test_int8_h_o WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int8_h_o WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
SELECT id FROM test_int8_h_o WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
CREATE TABLE test_int8_h_a AS SELECT id::int8, t FROM tsts;
CREATE INDEX test_int8_h_a_idx ON test_int8_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_int8_h_a WHERE id < 400::int8;
SELECT count(*) FROM test_int8_h_a WHERE id < 400::int8;
EXPLAIN (costs off)
SELECT id, id <=> 400 FROM test_int8_h_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
SELECT id, id <=> 400 FROM test_int8_h_a WHERE t @@ 'wr&qh' ORDER BY id <=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id <=| 400 FROM test_int8_h_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
SELECT id, id <=| 400 FROM test_int8_h_a WHERE t @@ 'wr&qh' ORDER BY id <=| 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id, id |=> 400 FROM test_int8_h_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
SELECT id, id |=> 400 FROM test_int8_h_a WHERE t @@ 'wr&qh' ORDER BY id |=> 400 LIMIT 5;
EXPLAIN (costs off)
SELECT id FROM test_int8_h_a WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
SELECT id FROM test_int8_h_a WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int8_h_a WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
SELECT id FROM test_int8_h_a WHERE t @@ 'wr&qh' AND id >= 400::int8 ORDER BY id;
CREATE TABLE test_int8_id_t AS SELECT id::int8, t FROM tsts;
CREATE INDEX test_int8_id_t_idx ON test_int8_o USING rum
(t rum_tsvector_ops, id);
EXPLAIN (costs off)
SELECT id FROM test_int8_h_a WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id <=> 400::int8;
SELECT id FROM test_int8_h_a WHERE t @@ 'wr&qh' AND id <= 400::int8 ORDER BY id <=> 400::int8;
|
