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

Commite44bec7

Browse files
committed
add index support for <@
1 parente6ab2bc commite44bec7

File tree

4 files changed

+177
-5
lines changed

4 files changed

+177
-5
lines changed

‎data/test_jsquery.data‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -999,3 +999,9 @@
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}
10011001
{"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}
1002+
{"array": [1,2,3]}
1003+
{"array": [2,3,4]}
1004+
{"array": [3,4,5]}
1005+
{"array": [2,3]}
1006+
{"array": [2]}
1007+
{"array": [4,5]}

‎expected/jsquery.out‎

Lines changed: 149 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1184,7 +1184,10 @@ SELECT gin_debug_query_path_value('x @> [1,2,3]');
11841184
SELECT gin_debug_query_path_value('x <@ [1,2,3]');
11851185
gin_debug_query_path_value
11861186
----------------------------
1187-
NULL +
1187+
OR +
1188+
x.# = 1 , entry 0 +
1189+
x.# = 2 , entry 1 +
1190+
x.# = 3 , entry 2 +
11881191

11891192
(1 row)
11901193

@@ -1293,7 +1296,10 @@ SELECT gin_debug_query_value_path('x @> [1,2,3]');
12931296
SELECT gin_debug_query_value_path('x <@ [1,2,3]');
12941297
gin_debug_query_value_path
12951298
----------------------------
1296-
NULL +
1299+
OR +
1300+
x.# = 1 , entry 0 +
1301+
x.# = 2 , entry 1 +
1302+
x.# = 3 , entry 2 +
12971303

12981304
(1 row)
12991305

@@ -1438,6 +1444,31 @@ select count(*) from test_jsquery where v @@ 'product_group = false';
14381444
1
14391445
(1 row)
14401446

1447+
select v from test_jsquery where v @@ 'array <@ [2,3]' order by v;
1448+
v
1449+
-------------------
1450+
{"array": [2]}
1451+
{"array": [2, 3]}
1452+
(2 rows)
1453+
1454+
select v from test_jsquery where v @@ 'array && [2,3]' order by v;
1455+
v
1456+
----------------------
1457+
{"array": [2]}
1458+
{"array": [2, 3]}
1459+
{"array": [1, 2, 3]}
1460+
{"array": [2, 3, 4]}
1461+
{"array": [3, 4, 5]}
1462+
(5 rows)
1463+
1464+
select v from test_jsquery where v @@ 'array @> [2,3]' order by v;
1465+
v
1466+
----------------------
1467+
{"array": [2, 3]}
1468+
{"array": [1, 2, 3]}
1469+
{"array": [2, 3, 4]}
1470+
(3 rows)
1471+
14411472
create index t_idx on test_jsquery using gin (v jsonb_value_path_ops);
14421473
set enable_seqscan = off;
14431474
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
@@ -1547,6 +1578,64 @@ select count(*) from test_jsquery where v @@ 'product_group = false';
15471578
1
15481579
(1 row)
15491580

1581+
explain (costs off) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v;
1582+
QUERY PLAN
1583+
---------------------------------------------------------------
1584+
Sort
1585+
Sort Key: v
1586+
-> Bitmap Heap Scan on test_jsquery
1587+
Recheck Cond: (v @@ '"array" <@ [2, 3]'::jsquery)
1588+
-> Bitmap Index Scan on t_idx
1589+
Index Cond: (v @@ '"array" <@ [2, 3]'::jsquery)
1590+
(6 rows)
1591+
1592+
explain (costs off) select v from test_jsquery where v @@ 'array && [2,3]' order by v;
1593+
QUERY PLAN
1594+
---------------------------------------------------------------
1595+
Sort
1596+
Sort Key: v
1597+
-> Bitmap Heap Scan on test_jsquery
1598+
Recheck Cond: (v @@ '"array" && [2, 3]'::jsquery)
1599+
-> Bitmap Index Scan on t_idx
1600+
Index Cond: (v @@ '"array" && [2, 3]'::jsquery)
1601+
(6 rows)
1602+
1603+
explain (costs off) select v from test_jsquery where v @@ 'array @> [2,3]' order by v;
1604+
QUERY PLAN
1605+
---------------------------------------------------------------
1606+
Sort
1607+
Sort Key: v
1608+
-> Bitmap Heap Scan on test_jsquery
1609+
Recheck Cond: (v @@ '"array" @> [2, 3]'::jsquery)
1610+
-> Bitmap Index Scan on t_idx
1611+
Index Cond: (v @@ '"array" @> [2, 3]'::jsquery)
1612+
(6 rows)
1613+
1614+
select v from test_jsquery where v @@ 'array <@ [2,3]' order by v;
1615+
v
1616+
-------------------
1617+
{"array": [2]}
1618+
{"array": [2, 3]}
1619+
(2 rows)
1620+
1621+
select v from test_jsquery where v @@ 'array && [2,3]' order by v;
1622+
v
1623+
----------------------
1624+
{"array": [2]}
1625+
{"array": [2, 3]}
1626+
{"array": [1, 2, 3]}
1627+
{"array": [2, 3, 4]}
1628+
{"array": [3, 4, 5]}
1629+
(5 rows)
1630+
1631+
select v from test_jsquery where v @@ 'array @> [2,3]' order by v;
1632+
v
1633+
----------------------
1634+
{"array": [2, 3]}
1635+
{"array": [1, 2, 3]}
1636+
{"array": [2, 3, 4]}
1637+
(3 rows)
1638+
15501639
drop index t_idx;
15511640
create index t_idx on test_jsquery using gin (v jsonb_path_value_ops);
15521641
set enable_seqscan = off;
@@ -1657,4 +1746,62 @@ select count(*) from test_jsquery where v @@ 'product_group = false';
16571746
1
16581747
(1 row)
16591748

1749+
explain (costs off) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v;
1750+
QUERY PLAN
1751+
---------------------------------------------------------------
1752+
Sort
1753+
Sort Key: v
1754+
-> Bitmap Heap Scan on test_jsquery
1755+
Recheck Cond: (v @@ '"array" <@ [2, 3]'::jsquery)
1756+
-> Bitmap Index Scan on t_idx
1757+
Index Cond: (v @@ '"array" <@ [2, 3]'::jsquery)
1758+
(6 rows)
1759+
1760+
explain (costs off) select v from test_jsquery where v @@ 'array && [2,3]' order by v;
1761+
QUERY PLAN
1762+
---------------------------------------------------------------
1763+
Sort
1764+
Sort Key: v
1765+
-> Bitmap Heap Scan on test_jsquery
1766+
Recheck Cond: (v @@ '"array" && [2, 3]'::jsquery)
1767+
-> Bitmap Index Scan on t_idx
1768+
Index Cond: (v @@ '"array" && [2, 3]'::jsquery)
1769+
(6 rows)
1770+
1771+
explain (costs off) select v from test_jsquery where v @@ 'array @> [2,3]' order by v;
1772+
QUERY PLAN
1773+
---------------------------------------------------------------
1774+
Sort
1775+
Sort Key: v
1776+
-> Bitmap Heap Scan on test_jsquery
1777+
Recheck Cond: (v @@ '"array" @> [2, 3]'::jsquery)
1778+
-> Bitmap Index Scan on t_idx
1779+
Index Cond: (v @@ '"array" @> [2, 3]'::jsquery)
1780+
(6 rows)
1781+
1782+
select v from test_jsquery where v @@ 'array <@ [2,3]' order by v;
1783+
v
1784+
-------------------
1785+
{"array": [2]}
1786+
{"array": [2, 3]}
1787+
(2 rows)
1788+
1789+
select v from test_jsquery where v @@ 'array && [2,3]' order by v;
1790+
v
1791+
----------------------
1792+
{"array": [2]}
1793+
{"array": [2, 3]}
1794+
{"array": [1, 2, 3]}
1795+
{"array": [2, 3, 4]}
1796+
{"array": [3, 4, 5]}
1797+
(5 rows)
1798+
1799+
select v from test_jsquery where v @@ 'array @> [2,3]' order by v;
1800+
v
1801+
----------------------
1802+
{"array": [2, 3]}
1803+
{"array": [1, 2, 3]}
1804+
{"array": [2, 3, 4]}
1805+
(3 rows)
1806+
16601807
RESET enable_seqscan;

‎jsquery_extract.c‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -133,6 +133,7 @@ recursiveExtract(JsQueryItem *jsq, bool not, bool indirect, PathItem *path)
133133
casejqiIn:
134134
casejqiOverlap:
135135
casejqiContains:
136+
casejqiContained:
136137
if (not)returnNULL;
137138
result= (ExtractedNode*)palloc(sizeof(ExtractedNode));
138139
result->type= (jsq->type==jqiContains) ?eAnd :eOr;
@@ -142,7 +143,7 @@ recursiveExtract(JsQueryItem *jsq, bool not, bool indirect, PathItem *path)
142143
result->indirect=indirect;
143144
result->args.items= (ExtractedNode**)palloc(elem.array.nelems*sizeof(ExtractedNode*));
144145
result->args.count=0;
145-
if (jsq->type==jqiContains||jsq->type==jqiOverlap)
146+
if (jsq->type==jqiContains||jsq->type==jqiOverlap||jsq->type==jqiContained)
146147
{
147148
pathItem= (PathItem*)palloc(sizeof(PathItem));
148149
pathItem->type=iAnyArray;
@@ -197,8 +198,6 @@ recursiveExtract(JsQueryItem *jsq, bool not, bool indirect, PathItem *path)
197198
jsqGetArg(jsq,result->bounds.rightBound);
198199
}
199200
returnresult;
200-
casejqiContained:
201-
returnNULL;
202201
default:
203202
elog(ERROR,"Wrong state: %d",jsq->type);
204203
}

