Kouhei Sutou
null+****@clear*****
Tue May 2 12:50:09 JST 2017
Kouhei Sutou 2017-05-02 12:50:09 +0900 (Tue, 02 May 2017) New Revision: 66485dcc8dfc738e5610e578ac436ce88648a56a https://github.com/pgroonga/pgroonga/commit/66485dcc8dfc738e5610e578ac436ce88648a56a Message: Add v2 operators for text[] to v1 operator class Added files: expected/full-text-search/text-array/single/compatibility/v1/match-v2/bitmapscan.out expected/full-text-search/text-array/single/compatibility/v1/match-v2/indexscan.out expected/full-text-search/text-array/single/compatibility/v1/match-v2/seqscan.out expected/full-text-search/text-array/single/compatibility/v1/query-v2/bitmapscan.out expected/full-text-search/text-array/single/compatibility/v1/query-v2/indexscan.out expected/full-text-search/text-array/single/compatibility/v1/query-v2/seqscan.out sql/full-text-search/text-array/single/compatibility/v1/match-v2/bitmapscan.sql sql/full-text-search/text-array/single/compatibility/v1/match-v2/indexscan.sql sql/full-text-search/text-array/single/compatibility/v1/match-v2/seqscan.sql sql/full-text-search/text-array/single/compatibility/v1/query-v2/bitmapscan.sql sql/full-text-search/text-array/single/compatibility/v1/query-v2/indexscan.sql sql/full-text-search/text-array/single/compatibility/v1/query-v2/seqscan.sql Modified files: data/pgroonga.sql Modified: data/pgroonga.sql (+3 -1) =================================================================== --- data/pgroonga.sql 2017-05-02 11:39:34 +0900 (ecea3f1) +++ data/pgroonga.sql 2017-05-02 12:50:09 +0900 (69cca0a) @@ -605,7 +605,9 @@ CREATE OPERATOR CLASS pgroonga.text_array_full_text_search_ops FOR TYPE text[] USING pgroonga AS OPERATOR 8 %% (text[], text), - OPERATOR 9 @@ (text[], text); + OPERATOR 9 @@ (text[], text), + OPERATOR 12 &@ (text[], text), + OPERATOR 13 &? (text[], text); CREATE OPERATOR CLASS pgroonga.varchar_full_text_search_ops FOR TYPE varchar USING pgroonga AS Added: expected/full-text-search/text-array/single/compatibility/v1/match-v2/bitmapscan.out (+42 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text-array/single/compatibility/v1/match-v2/bitmapscan.out 2017-05-02 12:50:09 +0900 (e343ff6) @@ -0,0 +1,42 @@ +CREATE TABLE memos ( + title text, + contents text[] +); +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + QUERY PLAN +--------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (contents &@ 'Groonga'::text) + -> Bitmap Index Scan on pgroonga_memos_index + Index Cond: (contents &@ 'Groonga'::text) +(4 rows) + +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + title | contents +----------+-------------------------------------------------------------------------------------------------------------------------- + Groonga | {"Groonga is an OSS full-text search engine","Groonga has full full-text search support"} + PGroonga | {"PGroonga is an OSS PostgreSQL extension","PGroonga adds full full-text search support based on Groonga to PostgreSQL"} +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text-array/single/compatibility/v1/match-v2/indexscan.out (+40 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text-array/single/compatibility/v1/match-v2/indexscan.out 2017-05-02 12:50:09 +0900 (e28afb5) @@ -0,0 +1,40 @@ +CREATE TABLE memos ( + title text, + contents text[] +); +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + QUERY PLAN +------------------------------------------------ + Index Scan using pgroonga_memos_index on memos + Index Cond: (contents &@ 'Groonga'::text) +(2 rows) + +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + title | contents +----------+-------------------------------------------------------------------------------------------------------------------------- + Groonga | {"Groonga is an OSS full-text search engine","Groonga has full full-text search support"} + PGroonga | {"PGroonga is an OSS PostgreSQL extension","PGroonga adds full full-text search support based on Groonga to PostgreSQL"} +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text-array/single/compatibility/v1/match-v2/seqscan.out (+30 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text-array/single/compatibility/v1/match-v2/seqscan.out 2017-05-02 12:50:09 +0900 (091842b) @@ -0,0 +1,30 @@ +CREATE TABLE memos ( + title text, + contents text[] +); +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + title | contents +----------+-------------------------------------------------------------------------------------------------------------------------- + Groonga | {"Groonga is an OSS full-text search engine","Groonga has full full-text search support"} + PGroonga | {"PGroonga is an OSS PostgreSQL extension","PGroonga adds full full-text search support based on Groonga to PostgreSQL"} +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text-array/single/compatibility/v1/query-v2/bitmapscan.out (+42 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text-array/single/compatibility/v1/query-v2/bitmapscan.out 2017-05-02 12:50:09 +0900 (fa3b330) @@ -0,0 +1,42 @@ +CREATE TABLE memos ( + title text, + contents text[] +); +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + QUERY PLAN +----------------------------------------------------------- + Bitmap Heap Scan on memos + Recheck Cond: (contents &? 'rdbms OR engine'::text) + -> Bitmap Index Scan on pgroonga_memos_index + Index Cond: (contents &? 'rdbms OR engine'::text) +(4 rows) + +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + title | contents +------------+------------------------------------------------------------------------------------------- + PostgreSQL | {"PostgreSQL is an OSS RDBMS","PostgreSQL has partial full-text search support"} + Groonga | {"Groonga is an OSS full-text search engine","Groonga has full full-text search support"} +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text-array/single/compatibility/v1/query-v2/indexscan.out (+40 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text-array/single/compatibility/v1/query-v2/indexscan.out 2017-05-02 12:50:09 +0900 (fe3f85e) @@ -0,0 +1,40 @@ +CREATE TABLE memos ( + title text, + contents text[] +); +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + QUERY PLAN +----------------------------------------------------- + Index Scan using pgroonga_memos_index on memos + Index Cond: (contents &? 'rdbms OR engine'::text) +(2 rows) + +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + title | contents +------------+------------------------------------------------------------------------------------------- + PostgreSQL | {"PostgreSQL is an OSS RDBMS","PostgreSQL has partial full-text search support"} + Groonga | {"Groonga is an OSS full-text search engine","Groonga has full full-text search support"} +(2 rows) + +DROP TABLE memos; Added: expected/full-text-search/text-array/single/compatibility/v1/query-v2/seqscan.out (+30 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text-array/single/compatibility/v1/query-v2/seqscan.out 2017-05-02 12:50:09 +0900 (56921aa) @@ -0,0 +1,30 @@ +CREATE TABLE memos ( + title text, + contents text[] +); +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + title | contents +------------+------------------------------------------------------------------------------------------- + PostgreSQL | {"PostgreSQL is an OSS RDBMS","PostgreSQL has partial full-text search support"} + Groonga | {"Groonga is an OSS full-text search engine","Groonga has full full-text search support"} +(2 rows) + +DROP TABLE memos; Added: sql/full-text-search/text-array/single/compatibility/v1/match-v2/bitmapscan.sql (+34 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text-array/single/compatibility/v1/match-v2/bitmapscan.sql 2017-05-02 12:50:09 +0900 (ce26502) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + title text, + contents text[] +); + +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); + +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + +DROP TABLE memos; Added: sql/full-text-search/text-array/single/compatibility/v1/match-v2/indexscan.sql (+34 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text-array/single/compatibility/v1/match-v2/indexscan.sql 2017-05-02 12:50:09 +0900 (bda19bc) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + title text, + contents text[] +); + +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); + +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + +DROP TABLE memos; Added: sql/full-text-search/text-array/single/compatibility/v1/match-v2/seqscan.sql (+29 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text-array/single/compatibility/v1/match-v2/seqscan.sql 2017-05-02 12:50:09 +0900 (eacc789) @@ -0,0 +1,29 @@ +CREATE TABLE memos ( + title text, + contents text[] +); + +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); + +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT title, contents + FROM memos + WHERE contents &@ 'Groonga'; + +DROP TABLE memos; Added: sql/full-text-search/text-array/single/compatibility/v1/query-v2/bitmapscan.sql (+34 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text-array/single/compatibility/v1/query-v2/bitmapscan.sql 2017-05-02 12:50:09 +0900 (3a1a294) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + title text, + contents text[] +); + +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); + +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + +DROP TABLE memos; Added: sql/full-text-search/text-array/single/compatibility/v1/query-v2/indexscan.sql (+34 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text-array/single/compatibility/v1/query-v2/indexscan.sql 2017-05-02 12:50:09 +0900 (6574103) @@ -0,0 +1,34 @@ +CREATE TABLE memos ( + title text, + contents text[] +); + +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); + +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +EXPLAIN (COSTS OFF) +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + +DROP TABLE memos; Added: sql/full-text-search/text-array/single/compatibility/v1/query-v2/seqscan.sql (+29 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text-array/single/compatibility/v1/query-v2/seqscan.sql 2017-05-02 12:50:09 +0900 (e399c31) @@ -0,0 +1,29 @@ +CREATE TABLE memos ( + title text, + contents text[] +); + +INSERT INTO memos + VALUES ('PostgreSQL', + ARRAY['PostgreSQL is an OSS RDBMS', + 'PostgreSQL has partial full-text search support']); +INSERT INTO memos + VALUES ('Groonga', ARRAY['Groonga is an OSS full-text search engine', + 'Groonga has full full-text search support']); +INSERT INTO memos + VALUES ('PGroonga', + ARRAY['PGroonga is an OSS PostgreSQL extension', + 'PGroonga adds full full-text search support based on Groonga to PostgreSQL']); + +CREATE INDEX pgroonga_memos_index ON memos + USING pgroonga (contents pgroonga.text_array_full_text_search_ops); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT title, contents + FROM memos + WHERE contents &? 'rdbms OR engine'; + +DROP TABLE memos; -------------- next part -------------- HTML����������������������������...Télécharger
