- Notifications
You must be signed in to change notification settings - Fork 59
/
Copy pathorderby_hash.sql
135 lines (106 loc) · 6.41 KB
/
orderby_hash.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
/*
* ------------------------------------
* NOTE: This test behaves differenly
* ------------------------------------
*
* orderby_hash.out - test output for 64-bit systems and
* orderby_hash_1.out - test output for 32-bit systems.
*
*/
CREATETABLEtstsh (id int, t tsvector, d timestamp);
\copy tstsh from'data/tsts.data'
CREATEINDEXtstsh_idxON tstsh USING rum (t rum_tsvector_hash_addon_ops, d)
WITH (attach ='d', to ='t');
INSERT INTO tstsh VALUES (-1, 't1 t2', '2016-05-02 02:24:22.326724');
INSERT INTO tstsh 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 tstsh 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 tstsh 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 tstsh WHERE t @@ 'wr&qh'ORDER BY d |=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tstsh 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 tstsh WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tstsh WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tstsh WHERE t @@ 'wr&qh';
SELECTcount(*) FROM tstsh WHERE t @@ 'eq&yt';
SELECTcount(*) FROM tstsh WHERE t @@ 'eq|yt';
SELECTcount(*) FROM tstsh WHERE t @@ '(eq&yt)|(wr&qh)';
SELECTcount(*) FROM tstsh WHERE t @@ '(eq|yt)&(wr|qh)';
EXPLAIN (costs off)
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tstsh 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 tstsh 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 tstsh 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 tstsh 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 tstsh 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 tstsh 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 tstsh ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tstsh ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
EXPLAIN (costs off)
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tstsh 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 tstsh WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tstsh WHERE t @@ 'wr|qh';
SELECTcount(*) FROM tstsh WHERE t @@ 'wr&qh';
SELECTcount(*) FROM tstsh WHERE t @@ 'eq&yt';
SELECTcount(*) FROM tstsh WHERE t @@ 'eq|yt';
SELECTcount(*) FROM tstsh WHERE t @@ '(eq&yt)|(wr&qh)';
SELECTcount(*) FROM tstsh WHERE t @@ '(eq|yt)&(wr|qh)';
EXPLAIN (costs off)
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tstsh 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 tstsh 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 tstsh 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 tstsh 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 tstsh 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 tstsh 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 tstsh ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
SELECT id, d, d <=>'2016-05-16 14:21:25'FROM tstsh ORDER BY d <=>'2016-05-16 14:21:25'LIMIT5;
EXPLAIN (costs off)
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d;
EXPLAIN (costs off)
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d ASCLIMIT3;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d <='2016-05-16 14:21:25'ORDER BY d DESCLIMIT3;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d ASCLIMIT3;
SELECT id, d FROM tstsh WHERE t @@ 'wr&qh'AND d >='2016-05-16 14:21:25'ORDER BY d DESCLIMIT3;
-- Test multicolumn index
RESET enable_indexscan;
RESET enable_indexonlyscan;
RESET enable_bitmapscan;
SET enable_seqscan = off;
DROPINDEX tstsh_idx;
CREATEINDEXtstsh_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;
