Kouhei Sutou
null+****@clear*****
Mon May 8 18:49:51 JST 2017
Kouhei Sutou 2017-05-08 18:49:51 +0900 (Mon, 08 May 2017) New Revision: 8d951d110adee72ef56bc68b8ecb2e14d056f14a https://github.com/pgroonga/pgroonga/commit/8d951d110adee72ef56bc68b8ecb2e14d056f14a Message: Add v2 compatible &` operator to jsonb_ops (v1) Added files: expected/jsonb/compatibility/v1/script-v2/delete/multiple-values.out expected/jsonb/compatibility/v1/script-v2/element/boolean/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/element/boolean/indexscan.out expected/jsonb/compatibility/v1/script-v2/element/boolean/seqscan.out expected/jsonb/compatibility/v1/script-v2/element/number/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/element/number/indexscan.out expected/jsonb/compatibility/v1/script-v2/element/number/seqscan.out expected/jsonb/compatibility/v1/script-v2/element/string/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/element/string/indexscan.out expected/jsonb/compatibility/v1/script-v2/element/string/seqscan.out expected/jsonb/compatibility/v1/script-v2/full-text-search/default.out expected/jsonb/compatibility/v1/script-v2/full-text-search/disable.out expected/jsonb/compatibility/v1/script-v2/object/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/object/indexscan.out expected/jsonb/compatibility/v1/script-v2/object/seqscan.out expected/jsonb/compatibility/v1/script-v2/value/boolean/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/value/boolean/indexscan.out expected/jsonb/compatibility/v1/script-v2/value/boolean/seqscan.out expected/jsonb/compatibility/v1/script-v2/value/number/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/value/number/indexscan.out expected/jsonb/compatibility/v1/script-v2/value/number/seqscan.out expected/jsonb/compatibility/v1/script-v2/value/string/bitmapscan.out expected/jsonb/compatibility/v1/script-v2/value/string/indexscan.out expected/jsonb/compatibility/v1/script-v2/value/string/seqscan.out sql/jsonb/compatibility/v1/script-v2/delete/multiple-values.sql sql/jsonb/compatibility/v1/script-v2/element/boolean/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/element/boolean/indexscan.sql sql/jsonb/compatibility/v1/script-v2/element/boolean/seqscan.sql sql/jsonb/compatibility/v1/script-v2/element/number/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/element/number/indexscan.sql sql/jsonb/compatibility/v1/script-v2/element/number/seqscan.sql sql/jsonb/compatibility/v1/script-v2/element/string/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/element/string/indexscan.sql sql/jsonb/compatibility/v1/script-v2/element/string/seqscan.sql sql/jsonb/compatibility/v1/script-v2/full-text-search/#default.sql# sql/jsonb/compatibility/v1/script-v2/full-text-search/default.sql sql/jsonb/compatibility/v1/script-v2/full-text-search/disable.sql sql/jsonb/compatibility/v1/script-v2/object/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/object/indexscan.sql sql/jsonb/compatibility/v1/script-v2/object/seqscan.sql sql/jsonb/compatibility/v1/script-v2/value/boolean/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/value/boolean/indexscan.sql sql/jsonb/compatibility/v1/script-v2/value/boolean/seqscan.sql sql/jsonb/compatibility/v1/script-v2/value/number/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/value/number/indexscan.sql sql/jsonb/compatibility/v1/script-v2/value/number/seqscan.sql sql/jsonb/compatibility/v1/script-v2/value/string/bitmapscan.sql sql/jsonb/compatibility/v1/script-v2/value/string/indexscan.sql sql/jsonb/compatibility/v1/script-v2/value/string/seqscan.sql Modified files: data/pgroonga--1.2.0--1.2.1.sql data/pgroonga.sql Modified: data/pgroonga--1.2.0--1.2.1.sql (+16 -0) =================================================================== --- data/pgroonga--1.2.0--1.2.1.sql 2017-05-08 18:47:03 +0900 (a56f5c8) +++ data/pgroonga--1.2.0--1.2.1.sql 2017-05-08 18:49:51 +0900 (4f88b8c) @@ -395,3 +395,19 @@ BEGIN END IF; END; $$; + +-- Add v2 compatible operators to ops for jsonb +DO LANGUAGE plpgsql $$ +BEGIN + PERFORM 1 + FROM pg_type + WHERE typname = 'jsonb'; + + IF FOUND THEN + ALTER OPERATOR FAMILY pgroonga.jsonb_ops USING pgroonga + ADD + OPERATOR 15 &` (jsonb, text); + END IF; +END; +$$; + Modified: data/pgroonga.sql (+2 -1) =================================================================== --- data/pgroonga.sql 2017-05-08 18:47:03 +0900 (779023f) +++ data/pgroonga.sql 2017-05-08 18:49:51 +0900 (7e0089e) @@ -909,7 +909,8 @@ BEGIN CREATE OPERATOR CLASS pgroonga.jsonb_ops DEFAULT FOR TYPE jsonb USING pgroonga AS OPERATOR 9 @@ (jsonb, text), - OPERATOR 11 @>; + OPERATOR 11 @>, + OPERATOR 15 &` (jsonb, text); END IF; END; $$; Added: expected/jsonb/compatibility/v1/script-v2/delete/multiple-values.out (+24 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/delete/multiple-values.out 2017-05-08 18:49:51 +0900 (d55a23e) @@ -0,0 +1,24 @@ +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); +DELETE FROM fruits WHERE id = 1; +VACUUM; +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + id | items +----+--------------------- + 2 | ["banana", "apple"] +(1 row) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/boolean/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/boolean/bitmapscan.out 2017-05-08 18:49:51 +0900 (51060a4) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '[true]'); +INSERT INTO fruits VALUES (2, '[false]'); +INSERT INTO fruits VALUES (3, '[true]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + id | items +----+-------- + 1 | [true] + 3 | [true] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/boolean/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/boolean/indexscan.out 2017-05-08 18:49:51 +0900 (54977a8) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '[true]'); +INSERT INTO fruits VALUES (2, '[false]'); +INSERT INTO fruits VALUES (3, '[true]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + id | items +----+-------- + 1 | [true] + 3 | [true] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/boolean/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/boolean/seqscan.out 2017-05-08 18:49:51 +0900 (483805a) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '[true]'); +INSERT INTO fruits VALUES (2, '[false]'); +INSERT INTO fruits VALUES (3, '[true]'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + id | items +----+-------- + 1 | [true] + 3 | [true] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/number/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/number/bitmapscan.out 2017-05-08 18:49:51 +0900 (a4fa3ed) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '[100]'); +INSERT INTO fruits VALUES (2, '[200, 30]'); +INSERT INTO fruits VALUES (3, '[150]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + id | items +----+----------- + 1 | [100] + 2 | [200, 30] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/number/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/number/indexscan.out 2017-05-08 18:49:51 +0900 (74aeb0e) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '[100]'); +INSERT INTO fruits VALUES (2, '[200, 30]'); +INSERT INTO fruits VALUES (3, '[150]'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + id | items +----+----------- + 1 | [100] + 2 | [200, 30] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/number/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/number/seqscan.out 2017-05-08 18:49:51 +0900 (2adcbf3) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '[100]'); +INSERT INTO fruits VALUES (2, '[200, 30]'); +INSERT INTO fruits VALUES (3, '[150]'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + id | items +----+----------- + 1 | [100] + 2 | [200, 30] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/string/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/string/bitmapscan.out 2017-05-08 18:49:51 +0900 (08bd1ce) @@ -0,0 +1,23 @@ +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); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + id | items +----+--------------------- + 1 | ["apple"] + 2 | ["banana", "apple"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/string/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/string/indexscan.out 2017-05-08 18:49:51 +0900 (3e6da99) @@ -0,0 +1,23 @@ +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); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + id | items +----+--------------------- + 1 | ["apple"] + 2 | ["banana", "apple"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/element/string/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/element/string/seqscan.out 2017-05-08 18:49:51 +0900 (5aedbca) @@ -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 &` 'string == "apple"' + ORDER BY id; + id | items +----+--------------------- + 1 | ["apple"] + 2 | ["banana", "apple"] +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/full-text-search/default.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/full-text-search/default.out 2017-05-08 18:49:51 +0900 (b84a1ab) @@ -0,0 +1,23 @@ +CREATE TABLE logs ( + id int, + record jsonb +); +INSERT INTO logs VALUES (1, '{"message": "Hello World"}'); +INSERT INTO logs VALUES (2, '{"message": "This is a pen"}'); +INSERT INTO logs VALUES (3, '{"message": "Good-by World"}'); +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, record + FROM logs + WHERE record &` 'string @ "world"' + ORDER BY id; + id | record +----+------------------------------ + 1 | {"message": "Hello World"} + 3 | {"message": "Good-by World"} +(2 rows) + +DROP TABLE logs; Added: expected/jsonb/compatibility/v1/script-v2/full-text-search/disable.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/full-text-search/disable.out 2017-05-08 18:49:51 +0900 (f8da67a) @@ -0,0 +1,31 @@ +CREATE TABLE logs ( + id int, + record jsonb +); +INSERT INTO logs VALUES (1, '{"message": "Hello World"}'); +INSERT INTO logs VALUES (2, '{"message": "This is a pen"}'); +INSERT INTO logs VALUES (3, '{"message": "Good-by World"}'); +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops) + WITH (tokenizer = ''); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, record + FROM logs + WHERE record &` 'string @ "world"' + ORDER BY id; + id | record +----+-------- +(0 rows) + +SELECT id, record + FROM logs + WHERE record &` 'string == "Hello World"' + ORDER BY id; + id | record +----+---------------------------- + 1 | {"message": "Hello World"} +(1 row) + +DROP TABLE logs; Added: expected/jsonb/compatibility/v1/script-v2/object/bitmapscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/object/bitmapscan.out 2017-05-08 18:49:51 +0900 (ba295d5) @@ -0,0 +1,22 @@ +CREATE TABLE logs ( + id int, + record jsonb +); +INSERT INTO logs VALUES (1, '{"message": {"code": 100, "content": "hello"}}'); +INSERT INTO logs VALUES (1, '{"message": "hello"}'); +INSERT INTO logs VALUES (1, '{"message": ["hello", "world"]}'); +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, record + FROM logs + WHERE record &` 'paths @ ".message" && type == "object"' + ORDER BY id; + id | record +----+------------------------------------------------ + 1 | {"message": {"code": 100, "content": "hello"}} +(1 row) + +DROP TABLE logs; Added: expected/jsonb/compatibility/v1/script-v2/object/indexscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/object/indexscan.out 2017-05-08 18:49:51 +0900 (c25a2bb) @@ -0,0 +1,22 @@ +CREATE TABLE logs ( + id int, + record jsonb +); +INSERT INTO logs VALUES (1, '{"message": {"code": 100, "content": "hello"}}'); +INSERT INTO logs VALUES (1, '{"message": "hello"}'); +INSERT INTO logs VALUES (1, '{"message": ["hello", "world"]}'); +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, record + FROM logs + WHERE record &` 'paths @ ".message" && type == "object"' + ORDER BY id; + id | record +----+------------------------------------------------ + 1 | {"message": {"code": 100, "content": "hello"}} +(1 row) + +DROP TABLE logs; Added: expected/jsonb/compatibility/v1/script-v2/object/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/object/seqscan.out 2017-05-08 18:49:51 +0900 (6743d1a) @@ -0,0 +1,20 @@ +CREATE TABLE logs ( + id int, + record jsonb +); +INSERT INTO logs VALUES (1, '{"message": {"code": 100, "content": "hello"}}'); +INSERT INTO logs VALUES (1, '{"message": "hello"}'); +INSERT INTO logs VALUES (1, '{"message": ["hello", "world"]}'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, record + FROM logs + WHERE record &` 'paths @ ".message" && type == "object"' + ORDER BY id; + id | record +----+------------------------------------------------ + 1 | {"message": {"code": 100, "content": "hello"}} +(1 row) + +DROP TABLE logs; Added: expected/jsonb/compatibility/v1/script-v2/value/boolean/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/boolean/bitmapscan.out 2017-05-08 18:49:51 +0900 (47ccd31) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"apple": true}'); +INSERT INTO fruits VALUES (2, '{"banana": false}'); +INSERT INTO fruits VALUES (3, '{"peach": true}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + id | items +----+----------------- + 1 | {"apple": true} + 3 | {"peach": true} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/boolean/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/boolean/indexscan.out 2017-05-08 18:49:51 +0900 (2cda322) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"apple": true}'); +INSERT INTO fruits VALUES (2, '{"banana": false}'); +INSERT INTO fruits VALUES (3, '{"peach": true}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + id | items +----+----------------- + 1 | {"apple": true} + 3 | {"peach": true} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/boolean/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/boolean/seqscan.out 2017-05-08 18:49:51 +0900 (e28b27d) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"apple": true}'); +INSERT INTO fruits VALUES (2, '{"banana": false}'); +INSERT INTO fruits VALUES (3, '{"peach": true}'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + id | items +----+----------------- + 1 | {"apple": true} + 3 | {"peach": true} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/number/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/number/bitmapscan.out 2017-05-08 18:49:51 +0900 (62f4256) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"apple": 100}'); +INSERT INTO fruits VALUES (2, '{"banana": 30}'); +INSERT INTO fruits VALUES (3, '{"peach": 150}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + id | items +----+---------------- + 1 | {"apple": 100} + 2 | {"banana": 30} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/number/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/number/indexscan.out 2017-05-08 18:49:51 +0900 (2ee5f9c) @@ -0,0 +1,23 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"apple": 100}'); +INSERT INTO fruits VALUES (2, '{"banana": 30}'); +INSERT INTO fruits VALUES (3, '{"peach": 150}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + id | items +----+---------------- + 1 | {"apple": 100} + 2 | {"banana": 30} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/number/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/number/seqscan.out 2017-05-08 18:49:51 +0900 (bd79b2e) @@ -0,0 +1,21 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); +INSERT INTO fruits VALUES (1, '{"apple": 100}'); +INSERT INTO fruits VALUES (2, '{"banana": 30}'); +INSERT INTO fruits VALUES (3, '{"peach": 150}'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + id | items +----+---------------- + 1 | {"apple": 100} + 2 | {"banana": 30} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/string/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/string/bitmapscan.out 2017-05-08 18:49:51 +0900 (fae084c) @@ -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"}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + id | items +----+------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/string/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/string/indexscan.out 2017-05-08 18:49:51 +0900 (9352c79) @@ -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"}'); +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + id | items +----+------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} +(2 rows) + +DROP TABLE fruits; Added: expected/jsonb/compatibility/v1/script-v2/value/string/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/jsonb/compatibility/v1/script-v2/value/string/seqscan.out 2017-05-08 18:49:51 +0900 (5d2de45) @@ -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 &` 'string == "apple"' + ORDER BY id; + id | items +----+------------------- + 1 | {"name": "apple"} + 2 | {"type": "apple"} +(2 rows) + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/delete/multiple-values.sql (+24 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/delete/multiple-values.sql 2017-05-08 18:49:51 +0900 (800e62b) @@ -0,0 +1,24 @@ +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); +DELETE FROM fruits WHERE id = 1; +VACUUM; + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/boolean/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/boolean/bitmapscan.sql 2017-05-08 18:49:51 +0900 (21fbdc5) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '[true]'); +INSERT INTO fruits VALUES (2, '[false]'); +INSERT INTO fruits VALUES (3, '[true]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/boolean/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/boolean/indexscan.sql 2017-05-08 18:49:51 +0900 (f2302ac) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '[true]'); +INSERT INTO fruits VALUES (2, '[false]'); +INSERT INTO fruits VALUES (3, '[true]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/boolean/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/boolean/seqscan.sql 2017-05-08 18:49:51 +0900 (1eaa3fd) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '[true]'); +INSERT INTO fruits VALUES (2, '[false]'); +INSERT INTO fruits VALUES (3, '[true]'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/number/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/number/bitmapscan.sql 2017-05-08 18:49:51 +0900 (d346d5a) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '[100]'); +INSERT INTO fruits VALUES (2, '[200, 30]'); +INSERT INTO fruits VALUES (3, '[150]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/number/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/number/indexscan.sql 2017-05-08 18:49:51 +0900 (30a8912) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '[100]'); +INSERT INTO fruits VALUES (2, '[200, 30]'); +INSERT INTO fruits VALUES (3, '[150]'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/number/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/number/seqscan.sql 2017-05-08 18:49:51 +0900 (84ec76b) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '[100]'); +INSERT INTO fruits VALUES (2, '[200, 30]'); +INSERT INTO fruits VALUES (3, '[150]'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/string/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/string/bitmapscan.sql 2017-05-08 18:49:51 +0900 (f7ed167) @@ -0,0 +1,22 @@ +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); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/string/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/string/indexscan.sql 2017-05-08 18:49:51 +0900 (4ea1e56) @@ -0,0 +1,22 @@ +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); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/element/string/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/element/string/seqscan.sql 2017-05-08 18:49:51 +0900 (f141759) @@ -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 &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/full-text-search/#default.sql# (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/full-text-search/#default.sql# 2017-05-08 18:49:51 +0900 (458d382) @@ -0,0 +1,22 @@ +CREATE TABLE logs ( + id int, + record jsonb +); + +INSERT INTO logs VALUES (1, '{"message": "Hello World"}'); +INSERT INTO logs VALUES (2, '{"message": "This is a pen"}'); +INSERT INTO logs VALUES (3, '{"message": "Good-by World"}'); + +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, record + FROM logs + WHERE record &` 'string @ "world"' + ORDER BY id; + +DROP TABLE logs; Added: sql/jsonb/compatibility/v1/script-v2/full-text-search/default.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/full-text-search/default.sql 2017-05-08 18:49:51 +0900 (458d382) @@ -0,0 +1,22 @@ +CREATE TABLE logs ( + id int, + record jsonb +); + +INSERT INTO logs VALUES (1, '{"message": "Hello World"}'); +INSERT INTO logs VALUES (2, '{"message": "This is a pen"}'); +INSERT INTO logs VALUES (3, '{"message": "Good-by World"}'); + +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, record + FROM logs + WHERE record &` 'string @ "world"' + ORDER BY id; + +DROP TABLE logs; Added: sql/jsonb/compatibility/v1/script-v2/full-text-search/disable.sql (+28 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/full-text-search/disable.sql 2017-05-08 18:49:51 +0900 (aa4c10b) @@ -0,0 +1,28 @@ +CREATE TABLE logs ( + id int, + record jsonb +); + +INSERT INTO logs VALUES (1, '{"message": "Hello World"}'); +INSERT INTO logs VALUES (2, '{"message": "This is a pen"}'); +INSERT INTO logs VALUES (3, '{"message": "Good-by World"}'); + +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops) + WITH (tokenizer = ''); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, record + FROM logs + WHERE record &` 'string @ "world"' + ORDER BY id; + +SELECT id, record + FROM logs + WHERE record &` 'string == "Hello World"' + ORDER BY id; + +DROP TABLE logs; Added: sql/jsonb/compatibility/v1/script-v2/object/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/object/bitmapscan.sql 2017-05-08 18:49:51 +0900 (54d33a9) @@ -0,0 +1,22 @@ +CREATE TABLE logs ( + id int, + record jsonb +); + +INSERT INTO logs VALUES (1, '{"message": {"code": 100, "content": "hello"}}'); +INSERT INTO logs VALUES (1, '{"message": "hello"}'); +INSERT INTO logs VALUES (1, '{"message": ["hello", "world"]}'); + +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, record + FROM logs + WHERE record &` 'paths @ ".message" && type == "object"' + ORDER BY id; + +DROP TABLE logs; Added: sql/jsonb/compatibility/v1/script-v2/object/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/object/indexscan.sql 2017-05-08 18:49:51 +0900 (8a59902) @@ -0,0 +1,22 @@ +CREATE TABLE logs ( + id int, + record jsonb +); + +INSERT INTO logs VALUES (1, '{"message": {"code": 100, "content": "hello"}}'); +INSERT INTO logs VALUES (1, '{"message": "hello"}'); +INSERT INTO logs VALUES (1, '{"message": ["hello", "world"]}'); + +CREATE INDEX pgroonga_index ON logs + USING pgroonga (record pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, record + FROM logs + WHERE record &` 'paths @ ".message" && type == "object"' + ORDER BY id; + +DROP TABLE logs; Added: sql/jsonb/compatibility/v1/script-v2/object/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/object/seqscan.sql 2017-05-08 18:49:51 +0900 (fe13ad4) @@ -0,0 +1,19 @@ +CREATE TABLE logs ( + id int, + record jsonb +); + +INSERT INTO logs VALUES (1, '{"message": {"code": 100, "content": "hello"}}'); +INSERT INTO logs VALUES (1, '{"message": "hello"}'); +INSERT INTO logs VALUES (1, '{"message": ["hello", "world"]}'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, record + FROM logs + WHERE record &` 'paths @ ".message" && type == "object"' + ORDER BY id; + +DROP TABLE logs; Added: sql/jsonb/compatibility/v1/script-v2/value/boolean/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/boolean/bitmapscan.sql 2017-05-08 18:49:51 +0900 (77a6837) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"apple": true}'); +INSERT INTO fruits VALUES (2, '{"banana": false}'); +INSERT INTO fruits VALUES (3, '{"peach": true}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/boolean/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/boolean/indexscan.sql 2017-05-08 18:49:51 +0900 (de968f6) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"apple": true}'); +INSERT INTO fruits VALUES (2, '{"banana": false}'); +INSERT INTO fruits VALUES (3, '{"peach": true}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/boolean/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/boolean/seqscan.sql 2017-05-08 18:49:51 +0900 (b830c3e) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"apple": true}'); +INSERT INTO fruits VALUES (2, '{"banana": false}'); +INSERT INTO fruits VALUES (3, '{"peach": true}'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'boolean == true' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/number/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/number/bitmapscan.sql 2017-05-08 18:49:51 +0900 (8a1deb8) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"apple": 100}'); +INSERT INTO fruits VALUES (2, '{"banana": 30}'); +INSERT INTO fruits VALUES (3, '{"peach": 150}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/number/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/number/indexscan.sql 2017-05-08 18:49:51 +0900 (0e9484b) @@ -0,0 +1,22 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"apple": 100}'); +INSERT INTO fruits VALUES (2, '{"banana": 30}'); +INSERT INTO fruits VALUES (3, '{"peach": 150}'); + +CREATE INDEX pgroonga_index ON fruits + USING pgroonga (items pgroonga.jsonb_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/number/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/number/seqscan.sql 2017-05-08 18:49:51 +0900 (5cfa1a9) @@ -0,0 +1,19 @@ +CREATE TABLE fruits ( + id int, + items jsonb +); + +INSERT INTO fruits VALUES (1, '{"apple": 100}'); +INSERT INTO fruits VALUES (2, '{"banana": 30}'); +INSERT INTO fruits VALUES (3, '{"peach": 150}'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'type == "number" && number <= 100' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/string/bitmapscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/string/bitmapscan.sql 2017-05-08 18:49:51 +0900 (223bbc6) @@ -0,0 +1,22 @@ +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); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/string/indexscan.sql (+22 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/string/indexscan.sql 2017-05-08 18:49:51 +0900 (632c422) @@ -0,0 +1,22 @@ +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); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, items + FROM fruits + WHERE items &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; Added: sql/jsonb/compatibility/v1/script-v2/value/string/seqscan.sql (+19 -0) 100644 =================================================================== --- /dev/null +++ sql/jsonb/compatibility/v1/script-v2/value/string/seqscan.sql 2017-05-08 18:49:51 +0900 (5962ef4) @@ -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 &` 'string == "apple"' + ORDER BY id; + +DROP TABLE fruits; -------------- next part -------------- HTML����������������������������...下载
