Kouhei Sutou
null+****@clear*****
Wed Oct 14 18:31:35 JST 2015
Kouhei Sutou 2015-10-14 18:31:35 +0900 (Wed, 14 Oct 2015) New Revision: c8cff3f4bdd1586eb320d7b8f96384a9e9076759 https://github.com/pgroonga/pgroonga/commit/c8cff3f4bdd1586eb320d7b8f96384a9e9076759 Message: Support LIKE by pgroonga.text_regexp_ops Added files: expected/regexp/text/like/begin-of-text/bitmapscan.out expected/regexp/text/like/begin-of-text/indexscan.out expected/regexp/text/like/begin-of-text/seqscan.out expected/regexp/text/like/end-of-text/bitmapscan.out expected/regexp/text/like/end-of-text/indexscan.out expected/regexp/text/like/end-of-text/seqscan.out expected/regexp/text/like/exact/bitmapscan.out expected/regexp/text/like/exact/indexscan.out expected/regexp/text/like/exact/seqscan.out expected/regexp/text/like/partial/bitmapscan.out expected/regexp/text/like/partial/indexscan.out expected/regexp/text/like/partial/seqscan.out sql/regexp/text/like/begin-of-text/bitmapscan.sql sql/regexp/text/like/begin-of-text/indexscan.sql sql/regexp/text/like/begin-of-text/seqscan.sql sql/regexp/text/like/end-of-text/bitmapscan.sql sql/regexp/text/like/end-of-text/indexscan.sql sql/regexp/text/like/end-of-text/seqscan.sql sql/regexp/text/like/exact/bitmapscan.sql sql/regexp/text/like/exact/indexscan.sql sql/regexp/text/like/exact/seqscan.sql sql/regexp/text/like/partial/bitmapscan.sql sql/regexp/text/like/partial/indexscan.sql sql/regexp/text/like/partial/seqscan.sql Modified files: pgroonga--0.9.0--1.0.0.sql pgroonga.c pgroonga.sql Added: expected/regexp/text/like/begin-of-text/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/begin-of-text/bitmapscan.out 2015-10-14 18:31:35 +0900 (9d24832) @@ -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 LIKE 'Groonga%'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/begin-of-text/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/begin-of-text/indexscan.out 2015-10-14 18:31:35 +0900 (5f2b916) @@ -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 LIKE 'Groonga%'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/begin-of-text/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/begin-of-text/seqscan.out 2015-10-14 18:31:35 +0900 (760328e) @@ -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 LIKE 'Groonga%'; + id | content +----+------------------------------------------ + 2 | Groonga is fast full text search engine. +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/end-of-text/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/end-of-text/bitmapscan.out 2015-10-14 18:31:35 +0900 (beb001d) @@ -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 LIKE '%Groonga'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/end-of-text/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/end-of-text/indexscan.out 2015-10-14 18:31:35 +0900 (297fe20) @@ -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 LIKE '%Groonga'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/end-of-text/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/end-of-text/seqscan.out 2015-10-14 18:31:35 +0900 (8c7550c) @@ -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 LIKE '%Groonga'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/exact/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/exact/bitmapscan.out 2015-10-14 18:31:35 +0900 (b17401e) @@ -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 LIKE 'Groonga'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/exact/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/exact/indexscan.out 2015-10-14 18:31:35 +0900 (f5f3676) @@ -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 LIKE 'Groonga'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/exact/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/exact/seqscan.out 2015-10-14 18:31:35 +0900 (1c83e66) @@ -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 LIKE 'Groonga'; + id | content +----+--------- + 2 | Groonga +(1 row) + +DROP TABLE memos; Added: expected/regexp/text/like/partial/bitmapscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/partial/bitmapscan.out 2015-10-14 18:31:35 +0900 (66f9750) @@ -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 LIKE '%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/like/partial/indexscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/partial/indexscan.out 2015-10-14 18:31:35 +0900 (9bfb42f) @@ -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 LIKE '%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/like/partial/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/regexp/text/like/partial/seqscan.out 2015-10-14 18:31:35 +0900 (13a3efd) @@ -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 LIKE '%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 (+1 -0) =================================================================== --- pgroonga--0.9.0--1.0.0.sql 2015-10-14 17:41:37 +0900 (b47c389) +++ pgroonga--0.9.0--1.0.0.sql 2015-10-14 18:31:35 +0900 (51ae786) @@ -40,6 +40,7 @@ CREATE OPERATOR @~ ( CREATE OPERATOR CLASS pgroonga.text_regexp_ops FOR TYPE text USING pgroonga AS + OPERATOR 6 pg_catalog.~~, OPERATOR 10 @~; CREATE OPERATOR CLASS pgroonga.varchar_regexp_ops FOR TYPE varchar Modified: pgroonga.c (+128 -4) =================================================================== --- pgroonga.c 2015-10-14 17:41:37 +0900 (058caf7) +++ pgroonga.c 2015-10-14 18:31:35 +0900 (66c39b1) @@ -196,6 +196,7 @@ static grn_ctx grnContext; static grn_ctx *ctx = NULL; static grn_obj buffer; static grn_obj pathBuffer; +static grn_obj patternBuffer; static grn_obj ctidBuffer; static grn_obj scoreBuffer; static grn_obj headBuffer; @@ -427,6 +428,7 @@ PGrnOnProcExit(int code, Datum arg) GRN_OBJ_FIN(ctx, &headBuffer); GRN_OBJ_FIN(ctx, &ctidBuffer); GRN_OBJ_FIN(ctx, &scoreBuffer); + GRN_OBJ_FIN(ctx, &patternBuffer); GRN_OBJ_FIN(ctx, &pathBuffer); GRN_OBJ_FIN(ctx, &buffer); @@ -576,6 +578,7 @@ _PG_init(void) GRN_VOID_INIT(&buffer); GRN_TEXT_INIT(&pathBuffer, 0); + GRN_TEXT_INIT(&patternBuffer, 0); GRN_FLOAT_INIT(&scoreBuffer, 0); GRN_UINT64_INIT(&ctidBuffer, 0); GRN_TEXT_INIT(&headBuffer, 0); @@ -2994,9 +2997,9 @@ pgroonga_beginscan(PG_FUNCTION_ARGS) } static void -PGrnSearchBuildConditionLike(PGrnSearchData *data, - grn_obj *matchTarget, - grn_obj *query) +PGrnSearchBuildConditionLikeMatch(PGrnSearchData *data, + grn_obj *matchTarget, + grn_obj *query) { grn_obj *expression; const char *queryRaw; @@ -3025,6 +3028,124 @@ PGrnSearchBuildConditionLike(PGrnSearchData *data, grn_expr_append_op(ctx, expression, GRN_OP_MATCH, 2); } +static void +PGrnSearchBuildConditionLikeRegexp(PGrnSearchData *data, + grn_obj *matchTarget, + grn_obj *query) +{ + grn_obj *expression; + const char *queryRaw; + const char *queryRawEnd; + const char *queryRawCurrent; + size_t querySize; + int characterSize; + bool escaping = false; + bool lastIsPercent = false; + + expression = data->expression; + queryRaw = GRN_TEXT_VALUE(query); + querySize = GRN_TEXT_LEN(query); + queryRawEnd = queryRaw + querySize; + + GRN_BULK_REWIND(&patternBuffer); + if (queryRaw[0] != '%') + GRN_TEXT_PUTS(ctx, &patternBuffer, "\\A"); + + queryRawCurrent = queryRaw; + while ((characterSize = grn_charlen(ctx, queryRawCurrent, queryRawEnd)) > 0) + { + const char *current = queryRawCurrent; + bool needToAddCharacter = true; + + queryRawCurrent += characterSize; + + if (!escaping && characterSize == 1) + { + switch (current[0]) + { + case '%': + if (queryRaw == current) + { + /* do nothing */ + } + else if (queryRawCurrent == queryRawEnd) + { + lastIsPercent = true; + } + else + { + GRN_TEXT_PUTS(ctx, &patternBuffer, ".*"); + } + needToAddCharacter = false; + break; + case '_': + GRN_TEXT_PUTC(ctx, &patternBuffer, '.'); + needToAddCharacter = false; + break; + case '\\': + escaping = true; + needToAddCharacter = false; + break; + default: + break; + } + + if (!needToAddCharacter) + continue; + } + + if (characterSize == 1) + { + switch (current[0]) + { + case '\\': + case '|': + case '(': + case ')': + case '[': + case ']': + case '.': + case '*': + case '+': + case '?': + case '{': + case '}': + case '^': + case '$': + GRN_TEXT_PUTC(ctx, &patternBuffer, '\\'); + GRN_TEXT_PUTC(ctx, &patternBuffer, current[0]); + break; + default: + GRN_TEXT_PUTC(ctx, &patternBuffer, current[0]); + break; + } + } + else + { + GRN_TEXT_PUT(ctx, &patternBuffer, current, characterSize); + } + escaping = false; + } + + if (queryRawCurrent != queryRawEnd) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid encoding character exist: <%.*s>", + (int) querySize, queryRaw))); + } + + if (!lastIsPercent) + GRN_TEXT_PUTS(ctx, &patternBuffer, "\\z"); + + grn_expr_append_obj(ctx, expression, matchTarget, GRN_OP_PUSH, 1); + grn_expr_append_const_str(ctx, expression, + GRN_TEXT_VALUE(&patternBuffer), + GRN_TEXT_LEN(&patternBuffer), + GRN_OP_PUSH, 1); + grn_expr_append_op(ctx, expression, GRN_OP_REGEXP, 2); +} + #ifdef JSONBOID static void PGrnSearchBuildConditionJSONQuery(PGrnSearchData *data, @@ -3348,7 +3469,10 @@ PGrnSearchBuildCondition(IndexScanDesc scan, switch (key->sk_strategy) { case PGrnLikeStrategyNumber: - PGrnSearchBuildConditionLike(data, matchTarget, &buffer); + if (PGrnIsForRegexpSearchIndex(index, key->sk_attno - 1)) + PGrnSearchBuildConditionLikeRegexp(data, matchTarget, &buffer); + else + PGrnSearchBuildConditionLikeMatch(data, matchTarget, &buffer); break; case PGrnQueryStrategyNumber: { Modified: pgroonga.sql (+1 -0) =================================================================== --- pgroonga.sql 2015-10-14 17:41:37 +0900 (58abd8b) +++ pgroonga.sql 2015-10-14 18:31:35 +0900 (f4898c9) @@ -364,6 +364,7 @@ $$; CREATE OPERATOR CLASS pgroonga.text_regexp_ops FOR TYPE text USING pgroonga AS + OPERATOR 6 pg_catalog.~~, OPERATOR 10 @~; CREATE OPERATOR CLASS pgroonga.varchar_regexp_ops FOR TYPE varchar Added: sql/regexp/text/like/begin-of-text/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/begin-of-text/bitmapscan.sql 2015-10-14 18:31:35 +0900 (5d841ff) @@ -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 LIKE 'Groonga%'; + +DROP TABLE memos; Added: sql/regexp/text/like/begin-of-text/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/begin-of-text/indexscan.sql 2015-10-14 18:31:35 +0900 (0f660d8) @@ -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 LIKE 'Groonga%'; + +DROP TABLE memos; Added: sql/regexp/text/like/begin-of-text/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/begin-of-text/seqscan.sql 2015-10-14 18:31:35 +0900 (0727588) @@ -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 LIKE 'Groonga%'; + +DROP TABLE memos; Added: sql/regexp/text/like/end-of-text/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/end-of-text/bitmapscan.sql 2015-10-14 18:31:35 +0900 (43a94d7) @@ -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 LIKE '%Groonga'; + +DROP TABLE memos; Added: sql/regexp/text/like/end-of-text/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/end-of-text/indexscan.sql 2015-10-14 18:31:35 +0900 (5b94877) @@ -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 LIKE '%Groonga'; + +DROP TABLE memos; Added: sql/regexp/text/like/end-of-text/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/end-of-text/seqscan.sql 2015-10-14 18:31:35 +0900 (516fab3) @@ -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 LIKE '%Groonga'; + +DROP TABLE memos; Added: sql/regexp/text/like/exact/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/exact/bitmapscan.sql 2015-10-14 18:31:35 +0900 (aa1f891) @@ -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 LIKE 'Groonga'; + +DROP TABLE memos; Added: sql/regexp/text/like/exact/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/exact/indexscan.sql 2015-10-14 18:31:35 +0900 (b7cd1e6) @@ -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 LIKE 'Groonga'; + +DROP TABLE memos; Added: sql/regexp/text/like/exact/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/exact/seqscan.sql 2015-10-14 18:31:35 +0900 (e64e398) @@ -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 LIKE 'Groonga'; + +DROP TABLE memos; Added: sql/regexp/text/like/partial/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/partial/bitmapscan.sql 2015-10-14 18:31:35 +0900 (2982dce) @@ -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 LIKE '%Groonga%'; + +DROP TABLE memos; Added: sql/regexp/text/like/partial/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/partial/indexscan.sql 2015-10-14 18:31:35 +0900 (077ba8b) @@ -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 LIKE '%Groonga%'; + +DROP TABLE memos; Added: sql/regexp/text/like/partial/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/regexp/text/like/partial/seqscan.sql 2015-10-14 18:31:35 +0900 (7f21162) @@ -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 LIKE '%Groonga%'; + +DROP TABLE memos; -------------- next part -------------- HTML����������������������������...Download