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

Commitaed521c

Browse files
committed
improve index tests
1 parent5a77573 commitaed521c

File tree

3 files changed

+76
-12
lines changed

3 files changed

+76
-12
lines changed

‎data/test_jsquery.data‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -998,3 +998,4 @@
998998
{"product_id": "B00004W190", "customer_id": "ABH4G7TVI6G2T", "review_date": {"$date": 1013644800000}, "review_votes": 3, "product_group": "DVD", "product_title": "Bad Girls Go To Hell/Another Day Another Man", "review_rating": 5, "product_category": "Classics", "product_sales_rank": 40553, "product_subcategory": "General", "similar_product_ids": ["B000056NW6", "B0002IQB8U", "B00022PYRU", "B0002NRRQ0", "B0002W4TL2"], "review_helpful_votes": 2}
999999
{"product_id": "B00005ALM0", "customer_id": "ABH4G7TVI6G2T", "review_date": {"$date": 1013644800000}, "review_votes": 8, "product_group": "DVD", "product_title": "The Violent Years/Girl Gang", "review_rating": 5, "product_category": "Action & Adventure", "product_sales_rank": 15399, "product_subcategory": "General", "similar_product_ids": ["B00005ALLZ", "B0002235LM", "B00005LP6F", "B0002NRRT2", "B0000CG8H0"], "review_helpful_votes": 6}
10001000
{"product_id": "B00005B6KQ", "customer_id": "ABH4G7TVI6G2T", "review_date": {"$date": 1013644800000}, "review_votes": 8, "product_group": "DVD", "product_title": "The Notorious Daughter of Fanny Hill / The Head Mistress", "review_rating": 5, "product_category": "Science Fiction & Fantasy", "product_sales_rank": 20963, "product_subcategory": "General", "similar_product_ids": ["B0002W4TL2", "B00005MM5O", "B0002NRRQ0", "B0002EJ7JE", "B00005R1NR"], "review_helpful_votes": 7}
1001+
{"product_id": "B00005B6KQ", "customer_id": null, "review_date": {"$date": 1013644800000}, "review_votes": true, "product_group": false, "product_title": "The Notorious Daughter of Fanny Hill / The Head Mistress", "review_rating": 5, "product_category": "Science Fiction & Fantasy", "product_sales_rank": 20963, "product_subcategory": "General", "similar_product_ids": ["B0002W4TL2", "B00005MM5O", "B0002NRRQ0", "B0002EJ7JE", "B00005R1NR"], "review_helpful_votes": 7}

‎expected/jsquery.out‎

Lines changed: 66 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -865,7 +865,7 @@ select 'a\r = "\n\""'::jsquery;
865865
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 0;
866866
count
867867
-------
868-
653
868+
654
869869
(1 row)
870870

871871
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 19;
@@ -877,7 +877,7 @@ select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 19;
877877
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 < 19;
878878
count
879879
-------
880-
984
880+
985
881881
(1 row)
882882

883883
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 >= 19;
@@ -889,7 +889,7 @@ select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 >= 19
889889
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 <= 19;
890890
count
891891
-------
892-
987
892+
988
893893
(1 row)
894894

895895
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 = 19;
@@ -908,7 +908,7 @@ select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 16
908908
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
909909
count
910910
-------
911-
653
911+
654
912912
(1 row)
913913

914914
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
@@ -920,7 +920,7 @@ select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
920920
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
921921
count
922922
-------
923-
984
923+
985
924924
(1 row)
925925

926926
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
@@ -932,7 +932,7 @@ select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
932932
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
933933
count
934934
-------
935-
987
935+
988
936936
(1 row)
937937

938938
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
@@ -984,6 +984,24 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
984984
4
985985
(1 row)
986986

987+
select count(*) from test_jsquery where v @@ 'customer_id = null';
988+
count
989+
-------
990+
1
991+
(1 row)
992+
993+
select count(*) from test_jsquery where v @@ 'review_votes = true';
994+
count
995+
-------
996+
1
997+
(1 row)
998+
999+
select count(*) from test_jsquery where v @@ 'product_group = false';
1000+
count
1001+
-------
1002+
1
1003+
(1 row)
1004+
9871005
create index t_idx on test_jsquery using gin (v jsonb_bloom_value_ops);
9881006
set enable_seqscan = off;
9891007
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
@@ -999,7 +1017,7 @@ explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful
9991017
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
10001018
count
10011019
-------
1002-
653
1020+
654
10031021
(1 row)
10041022

