Kouhei Sutou
null+****@clear*****
Mon May 1 19:28:50 JST 2017
Kouhei Sutou 2017-05-01 19:28:50 +0900 (Mon, 01 May 2017) New Revision: d8ae4f3d7360b7b457d1803998e6a4e761036955 https://github.com/pgroonga/pgroonga/commit/d8ae4f3d7360b7b457d1803998e6a4e761036955 Message: Support @@ and %% in v2 full text search operator class Added files: data/pgroonga--1.2.0--1.2.1.sql expected/full-text-search/text/single/compatibility/v2/match-v1/bitmapscan.out expected/full-text-search/text/single/compatibility/v2/match-v1/indexscan.out expected/full-text-search/text/single/compatibility/v2/match-v1/seqscan.out expected/full-text-search/text/single/compatibility/v2/query-v1/bitmapscan.out expected/full-text-search/text/single/compatibility/v2/query-v1/indexscan.out expected/full-text-search/text/single/compatibility/v2/query-v1/seqscan.out sql/full-text-search/text/single/compatibility/v2/match-v1/bitmapscan.sql sql/full-text-search/text/single/compatibility/v2/match-v1/indexscan.sql sql/full-text-search/text/single/compatibility/v2/match-v1/seqscan.sql sql/full-text-search/text/single/compatibility/v2/query-v1/bitmapscan.sql sql/full-text-search/text/single/compatibility/v2/query-v1/indexscan.sql sql/full-text-search/text/single/compatibility/v2/query-v1/seqscan.sql Modified files: data/pgroonga.sql Added: data/pgroonga--1.2.0--1.2.1.sql (+4 -0) 100644 =================================================================== --- /dev/null +++ data/pgroonga--1.2.0--1.2.1.sql 2017-05-01 19:28:50 +0900 (c48c249) @@ -0,0 +1,4 @@ +ALTER OPERATOR FAMILY pgroonga.text_full_text_search_ops_v2 USING pgroonga + ADD + OPERATOR 8 %% (text, text), + OPERATOR 9 @@ (text, text); Modified: data/pgroonga.sql (+2 -0) =================================================================== --- data/pgroonga.sql 2017-05-01 19:06:38 +0900 (4c2b204) +++ data/pgroonga.sql 2017-05-01 19:28:50 +0900 (8f0a0c8) @@ -656,6 +656,8 @@ CREATE OPERATOR CLASS pgroonga.text_full_text_search_ops_v2 FOR TYPE text USING pgroonga AS OPERATOR 6 ~~, OPERATOR 7 ~~*, + OPERATOR 8 %%, -- For backward compatibility + OPERATOR 9 @@, -- For backward compatibility OPERATOR 12 &@, OPERATOR 13 &?, OPERATOR 14 &~?, Added: expected/full-text-search/text/single/compatibility/v2/match-v1/bitmapscan.out (+34 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/compatibility/v2/match-v1/bitmapscan.out 2017-05-01 19:28:50 +0900 (6554bcf) @@ -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_full_text_search_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN +SELECT id, content + FROM memos + WHERE content %% 'Groonga'; + QUERY PLAN +----------------------------------------------------------------------- + Bitmap Heap Scan on memos (cost=0.00..4.01 rows=2 width=36) + Recheck Cond: (content %% 'Groonga'::text) + -> Bitmap Index Scan on grnindex (cost=0.00..0.00 rows=1 width=0) + 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/full-text-search/text/single/compatibility/v2/match-v1/indexscan.out (+32 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/compatibility/v2/match-v1/indexscan.out 2017-05-01 19:28:50 +0900 (f4ded27) @@ -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_full_text_search_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN +SELECT id, content + FROM memos + WHERE content %% 'Groonga'; + QUERY PLAN +----------------------------------------------------------------------- + Index Scan using grnindex on memos (cost=0.00..4.01 rows=2 width=36) + 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/full-text-search/text/single/compatibility/v2/match-v1/seqscan.out (+22 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/compatibility/v2/match-v1/seqscan.out 2017-05-01 19:28:50 +0900 (24c78d3) @@ -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_full_text_search_ops_v2); +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/full-text-search/text/single/compatibility/v2/query-v1/bitmapscan.out (+34 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/compatibility/v2/query-v1/bitmapscan.out 2017-05-01 19:28:50 +0900 (8156288) @@ -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_full_text_search_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + QUERY PLAN +----------------------------------------------------------------------- + Bitmap Heap Scan on memos (cost=0.00..4.26 rows=1 width=36) + Recheck Cond: (content @@ 'rdbms OR engine'::text) + -> Bitmap Index Scan on grnindex (cost=0.00..0.00 rows=1 width=0) + Index Cond: (content @@ 'rdbms OR engine'::text) +(4 rows) + +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + id | content +----+------------------------------------------ + 1 | PostgreSQL is a RDBMS. + 2 | Groonga is fast full text search engine. +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text/single/compatibility/v2/query-v1/indexscan.out (+32 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/compatibility/v2/query-v1/indexscan.out 2017-05-01 19:28:50 +0900 (058379c) @@ -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_full_text_search_ops_v2); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + QUERY PLAN +----------------------------------------------------------------------- + Index Scan using grnindex on memos (cost=0.00..4.01 rows=1 width=36) + Index Cond: (content @@ 'rdbms OR engine'::text) +(2 rows) + +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + id | content +----+------------------------------------------ + 1 | PostgreSQL is a RDBMS. + 2 | Groonga is fast full text search engine. +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text/single/compatibility/v2/query-v1/seqscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/compatibility/v2/query-v1/seqscan.out 2017-05-01 19:28:50 +0900 (b8578bf) @@ -0,0 +1,23 @@ +SET search_path = "$user",public,pgroonga,pg_catalog; +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_full_text_search_ops_v2); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + id | content +----+------------------------------------------ + 1 | PostgreSQL is a RDBMS. + 2 | Groonga is fast full text search engine. +(2 rows) + +DROP TABLE memos; Added: sql/full-text-search/text/single/compatibility/v2/match-v1/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/compatibility/v2/match-v1/bitmapscan.sql 2017-05-01 19:28:50 +0900 (01f7211) @@ -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_full_text_search_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN +SELECT id, content + FROM memos + WHERE content %% 'Groonga'; + +SELECT id, content + FROM memos + WHERE content %% 'Groonga'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/compatibility/v2/match-v1/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/compatibility/v2/match-v1/indexscan.sql 2017-05-01 19:28:50 +0900 (f30f4e8) @@ -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_full_text_search_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN +SELECT id, content + FROM memos + WHERE content %% 'Groonga'; + +SELECT id, content + FROM memos + WHERE content %% 'Groonga'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/compatibility/v2/match-v1/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/compatibility/v2/match-v1/seqscan.sql 2017-05-01 19:28:50 +0900 (30b1fc7) @@ -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_full_text_search_ops_v2); + +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/full-text-search/text/single/compatibility/v2/query-v1/bitmapscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/compatibility/v2/query-v1/bitmapscan.sql 2017-05-01 19:28:50 +0900 (3757dbf) @@ -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_full_text_search_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/compatibility/v2/query-v1/indexscan.sql (+26 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/compatibility/v2/query-v1/indexscan.sql 2017-05-01 19:28:50 +0900 (c579536) @@ -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_full_text_search_ops_v2); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/compatibility/v2/query-v1/seqscan.sql (+23 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/compatibility/v2/query-v1/seqscan.sql 2017-05-01 19:28:50 +0900 (20bdfec) @@ -0,0 +1,23 @@ +SET search_path = "$user",public,pgroonga,pg_catalog; + +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_full_text_search_ops_v2); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content @@ 'rdbms OR engine'; + +DROP TABLE memos; -------------- next part -------------- HTML����������������������������...Download