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 e44bec7

Browse filesBrowse files
feodorfeodor
committed
add index support for <@
1 parent e6ab2bc commit e44bec7
Copy full SHA for e44bec7

File tree

4 files changed

+177
-5
lines changed
Filter options

4 files changed

+177
-5
lines changed

‎data/test_jsquery.data

Copy file name to clipboardExpand all lines: data/test_jsquery.data
+6Lines 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

Copy file name to clipboardExpand all lines: expected/jsquery.out
+149-2Lines 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

Copy file name to clipboardExpand all lines: jsquery_extract.c
+2-3Lines 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
case jqiIn:
134134
case jqiOverlap:
135135
case jqiContains:
136+
case jqiContained:
136137
if (not) return NULL;
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
return result;
200-
case jqiContained:
201-
return NULL;
202201
default:
203202
elog(ERROR,"Wrong state: %d", jsq->type);
204203
}

‎sql/jsquery.sql

Copy file name to clipboardExpand all lines: sql/jsquery.sql
+20Lines 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
select count(*) from test_jsquery where v @@ 'review_votes = true';
280280
select count(*) from test_jsquery where v @@ 'product_group = false';
281281

282+
select v from test_jsquery where v @@ 'array <@ [2,3]' order by v;
283+
select v from test_jsquery where v @@ 'array && [2,3]' order by v;
284+
select v from test_jsquery where v @@ 'array @> [2,3]' order by v;
285+
282286
create index t_idx on 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
select count(*) from test_jsquery where v @@ 'review_votes = true';
303307
select count(*) from test_jsquery where v @@ 'product_group = false';
304308

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

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

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

0 commit comments

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