Kouhei Sutou
null+****@clear*****
Tue May 9 08:59:08 JST 2017
Kouhei Sutou 2017-05-09 08:59:08 +0900 (Tue, 09 May 2017) New Revision: e90f05cbd4ff0cd81d11e1d49c894f08d810713e https://github.com/pgroonga/pgroonga/commit/e90f05cbd4ff0cd81d11e1d49c894f08d810713e Message: Add v2 operator @~ to v1 regexp op class Added files: expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.out expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/indexscan.out expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/seqscan.out expected/regexp/text/compatibility/v1/regexp-v2/dot/bitmapscan.out expected/regexp/text/compatibility/v1/regexp-v2/dot/indexscan.out expected/regexp/text/compatibility/v1/regexp-v2/dot/seqscan.out expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/bitmapscan.out expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/indexscan.out expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/seqscan.out expected/regexp/text/compatibility/v1/regexp-v2/exact/bitmapscan.out expected/regexp/text/compatibility/v1/regexp-v2/exact/indexscan.out expected/regexp/text/compatibility/v1/regexp-v2/exact/seqscan.out expected/regexp/text/compatibility/v1/regexp-v2/partial/bitmapscan.out expected/regexp/text/compatibility/v1/regexp-v2/partial/indexscan.out expected/regexp/text/compatibility/v1/regexp-v2/partial/seqscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/indexscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/seqscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/dot/bitmapscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/dot/indexscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/dot/seqscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/bitmapscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/indexscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/seqscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/exact/bitmapscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/exact/indexscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/exact/seqscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/partial/bitmapscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/partial/indexscan.out expected/regexp/varchar/compatibility/v1/regexp-v2/partial/seqscan.out sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.sql sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/indexscan.sql sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/seqscan.sql sql/regexp/text/compatibility/v1/regexp-v2/dot/bitmapscan.sql sql/regexp/text/compatibility/v1/regexp-v2/dot/indexscan.sql sql/regexp/text/compatibility/v1/regexp-v2/dot/seqscan.sql sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/bitmapscan.sql sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/indexscan.sql sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/seqscan.sql sql/regexp/text/compatibility/v1/regexp-v2/exact/bitmapscan.sql sql/regexp/text/compatibility/v1/regexp-v2/exact/indexscan.sql sql/regexp/text/compatibility/v1/regexp-v2/exact/seqscan.sql sql/regexp/text/compatibility/v1/regexp-v2/partial/bitmapscan.sql sql/regexp/text/compatibility/v1/regexp-v2/partial/indexscan.sql sql/regexp/text/compatibility/v1/regexp-v2/partial/seqscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/indexscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/seqscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/dot/bitmapscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/dot/indexscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/dot/seqscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/bitmapscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/indexscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/seqscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/exact/bitmapscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/exact/indexscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/exact/seqscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/partial/bitmapscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/partial/indexscan.sql sql/regexp/varchar/compatibility/v1/regexp-v2/partial/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 (+8 -0) =================================================================== --- data/pgroonga--1.2.0--1.2.1.sql 2017-05-08 21:03:33 +0900 (7e11f37) +++ data/pgroonga--1.2.0--1.2.1.sql 2017-05-09 08:59:08 +0900 (07e6118) @@ -473,3 +473,11 @@ BEGIN END IF; END; $$; + +-- Add v2 compatible operators to regexp ops for text and varchar +ALTER OPERATOR FAMILY pgroonga.text_regexp_ops USING pgroonga + ADD + OPERATOR 22 &~ (text, text); +ALTER OPERATOR FAMILY pgroonga.varchar_regexp_ops USING pgroonga + ADD + OPERATOR 22 &~ (varchar, varchar); Modified: data/pgroonga.sql (+4 -3) =================================================================== --- data/pgroonga.sql 2017-05-08 21:03:33 +0900 (4146ac6) +++ data/pgroonga.sql 2017-05-09 08:59:08 +0900 (6ed078d) @@ -967,12 +967,13 @@ CREATE OPERATOR CLASS pgroonga.text_regexp_ops FOR TYPE text USING pgroonga AS OPERATOR 6 ~~, OPERATOR 7 ~~*, - OPERATOR 10 @~; + OPERATOR 10 @~, + OPERATOR 22 &~; CREATE OPERATOR CLASS pgroonga.varchar_regexp_ops FOR TYPE varchar USING pgroonga AS - OPERATOR 10 @~; - + OPERATOR 10 @~, + OPERATOR 22 &~; /* v2 */ CREATE OPERATOR CLASS pgroonga.text_full_text_search_ops_v2 FOR TYPE text Added: expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.out (+33 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.out 2017-05-09 08:59:08 +0900 (d87a3ae) @@ -0,0 +1,33 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + QUERY PLAN +---------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ '\Agroonga'::text) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ '\Agroonga'::text) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/indexscan.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/indexscan.out 2017-05-09 08:59:08 +0900 (042ba2b) @@ -0,0 +1,31 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + QUERY PLAN +---------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ '\Agroonga'::text) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/begin-of-text/seqscan.out 2017-05-09 08:59:08 +0900 (654678c) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/dot/bitmapscan.out (+34 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/dot/bitmapscan.out 2017-05-09 08:59:08 +0900 (b03c9bc) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + QUERY PLAN +-------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ '.roonga'::text) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ '.roonga'::text) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/dot/indexscan.out (+32 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/dot/indexscan.out 2017-05-09 08:59:08 +0900 (b9a7b3f) @@ -0,0 +1,32 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + QUERY PLAN +-------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ '.roonga'::text) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/dot/seqscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/dot/seqscan.out 2017-05-09 08:59:08 +0900 (22842ed) @@ -0,0 +1,22 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/bitmapscan.out (+33 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/bitmapscan.out 2017-05-09 08:59:08 +0900 (ff26e4c) @@ -0,0 +1,33 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + QUERY PLAN +---------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ 'groonga\z'::text) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ 'groonga\z'::text) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/indexscan.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/indexscan.out 2017-05-09 08:59:08 +0900 (e805b51) @@ -0,0 +1,31 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + QUERY PLAN +---------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ 'groonga\z'::text) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/end-of-text/seqscan.out 2017-05-09 08:59:08 +0900 (e89976c) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/exact/bitmapscan.out (+33 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/exact/bitmapscan.out 2017-05-09 08:59:08 +0900 (d24a0c2) @@ -0,0 +1,33 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + QUERY PLAN +------------------------------------------------------ + Bitmap Heap Scan on memos + Recheck Cond: (content &~ '\Agroonga\z'::text) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ '\Agroonga\z'::text) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/exact/indexscan.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/exact/indexscan.out 2017-05-09 08:59:08 +0900 (2d9a1e9) @@ -0,0 +1,31 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + QUERY PLAN +------------------------------------------------ + Index Scan using grnindex on memos + Index Cond: (content &~ '\Agroonga\z'::text) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/exact/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/exact/seqscan.out 2017-05-09 08:59:08 +0900 (4d90457) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/partial/bitmapscan.out (+34 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/partial/bitmapscan.out 2017-05-09 08:59:08 +0900 (d1b6cea) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + QUERY PLAN +-------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ 'groonga'::text) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ 'groonga'::text) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/partial/indexscan.out (+32 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/partial/indexscan.out 2017-05-09 08:59:08 +0900 (4dbe148) @@ -0,0 +1,32 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + QUERY PLAN +-------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ 'groonga'::text) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/text/compatibility/v1/regexp-v2/partial/seqscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/compatibility/v1/regexp-v2/partial/seqscan.out 2017-05-09 08:59:08 +0900 (13c6cff) @@ -0,0 +1,22 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.out (+33 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.out 2017-05-09 08:59:08 +0900 (982d9f9) @@ -0,0 +1,33 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + QUERY PLAN +----------------------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ '\Agroonga'::character varying) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ '\Agroonga'::character varying) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/indexscan.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/indexscan.out 2017-05-09 08:59:08 +0900 (d20913a) @@ -0,0 +1,31 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + QUERY PLAN +----------------------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ '\Agroonga'::character varying) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/seqscan.out 2017-05-09 08:59:08 +0900 (94fbc0a) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/dot/bitmapscan.out (+34 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/dot/bitmapscan.out 2017-05-09 08:59:08 +0900 (d1b5f9a) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + QUERY PLAN +--------------------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ '.roonga'::character varying) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ '.roonga'::character varying) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/dot/indexscan.out (+32 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/dot/indexscan.out 2017-05-09 08:59:08 +0900 (784b2e0) @@ -0,0 +1,32 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + QUERY PLAN +--------------------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ '.roonga'::character varying) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/dot/seqscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/dot/seqscan.out 2017-05-09 08:59:08 +0900 (3c8de91) @@ -0,0 +1,22 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/bitmapscan.out (+33 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/bitmapscan.out 2017-05-09 08:59:08 +0900 (4e58171) @@ -0,0 +1,33 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + QUERY PLAN +----------------------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ 'groonga\z'::character varying) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ 'groonga\z'::character varying) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/indexscan.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/indexscan.out 2017-05-09 08:59:08 +0900 (ce3ad0e) @@ -0,0 +1,31 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + QUERY PLAN +----------------------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ 'groonga\z'::character varying) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/seqscan.out 2017-05-09 08:59:08 +0900 (b01d172) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/exact/bitmapscan.out (+33 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/exact/bitmapscan.out 2017-05-09 08:59:08 +0900 (601a3f2) @@ -0,0 +1,33 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + QUERY PLAN +------------------------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ '\Agroonga\z'::character varying) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ '\Agroonga\z'::character varying) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/exact/indexscan.out (+31 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/exact/indexscan.out 2017-05-09 08:59:08 +0900 (65c4fe3) @@ -0,0 +1,31 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + QUERY PLAN +------------------------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ '\Agroonga\z'::character varying) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/exact/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/exact/seqscan.out 2017-05-09 08:59:08 +0900 (67ddc9c) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/partial/bitmapscan.out (+34 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/partial/bitmapscan.out 2017-05-09 08:59:08 +0900 (ebabffe) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + QUERY PLAN +--------------------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (content &~ 'groonga'::character varying) + -> Bitmap Index Scan on grnindex + Index Cond: (content &~ 'groonga'::character varying) +(4 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/partial/indexscan.out (+32 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/partial/indexscan.out 2017-05-09 08:59:08 +0900 (5fef700) @@ -0,0 +1,32 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + QUERY PLAN +--------------------------------------------------------- + Index Scan using grnindex on memos + Index Cond: (content &~ 'groonga'::character varying) +(2 rows) + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: expected/regexp/varchar/compatibility/v1/regexp-v2/partial/seqscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/varchar/compatibility/v1/regexp-v2/partial/seqscan.out 2017-05-09 08:59:08 +0900 (8e93132) @@ -0,0 +1,22 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.sql 2017-05-09 08:59:08 +0900 (029bd01) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/indexscan.sql 2017-05-09 08:59:08 +0900 (b4328a9) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/begin-of-text/seqscan.sql 2017-05-09 08:59:08 +0900 (364c1a8) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/dot/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/dot/bitmapscan.sql 2017-05-09 08:59:08 +0900 (ba9b34b) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/dot/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/dot/indexscan.sql 2017-05-09 08:59:08 +0900 (8b765f5) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/dot/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/dot/seqscan.sql 2017-05-09 08:59:08 +0900 (8985af4) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/bitmapscan.sql 2017-05-09 08:59:08 +0900 (ff5373a) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/indexscan.sql 2017-05-09 08:59:08 +0900 (730c845) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/end-of-text/seqscan.sql 2017-05-09 08:59:08 +0900 (648da0b) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/exact/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/exact/bitmapscan.sql 2017-05-09 08:59:08 +0900 (7e8879d) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/exact/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/exact/indexscan.sql 2017-05-09 08:59:08 +0900 (b913a8e) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/exact/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/exact/seqscan.sql 2017-05-09 08:59:08 +0900 (a6279eb) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/partial/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/partial/bitmapscan.sql 2017-05-09 08:59:08 +0900 (9df3a8d) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/partial/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/partial/indexscan.sql 2017-05-09 08:59:08 +0900 (b0e06aa) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/text/compatibility/v1/regexp-v2/partial/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/compatibility/v1/regexp-v2/partial/seqscan.sql 2017-05-09 08:59:08 +0900 (76c4236) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.text_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/bitmapscan.sql 2017-05-09 08:59:08 +0900 (e6e2ca9) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/indexscan.sql 2017-05-09 08:59:08 +0900 (9a5c6a9) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/begin-of-text/seqscan.sql 2017-05-09 08:59:08 +0900 (23e23fa) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/dot/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/dot/bitmapscan.sql 2017-05-09 08:59:08 +0900 (218b620) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/dot/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/dot/indexscan.sql 2017-05-09 08:59:08 +0900 (4af8e18) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/dot/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/dot/seqscan.sql 2017-05-09 08:59:08 +0900 (a8dfc70) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ '.roonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/bitmapscan.sql 2017-05-09 08:59:08 +0900 (0c87878) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/indexscan.sql 2017-05-09 08:59:08 +0900 (28b99e4) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/end-of-text/seqscan.sql 2017-05-09 08:59:08 +0900 (eff63e9) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/exact/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/exact/bitmapscan.sql 2017-05-09 08:59:08 +0900 (ba85317) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/exact/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/exact/indexscan.sql 2017-05-09 08:59:08 +0900 (26f10a7) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/exact/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/exact/seqscan.sql 2017-05-09 08:59:08 +0900 (b586ba5) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL'); +INSERT INTO memos VALUES (2, 'Groonga'); +INSERT INTO memos VALUES (3, 'PGroonga'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/partial/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/partial/bitmapscan.sql 2017-05-09 08:59:08 +0900 (3c874df) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/partial/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/partial/indexscan.sql 2017-05-09 08:59:08 +0900 (97e30b8) @@ -0,0 +1,26 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/varchar/compatibility/v1/regexp-v2/partial/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/varchar/compatibility/v1/regexp-v2/partial/seqscan.sql 2017-05-09 08:59:08 +0900 (14dc933) @@ -0,0 +1,21 @@ +CREATE TABLE memos ( + id integer, + content varchar(256) +); + +INSERT INTO memos VALUES (1, 'PostgreSQL is a RDBMS.'); +INSERT INTO memos VALUES (2, 'Groonga is fast full text search engine.'); +INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga.'); + +CREATE INDEX grnindex ON memos + USING pgroonga (content pgroonga.varchar_regexp_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content &~ 'groonga'; + +DROP TABLE memos;
