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

Commit5bbd4f8

Browse files
author
Arseny Kositsyn
committed
[PGPRO-11599] Added a test of scanning with keys that are ordered differently.
Tags: rum
1 parent70236b5 commit5bbd4f8

File tree

4 files changed

+163
-0
lines changed

4 files changed

+163
-0
lines changed

‎expected/altorder.out‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -573,3 +573,48 @@ SELECT id, d FROM atsts WHERE t @@ 'wr&q:*' AND d >= '2016-05-16 14:21:25' ORDE
573573
506 | Sun May 22 21:21:22.326724 2016
574574
(112 rows)
575575

576+
CREATE TABLE test_table (id bigint, folder bigint, time bigint, tsv tsvector);
577+
CREATE INDEX test_idx ON test_table USING rum(folder, tsv rum_tsvector_addon_ops, time) with (attach = 'time', to = 'tsv', order_by_attach=TRUE);
578+
INSERT INTO test_table (id, folder, time, tsv) VALUES (1, 10, 100, to_tsvector('wordA'));
579+
INSERT INTO test_table (id, folder, time, tsv) VALUES (2, 20, 200, to_tsvector('wordB'));
580+
INSERT INTO test_table (id, folder, time, tsv) VALUES (3, 10, 300, to_tsvector('wordA'));
581+
INSERT INTO test_table (id, folder, time, tsv) VALUES (4, 20, 400, to_tsvector('wordB'));
582+
INSERT INTO test_table (id, folder, time, tsv) VALUES (5, 20, 60, to_tsvector('wordB'));
583+
INSERT INTO test_table (id, folder, time, tsv) VALUES (6, 10, 40, to_tsvector('wordA'));
584+
INSERT INTO test_table (id, folder, time, tsv) VALUES (7, 20, 50, to_tsvector('wordB'));
585+
INSERT INTO test_table (id, folder, time, tsv) VALUES (8, 10, 30, to_tsvector('wordA'));
586+
EXPLAIN (costs off)
587+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint);
588+
QUERY PLAN
589+
--------------------------------------------------------------------------------
590+
Index Scan using test_idx on test_table
591+
Index Cond: ((folder = '10'::bigint) AND (tsv @@ to_tsquery('wordA'::text)))
592+
(2 rows)
593+
594+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint);
595+
id | folder | time | tsv
596+
----+--------+------+-----------
597+
8 | 10 | 30 | 'worda':1
598+
6 | 10 | 40 | 'worda':1
599+
1 | 10 | 100 | 'worda':1
600+
3 | 10 | 300 | 'worda':1
601+
(4 rows)
602+
603+
EXPLAIN (costs off)
604+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint) ORDER BY time <=| 500::bigint;
605+
QUERY PLAN
606+
--------------------------------------------------------------------------------
607+
Index Scan using test_idx on test_table
608+
Index Cond: ((folder = '10'::bigint) AND (tsv @@ to_tsquery('wordA'::text)))
609+
Order By: ("time" <=| '500'::bigint)
610+
(3 rows)
611+
612+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint) ORDER BY time <=| 500::bigint;
613+
id | folder | time | tsv
614+
----+--------+------+-----------
615+
3 | 10 | 300 | 'worda':1
616+
1 | 10 | 100 | 'worda':1
617+
6 | 10 | 40 | 'worda':1
618+
8 | 10 | 30 | 'worda':1
619+
(4 rows)
620+

‎expected/altorder_1.out‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -572,3 +572,50 @@ SELECT id, d FROM atsts WHERE t @@ 'wr&q:*' AND d >= '2016-05-16 14:21:25' ORDE
572572
506 | Sun May 22 21:21:22.326724 2016
573573
(112 rows)
574574

