Kouhei Sutou
null+****@clear*****
Wed Oct 28 22:27:53 JST 2015
Kouhei Sutou 2015-10-28 22:27:53 +0900 (Wed, 28 Oct 2015) New Revision: 9ece27b675d4e6c53cf91599254774f84e840adf https://github.com/pgroonga/pgroonga/commit/9ece27b675d4e6c53cf91599254774f84e840adf Message: Support ILIKE with index Added files: expected/full-text-search/text/single/ilike/begin/bitmapscan.out expected/full-text-search/text/single/ilike/begin/indexscan.out expected/full-text-search/text/single/ilike/begin/seqscan.out expected/full-text-search/text/single/ilike/end/bitmapscan.out expected/full-text-search/text/single/ilike/end/indexscan.out expected/full-text-search/text/single/ilike/end/seqscan.out expected/full-text-search/text/single/ilike/partial/bitmapscan.out expected/full-text-search/text/single/ilike/partial/indexscan.out expected/full-text-search/text/single/ilike/partial/seqscan.out expected/full-text-search/text/single/ilike/underscore/bitmapscan.out expected/full-text-search/text/single/ilike/underscore/indexscan.out expected/full-text-search/text/single/ilike/underscore/seqscan.out sql/full-text-search/text/single/ilike/begin/bitmapscan.sql sql/full-text-search/text/single/ilike/begin/indexscan.sql sql/full-text-search/text/single/ilike/begin/seqscan.sql sql/full-text-search/text/single/ilike/end/bitmapscan.sql sql/full-text-search/text/single/ilike/end/indexscan.sql sql/full-text-search/text/single/ilike/end/seqscan.sql sql/full-text-search/text/single/ilike/partial/bitmapscan.sql sql/full-text-search/text/single/ilike/partial/indexscan.sql sql/full-text-search/text/single/ilike/partial/seqscan.sql sql/full-text-search/text/single/ilike/underscore/bitmapscan.sql sql/full-text-search/text/single/ilike/underscore/indexscan.sql sql/full-text-search/text/single/ilike/underscore/seqscan.sql Modified files: pgroonga.c pgroonga.h Added: expected/full-text-search/text/single/ilike/begin/bitmapscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/begin/bitmapscan.out 2015-10-28 22:27:53 +0900 (b3df8a5) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, content + FROM memos + WHERE content ILIKE 'POSTGRESQL%'; + id | content +----+------------------------ + 1 | PostgreSQL is a RDBMS. +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/begin/indexscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/begin/indexscan.out 2015-10-28 22:27:53 +0900 (5f54b01) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE 'POSTGRESQL%'; + id | content +----+------------------------ + 1 | PostgreSQL is a RDBMS. +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/begin/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/begin/seqscan.out 2015-10-28 22:27:53 +0900 (7d03065) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE 'POSTGRESQL%'; + id | content +----+------------------------ + 1 | PostgreSQL is a RDBMS. +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/end/bitmapscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/end/bitmapscan.out 2015-10-28 22:27:53 +0900 (196aa12) @@ -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'); +INSERT INTO memos VALUES (4, 'groonga command is provided'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/end/indexscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/end/indexscan.out 2015-10-28 22:27:53 +0900 (6843ecb) @@ -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'); +INSERT INTO memos VALUES (4, 'groonga command is provided'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/end/seqscan.out (+21 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/end/seqscan.out 2015-10-28 22:27:53 +0900 (976cc37) @@ -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'); +INSERT INTO memos VALUES (4, 'groonga command is provided'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA'; + id | content +----+------------------------------------------------------ + 3 | PGroonga is a PostgreSQL extension that uses Groonga +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/partial/bitmapscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/partial/bitmapscan.out 2015-10-28 22:27:53 +0900 (9ce6e8e) @@ -0,0 +1,23 @@ +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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA%'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. + 4 | groonga command is provided. +(3 rows) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/partial/indexscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/partial/indexscan.out 2015-10-28 22:27:53 +0900 (e5be610) @@ -0,0 +1,23 @@ +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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA%'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. + 4 | groonga command is provided. +(3 rows) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/partial/seqscan.out (+23 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/partial/seqscan.out 2015-10-28 22:27:53 +0900 (0962ba7) @@ -0,0 +1,23 @@ +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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA%'; + id | content +----+------------------------------------------------------- + 2 | Groonga is fast full text search engine. + 3 | PGroonga is a PostgreSQL extension that uses Groonga. + 4 | groonga command is provided. +(3 rows) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/underscore/bitmapscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/underscore/bitmapscan.out 2015-10-28 22:27:53 +0900 (9d18777) @@ -0,0 +1,20 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, ''); +INSERT INTO memos VALUES (2, 'a'); +INSERT INTO memos VALUES (3, 'ab'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT id, content + FROM memos + WHERE content ILIKE '_'; + id | content +----+--------- + 2 | a +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/underscore/indexscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/underscore/indexscan.out 2015-10-28 22:27:53 +0900 (01b8eac) @@ -0,0 +1,20 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, ''); +INSERT INTO memos VALUES (2, 'a'); +INSERT INTO memos VALUES (3, 'ab'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE '_'; + id | content +----+--------- + 2 | a +(1 row) + +DROP TABLE memos; Added: expected/full-text-search/text/single/ilike/underscore/seqscan.out (+20 -0) 100644 =================================================================== --- /dev/null +++ expected/full-text-search/text/single/ilike/underscore/seqscan.out 2015-10-28 22:27:53 +0900 (839b4c5) @@ -0,0 +1,20 @@ +CREATE TABLE memos ( + id integer, + content text +); +INSERT INTO memos VALUES (1, ''); +INSERT INTO memos VALUES (2, 'a'); +INSERT INTO memos VALUES (3, 'ab'); +CREATE INDEX grnindex ON memos USING pgroonga (content); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT id, content + FROM memos + WHERE content ILIKE '_'; + id | content +----+--------- + 2 | a +(1 row) + +DROP TABLE memos; Modified: pgroonga.c (+7 -2) =================================================================== --- pgroonga.c 2015-10-28 22:21:53 +0900 (4eefe5b) +++ pgroonga.c 2015-10-28 22:27:53 +0900 (9c3715b) @@ -3662,6 +3662,7 @@ PGrnSearchBuildCondition(IndexScanDesc scan, operator = GRN_OP_GREATER; break; case PGrnLikeStrategyNumber: + case PGrnILikeStrategyNumber: break; case PGrnMatchStrategyNumber: operator = GRN_OP_MATCH; @@ -3687,7 +3688,10 @@ PGrnSearchBuildCondition(IndexScanDesc scan, else PGrnSearchBuildConditionLikeMatch(data, matchTarget, &buffer); break; - case PGrnQueryStrategyNumber: + case PGrnILikeStrategyNumber: + PGrnSearchBuildConditionLikeMatch(data, matchTarget, &buffer); + break; + case PGrnQueryStrategyNumber: { grn_rc rc; grn_expr_flags flags = @@ -4082,7 +4086,8 @@ PGrnEnsureCursorOpened(IndexScanDesc scan, ScanDirection dir) for (i = 0; i < scan->numberOfKeys; i++) { ScanKey key = &(scan->keyData[i]); - if (key->sk_strategy == PGrnLikeStrategyNumber) + if (key->sk_strategy == PGrnLikeStrategyNumber || + key->sk_strategy == PGrnILikeStrategyNumber) { scan->xs_recheck = true; break; Modified: pgroonga.h (+4 -3) =================================================================== --- pgroonga.h 2015-10-28 22:21:53 +0900 (7a321e0) +++ pgroonga.h 2015-10-28 22:27:53 +0900 (3d46fe4) @@ -19,10 +19,11 @@ #define PGrnGreaterEqualStrategyNumber 4 /* operator >= */ #define PGrnGreaterStrategyNumber 5 /* operator > */ #define PGrnLikeStrategyNumber 6 /* operator ~~ (LIKE) */ -#define PGrnMatchStrategyNumber 7 /* operator %% (@ in Groonga) */ -#define PGrnQueryStrategyNumber 8 /* operator @@ (Groonga query) */ -#define PGrnJSONContainStrategyNumber 9 /* operator @> */ +#define PGrnILikeStrategyNumber 7 /* operator ~~* (ILIKE) */ +#define PGrnMatchStrategyNumber 8 /* operator %% (@ in Groonga) */ +#define PGrnQueryStrategyNumber 9 /* operator @@ (Groonga query) */ #define PGrnRegexpStrategyNumber 10 /* operator @~ (@~ in Groonga) */ +#define PGrnJSONContainStrategyNumber 11 /* operator @> */ /* file and table names */ #define PGrnLogBasename "pgroonga.log" Added: sql/full-text-search/text/single/ilike/begin/bitmapscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/begin/bitmapscan.sql 2015-10-28 22:27:53 +0900 (7033f72) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, content + FROM memos + WHERE content ILIKE 'POSTGRESQL%'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/begin/indexscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/begin/indexscan.sql 2015-10-28 22:27:53 +0900 (3b7d989) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE 'POSTGRESQL%'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/begin/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/begin/seqscan.sql 2015-10-28 22:27:53 +0900 (9d701f0) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE 'POSTGRESQL%'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/end/bitmapscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/end/bitmapscan.sql 2015-10-28 22:27:53 +0900 (4dee0c5) @@ -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'); +INSERT INTO memos VALUES (4, 'groonga command is provided'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/end/indexscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/end/indexscan.sql 2015-10-28 22:27:53 +0900 (8445698) @@ -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'); +INSERT INTO memos VALUES (4, 'groonga command is provided'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/end/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/end/seqscan.sql 2015-10-28 22:27:53 +0900 (de53c99) @@ -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'); +INSERT INTO memos VALUES (4, 'groonga command is provided'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/partial/bitmapscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/partial/bitmapscan.sql 2015-10-28 22:27:53 +0900 (0c66090) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA%'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/partial/indexscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/partial/indexscan.sql 2015-10-28 22:27:53 +0900 (c07575e) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA%'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/partial/seqscan.sql (+21 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/partial/seqscan.sql 2015-10-28 22:27:53 +0900 (00cfe02) @@ -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.'); +INSERT INTO memos VALUES (4, 'groonga command is provided.'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE '%GROONGA%'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/underscore/bitmapscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/underscore/bitmapscan.sql 2015-10-28 22:27:53 +0900 (5f7d942) @@ -0,0 +1,20 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, ''); +INSERT INTO memos VALUES (2, 'a'); +INSERT INTO memos VALUES (3, 'ab'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT id, content + FROM memos + WHERE content ILIKE '_'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/underscore/indexscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/underscore/indexscan.sql 2015-10-28 22:27:53 +0900 (6306d51) @@ -0,0 +1,20 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, ''); +INSERT INTO memos VALUES (2, 'a'); +INSERT INTO memos VALUES (3, 'ab'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE '_'; + +DROP TABLE memos; Added: sql/full-text-search/text/single/ilike/underscore/seqscan.sql (+20 -0) 100644 =================================================================== --- /dev/null +++ sql/full-text-search/text/single/ilike/underscore/seqscan.sql 2015-10-28 22:27:53 +0900 (1702c65) @@ -0,0 +1,20 @@ +CREATE TABLE memos ( + id integer, + content text +); + +INSERT INTO memos VALUES (1, ''); +INSERT INTO memos VALUES (2, 'a'); +INSERT INTO memos VALUES (3, 'ab'); + +CREATE INDEX grnindex ON memos USING pgroonga (content); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT id, content + FROM memos + WHERE content ILIKE '_'; + +DROP TABLE memos; -------------- next part -------------- HTML����������������������������...Download