Kouhei Sutou
null+****@clear*****
Mon May 8 20:19:05 JST 2017
Kouhei Sutou 2017-05-08 20:19:05 +0900 (Mon, 08 May 2017) New Revision: 6572bba48b752c4fc2a31577f1844704a27fa5c8 https://github.com/pgroonga/pgroonga/commit/6572bba48b752c4fc2a31577f1844704a27fa5c8 Message: Support &@ and &? in jsonb Added files: expected/jsonb/match-v2/element/bitmapscan.out expected/jsonb/match-v2/element/indexscan.out expected/jsonb/match-v2/element/seqscan.out expected/jsonb/match-v2/value/bitmapscan.out expected/jsonb/match-v2/value/indexscan.out expected/jsonb/match-v2/value/seqscan.out expected/jsonb/query-v2/element/bitmapscan.out expected/jsonb/query-v2/element/indexscan.out expected/jsonb/query-v2/element/seqscan.out expected/jsonb/query-v2/value/bitmapscan.out expected/jsonb/query-v2/value/indexscan.out expected/jsonb/query-v2/value/seqscan.out sql/jsonb/match-v2/element/bitmapscan.sql sql/jsonb/match-v2/element/indexscan.sql sql/jsonb/match-v2/element/seqscan.sql sql/jsonb/match-v2/value/bitmapscan.sql sql/jsonb/match-v2/value/indexscan.sql sql/jsonb/match-v2/value/seqscan.sql sql/jsonb/query-v2/element/bitmapscan.sql sql/jsonb/query-v2/element/indexscan.sql sql/jsonb/query-v2/element/seqscan.sql sql/jsonb/query-v2/value/bitmapscan.sql sql/jsonb/query-v2/value/indexscan.sql sql/jsonb/query-v2/value/seqscan.sql Modified files: data/pgroonga--1.2.0--1.2.1.sql data/pgroonga.sql src/pgrn-jsonb.c Modified: data/pgroonga--1.2.0--1.2.1.sql (+61 -1) =================================================================== --- data/pgroonga--1.2.0--1.2.1.sql 2017-05-08 18:49:51 +0900 (4f88b8c) +++ data/pgroonga--1.2.0--1.2.1.sql 2017-05-08 20:19:05 +0900 (6ec3679) @@ -366,6 +366,39 @@ ALTER OPERATOR FAMILY pgroonga.varchar_array_ops USING pgroonga ADD OPERATOR 12 &@ (varchar[], varchar); +-- Rename "pgroonga_match_jsonb" to "pgroonga_match_script_jsonb" +DO LANGUAGE plpgsql $$ +BEGIN + PERFORM 1 + FROM pg_type + WHERE typname = 'jsonb'; + + IF FOUND THEN + DROP OPERATOR CLASS pgroonga.jsonb_ops USING pgroonga; + DROP OPERATOR @@ (jsonb, text); + DROP FUNCTION pgroonga.match_query(jsonb, text); + + CREATE FUNCTION pgroonga.match_script_jsonb(jsonb, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_script_jsonb' + LANGUAGE C + IMMUTABLE + STRICT; + + CREATE OPERATOR @@ ( + PROCEDURE = pgroonga.match_script_jsonb, + LEFTARG = jsonb, + RIGHTARG = text + ); + + CREATE OPERATOR CLASS pgroonga.jsonb_ops DEFAULT FOR TYPE jsonb + USING pgroonga AS + OPERATOR 9 @@ (jsonb, text), + OPERATOR 11 @>; + END IF; +END; +$$; + -- Add pgroonga.jsonb_ops_v2 DO LANGUAGE plpgsql $$ BEGIN @@ -374,6 +407,32 @@ BEGIN WHERE typname = 'jsonb'; IF FOUND THEN + CREATE FUNCTION pgroonga.match_jsonb(jsonb, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_jsonb' + LANGUAGE C + IMMUTABLE + STRICT; + + CREATE OPERATOR &@ ( + PROCEDURE = pgroonga.match_jsonb, + LEFTARG = jsonb, + RIGHTARG = text + ); + + CREATE FUNCTION pgroonga.query_jsonb(jsonb, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_query_jsonb' + LANGUAGE C + IMMUTABLE + STRICT; + + CREATE OPERATOR &? ( + PROCEDURE = pgroonga.query_jsonb, + LEFTARG = jsonb, + RIGHTARG = text + ); + CREATE FUNCTION pgroonga.script_jsonb(jsonb, text) RETURNS bool AS 'MODULE_PATHNAME', 'pgroonga_script_jsonb' @@ -391,6 +450,8 @@ BEGIN USING pgroonga AS OPERATOR 9 @@ (jsonb, text), -- For backward compatibility OPERATOR 11 @>, + OPERATOR 12 &@ (jsonb, text), + OPERATOR 13 &? (jsonb, text), OPERATOR 15 &` (jsonb, text); END IF; END; @@ -410,4 +471,3 @@ BEGIN END IF; END; $$; - Modified: data/pgroonga.sql (+51 -3) =================================================================== --- data/pgroonga.sql 2017-05-08 18:49:51 +0900 (7e0089e) +++ data/pgroonga.sql 2017-05-08 20:19:05 +0900 (62b57d6) @@ -331,6 +331,29 @@ CREATE OPERATOR &@ ( RIGHTARG = varchar ); +DO LANGUAGE plpgsql $$ +BEGIN + PERFORM 1 + FROM pg_type + WHERE typname = 'jsonb'; + + IF FOUND THEN + CREATE FUNCTION pgroonga.match_jsonb(jsonb, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_jsonb' + LANGUAGE C + IMMUTABLE + STRICT; + + CREATE OPERATOR &@ ( + PROCEDURE = pgroonga.match_jsonb, + LEFTARG = jsonb, + RIGHTARG = text + ); + END IF; +END; +$$; + CREATE FUNCTION pgroonga.query_text(text, text) RETURNS bool AS 'MODULE_PATHNAME', 'pgroonga_query_text' @@ -370,6 +393,29 @@ CREATE OPERATOR &? ( RIGHTARG = varchar ); +DO LANGUAGE plpgsql $$ +BEGIN + PERFORM 1 + FROM pg_type + WHERE typname = 'jsonb'; + + IF FOUND THEN + CREATE FUNCTION pgroonga.query_jsonb(jsonb, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_query_jsonb' + LANGUAGE C + IMMUTABLE + STRICT; + + CREATE OPERATOR &? ( + PROCEDURE = pgroonga.query_jsonb, + LEFTARG = jsonb, + RIGHTARG = text + ); + END IF; +END; +$$; + CREATE FUNCTION pgroonga.similar_text(text, text) RETURNS bool AS 'MODULE_PATHNAME', 'pgroonga_similar_text' @@ -893,15 +939,15 @@ BEGIN WHERE typname = 'jsonb'; IF FOUND THEN - CREATE FUNCTION pgroonga.match_query(jsonb, text) + CREATE FUNCTION pgroonga.match_script_jsonb(jsonb, text) RETURNS bool - AS 'MODULE_PATHNAME', 'pgroonga_match_jsonb' + AS 'MODULE_PATHNAME', 'pgroonga_match_script_jsonb' LANGUAGE C IMMUTABLE STRICT; CREATE OPERATOR @@ ( - PROCEDURE = pgroonga.match_query, + PROCEDURE = pgroonga.match_script_jsonb, LEFTARG = jsonb, RIGHTARG = text ); @@ -1007,6 +1053,8 @@ BEGIN USING pgroonga AS OPERATOR 9 @@ (jsonb, text), -- For backward compatibility OPERATOR 11 @>, + OPERATOR 12 &@ (jsonb, text), + OPERATOR 13 &? (jsonb, text), OPERATOR 15 &` (jsonb, text); END IF; END; Added: expected/jsonb/match-v2/element/bitmapscan.out (+38 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/match-v2/element/bitmapscan.out 2017-05-08 20:19:05 +0900 (a3fdf59) @@ -0,0 +1,38 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: id + -> Bitmap Heap Scan on fruits + Recheck Cond: (items &@ 'app'::text) + -> Bitmap Index Scan on pgroonga_index + Index Cond: (items &@ 'app'::text) +(6 rows) + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + id | items +----+--------------------- + 1 | ["apple"] + 2 | ["banana", "apple"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/match-v2/element/indexscan.out (+36 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/match-v2/element/indexscan.out 2017-05-08 20:19:05 +0900 (ee6642d) @@ -0,0 +1,36 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + QUERY PLAN +------------------------------------------------- + Sort + Sort Key: id + -> Index Scan using pgroonga_index on fruits + Index Cond: (items &@ 'app'::text) +(4 rows) + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + id | items +----+--------------------- + 1 | ["apple"] + 2 | ["banana", "apple"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/match-v2/element/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/match-v2/element/seqscan.out 2017-05-08 20:19:05 +0900 (df4164a) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + id | items +----+--------------------- + 1 | ["apple"] + 2 | ["banana", "apple"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/match-v2/value/bitmapscan.out (+38 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/match-v2/value/bitmapscan.out 2017-05-08 20:19:05 +0900 (9864d71) @@ -0,0 +1,38 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: id + -> Bitmap Heap Scan on fruits + Recheck Cond: (items &@ 'app'::text) + -> Bitmap Index Scan on pgroonga_index + Index Cond: (items &@ 'app'::text) +(6 rows) + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + id | items +----+------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/match-v2/value/indexscan.out (+36 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/match-v2/value/indexscan.out 2017-05-08 20:19:05 +0900 (4981a9f) @@ -0,0 +1,36 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + QUERY PLAN +------------------------------------------------- + Sort + Sort Key: id + -> Index Scan using pgroonga_index on fruits + Index Cond: (items &@ 'app'::text) +(4 rows) + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + id | items +----+------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/match-v2/value/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/match-v2/value/seqscan.out 2017-05-08 20:19:05 +0900 (24c85e8) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + id | items +----+------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/query-v2/element/bitmapscan.out (+38 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/query-v2/element/bitmapscan.out 2017-05-08 20:19:05 +0900 (943df62) @@ -0,0 +1,38 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: id + -> Bitmap Heap Scan on fruits + Recheck Cond: (items &? 'banana OR peach'::text) + -> Bitmap Index Scan on pgroonga_index + Index Cond: (items &? 'banana OR peach'::text) +(6 rows) + +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + id | items +----+--------------------- + 2 | ["banana", "apple"] + 3 | ["peach"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/query-v2/element/indexscan.out (+36 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/query-v2/element/indexscan.out 2017-05-08 20:19:05 +0900 (6f3ddac) @@ -0,0 +1,36 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + QUERY PLAN +-------------------------------------------------------- + Sort + Sort Key: id + -> Index Scan using pgroonga_index on fruits + Index Cond: (items &? 'banana OR peach'::text) +(4 rows) + +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + id | items +----+--------------------- + 2 | ["banana", "apple"] + 3 | ["peach"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/query-v2/element/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/query-v2/element/seqscan.out 2017-05-08 20:19:05 +0900 (da01d1d) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + id | items +----+--------------------- + 2 | ["banana", "apple"] + 3 | ["peach"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/query-v2/value/bitmapscan.out (+40 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/query-v2/value/bitmapscan.out 2017-05-08 20:19:05 +0900 (0bd4889) @@ -0,0 +1,40 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +INSERT INTO fruits VALUES (4, '{"like": "banana"}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: id + -> Bitmap Heap Scan on fruits + Recheck Cond: (items &? 'apple OR banana'::text) + -> Bitmap Index Scan on pgroonga_index + Index Cond: (items &? 'apple OR banana'::text) +(6 rows) + +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + id | items +----+-------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} + 4 | {"like": "banana"} +(3 rows) + +DROP TABLE fruits; Added: expected/jsonb/query-v2/value/indexscan.out (+38 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/query-v2/value/indexscan.out 2017-05-08 20:19:05 +0900 (1a6ec71) @@ -0,0 +1,38 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +INSERT INTO fruits VALUES (4, '{"like": "banana"}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + QUERY PLAN +-------------------------------------------------------- + Sort + Sort Key: id + -> Index Scan using pgroonga_index on fruits + Index Cond: (items &? 'apple OR banana'::text) +(4 rows) + +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + id | items +----+-------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} + 4 | {"like": "banana"} +(3 rows) + +DROP TABLE fruits; Added: expected/jsonb/query-v2/value/seqscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/query-v2/value/seqscan.out 2017-05-08 20:19:05 +0900 (2cab725) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +INSERT INTO fruits VALUES (4, '{"like": "banana"}'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + id | items +----+-------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} + 4 | {"like": "banana"} +(3 rows) + +DROP TABLE fruits; Added: sql/jsonb/match-v2/element/bitmapscan.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/match-v2/element/bitmapscan.sql 2017-05-08 20:19:05 +0900 (9f9ba58) @@ -0,0 +1,28 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/match-v2/element/indexscan.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/match-v2/element/indexscan.sql 2017-05-08 20:19:05 +0900 (59881a9) @@ -0,0 +1,28 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/match-v2/element/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/match-v2/element/seqscan.sql 2017-05-08 20:19:05 +0900 (4cd4b99) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/match-v2/value/bitmapscan.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/match-v2/value/bitmapscan.sql 2017-05-08 20:19:05 +0900 (2da4383) @@ -0,0 +1,28 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/match-v2/value/indexscan.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/match-v2/value/indexscan.sql 2017-05-08 20:19:05 +0900 (e0d672c) @@ -0,0 +1,28 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/match-v2/value/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/match-v2/value/seqscan.sql 2017-05-08 20:19:05 +0900 (258cdbd) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &@ 'app' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/query-v2/element/bitmapscan.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/query-v2/element/bitmapscan.sql 2017-05-08 20:19:05 +0900 (086addd) @@ -0,0 +1,28 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/query-v2/element/indexscan.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/query-v2/element/indexscan.sql 2017-05-08 20:19:05 +0900 (f049788) @@ -0,0 +1,28 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/query-v2/element/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/query-v2/element/seqscan.sql 2017-05-08 20:19:05 +0900 (50f0642) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '["apple"]'); +INSERT INTO fruits VALUES (2, '["banana", "apple"]'); +INSERT INTO fruits VALUES (3, '["peach"]'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &? 'banana OR peach' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/query-v2/value/bitmapscan.sql (+29 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/query-v2/value/bitmapscan.sql 2017-05-08 20:19:05 +0900 (bb950de) @@ -0,0 +1,29 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +INSERT INTO fruits VALUES (4, '{"like": "banana"}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/query-v2/value/indexscan.sql (+29 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/query-v2/value/indexscan.sql 2017-05-08 20:19:05 +0900 (80e5123) @@ -0,0 +1,29 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +INSERT INTO fruits VALUES (4, '{"like": "banana"}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/query-v2/value/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/query-v2/value/seqscan.sql 2017-05-08 20:19:05 +0900 (4d277ab) @@ -0,0 +1,20 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"name": "apple"}'); +INSERT INTO fruits VALUES (2, '{"type": "apple"}'); +INSERT INTO fruits VALUES (3, '{"name": "peach"}'); +INSERT INTO fruits VALUES (4, '{"like": "banana"}'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &? 'apple OR banana' + ORDER BY id; + +DROP TABLE fruits; Modified: src/pgrn-jsonb.c (+229 -46) =================================================================== --- src/pgrn-jsonb.c 2017-05-08 18:49:51 +0900 (b177bfa) +++ src/pgrn-jsonb.c 2017-05-08 20:19:05 +0900 (66e7a05) @@ -19,10 +19,14 @@ #include <xxhash.h> +#include <string.h> + #ifdef PGRN_SUPPORT_JSONB -PGRN_FUNCTION_INFO_V1(pgroonga_match_jsonb); +PGRN_FUNCTION_INFO_V1(pgroonga_match_script_jsonb); /* v2 */ +PGRN_FUNCTION_INFO_V1(pgroonga_match_jsonb); +PGRN_FUNCTION_INFO_V1(pgroonga_query_jsonb); PGRN_FUNCTION_INFO_V1(pgroonga_script_jsonb); #endif @@ -1041,17 +1045,27 @@ PGrnJSONBDeleteValues(grn_obj *valuesTable, grn_obj *valueIDs) } static bool -pgroonga_script_jsonb_raw(Jsonb *target, - const char *script, unsigned int scriptSize) +PGrnJSONBMatchExpression(Jsonb *target, + const char *term, + unsigned int termSize, + const char *query, + unsigned int querySize, + const char *script, + unsigned int scriptSize, + const char *logTag) { grn_obj valueIDs; PGrnJSONBInsertData data; JsonbIterator *iter; - grn_obj *filter = NULL; - grn_obj *dummy_variable = NULL; + grn_obj *matchTarget = NULL; + grn_obj *condition = NULL; + grn_obj *dummyVariable = NULL; grn_obj *result = NULL; bool matched = false; + if (termSize == 0 && querySize == 0 && scriptSize == 0) + return false; + data.index = NULL; data.pathsTable = tmpPathsTable; data.valuesTable = tmpValuesTable; @@ -1064,28 +1078,81 @@ pgroonga_script_jsonb_raw(Jsonb *target, PG_TRY(); { - GRN_EXPR_CREATE_FOR_QUERY(ctx, tmpValuesTable, filter, dummy_variable); - PGrnCheck("jsonb_script: failed to create expression object"); - grn_expr_parse(ctx, filter, - script, scriptSize, - NULL, GRN_OP_MATCH, GRN_OP_AND, - GRN_EXPR_SYNTAX_SCRIPT); - PGrnCheck("jsonb_script: failed to parse script: <%.*s>", - (int)scriptSize, - script); + GRN_EXPR_CREATE_FOR_QUERY(ctx, tmpValuesTable, condition, dummyVariable); + PGrnCheck("jsonb: %s: failed to create condition expression object", + logTag); + if (termSize > 0) + { + const char *targetName = "string"; + grn_obj *column; + + column = grn_obj_column(ctx, + tmpValuesTable, + targetName, + strlen(targetName)); + grn_expr_append_obj(ctx, condition, column, GRN_OP_GET_VALUE, 1); + grn_expr_append_const_str(ctx, condition, + term, termSize, + GRN_OP_PUSH, 1); + grn_expr_append_op(ctx, condition, GRN_OP_MATCH, 2); + } + else if (querySize > 0) + { + const char *matchColumns = "string"; + + GRN_EXPR_CREATE_FOR_QUERY(ctx, + tmpValuesTable, + matchTarget, + dummyVariable); + PGrnCheck("jsonb: %s: " + "failed to create match target expression object", + logTag); + grn_expr_parse(ctx, matchTarget, + matchColumns, + strlen(matchColumns), + NULL, GRN_OP_MATCH, GRN_OP_AND, + GRN_EXPR_SYNTAX_SCRIPT); + PGrnCheck("jsonb: %s: failed to parse match columns: <%.*s>", + logTag, + (int)strlen(matchColumns), + matchColumns); + grn_expr_parse(ctx, condition, + query, querySize, + matchTarget, GRN_OP_MATCH, GRN_OP_AND, + GRN_EXPR_SYNTAX_QUERY); + PGrnCheck("jsonb: %s: failed to parse query: <%.*s>", + logTag, + (int)querySize, + query); + } + else if (scriptSize > 0) + { + grn_expr_parse(ctx, condition, + script, scriptSize, + NULL, GRN_OP_MATCH, GRN_OP_AND, + GRN_EXPR_SYNTAX_SCRIPT); + PGrnCheck("jsonb: %s: failed to parse script: <%.*s>", + logTag, + (int)scriptSize, + script); + } result = grn_table_create(ctx, NULL, 0, NULL, GRN_TABLE_HASH_KEY|GRN_OBJ_WITH_SUBREC, tmpValuesTable, NULL); - PGrnCheck("jsonb_script: failed to create result table"); - grn_table_select(ctx, tmpValuesTable, filter, result, GRN_OP_OR); - PGrnCheck("jsonb_script: failed to select"); + PGrnCheck("jsonb: %s: failed to create result table", + logTag); + grn_table_select(ctx, tmpValuesTable, condition, result, GRN_OP_OR); + PGrnCheck("jsonb: %s: failed to select", + logTag); } PG_CATCH(); { if (result) grn_obj_close(ctx, result); - if (filter) - grn_obj_close(ctx, filter); + if (condition) + grn_obj_close(ctx, condition); + if (matchTarget) + grn_obj_close(ctx, matchTarget); PGrnJSONBDeleteValues(tmpValuesTable, &valueIDs); GRN_OBJ_FIN(ctx, &valueIDs); PG_RE_THROW(); @@ -1094,8 +1161,10 @@ pgroonga_script_jsonb_raw(Jsonb *target, matched = grn_table_size(ctx, result) > 0; - grn_obj_close(ctx, filter); grn_obj_close(ctx, result); + grn_obj_close(ctx, condition); + if (matchTarget) + grn_obj_close(ctx, matchTarget); PGrnJSONBDeleteValues(tmpValuesTable, &valueIDs); GRN_OBJ_FIN(ctx, &valueIDs); @@ -1104,18 +1173,59 @@ pgroonga_script_jsonb_raw(Jsonb *target, } /** - * pgroonga.match_jsonb(jsonb, query) : bool + * pgroonga.match_script_jsonb(jsonb, query) : bool */ Datum -pgroonga_match_jsonb(PG_FUNCTION_ARGS) +pgroonga_match_script_jsonb(PG_FUNCTION_ARGS) { - Jsonb *jsonb = PG_GETARG_JSONB(0); + Jsonb *target = PG_GETARG_JSONB(0); text *script = PG_GETARG_TEXT_PP(1); bool matched; - matched = pgroonga_script_jsonb_raw(jsonb, - VARDATA_ANY(script), - VARSIZE_ANY_EXHDR(script)); + matched = PGrnJSONBMatchExpression(target, + NULL, 0, + NULL, 0, + VARDATA_ANY(script), + VARSIZE_ANY_EXHDR(script), + "script"); + PG_RETURN_BOOL(matched); +} + +/** + * pgroonga.match_jsonb(target jsonb, term text) : bool + */ +Datum +pgroonga_match_jsonb(PG_FUNCTION_ARGS) +{ + Jsonb *target = PG_GETARG_JSONB(0); + text *term = PG_GETARG_TEXT_PP(1); + bool matched; + + matched = PGrnJSONBMatchExpression(target, + VARDATA_ANY(term), + VARSIZE_ANY_EXHDR(term), + NULL, 0, + NULL, 0, + "match"); + PG_RETURN_BOOL(matched); +} + +/** + * pgroonga.query_jsonb(target jsonb, query text) : bool + */ +Datum +pgroonga_query_jsonb(PG_FUNCTION_ARGS) +{ + Jsonb *target = PG_GETARG_JSONB(0); + text *query = PG_GETARG_TEXT_PP(1); + bool matched; + + matched = PGrnJSONBMatchExpression(target, + NULL, 0, + VARDATA_ANY(query), + VARSIZE_ANY_EXHDR(query), + NULL, 0, + "query"); PG_RETURN_BOOL(matched); } @@ -1125,13 +1235,16 @@ pgroonga_match_jsonb(PG_FUNCTION_ARGS) Datum pgroonga_script_jsonb(PG_FUNCTION_ARGS) { - Jsonb *jsonb = PG_GETARG_JSONB(0); + Jsonb *target = PG_GETARG_JSONB(0); text *script = PG_GETARG_TEXT_PP(1); bool matched; - matched = pgroonga_script_jsonb_raw(jsonb, - VARDATA_ANY(script), - VARSIZE_ANY_EXHDR(script)); + matched = PGrnJSONBMatchExpression(target, + NULL, 0, + NULL, 0, + VARDATA_ANY(script), + VARSIZE_ANY_EXHDR(script), + "script"); PG_RETURN_BOOL(matched); } @@ -1242,11 +1355,11 @@ PGrnJSONBInsert(Relation index, #ifdef PGRN_SUPPORT_JSONB static void -PGrnSearchBuildConditionJSONQuery(PGrnSearchData *data, - grn_obj *subFilter, - grn_obj *targetColumn, - grn_obj *filter, - unsigned int *nthCondition) +PGrnSearchBuildConditionJSONScript(PGrnSearchData *data, + grn_obj *subFilter, + grn_obj *targetColumn, + grn_obj *filter, + unsigned int *nthCondition) { grn_expr_append_obj(ctx, data->expression, subFilter, GRN_OP_PUSH, 1); @@ -1263,6 +1376,51 @@ PGrnSearchBuildConditionJSONQuery(PGrnSearchData *data, } static void +PGrnSearchBuildConditionJSONMatch(PGrnSearchData *data, + grn_obj *subFilter, + grn_obj *targetColumn, + grn_obj *term) +{ + unsigned int nthCondition = 0; + + GRN_BULK_REWIND(&(buffers->general)); + + GRN_TEXT_PUTS(ctx, &(buffers->general), "string @ "); + grn_text_esc(ctx, &(buffers->general), + GRN_TEXT_VALUE(term), + GRN_TEXT_LEN(term)); + + PGrnSearchBuildConditionJSONScript(data, + subFilter, + targetColumn, + &(buffers->general), + &nthCondition); +} + +static void +PGrnSearchBuildConditionJSONQuery(PGrnSearchData *data, + grn_obj *subFilter, + grn_obj *targetColumn, + grn_obj *query) +{ + unsigned int nthCondition = 0; + + GRN_BULK_REWIND(&(buffers->general)); + + GRN_TEXT_PUTS(ctx, &(buffers->general), "query(\"string\", "); + grn_text_esc(ctx, &(buffers->general), + GRN_TEXT_VALUE(query), + GRN_TEXT_LEN(query)); + GRN_TEXT_PUTS(ctx, &(buffers->general), ")"); + + PGrnSearchBuildConditionJSONScript(data, + subFilter, + targetColumn, + &(buffers->general), + &nthCondition); +} + +static void PGrnSearchBuildConditionJSONContainType(PGrnSearchData *data, grn_obj *subFilter, grn_obj *targetColumn, @@ -1282,8 +1440,8 @@ PGrnSearchBuildConditionJSONContainType(PGrnSearchData *data, GRN_TEXT_VALUE(&(buffers->path)), GRN_TEXT_LEN(&(buffers->path))); - PGrnSearchBuildConditionJSONQuery(data, subFilter, targetColumn, - &(buffers->general), nthCondition); + PGrnSearchBuildConditionJSONScript(data, subFilter, targetColumn, + &(buffers->general), nthCondition); } static void @@ -1342,8 +1500,8 @@ PGrnSearchBuildConditionJSONContainValue(PGrnSearchData *data, GRN_TEXT_VALUE(&(buffers->path)), GRN_TEXT_LEN(&(buffers->path))); - PGrnSearchBuildConditionJSONQuery(data, subFilter, targetColumn, - &(buffers->general), nthCondition); + PGrnSearchBuildConditionJSONScript(data, subFilter, targetColumn, + &(buffers->general), nthCondition); } static void @@ -1448,23 +1606,48 @@ PGrnJSONBBuildSearchCondition(PGrnSearchData *data, subFilter = PGrnLookup("sub_filter", ERROR); grn_obj_reinit(ctx, &(buffers->general), GRN_DB_TEXT, 0); - if (key->sk_strategy == PGrnQueryStrategyNumber || - key->sk_strategy == PGrnScriptStrategyV2Number) + switch (key->sk_strategy) + { + case PGrnQueryStrategyNumber: /* For backward compatibility */ + case PGrnScriptStrategyV2Number: { unsigned int nthCondition = 0; PGrnConvertFromData(key->sk_argument, TEXTOID, &(buffers->general)); + PGrnSearchBuildConditionJSONScript(data, + subFilter, + targetColumn, + &(buffers->general), + &nthCondition); + break; + } + case PGrnMatchStrategyV2Number: + grn_obj_reinit(ctx, &(buffers->keyword), GRN_DB_TEXT, 0); + PGrnConvertFromData(key->sk_argument, TEXTOID, &(buffers->keyword)); + PGrnSearchBuildConditionJSONMatch(data, + subFilter, + targetColumn, + &(buffers->keyword)); + break; + case PGrnQueryStrategyV2Number: + grn_obj_reinit(ctx, &(buffers->keyword), GRN_DB_TEXT, 0); + PGrnConvertFromData(key->sk_argument, TEXTOID, &(buffers->keyword)); PGrnSearchBuildConditionJSONQuery(data, subFilter, targetColumn, - &(buffers->general), - &nthCondition); - } - else - { + &(buffers->keyword)); + break; + case PGrnJSONContainStrategyNumber: PGrnSearchBuildConditionJSONContain(data, subFilter, targetColumn, DatumGetJsonb(key->sk_argument)); + break; + default: + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("pgroonga: jsonb: unsupported strategy number: %d", + key->sk_strategy))); + break; } return true; #else -------------- next part -------------- HTML����������������������������...下载
