Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita3fdac3

Browse files
committed
tests for rum_anyarray_addon_ops
1 parentcbe3732 commita3fdac3

File tree

2 files changed

+148
-3
lines changed

2 files changed

+148
-3
lines changed

‎expected/array.out

Lines changed: 100 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,13 @@
11
set enable_seqscan=off;
2+
set enable_sort=off;
23
/*
34
* Complete checks for int2[].
45
*/
56
CREATE TABLE test_array (
67
i int2[]
78
);
89
INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');
9-
CREATE INDEX idx_array ON test_array USING rum (i);
10+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
1011
SELECT * FROM test_array WHERE i = '{NULL}';
1112
ERROR: array must not contain nulls
1213
SELECT * FROM test_array WHERE i = '{1,2,3,NULL}';
@@ -407,10 +408,93 @@ SELECT * FROM test_array WHERE i % '{100}';
407408
---
408409
(0 rows)
409410

411+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{1}' ORDER BY i <=> '{1}' ASC;
412+
QUERY PLAN
413+
------------------------------------------
414+
Index Scan using idx_array on test_array
415+
Index Cond: (i && '{1}'::smallint[])
416+
Order By: (i <=> '{1}'::smallint[])
417+
(3 rows)
418+
419+
SELECT * FROM test_array WHERE i && '{1}' ORDER BY i <=> '{1}' ASC;
420+
i
421+
-----------
422+
{1}
423+
{1,2}
424+
{1,2,3}
425+
{1,2,3,4}
426+
(4 rows)
427+
428+
DROP INDEX idx_array;
429+
ALTER TABLE test_array ADD COLUMN add_info timestamp;
430+
CREATE INDEX idx_array ON test_array
431+
USING rum (i rum_anyarray_addon_ops, add_info)
432+
WITH (attach = 'add_info', to = 'i');
433+
WITH q as (
434+
SELECT row_number() OVER (ORDER BY i) idx, ctid FROM test_array
435+
)
436+
UPDATE test_array SET add_info = '2016-05-16 14:21:25'::timestamp +
437+
format('%s days', q.idx)::interval
438+
FROM q WHERE test_array.ctid = q.ctid;
439+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
440+
QUERY PLAN
441+
------------------------------------------
442+
Index Scan using idx_array on test_array
443+
Index Cond: (i = '{}'::smallint[])
444+
(2 rows)
445+
446+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}';
447+
QUERY PLAN
448+
------------------------------------------
449+
Index Scan using idx_array on test_array
450+
Index Cond: (i && '{}'::smallint[])
451+
(2 rows)
452+
453+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
454+
QUERY PLAN
455+
------------------------------------------
456+
Index Scan using idx_array on test_array
457+
Index Cond: (i @> '{}'::smallint[])
458+
(2 rows)
459+
460+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}';
461+
QUERY PLAN
462+
------------------------------------------
463+
Index Scan using idx_array on test_array
464+
Index Cond: (i <@ '{}'::smallint[])
465+
(2 rows)
466+
467+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
468+
QUERY PLAN
469+
----------------------------------
470+
Seq Scan on test_array
471+
Filter: (i % '{}'::smallint[])
472+
(2 rows)
473+
474+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{1}' ORDER BY add_info <=> '2016-05-16 14:21:25' LIMIT 10;
475+
QUERY PLAN
476+
------------------------------------------------------------------------------------------
477+
Limit
478+
-> Index Scan using idx_array on test_array
479+
Index Cond: (i && '{1}'::smallint[])
480+
Order By: (add_info <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone)
481+
(4 rows)
482+
483+
SELECT * FROM test_array WHERE i && '{1}' ORDER BY add_info <=> '2016-05-16 14:21:25' LIMIT 10;
484+
i | add_info
485+
-----------+--------------------------
486+
{1} | Thu May 19 14:21:25 2016
487+
{1,2} | Fri May 20 14:21:25 2016
488+
{1,2,3} | Sat May 21 14:21:25 2016
489+
{1,2,3,4} | Sun May 22 14:21:25 2016
490+
(4 rows)
491+
492+
DROP INDEX idx_array;
410493
/*
411494
* Sanity checks for popular array types.
412495
*/
413496
ALTER TABLE test_array ALTER COLUMN i TYPE int4[];
497+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
414498
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
415499
QUERY PLAN
416500
------------------------------------------
@@ -446,7 +530,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
446530
Index Cond: (i % '{}'::integer[])
447531
(2 rows)
448532

533+
DROP INDEX idx_array;
449534
ALTER TABLE test_array ALTER COLUMN i TYPE int8[];
535+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
450536
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
451537
QUERY PLAN
452538
------------------------------------------
@@ -482,7 +568,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
482568
Index Cond: (i % '{}'::bigint[])
483569
(2 rows)
484570

571+
DROP INDEX idx_array;
485572
ALTER TABLE test_array ALTER COLUMN i TYPE text[];
573+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
486574
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
487575
QUERY PLAN
488576
------------------------------------------
@@ -518,7 +606,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
518606
Index Cond: (i % '{}'::text[])
519607
(2 rows)
520608

609+
DROP INDEX idx_array;
521610
ALTER TABLE test_array ALTER COLUMN i TYPE varchar[];
611+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
522612
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
523613
QUERY PLAN
524614
-----------------------------------------------
@@ -554,7 +644,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
554644
Index Cond: (i % '{}'::character varying[])
555645
(2 rows)
556646

