- Notifications
You must be signed in to change notification settings - Fork 59
/
Copy pathorderby.sql
142 lines (110 loc) · 6.58 KB
/
orderby.sql
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
/*
* ------------------------------------
* NOTE: This test behaves differenly
* ------------------------------------
*
* orderby.out - test output for 64-bit systems and
* orderby_1.out - test output for 32-bit systems.
*
*/
CREATETABLEtsts (id int, t tsvector, d timestamp);
\copy tsts from'data/tsts.data'
CREATEINDEXtsts_idxON tsts USING rum (t rum_tsvector_addon_ops, d)
WITH (attach ='d', to ='t');
INSERT INTO tsts VALUES (-1, 't1 t2', '2016-05-02 02:24:22.326724');
INSERT INTO tsts VALUES (-2, 't1 t2 t3', '2016-05-02 02:26:22.326724');
SET enable_indexscan=OFF;
SET enable_indexonlyscan=OFF;
SET enable_bitmapscan=OFF;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=| '2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=| '2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d |=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d |=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
-- Test bitmap index scan
RESET enable_bitmapscan;
SET enable_seqscan = off;
EXPLAIN (costs off)
SELECTcount(*) FROM tsts WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tsts WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tsts WHERE t @@ 'wr&qh';
SELECTcount(*) FROM tsts WHERE t @@ 'eq&yt';
SELECTcount(*) FROM tsts WHERE t @@ 'eq|yt';
SELECTcount(*) FROM tsts WHERE t @@ '(eq&yt)|(wr&qh)';
SELECTcount(*) FROM tsts WHERE t @@ '(eq|yt)&(wr|qh)';
EXPLAIN (costs off)
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d, d <=| '2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=| '2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=| '2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=| '2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d, d |=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d |=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d |=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d |=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
-- Test index scan
RESET enable_indexscan;
RESET enable_indexonlyscan;
SET enable_bitmapscan=OFF;
EXPLAIN (costs off)
SELECTcount(*) FROM tsts WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tsts WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tsts WHERE t @@ 'wr&qh';
SELECTcount(*) FROM tsts WHERE t @@ 'eq&yt';
SELECTcount(*) FROM tsts WHERE t @@ 'eq|yt';
SELECTcount(*) FROM tsts WHERE t @@ '(eq&yt)|(wr&qh)';
SELECTcount(*) FROM tsts WHERE t @@ '(eq|yt)&(wr|qh)';
EXPLAIN (costs off)
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d, d <=| '2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=| '2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=| '2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=| '2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d, d |=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d |=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d |=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d |=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d ASCLIMIT3;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d DESCLIMIT3;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d ASCLIMIT3;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d DESCLIMIT3;
-- Test "ORDER BY" error message
DROPINDEX tsts_idx;
CREATEINDEXtsts_idxON tsts USING rum (t rum_tsvector_addon_ops, d);
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tsts WHERE t @@ 'wr&qh'ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
-- Test multicolumn index
RESET enable_indexscan;
RESET enable_indexonlyscan;
RESET enable_bitmapscan;
SET enable_seqscan = off;
DROPINDEX tsts_idx;
CREATEINDEXtsts_id_idxON tsts USING rum (t rum_tsvector_addon_ops, id, d)
WITH (attach ='d', to ='t');
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND id =1::intORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND id =1::intORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND id =355::intORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND id =355::intORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d ='2016-05-11 11:21:22.326724'::timestampORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d ='2016-05-11 11:21:22.326724'::timestampORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d ='2000-05-01'::timestampORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d FROM tsts WHERE t @@ 'wr&qh'AND d ='2000-05-01'::timestampORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
