Kouhei Sutou
null+****@clear*****
Tue Oct 13 22:16:57 JST 2015
Kouhei Sutou 2015-10-13 22:16:57 +0900 (Tue, 13 Oct 2015) New Revision: d0b2d675e0a8c570a83484977bd7cf77fa8fc60a https://github.com/pgroonga/pgroonga/commit/d0b2d675e0a8c570a83484977bd7cf77fa8fc60a Message: Support regexp search by @~ TODO: * Document Added files: expected/regexp/text/begin-of-text/bitmapscan.out expected/regexp/text/begin-of-text/indexscan.out expected/regexp/text/begin-of-text/seqscan.out expected/regexp/text/end-of-text/bitmapscan.out expected/regexp/text/end-of-text/indexscan.out expected/regexp/text/end-of-text/seqscan.out expected/regexp/text/exact/bitmapscan.out expected/regexp/text/exact/indexscan.out expected/regexp/text/exact/seqscan.out expected/regexp/text/partial/bitmapscan.out expected/regexp/text/partial/indexscan.out expected/regexp/text/partial/seqscan.out sql/regexp/text/begin-of-text/bitmapscan.sql sql/regexp/text/begin-of-text/indexscan.sql sql/regexp/text/begin-of-text/seqscan.sql sql/regexp/text/end-of-text/bitmapscan.sql sql/regexp/text/end-of-text/indexscan.sql sql/regexp/text/end-of-text/seqscan.sql sql/regexp/text/exact/bitmapscan.sql sql/regexp/text/exact/indexscan.sql sql/regexp/text/exact/seqscan.sql sql/regexp/text/partial/bitmapscan.sql sql/regexp/text/partial/indexscan.sql sql/regexp/text/partial/seqscan.sql Modified files: pgroonga--0.9.0--1.0.0.sql pgroonga.c pgroonga.h pgroonga.sql Added: expected/regexp/text/begin-of-text/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/begin-of-text/bitmapscan.out 2015-10-13 22:16:57 +0900 (37df79d) @@ -0,0 +1,20 @@ +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; +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/begin-of-text/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/begin-of-text/indexscan.out 2015-10-13 22:16:57 +0900 (c524f3d) @@ -0,0 +1,20 @@ +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; +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/begin-of-text/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/begin-of-text/seqscan.out 2015-10-13 22:16:57 +0900 (70ae2cb) @@ -0,0 +1,20 @@ +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/end-of-text/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/end-of-text/bitmapscan.out 2015-10-13 22:16:57 +0900 (bb7e283) @@ -0,0 +1,20 @@ +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; +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/end-of-text/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/end-of-text/indexscan.out 2015-10-13 22:16:57 +0900 (1c511eb) @@ -0,0 +1,20 @@ +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; +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/end-of-text/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/end-of-text/seqscan.out 2015-10-13 22:16:57 +0900 (5169bc6) @@ -0,0 +1,20 @@ +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/exact/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/exact/bitmapscan.out 2015-10-13 22:16:57 +0900 (857f707) @@ -0,0 +1,20 @@ +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; +SELECT id, content + FROM memos + WHERE content @~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/exact/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/exact/indexscan.out 2015-10-13 22:16:57 +0900 (51a38e3) @@ -0,0 +1,20 @@ +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; +SELECT id, content + FROM memos + WHERE content @~ '\Agroonga\z'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/exact/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/exact/seqscan.out 2015-10-13 22:16:57 +0900 (5eec69d) @@ -0,0 +1,20 @@ +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/partial/bitmapscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/partial/bitmapscan.out 2015-10-13 22:16:57 +0900 (0c656c6) @@ -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 = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +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/partial/indexscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/partial/indexscan.out 2015-10-13 22:16:57 +0900 (6032d94) @@ -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 = off; +SET enable_indexscan = on; +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/text/partial/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/partial/seqscan.out 2015-10-13 22:16:57 +0900 (ad9397b) @@ -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'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. +(2 rows) + +DROP TABLE memos; Modified: pgroonga--0.9.0--1.0.0.sql (+39 -0) =================================================================== --- pgroonga--0.9.0--1.0.0.sql 2015-10-08 23:13:18 +0900 (8cadded) +++ pgroonga--0.9.0--1.0.0.sql 2015-10-13 22:16:57 +0900 (b47c389) @@ -1,3 +1,4 @@ +-- Support index only scan CREATE FUNCTION pgroonga.canreturn(internal) RETURNS internal AS 'MODULE_PATHNAME', 'pgroonga_canreturn' @@ -6,3 +7,41 @@ CREATE FUNCTION pgroonga.canreturn(internal) UPDATE pg_catalog.pg_am SET amcanreturn = 'pgroonga.canreturn' WHERE amname = 'pgroonga'; + + +-- Support @~ +UPDATE pg_catalog.pg_am SET amstrategies = 10 WHERE amname = 'pgroonga'; + +CREATE FUNCTION pgroonga.match_regexp(text, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_regexp_text' + LANGUAGE C + IMMUTABLE + STRICT; + +CREATE FUNCTION pgroonga.match_regexp(varchar, varchar) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_regexp_varchar' + LANGUAGE C + IMMUTABLE + STRICT; + +CREATE OPERATOR @~ ( + PROCEDURE = pgroonga.match_regexp, + LEFTARG = text, + RIGHTARG = text +); + +CREATE OPERATOR @~ ( + PROCEDURE = pgroonga.match_regexp, + LEFTARG = varchar, + RIGHTARG = varchar +); + +CREATE OPERATOR CLASS pgroonga.text_regexp_ops FOR TYPE text + USING pgroonga AS + OPERATOR 10 @~; + +CREATE OPERATOR CLASS pgroonga.varchar_regexp_ops FOR TYPE varchar + USING pgroonga AS + OPERATOR 10 @~; Modified: pgroonga.c (+89 -3) =================================================================== --- pgroonga.c 2015-10-08 23:13:18 +0900 (9a2a27b) +++ pgroonga.c 2015-10-13 22:16:57 +0900 (058caf7) @@ -109,6 +109,7 @@ typedef struct PGrnCreateData TupleDesc desc; Oid relNode; bool forFullTextSearch; + bool forRegexpSearch; grn_id attributeTypeID; unsigned char attributeFlags; } PGrnCreateData; @@ -174,6 +175,8 @@ PG_FUNCTION_INFO_V1(pgroonga_contain_text_array); PG_FUNCTION_INFO_V1(pgroonga_contain_varchar); PG_FUNCTION_INFO_V1(pgroonga_contain_varchar_array); PG_FUNCTION_INFO_V1(pgroonga_match); +PG_FUNCTION_INFO_V1(pgroonga_match_regexp_text); +PG_FUNCTION_INFO_V1(pgroonga_match_regexp_varchar); PG_FUNCTION_INFO_V1(pgroonga_insert); PG_FUNCTION_INFO_V1(pgroonga_beginscan); @@ -1197,6 +1200,22 @@ PGrnIsForFullTextSearchIndex(Relation index, int nthAttribute) return OidIsValid(queryStrategyOID); } +static bool +PGrnIsForRegexpSearchIndex(Relation index, int nthAttribute) +{ + Oid regexpStrategyOID; + Oid leftType; + Oid rightType; + + leftType = index->rd_opcintype[nthAttribute]; + rightType = leftType; + regexpStrategyOID = get_opfamily_member(index->rd_opfamily[nthAttribute], + leftType, + rightType, + PGrnRegexpStrategyNumber); + return OidIsValid(regexpStrategyOID); +} + static void PGrnCreateSourcesCtidColumn(PGrnCreateData *data) { @@ -1447,12 +1466,18 @@ PGrnCreateIndexColumn(PGrnCreateData *data) grn_ctx_at(ctx, typeID)); GRN_PTR_PUT(ctx, data->lexicons, lexicon); - if (data->forFullTextSearch) + if (data->forFullTextSearch || data->forRegexpSearch) { PGrnOptions *options; - const char *tokenizerName = PGRN_DEFAULT_TOKENIZER; + const char *tokenizerName; const char *normalizerName = PGRN_DEFAULT_NORMALIZER; + if (data->forRegexpSearch) { + tokenizerName = "TokenRegexp"; + } else { + tokenizerName = PGRN_DEFAULT_TOKENIZER; + } + options = (PGrnOptions *) (data->index->rd_options); if (options) { @@ -1473,7 +1498,7 @@ PGrnCreateIndexColumn(PGrnCreateData *data) { grn_obj_flags flags = GRN_OBJ_COLUMN_INDEX; - if (data->forFullTextSearch) + if (data->forFullTextSearch || data->forRegexpSearch) flags |= GRN_OBJ_WITH_POSITION; PGrnCreateColumn(lexicon, PGrnIndexColumnName, @@ -1526,6 +1551,7 @@ PGrnCreate(Relation index, PGrnCreateDataColumnsForJSON(&data); PGrnCreateIndexColumnsForJSON(&data); data.forFullTextSearch = false; + data.forRegexpSearch = false; data.attributeTypeID = grn_obj_id(ctx, data.jsonValuesTable); data.attributeFlags = GRN_OBJ_VECTOR; PGrnCreateDataColumn(&data); @@ -1534,6 +1560,7 @@ PGrnCreate(Relation index, #endif { data.forFullTextSearch = PGrnIsForFullTextSearchIndex(index, data.i); + data.forRegexpSearch = PGrnIsForRegexpSearchIndex(index, data.i); data.attributeTypeID = PGrnGetType(index, data.i, &(data.attributeFlags)); PGrnCreateDataColumn(&data); @@ -2267,6 +2294,62 @@ pgroonga_match_jsonb(PG_FUNCTION_ARGS) PG_RETURN_BOOL(false); } +static grn_bool +pgroonga_match_regexp_raw(const char *text, unsigned int textSize, + const char *pattern, unsigned int patternSize) +{ + grn_bool matched; + grn_obj targetBuffer; + grn_obj patternBuffer; + + GRN_TEXT_INIT(&targetBuffer, GRN_OBJ_DO_SHALLOW_COPY); + GRN_TEXT_SET(ctx, &targetBuffer, text, textSize); + + GRN_TEXT_INIT(&patternBuffer, GRN_OBJ_DO_SHALLOW_COPY); + GRN_TEXT_SET(ctx, &patternBuffer, pattern, patternSize); + + matched = grn_operator_exec_regexp(ctx, &targetBuffer, &patternBuffer); + + GRN_OBJ_FIN(ctx, &targetBuffer); + GRN_OBJ_FIN(ctx, &patternBuffer); + + return matched; +} + +/** + * pgroonga.match_regexp_text(target, pattern) : bool + */ +Datum +pgroonga_match_regexp_text(PG_FUNCTION_ARGS) +{ + text *target = PG_GETARG_TEXT_PP(0); + text *pattern = PG_GETARG_TEXT_PP(1); + grn_bool matched; + + matched = pgroonga_match_regexp_raw(VARDATA_ANY(target), + VARSIZE_ANY_EXHDR(target), + VARDATA_ANY(pattern), + VARSIZE_ANY_EXHDR(pattern)); + PG_RETURN_BOOL(matched); +} + +/** + * pgroonga.match_regexp_varchar(target, pattern) : bool + */ +Datum +pgroonga_match_regexp_varchar(PG_FUNCTION_ARGS) +{ + VarChar *target = PG_GETARG_VARCHAR_PP(0); + VarChar *pattern = PG_GETARG_VARCHAR_PP(1); + grn_bool matched; + + matched = pgroonga_match_regexp_raw(VARDATA_ANY(target), + VARSIZE_ANY_EXHDR(target), + VARDATA_ANY(pattern), + VARSIZE_ANY_EXHDR(pattern)); + PG_RETURN_BOOL(matched); +} + #ifdef JSONBOID typedef struct PGrnInsertJSONData { @@ -3251,6 +3334,9 @@ PGrnSearchBuildCondition(IndexScanDesc scan, break; case PGrnQueryStrategyNumber: break; + case PGrnRegexpStrategyNumber: + operator = GRN_OP_REGEXP; + break; default: ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), Modified: pgroonga.h (+1 -0) =================================================================== --- pgroonga.h 2015-10-08 23:13:18 +0900 (2239bac) +++ pgroonga.h 2015-10-13 22:16:57 +0900 (469c2ae) @@ -22,6 +22,7 @@ #define PGrnContainStrategyNumber 7 /* operator %% (@ in Groonga) */ #define PGrnQueryStrategyNumber 8 /* operator @@ (Groonga query) */ #define PGrnJSONContainStrategyNumber 9 /* operator @> */ +#define PGrnRegexpStrategyNumber 10 /* operator @~ (@~ in Groonga) */ /* file and table names */ #define PGrnLogBasename "pgroonga.log" Modified: pgroonga.sql (+36 -1) =================================================================== --- pgroonga.sql 2015-10-08 23:13:18 +0900 (b15ea6b) +++ pgroonga.sql 2015-10-13 22:16:57 +0900 (58abd8b) @@ -123,6 +123,33 @@ CREATE OPERATOR @@ ( ); +CREATE FUNCTION pgroonga.match_regexp(text, text) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_regexp_text' + LANGUAGE C + IMMUTABLE + STRICT; + +CREATE FUNCTION pgroonga.match_regexp(varchar, varchar) + RETURNS bool + AS 'MODULE_PATHNAME', 'pgroonga_match_regexp_varchar' + LANGUAGE C + IMMUTABLE + STRICT; + +CREATE OPERATOR @~ ( + PROCEDURE = pgroonga.match_regexp, + LEFTARG = text, + RIGHTARG = text +); + +CREATE OPERATOR @~ ( + PROCEDURE = pgroonga.match_regexp, + LEFTARG = varchar, + RIGHTARG = varchar +); + + CREATE FUNCTION pgroonga.insert(internal) RETURNS bool AS 'MODULE_PATHNAME', 'pgroonga_insert' @@ -179,7 +206,7 @@ CREATE FUNCTION pgroonga.options(internal) DELETE FROM pg_catalog.pg_am WHERE amname = 'pgroonga'; INSERT INTO pg_catalog.pg_am VALUES( 'pgroonga', -- amname - 9, -- amstrategies + 10, -- amstrategies 0, -- amsupport true, -- amcanorder true, -- amcanorderbyop @@ -334,3 +361,11 @@ BEGIN END IF; END; $$; + +CREATE OPERATOR CLASS pgroonga.text_regexp_ops FOR TYPE text + USING pgroonga AS + OPERATOR 10 @~; + +CREATE OPERATOR CLASS pgroonga.varchar_regexp_ops FOR TYPE varchar + USING pgroonga AS + OPERATOR 10 @~; Added: sql/regexp/text/begin-of-text/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/begin-of-text/bitmapscan.sql 2015-10-13 22:16:57 +0900 (511d07f) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/text/begin-of-text/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/begin-of-text/indexscan.sql 2015-10-13 22:16:57 +0900 (5e9323f) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ '\Agroonga'; + +DROP TABLE memos; Added: sql/regexp/text/begin-of-text/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/begin-of-text/seqscan.sql 2015-10-13 22:16:57 +0900 (996981a) @@ -0,0 +1,20 @@ +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/end-of-text/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/end-of-text/bitmapscan.sql 2015-10-13 22:16:57 +0900 (66eada8) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/end-of-text/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/end-of-text/indexscan.sql 2015-10-13 22:16:57 +0900 (a140f3a) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ 'groonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/end-of-text/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/end-of-text/seqscan.sql 2015-10-13 22:16:57 +0900 (5eff825) @@ -0,0 +1,20 @@ +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/exact/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/exact/bitmapscan.sql 2015-10-13 22:16:57 +0900 (3d6034d) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/exact/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/exact/indexscan.sql 2015-10-13 22:16:57 +0900 (fb57fe9) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ '\Agroonga\z'; + +DROP TABLE memos; Added: sql/regexp/text/exact/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/exact/seqscan.sql 2015-10-13 22:16:57 +0900 (5442233) @@ -0,0 +1,20 @@ +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/partial/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/partial/bitmapscan.sql 2015-10-13 22:16:57 +0900 (ee2da4d) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/text/partial/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/partial/indexscan.sql 2015-10-13 22:16:57 +0900 (316531d) @@ -0,0 +1,20 @@ +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; + +SELECT id, content + FROM memos + WHERE content @~ 'groonga'; + +DROP TABLE memos; Added: sql/regexp/text/partial/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/partial/seqscan.sql 2015-10-13 22:16:57 +0900 (108494f) @@ -0,0 +1,20 @@ +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; -------------- next part -------------- HTML����������������������������...Download