647+
DROP INDEX idx_array;
557648
ALTER TABLE test_array ALTER COLUMN i TYPE char[];
649+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
558650
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
559651
QUERY PLAN
560652
------------------------------------------
@@ -590,7 +682,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
590682
Index Cond: (i % '{}'::bpchar[])
591683
(2 rows)
592684

685+
DROP INDEX idx_array;
593686
ALTER TABLE test_array ALTER COLUMN i TYPE numeric[] USING i::numeric[];
687+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
594688
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
595689
QUERY PLAN
596690
------------------------------------------
@@ -626,7 +720,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
626720
Index Cond: (i % '{}'::numeric[])
627721
(2 rows)
628722

723+
DROP INDEX idx_array;
629724
ALTER TABLE test_array ALTER COLUMN i TYPE float4[] USING i::float4[];
725+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
630726
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
631727
QUERY PLAN
632728
------------------------------------------
@@ -662,7 +758,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
662758
Index Cond: (i % '{}'::real[])
663759
(2 rows)
664760

761+
DROP INDEX idx_array;
665762
ALTER TABLE test_array ALTER COLUMN i TYPE float8[] USING i::float8[];
763+
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
666764
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
667765
QUERY PLAN
668766
----------------------------------------------
@@ -698,3 +796,4 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
698796
Index Cond: (i % '{}'::double precision[])
699797
(2 rows)
700798

799+
DROP INDEX idx_array;

‎sql/array.sql

Lines changed: 48 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
set enable_seqscan=off;
2+
set enable_sort=off;
23

34

45
/*
@@ -8,10 +9,9 @@ set enable_seqscan=off;
89
CREATETABLEtest_array (
910
i int2[]
1011
);
11-
1212
INSERT INTO test_arrayVALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');
1313

14-
CREATEINDEXidx_arrayON test_array USING rum (i);
14+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
1515

1616
SELECT*FROM test_arrayWHERE i='{NULL}';
1717
SELECT*FROM test_arrayWHERE i='{1,2,3,NULL}';
@@ -78,64 +78,110 @@ SELECT * FROM test_array WHERE i % '{1,1,1,1,1}';
7878
SELECT*FROM test_arrayWHERE i %'{0,0}';
7979
SELECT*FROM test_arrayWHERE i %'{100}';
8080

81+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{1}'ORDER BY i<=>'{1}'ASC;
82+
SELECT*FROM test_arrayWHERE i &&'{1}'ORDER BY i<=>'{1}'ASC;
83+
84+
DROPINDEX idx_array;
85+
86+
87+
ALTERTABLE test_array ADD COLUMN add_infotimestamp;
88+
89+
CREATEINDEXidx_arrayON test_array
90+
USING rum (i rum_anyarray_addon_ops, add_info)
91+
WITH (attach='add_info', to='i');
92+
93+
WITH qas (
94+
SELECT row_number() OVER (ORDER BY i) idx, ctidFROM test_array
95+
)
96+
UPDATE test_arraySET add_info='2016-05-16 14:21:25'::timestamp+
97+
format('%s days',q.idx)::interval
98+
FROM qWHEREtest_array.ctid=q.ctid;
99+
100+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
101+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
102+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
103+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
104+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
105+
106+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{1}'ORDER BY add_info<=>'2016-05-16 14:21:25'LIMIT10;
107+
SELECT*FROM test_arrayWHERE i &&'{1}'ORDER BY add_info<=>'2016-05-16 14:21:25'LIMIT10;
108+
109+
DROPINDEX idx_array;
110+
81111

82112
/*
83113
* Sanity checks for popular array types.
84114
*/
85115

86116
ALTERTABLE test_array ALTER COLUMN i TYPE int4[];
117+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
87118
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
88119
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
89120
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
90121
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
91122
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
123+
DROPINDEX idx_array;
92124

93125
ALTERTABLE test_array ALTER COLUMN i TYPE int8[];
126+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
94127
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
95128
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
96129
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
97130
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
98131
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
132+
DROPINDEX idx_array;
99133

100134
ALTERTABLE test_array ALTER COLUMN i TYPEtext[];
135+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
101136
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
102137
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
103138
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
104139
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
105140
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
141+
DROPINDEX idx_array;
106142

107143
ALTERTABLE test_array ALTER COLUMN i TYPEvarchar[];
144+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
108145
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
109146
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
110147
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
111148
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
112149
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
150+
DROPINDEX idx_array;
113151

114152
ALTERTABLE test_array ALTER COLUMN i TYPEchar[];
153+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
115154
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
116155
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
117156
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
118157
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
119158
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
159+
DROPINDEX idx_array;
120160

121161
ALTERTABLE test_array ALTER COLUMN i TYPEnumeric[] USING i::numeric[];
162+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
122163
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
123164
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
124165
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
125166
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
126167
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
168+
DROPINDEX idx_array;
127169

128170
ALTERTABLE test_array ALTER COLUMN i TYPE float4[] USING i::float4[];
171+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
129172
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
130173
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
131174
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
132175
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
133176
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
177+
DROPINDEX idx_array;
134178

135179
ALTERTABLE test_array ALTER COLUMN i TYPE float8[] USING i::float8[];
180+
CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);
136181
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
137182
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
138183
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
139184
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
140185
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
186+
DROPINDEX idx_array;
141187

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp