rum/sql/int8.sql at master · postgrespro/rum · GitHub

Name: rum/sql/int8.sql at master · postgrespro/rum · GitHub
Rating: 4.5 (267 reviews)
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
/*
 * ------------------------------------
 * NOTE: This test behaves differenly
 * ------------------------------------
 *
 * int8.out - test output for 64-bit systems and
 * int8_1.out - test output for 32-bit systems.
 *
*/


set enable_seqscan=off;

CREATETABLEtest_int8 (
 i int8
);

INSERT INTO test_int8 VALUES (-2),(-1),(0),(1),(2),(3);

CREATEINDEXidx_int8ON 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;

CREATETABLEtest_int8_oASSELECT id::int8, t FROM tsts;

CREATEINDEXtest_int8_o_idxON 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 <=>400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=| 400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id |=>400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
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 <=>400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=>400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
EXPLAIN (costs off)
SELECT id, id <=| 400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id <=| 400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
EXPLAIN (costs off)
SELECT id, id |=>400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
SELECT id, id |=>400FROM test_int8_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;

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;

CREATETABLEtest_int8_aASSELECT id::int8, t FROM tsts;

CREATEINDEXtest_int8_a_idxON test_int8_a USING rum
 (t rum_tsvector_addon_ops, id)
 WITH (attach ='id', to ='t', order_by_attach='t');

EXPLAIN (costs off)
SELECTcount(*) FROM test_int8_a WHERE id <400::int8;
SELECTcount(*) FROM test_int8_a WHERE id <400::int8;

EXPLAIN (costs off)
SELECT id, id <=>400FROM test_int8_a WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=>400FROM test_int8_a WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
EXPLAIN (costs off)
SELECT id, id <=| 400FROM test_int8_a WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id <=| 400FROM test_int8_a WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
EXPLAIN (costs off)
SELECT id, id |=>400FROM test_int8_a WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
SELECT id, id |=>400FROM test_int8_a WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;

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;

CREATETABLEtest_int8_h_oASSELECT id::int8, t FROM tsts;

CREATEINDEXtest_int8_h_o_idxON 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 <=>400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=| 400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id |=>400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
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 <=>400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=>400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
EXPLAIN (costs off)
SELECT id, id <=| 400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id <=| 400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
EXPLAIN (costs off)
SELECT id, id |=>400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
SELECT id, id |=>400FROM test_int8_h_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;


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;

CREATETABLEtest_int8_h_aASSELECT id::int8, t FROM tsts;

CREATEINDEXtest_int8_h_a_idxON 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)
SELECTcount(*) FROM test_int8_h_a WHERE id <400::int8;
SELECTcount(*) FROM test_int8_h_a WHERE id <400::int8;

EXPLAIN (costs off)
SELECT id, id <=>400FROM test_int8_h_a WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=>400FROM test_int8_h_a WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
EXPLAIN (costs off)
SELECT id, id <=| 400FROM test_int8_h_a WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id <=| 400FROM test_int8_h_a WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
EXPLAIN (costs off)
SELECT id, id |=>400FROM test_int8_h_a WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
SELECT id, id |=>400FROM test_int8_h_a WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;

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;

CREATETABLEtest_int8_id_tASSELECT id::int8, t FROM tsts;

CREATEINDEXtest_int8_id_t_idxON 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;