575+
CREATE TABLE test_table (id bigint, folder bigint, time bigint, tsv tsvector);
576+
CREATE INDEX test_idx ON test_table USING rum(folder, tsv rum_tsvector_addon_ops, time) with (attach = 'time', to = 'tsv', order_by_attach=TRUE);
577+
ERROR: doesn't support order index over pass-by-reference column
578+
INSERT INTO test_table (id, folder, time, tsv) VALUES (1, 10, 100, to_tsvector('wordA'));
579+
INSERT INTO test_table (id, folder, time, tsv) VALUES (2, 20, 200, to_tsvector('wordB'));
580+
INSERT INTO test_table (id, folder, time, tsv) VALUES (3, 10, 300, to_tsvector('wordA'));
581+
INSERT INTO test_table (id, folder, time, tsv) VALUES (4, 20, 400, to_tsvector('wordB'));
582+
INSERT INTO test_table (id, folder, time, tsv) VALUES (5, 20, 60, to_tsvector('wordB'));
583+
INSERT INTO test_table (id, folder, time, tsv) VALUES (6, 10, 40, to_tsvector('wordA'));
584+
INSERT INTO test_table (id, folder, time, tsv) VALUES (7, 20, 50, to_tsvector('wordB'));
585+
INSERT INTO test_table (id, folder, time, tsv) VALUES (8, 10, 30, to_tsvector('wordA'));
586+
EXPLAIN (costs off)
587+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint);
588+
QUERY PLAN
589+
----------------------------------------------------------------------------
590+
Seq Scan on test_table
591+
Filter: ((folder = '10'::bigint) AND (tsv @@ to_tsquery('wordA'::text)))
592+
(2 rows)
593+
594+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint);
595+
id | folder | time | tsv
596+
----+--------+------+-----------
597+
1 | 10 | 100 | 'worda':1
598+
3 | 10 | 300 | 'worda':1
599+
6 | 10 | 40 | 'worda':1
600+
8 | 10 | 30 | 'worda':1
601+
(4 rows)
602+
603+
EXPLAIN (costs off)
604+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint) ORDER BY time <=| 500::bigint;
605+
QUERY PLAN
606+
----------------------------------------------------------------------------------
607+
Sort
608+
Sort Key: (("time" <=| '500'::bigint))
609+
-> Seq Scan on test_table
610+
Filter: ((folder = '10'::bigint) AND (tsv @@ to_tsquery('wordA'::text)))
611+
(4 rows)
612+
613+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint) ORDER BY time <=| 500::bigint;
614+
id | folder | time | tsv
615+
----+--------+------+-----------
616+
3 | 10 | 300 | 'worda':1
617+
1 | 10 | 100 | 'worda':1
618+
6 | 10 | 40 | 'worda':1
619+
8 | 10 | 30 | 'worda':1
620+
(4 rows)
621+

‎expected/altorder_2.out‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -582,3 +582,53 @@ SELECT id, d FROM atsts WHERE t @@ 'wr&q:*' AND d >= '2016-05-16 14:21:25' ORDE
582582
506 | Sun May 22 21:21:22.326724 2016
583583
(112 rows)
584584

