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

Name: rum/sql/int4.sql at master · postgrespro/rum · GitHub
Rating: 4.7 (4748 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
set enable_seqscan=off;

CREATETABLEtest_int4 (
 i int4
);

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

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

CREATETABLEtest_int4_oASSELECT id::int4, t FROM tsts;

CREATEINDEXtest_int4_o_idxON 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 <=>400FROM test_int4_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=| 400FROM test_int4_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id |=>400FROM test_int4_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;

RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_seqscan = off;

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

EXPLAIN (costs off)
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;
SELECT id FROM test_int4_o WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;

CREATETABLEtest_int4_aASSELECT id::int4, t FROM tsts;

CREATEINDEXtest_int4_a_idxON test_int4_a USING rum
 (t rum_tsvector_addon_ops, id)
 WITH (attach ='id', to ='t', order_by_attach='t');

EXPLAIN (costs off)
SELECTcount(*) FROM test_int4_a WHERE id <400;
SELECTcount(*) FROM test_int4_a WHERE id <400;

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

EXPLAIN (costs off)
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;
SELECT id FROM test_int4_a WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;

CREATETABLEtest_int4_h_oASSELECT id::int4, t FROM tsts;

CREATEINDEXtest_int4_h_o_idxON 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 <=>400FROM test_int4_h_o WHERE t @@ 'wr&qh'ORDER BY id <=>400LIMIT5;
SELECT id, id <=| 400FROM test_int4_h_o WHERE t @@ 'wr&qh'ORDER BY id <=| 400LIMIT5;
SELECT id, id |=>400FROM test_int4_h_o WHERE t @@ 'wr&qh'ORDER BY id |=>400LIMIT5;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;

RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_seqscan = off;

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

EXPLAIN (costs off)
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;
SELECT id FROM test_int4_h_o WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;

CREATETABLEtest_int4_h_aASSELECT id::int4, t FROM tsts;

CREATEINDEXtest_int4_h_a_idxON 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)
SELECTcount(*) FROM test_int4_h_a WHERE id <400;
SELECTcount(*) FROM test_int4_h_a WHERE id <400;

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

EXPLAIN (costs off)
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh'AND id <=400ORDER BY id;
EXPLAIN (costs off)
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;
SELECT id FROM test_int4_h_a WHERE t @@ 'wr&qh'AND id >=400ORDER BY id;

CREATETABLEtest_int4_id_tASSELECT id::int4, t FROM tsts;

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