‎sql/jsquery.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -279,6 +279,10 @@ select count(*) from test_jsquery where v @@ 'customer_id = null';
279279
selectcount(*)from test_jsquerywhere v @@'review_votes = true';
280280
selectcount(*)from test_jsquerywhere v @@'product_group = false';
281281

282+
select vfrom test_jsquerywhere v @@'array <@ [2,3]'order by v;
283+
select vfrom test_jsquerywhere v @@'array && [2,3]'order by v;
284+
select vfrom test_jsquerywhere v @@'array @> [2,3]'order by v;
285+
282286
createindext_idxon test_jsquery using gin (v jsonb_value_path_ops);
283287
set enable_seqscan= off;
284288

@@ -302,6 +306,14 @@ select count(*) from test_jsquery where v @@ 'customer_id = null';
302306
selectcount(*)from test_jsquerywhere v @@'review_votes = true';
303307
selectcount(*)from test_jsquerywhere v @@'product_group = false';
304308

309+
explain (costs off)select vfrom test_jsquerywhere v @@'array <@ [2,3]'order by v;
310+
explain (costs off)select vfrom test_jsquerywhere v @@'array && [2,3]'order by v;
311+
explain (costs off)select vfrom test_jsquerywhere v @@'array @> [2,3]'order by v;
312+
313+
select vfrom test_jsquerywhere v @@'array <@ [2,3]'order by v;
314+
select vfrom test_jsquerywhere v @@'array && [2,3]'order by v;
315+
select vfrom test_jsquerywhere v @@'array @> [2,3]'order by v;
316+
305317
dropindex t_idx;
306318

307319
createindext_idxon test_jsquery using gin (v jsonb_path_value_ops);
@@ -327,4 +339,12 @@ select count(*) from test_jsquery where v @@ 'customer_id = null';
327339
selectcount(*)from test_jsquerywhere v @@'review_votes = true';
328340
selectcount(*)from test_jsquerywhere v @@'product_group = false';
329341

342+
explain (costs off)select vfrom test_jsquerywhere v @@'array <@ [2,3]'order by v;
343+
explain (costs off)select vfrom test_jsquerywhere v @@'array && [2,3]'order by v;
344+
explain (costs off)select vfrom test_jsquerywhere v @@'array @> [2,3]'order by v;
345+
346+
select vfrom test_jsquerywhere v @@'array <@ [2,3]'order by v;
347+
select vfrom test_jsquerywhere v @@'array && [2,3]'order by v;
348+
select vfrom test_jsquerywhere v @@'array @> [2,3]'order by v;
349+
330350
RESET enable_seqscan;

0 commit comments

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