585+
CREATE TABLE test_table (id bigint, folder bigint, time bigint, tsv tsvector);
586+
CREATE INDEX test_idx ON test_table USING rum(folder, tsv rum_tsvector_addon_ops, time) with (attach = 'time', to = 'tsv', order_by_attach=TRUE);
587+
ERROR: doesn't support order index over pass-by-reference column
588+
INSERT INTO test_table (id, folder, time, tsv) VALUES (1, 10, 100, to_tsvector('wordA'));
589+
INSERT INTO test_table (id, folder, time, tsv) VALUES (2, 20, 200, to_tsvector('wordB'));
590+
INSERT INTO test_table (id, folder, time, tsv) VALUES (3, 10, 300, to_tsvector('wordA'));
591+
INSERT INTO test_table (id, folder, time, tsv) VALUES (4, 20, 400, to_tsvector('wordB'));
592+
INSERT INTO test_table (id, folder, time, tsv) VALUES (5, 20, 60, to_tsvector('wordB'));
593+
INSERT INTO test_table (id, folder, time, tsv) VALUES (6, 10, 40, to_tsvector('wordA'));
594+
INSERT INTO test_table (id, folder, time, tsv) VALUES (7, 20, 50, to_tsvector('wordB'));
595+
INSERT INTO test_table (id, folder, time, tsv) VALUES (8, 10, 30, to_tsvector('wordA'));
596+
EXPLAIN (costs off)
597+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint);
598+
QUERY PLAN
599+
----------------------------------------------------------------------------
600+
Seq Scan on test_table
601+
Disabled Nodes: 1
602+
Filter: ((folder = '10'::bigint) AND (tsv @@ to_tsquery('wordA'::text)))
603+
(3 rows)
604+
605+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint);
606+
id | folder | time | tsv
607+
----+--------+------+-----------
608+
1 | 10 | 100 | 'worda':1
609+
3 | 10 | 300 | 'worda':1
610+
6 | 10 | 40 | 'worda':1
611+
8 | 10 | 30 | 'worda':1
612+
(4 rows)
613+
614+
EXPLAIN (costs off)
615+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint) ORDER BY time <=| 500::bigint;
616+
QUERY PLAN
617+
----------------------------------------------------------------------------------
618+
Sort
619+
Disabled Nodes: 1
620+
Sort Key: (("time" <=| '500'::bigint))
621+
-> Seq Scan on test_table
622+
Disabled Nodes: 1
623+
Filter: ((folder = '10'::bigint) AND (tsv @@ to_tsquery('wordA'::text)))
624+
(6 rows)
625+
626+
SELECT * FROM test_table WHERE tsv @@ (to_tsquery('wordA')) AND (folder = 10::bigint) ORDER BY time <=| 500::bigint;
627+
id | folder | time | tsv
628+
----+--------+------+-----------
629+
3 | 10 | 300 | 'worda':1
630+
1 | 10 | 100 | 'worda':1
631+
6 | 10 | 40 | 'worda':1
632+
8 | 10 | 30 | 'worda':1
633+
(4 rows)
634+

‎sql/altorder.sql‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,3 +98,24 @@ SELECT id, d FROM atsts WHERE t @@ 'wr&qh' AND d >= '2016-05-16 14:21:25' ORDER
9898
EXPLAIN (costs off)
9999
SELECT id, dFROM atstsWHERE t @@'wr&q:*'AND d>='2016-05-16 14:21:25'ORDER BY d;
100100
SELECT id, dFROM atstsWHERE t @@'wr&q:*'AND d>='2016-05-16 14:21:25'ORDER BY d;
101+
102+
CREATETABLEtest_table (idbigint, folderbigint,timebigint, tsv tsvector);
103+
CREATEINDEXtest_idxON test_table USING rum(folder, tsv rum_tsvector_addon_ops,time) with (attach='time', to='tsv', order_by_attach=TRUE);
104+
105+
INSERT INTO test_table (id, folder,time, tsv)VALUES (1,10,100, to_tsvector('wordA'));
106+
INSERT INTO test_table (id, folder,time, tsv)VALUES (2,20,200, to_tsvector('wordB'));
107+
INSERT INTO test_table (id, folder,time, tsv)VALUES (3,10,300, to_tsvector('wordA'));
108+
INSERT INTO test_table (id, folder,time, tsv)VALUES (4,20,400, to_tsvector('wordB'));
109+
INSERT INTO test_table (id, folder,time, tsv)VALUES (5,20,60, to_tsvector('wordB'));
110+
INSERT INTO test_table (id, folder,time, tsv)VALUES (6,10,40, to_tsvector('wordA'));
111+
INSERT INTO test_table (id, folder,time, tsv)VALUES (7,20,50, to_tsvector('wordB'));
112+
INSERT INTO test_table (id, folder,time, tsv)VALUES (8,10,30, to_tsvector('wordA'));
113+
114+
EXPLAIN (costs off)
115+
SELECT*FROM test_tableWHERE tsv @@ (to_tsquery('wordA'))AND (folder=10::bigint);
116+
SELECT*FROM test_tableWHERE tsv @@ (to_tsquery('wordA'))AND (folder=10::bigint);
117+
118+
EXPLAIN (costs off)
119+
SELECT*FROM test_tableWHERE tsv @@ (to_tsquery('wordA'))AND (folder=10::bigint)ORDER BYtime<=|500::bigint;
120+
SELECT*FROM test_tableWHERE tsv @@ (to_tsquery('wordA'))AND (folder=10::bigint)ORDER BYtime<=|500::bigint;
121+

0 commit comments

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