Kouhei Sutou
null+****@clear*****
Sun Oct 4 22:49:24 JST 2015
Kouhei Sutou 2015-10-04 22:49:24 +0900 (Sun, 04 Oct 2015) New Revision: 7ded84603d11ec4c667b495aa80ad36574a13186 https://github.com/pgroonga/pgroonga.github.io/commit/7ded84603d11ec4c667b495aa80ad36574a13186 Message: Document CREATE INDEX Modified files: reference/create-index-using-pgroonga.md Modified: reference/create-index-using-pgroonga.md (+43 -30) =================================================================== --- reference/create-index-using-pgroonga.md 2015-10-04 21:39:19 +0900 (bb22735) +++ reference/create-index-using-pgroonga.md 2015-10-04 22:49:24 +0900 (55b5750) @@ -11,43 +11,51 @@ You need to specify `USING pgroonga` to `CREATE INDEX` to use PGroonga as index This section describes only `pgroonga` index method related `CREATE INDEX` syntax. See [CREATE INDEX document by PostgreSQL](http://www.postgresql.org/docs/{{ site.postgresql_short_version }}/static/sql-createindex.html) for full `CREATE INDEX` syntax. -Here is syntax for creating single column index: +Here is a basic syntax for creating a single column index: ```sql -CREATE INDEX ${INDEX_NAME} ON ${TABLE_NAME} USING pgroonga (${COLUMN}); +CREATE INDEX ${INDEX_NAME} + ON ${TABLE_NAME} + USING pgroonga (${COLUMN}); ``` This syntax can be used for the following cases: * Creating a full text search index for a `text` type column * Creating a full text search index for an array of `text` type column - * Creating a equality condition and comparison conditions search for a non `text` type columns - * Creating a equality condition and comparison conditions search for an array of non `text` type columns + * Creating a equality condition and comparison conditions search index for a non `text` type columns + * Creating a equality condition and comparison conditions search index for an array of non `text` type columns + * Creating a contain search and complex search index for `jsonb` type column -TODO +Here is a basic syntax for creating a full text search index for a `varchar` type column: -（`varchar`型に対して全文検索をする場合は追加で -`pgroonga.varchar_fulltext_search_ops`演算子クラスを指定する必要があり -ます。） +```sql +CREATE INDEX ${INDEX_NAME} + ON ${TABLE_NAME} + USING pgroonga (${COLUMN}) pgroonga.varchar_full_text_search_ops; +``` + +You need to specify `pgroonga.varchar_fulltext_search_ops` operator class for the case. + +### Customization + +You can custom the followings by `WITH` option of `CREATE INDEX`: + + * Tokenizer: It's a module for full text search. + * Normalizer: It's a module for customizing equality of `text` and `varchar` types. +Normally, you don't need to custom them because default values of them are suitable for most cases. Features to custom them are for advanced users. -### Customize +Here are default tokenizer and normalizer: -`CREATE INDEX`の`WITH`でトークナイザーとノーマライザーをカスタマイズす -ることができます。デフォルトで適切なトークナイザーとノーマライザーが設 -定されているので、通常はカスタマイズする必要はありません。上級者向けの -機能です。 + * Tokenizer: [`TokenBigram`](http://groonga.org/docs/reference/tokenizers.html#token-bigram): It's a bigram based tokenizer. It combines bigram tokenization and white space based tokenization. It uses bigram tokenization for non ASCII characters and white space based tokenization for ASCII characters. It reduces noise for ASCII characters only query. + * Normalizer: [`NormalizerAuto`](http://groonga.org/docs/reference/normalizers.html#normalizer-auto): It chooses suitable normalization based on target encoding. For example, it uses [Unicode NFKC](http://unicode.org/reports/tr15/) based normalization for UTF-8. -なお、デフォルトのトークナイザーとノーマライザーは次の通りです。 +#### How to custom tokenizer - * トークナイザー: `TokenBigram`: Bigramベースのトークナイザーです。 - * ノーマライザー: [NormalizerAuto](http://groonga.org/ja/docs/reference/normalizers.html#normalizer-auto): エンコーディングに合わせて適切な正規化を行います。たとえば、UTF-8の場合はUnicodeのNFKCベースの正規化を行います。 +Specify `tokenizer='${TOKENIZER_NAME}'` for customizing tokenizer. Normally, you don't need to custom tokenizer. -トークナイザーをカスタマイズするときは`tokenizer='トークナイザー名'`を -指定します。例えば、[MeCab](http://taku910.github.io/mecab/)ベースのトー -クナイザーを指定する場合は次のように`tokenizer='TokenMecab'`を指定しま -す。（`TokenMecab`を使う場合は`groonga-tokenizer-mecab`パッケージをイ -ンストールしておく必要があります。） +Here is an example to use [MeCab](http://taku910.github.io/mecab/) based tokenizer. You need to specify `tokenizer='TokenMecab'`. [`TokenMecab`](http://groonga.org/docs/reference/tokenizers.html#token-mecab) is a name of MeCab based tokenizer. ```sql CREATE TABLE memos ( @@ -61,10 +69,9 @@ CREATE INDEX pgroonga_content_index WITH (tokenizer='TokenMecab'); ``` -次のように`tokenizer=''`を指定することでトークナイザーを無効にできます。 -トークナイザーを無効にするとカラムの値そのもの、あるいは値の前方一致検 -索でのみヒットするようになります。これは、タグ検索や名前検索などに有用 -です。（タグ検索には`tokenizer='TokenDelimit'`も有用です。） +You can disable tokenizer by specifying `tokenizer=''`. If you disable tokenizer, you can search column value only by exact match search and prefix search. It reduces noise for some cases. For example, it's useful for tag search, name search and so on. + +Here is an example to disable tokenizer: ```sql CREATE TABLE memos ( @@ -78,12 +85,17 @@ CREATE INDEX pgroonga_tag_index WITH (tokenizer=''); ``` -ノーマライザーをカスタマイズするときは`normalizer='ノーマライザー名'`を -指定します。通常は指定する必要はありません。 +`tokenizer='TokenDelimit'` will be useful for tag search. See also [`TokenDelimit`](http://groonga.org/docs/reference/tokenizers.html#token-delimit). + +See [Tokenizers](http://groonga.org/docs/reference/tokenizers.html) for other tokenizers. + +#### How to custom normalizer + +Specify `normalizer='${NORMALIZER_NAME}'` for customizing normalizer. Normally, you don't need to custom normalizer. + +You can disable normalizer by specifying `normalizer=''`. If you disable normalizer, you can search column value only by the original column value. If normalizer increases noise, it's useful. -次のように`normalizer=''`を指定することでノーマライザーを無効にできま -す。ノーマライザーを無効にするとカラムの値そのものでのみヒットするよう -になります。正規化によりノイズが増える場合は有用な機能です。 +Here is an example to disable normalizer: ```sql CREATE TABLE memos ( @@ -97,3 +109,4 @@ CREATE INDEX pgroonga_tag_index WITH (normalizer=''); ``` +See [Normalizers](http://groonga.org/docs/reference/normalizers.html) for other normalizers. -------------- next part -------------- HTML����������������������������...Download