10051023
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
@@ -1011,7 +1029,7 @@ select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
10111029
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
10121030
count
10131031
-------
1014-
984
1032+
985
10151033
(1 row)
10161034

10171035
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
@@ -1023,7 +1041,7 @@ select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
10231041
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
10241042
count
10251043
-------
1026-
987
1044+
988
10271045
(1 row)
10281046

10291047
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
@@ -1075,6 +1093,24 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
10751093
4
10761094
(1 row)
10771095

1096+
select count(*) from test_jsquery where v @@ 'customer_id = null';
1097+
count
1098+
-------
1099+
1
1100+
(1 row)
1101+
1102+
select count(*) from test_jsquery where v @@ 'review_votes = true';
1103+
count
1104+
-------
1105+
1
1106+
(1 row)
1107+
1108+
select count(*) from test_jsquery where v @@ 'product_group = false';
1109+
count
1110+
-------
1111+
1
1112+
(1 row)
1113+
10781114
drop index t_idx;
10791115
create index t_idx on test_jsquery using gin (v jsonb_hash_value_ops);
10801116
set enable_seqscan = off;
@@ -1091,7 +1127,7 @@ explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful
10911127
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
10921128
count
10931129
-------
1094-
653
1130+
654
10951131
(1 row)
10961132

10971133
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
@@ -1103,7 +1139,7 @@ select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
11031139
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
11041140
count
11051141
-------
1106-
984
1142+
985
11071143
(1 row)
11081144

11091145
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
@@ -1115,7 +1151,7 @@ select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
11151151
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
11161152
count
11171153
-------
1118-
987
1154+
988
11191155
(1 row)
11201156

11211157
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
@@ -1167,4 +1203,22 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
11671203
4
11681204
(1 row)
11691205

1206+
select count(*) from test_jsquery where v @@ 'customer_id = null';
1207+
count
1208+
-------
1209+
1
1210+
(1 row)
1211+
1212+
select count(*) from test_jsquery where v @@ 'review_votes = true';
1213+
count
1214+
-------
1215+
1
1216+
(1 row)
1217+
1218+
select count(*) from test_jsquery where v @@ 'product_group = false';
1219+
count
1220+
-------
1221+
1
1222+
(1 row)
1223+
11701224
RESET enable_seqscan;

‎sql/jsquery.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -202,6 +202,9 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
202202
selectcount(*)from test_jsquerywhere v @@'similar_product_ids(# = "0440180295")';
203203
selectcount(*)from test_jsquerywhere v @@'similar_product_ids.#($ = "0440180295")';
204204
selectcount(*)from test_jsquerywhere v @@'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
205+
selectcount(*)from test_jsquerywhere v @@'customer_id = null';
206+
selectcount(*)from test_jsquerywhere v @@'review_votes = true';
207+
selectcount(*)from test_jsquerywhere v @@'product_group = false';
205208

206209
createindext_idxon test_jsquery using gin (v jsonb_bloom_value_ops);
207210
set enable_seqscan= off;
@@ -222,6 +225,9 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
222225
selectcount(*)from test_jsquerywhere v @@'similar_product_ids(# = "0440180295")';
223226
selectcount(*)from test_jsquerywhere v @@'similar_product_ids.#($ = "0440180295")';
224227
selectcount(*)from test_jsquerywhere v @@'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
228+
selectcount(*)from test_jsquerywhere v @@'customer_id = null';
229+
selectcount(*)from test_jsquerywhere v @@'review_votes = true';
230+
selectcount(*)from test_jsquerywhere v @@'product_group = false';
225231

226232
dropindex t_idx;
227233

@@ -244,5 +250,8 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
244250
selectcount(*)from test_jsquerywhere v @@'similar_product_ids(# = "0440180295")';
245251
selectcount(*)from test_jsquerywhere v @@'similar_product_ids.#($ = "0440180295")';
246252
selectcount(*)from test_jsquerywhere v @@'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
253+
selectcount(*)from test_jsquerywhere v @@'customer_id = null';
254+
selectcount(*)from test_jsquerywhere v @@'review_votes = true';
255+
selectcount(*)from test_jsquerywhere v @@'product_group = false';
247256

248257
RESET enable_seqscan;

0 commit comments

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