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
Appearance settings

Commit 90a853d

Browse filesBrowse files
feodorfeodor
committed
add test for index
1 parent d3b4672 commit 90a853d
Copy full SHA for 90a853d

File tree

2 files changed

+228
-0
lines changed
Filter options

2 files changed

+228
-0
lines changed

‎expected/jsquery.out

Copy file name to clipboardExpand all lines: expected/jsquery.out
+184Lines changed: 184 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -953,3 +953,187 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
953953
4
954954
(1 row)
955955

956+
create index t_idx on test_jsquery using gin (v jsonb_bloom_value_ops);
957+
set enable_seqscan = off;
958+
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
959+
QUERY PLAN
960+
------------------------------------------------------------------------
961+
Aggregate
962+
-> Bitmap Heap Scan on test_jsquery
963+
Recheck Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
964+
-> Bitmap Index Scan on t_idx
965+
Index Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
966+
(5 rows)
967+
968+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
969+
count
970+
-------
971+
653
972+
(1 row)
973+
974+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
975+
count
976+
-------
977+
13
978+
(1 row)
979+
980+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
981+
count
982+
-------
983+
984
984+
(1 row)
985+
986+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
987+
count
988+
-------
989+
16
990+
(1 row)
991+
992+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
993+
count
994+
-------
995+
987
996+
(1 row)
997+
998+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
999+
count
1000+
-------
1001+
3
1002+
(1 row)
1003+
1004+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
1005+
v @@ 'review_helpful_votes < 20';
1006+
count
1007+
-------
1008+
8
1009+
(1 row)
1010+
1011+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
1012+
count
1013+
-------
1014+
8
1015+
(1 row)
1016+
1017+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
1018+
count
1019+
-------
1020+
8
1021+
(1 row)
1022+
1023+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
1024+
count
1025+
-------
1026+
7
1027+
(1 row)
1028+
1029+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
1030+
count
1031+
-------
1032+
7
1033+
(1 row)
1034+
1035+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
1036+
count
1037+
-------
1038+
7
1039+
(1 row)
1040+
1041+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
1042+
count
1043+
-------
1044+
4
1045+
(1 row)
1046+
1047+
drop index t_idx;
1048+
create index t_idx on test_jsquery using gin (v jsonb_hash_value_ops);
1049+
set enable_seqscan = off;
1050+
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
1051+
QUERY PLAN
1052+
------------------------------------------------------------------------
1053+
Aggregate
1054+
-> Bitmap Heap Scan on test_jsquery
1055+
Recheck Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
1056+
-> Bitmap Index Scan on t_idx
1057+
Index Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
1058+
(5 rows)
1059+
1060+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
1061+
count
1062+
-------
1063+
653
1064+
(1 row)
1065+
1066+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
1067+
count
1068+
-------
1069+
13
1070+
(1 row)
1071+
1072+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
1073+
count
1074+
-------
1075+
984
1076+
(1 row)
1077+
1078+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
1079+
count
1080+
-------
1081+
16
1082+
(1 row)
1083+
1084+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
1085+
count
1086+
-------
1087+
987
1088+
(1 row)
1089+
1090+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
1091+
count
1092+
-------
1093+
3
1094+
(1 row)
1095+
1096+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
1097+
v @@ 'review_helpful_votes < 20';
1098+
count
1099+
-------
1100+
8
1101+
(1 row)
1102+
1103+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
1104+
count
1105+
-------
1106+
8
1107+
(1 row)
1108+
1109+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
1110+
count
1111+
-------
1112+
8
1113+
(1 row)
1114+
1115+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
1116+
count
1117+
-------
1118+
7
1119+
(1 row)
1120+
1121+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
1122+
count
1123+
-------
1124+
7
1125+
(1 row)
1126+
1127+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
1128+
count
1129+
-------
1130+
7
1131+
(1 row)
1132+
1133+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
1134+
count
1135+
-------
1136+
4
1137+
(1 row)
1138+
1139+
RESET enable_seqscan;

‎sql/jsquery.sql

Copy file name to clipboardExpand all lines: sql/jsquery.sql
+44Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -195,3 +195,47 @@ select count(*) from test_jsquery where v @@ 'similar_product_ids && ["044018029
195195
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
196196
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
197197
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
198+
199+
create index t_idx on test_jsquery using gin (v jsonb_bloom_value_ops);
200+
set enable_seqscan = off;
201+
202+
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
203+
204+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
205+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
206+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
207+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
208+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
209+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
210+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
211+
v @@ 'review_helpful_votes < 20';
212+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
213+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
214+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
215+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
216+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
217+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
218+
219+
drop index t_idx;
220+
221+
create index t_idx on test_jsquery using gin (v jsonb_hash_value_ops);
222+
set enable_seqscan = off;
223+
224+
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
225+
226+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
227+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
228+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
229+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
230+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
231+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
232+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
233+
v @@ 'review_helpful_votes < 20';
234+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
235+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
236+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
237+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
238+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
239+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
240+
241+
RESET enable_seqscan;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.