CREATE INDEX USING pgroonga

You need to specify USING pgroonga to CREATE INDEX to use PGroonga as index method. This section describes about pgroonga index method.

Syntax

This section describes only pgroonga index method related CREATE INDEX syntax. See CREATE INDEX document by PostgreSQL for full CREATE INDEX syntax.

Here is a basic syntax for creating a single column index:

CREATE INDEX ${INDEX_NAME}
          ON ${TABLE_NAME}
       USING pgroonga (${COLUMN});

This syntax can be used for the following cases:

Here is a basic syntax for creating a full text search index for a varchar type column:

CREATE INDEX ${INDEX_NAME}
          ON ${TABLE_NAME}
       USING pgroonga (${COLUMN} pgroonga.varchar_full_text_search_ops);

You need to specify pgroonga.varchar_full_text_search_ops operator class for the case.

Customization

You can custom the followings by WITH option of CREATE INDEX:

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.

Here are default tokenizer and normalizer:

How to custom tokenizer

Specify tokenizer='${TOKENIZER_NAME}' for customizing tokenizer. Normally, you don't need to custom tokenizer.

Here is an example to use MeCab based tokenizer. You need to specify tokenizer='TokenMecab'. TokenMecab is a name of MeCab based tokenizer.

CREATE TABLE memos (
  id integer,
  content text
);

CREATE INDEX pgroonga_content_index
          ON memos
       USING pgroonga (content)
        WITH (tokenizer='TokenMecab');

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:

CREATE TABLE memos (
  id integer,
  tag text
);

CREATE INDEX pgroonga_tag_index
          ON memos
       USING pgroonga (tag)
        WITH (tokenizer='');

tokenizer='TokenDelimit' will be useful for tag search. See also TokenDelimit.

See Tokenizers 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.

Here is an example to disable normalizer:

CREATE TABLE memos (
  id integer,
  tag text
);

CREATE INDEX pgroonga_tag_index
          ON memos
       USING pgroonga (tag)
        WITH (normalizer='');

See Normalizers for other normalizers.

How to change tablespace

Since 1.1.6.

Specify TABLESPACE ${TABLESPACE_NAME} for customizing tablespace. If you have fast storage, you may want to change tablespace for PGroonga indexes.

Here is an example to change tablespace:

CREATE TABLESPACE fast LOCATION '/data/fast_disk';

CREATE TABLE memos (
  id integer,
  tag text
);

CREATE INDEX pgroonga_tag_index
          ON memos
       USING pgroonga (tag)
  TABLESPACE fast;