Hologres:Full-Text Inverted Index

Create an index

Syntax

CREATE INDEX [ IF NOT EXISTS ] idx_name ON table_name
       USING FULLTEXT (column_name [ , ... ])
       [ WITH ( storage_parameter [ = value ] [ , ... ] ) ];

Parameters

`tokenizer`

The tokenizer to use. Defaults to jieba. Supported values:

Each index supports only one tokenizer and one analyzer_params setting.

`analyzer_params`

Tokenizer configuration as a JSON string. Each tokenizer has defaults that work for most use cases—specify only tokenizer and omit analyzer_params unless you need custom behavior. For details, see Customize tokenizer configuration.

`index_options`

Controls how much information the index stores and which query features are available. Available in version 4.1.9 and later.

index_options supports three levels. Higher levels include all information from lower levels: freqs includes docs, and positions includes freqs and docs.

For indexes using the keyword tokenizer, the record level is fixed at docs regardless of index_options.

Examples

Create an index using the default tokenizer (jieba) and default configuration:

CREATE INDEX idx1 ON tbl
       USING FULLTEXT (col1);

Use the ik tokenizer with its default configuration:

CREATE INDEX idx1 ON tbl
       USING FULLTEXT (col1)
       WITH (tokenizer = 'ik');

Use a custom jieba configuration—exact mode with lowercase filter only:

CREATE INDEX idx1 ON tbl
       USING FULLTEXT (col1)
       WITH (tokenizer = 'jieba',
             analyzer_params = '{"tokenizer":{"type":"jieba","mode":"exact"}, "filter":["lowercase"]}');

Set index_options to freqs to save storage at the cost of disabling phrase queries (version 4.1.9 and later):

CREATE INDEX idx1 ON tbl
       USING FULLTEXT (col1)
       WITH (index_options = 'freqs');

Build indexes after data import

Index files are built during compaction, not immediately after the CREATE INDEX statement. To build indexes right away, use one of the following approaches:

• With Serverless Computing (recommended): Serverless resources perform compaction and build full-text indexes synchronously during import. See Run Read/Write Tasks with Serverless Computing and Run Compaction Tasks with Serverless Computing.

• Without Serverless Computing: Trigger compaction manually after creating the index or finishing a batch import:

  VACUUM <schema_name>.<table_name>;

  See Compaction (Beta) for details.

Modify an index

Syntax

-- Modify index configuration
ALTER INDEX [ IF EXISTS ] <idx_name> SET ( <storage_parameter> = '<storage_value>' [ , ... ] );

-- Restore default configuration
ALTER INDEX [ IF EXISTS ] <idx_name> RESET ( <storage_parameter> [ , ... ] );

After modifying a full-text inverted index, index files are rebuilt during the next compaction. Run VACUUM <schema_name>.<table_name>; to trigger compaction immediately. See Compaction.

Examples

Change the tokenizer to standard:

ALTER INDEX idx1 SET (tokenizer = 'standard');

Change the tokenizer to ik in ik_max_word mode with lowercase conversion disabled:

ALTER INDEX idx1 SET (
  tokenizer = 'ik',
  analyzer_params = '{"tokenizer":{"type":"ik","mode":"ik_max_word","enable_lowercase": false}}'
);

Restore the default jieba tokenizer and its default analyzer_params:

ALTER INDEX idx1 RESET (tokenizer, analyzer_params);

Restore index_options to the default (positions):

ALTER INDEX idx1 RESET (index_options);

Change index_options to docs:

ALTER INDEX idx1 SET (index_options = 'docs');

Delete an index

Syntax

DROP INDEX [ IF EXISTS ] <idx_name> [ RESTRICT ];

View indexes

Use the system table hologres.hg_index_properties to list all full-text inverted indexes:

SELECT * FROM hologres.hg_index_properties;

To find the table and column associated with a specific index:

SELECT
    t.relname AS table_name,
    a.attname AS column_name
FROM pg_class t
    JOIN pg_index i ON t.oid = i.indrelid
    JOIN pg_class idx ON i.indexrelid = idx.oid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '<namespace>')
    AND idx.relname = '<indexname>'
LIMIT 1;

Replace the placeholders:

• <namespace>: The table_namespace value from SELECT * FROM hologres.hg_index_properties;.

• <indexname>: The index name you created.

TEXT_SEARCH function

TEXT_SEARCH computes a BM25 relevance score for a query against source text.

Syntax

TEXT_SEARCH (
  <search_data> TEXT/VARCHAR/CHAR
  ,<search_expression> TEXT
  [ ,<mode> TEXT DEFAULT 'match'
  ,<operator> TEXT DEFAULT 'OR'
  ,<tokenizer> TEXT DEFAULT ''
  ,<analyzer_params> TEXT DEFAULT ''
  ,<options> TEXT DEFAULT '']
)

Parameters

`mode` values

`slop` unit by tokenizer

The slop parameter in phrase mode measures distance differently depending on the tokenizer:

• Characters: jieba, keyword, icu

• Tokens: standard, simple, whitespace

Return value

A non-negative FLOAT representing the BM25 relevance score. Higher values indicate greater relevance. A score of 0 means no match.

Examples

Keyword match with AND operator:

-- Recommended: use named parameters
SELECT TEXT_SEARCH(content, 'machine learning', operator => 'AND') FROM tbl;

Phrase search with slop=2:

SELECT TEXT_SEARCH(content, 'machine learning', 'phrase', options => 'slop=2;') FROM tbl;

Natural language search with AND/OR logic:

SELECT TEXT_SEARCH(content, 'machine AND (system OR recognition)', 'natural_language') FROM tbl;

Natural language search with required and excluded terms:

-- Must contain 'learning', must not contain 'machine'
SELECT TEXT_SEARCH(content, '+learning -machine system', 'natural_language') FROM tbl;

Term search (exact match):

SELECT TEXT_SEARCH(content, 'machine learning', 'term') FROM tbl;

TOKENIZE function

TOKENIZE returns the tokens produced by a given tokenizer. Use it to test and debug tokenization behavior before building indexes.

Syntax

TOKENIZE (
  <search_data> TEXT
  [ ,<tokenizer> TEXT DEFAULT ''
  ,<analyzer_params> TEXT DEFAULT '']
)

Parameters

• search_data: Required. The text to tokenize. Accepts constants.

• tokenizer, analyzer_params: Optional. Defaults to jieba if not specified.

Return value

A TEXT array of tokens.

Verify index usage

Run EXPLAIN ANALYZE to confirm whether a query uses the full-text inverted index. If the output contains Fulltext Filter, the index is active.

EXPLAIN ANALYZE SELECT * FROM wiki_articles WHERE text_search(content, 'Yangtze River') > 0;

Example output:

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=12)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=12)
        ->  Index Scan using Clustering_index on wiki_articles  (cost=0.00..1.00 rows=1 width=12)
              Fulltext Filter: (text_search(content, search_expression => 'Yangtze River'::text, mode => match, operator => OR, tokenizer => jieba, analyzer_params => {"filter":["removepunct","lowercase",{"stop_words":["_english_"],"type":"stop"},{"language":"english","type":"stemmer"}],"tokenizer":{"hmm":true,"mode":"search","type":"jieba"}}, options => ) > '0'::double precision)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

The Fulltext Filter line confirms the index is used. It also shows the resolved tokenizer and analyzer_params—useful for verifying that the index configuration matches what you expect.

For more on reading execution plans, see EXPLAIN and EXPLAIN ANALYZE.

Data preparation

Create a test table, add a full-text index, and insert sample data:

-- Create table
CREATE TABLE wiki_articles (id int, content text);

-- Create index
CREATE INDEX ft_idx_1 ON wiki_articles
       USING FULLTEXT (content)
       WITH (tokenizer = 'jieba');

-- Insert data
INSERT INTO wiki_articles VALUES
  (1, 'The Yangtze River is China''s longest river and the world''s third-longest river, about 6,300 km long.'),
  (2, 'Li was born in 1962 in Wendeng County, Shandong.'),
  (3, 'He graduated from the department of physics at Shandong University.'),
  (4, 'The Spring Festival, also known as the Lunar New Year, is China''s most important traditional festival.'),
  (5, 'The Spring Festival usually falls between late January and mid-February on the Gregorian calendar. Major customs include pasting spring couplets, setting off firecrackers, eating reunion dinner, and giving New Year greetings.'),
  (6, 'In 2006, the Spring Festival was approved by the State Council as part of China''s first batch of national intangible cultural heritage.'),
  (7, 'Shandong has dozens of universities.'),
  (8, 'ShanDa is a famous university in Shandong.');

-- Trigger compaction to build index files
VACUUM wiki_articles;

Keyword match

-- OR operator (default): matches documents containing 'shandong' or 'university'
SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university') > 0;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  2 | Li was born in 1962 in Wendeng County, Shandong.
  3 | He graduated from the department of physics at Shandong University.
  7 | Shandong has dozens of universities.
  8 | ShanDa is a famous university in Shandong.

-- AND operator: matches documents containing both 'shandong' and 'university'
SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university', operator => 'AND') > 0;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  3 | He graduated from the department of physics at Shandong University.
  7 | Shandong has dozens of universities.
  8 | ShanDa is a famous university in Shandong.

Phrase search

-- Default slop=0: 'shandong' must be immediately followed by 'university'
SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university', mode => 'phrase') > 0;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  3 | He graduated from the department of physics at Shandong University.
(1 row)

-- slop=14: 'shandong' and 'university' can be up to 14 characters apart
SELECT * FROM wiki_articles
        WHERE TEXT_SEARCH(content, 'shandong university', mode => 'phrase', options => 'slop=14;') > 0;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  3 | He graduated from the department of physics at Shandong University.
  7 | Shandong has dozens of universities.
(2 rows)

-- slop=23: also matches out-of-order phrases ('university of Shandong' pattern)
SELECT * FROM wiki_articles
        WHERE TEXT_SEARCH(content, 'shandong university', mode => 'phrase', options => 'slop=23;') > 0;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  3 | He graduated from the department of physics at Shandong University.
  7 | Shandong has dozens of universities.
  8 | ShanDa is a famous university in Shandong.
(3 rows)

Punctuation is ignored in phrase searches. Even if the source text uses a comma between words and the query uses a period, it still matches.

Natural language search

-- Keyword match with AND/OR: requires 'shandong' AND 'university', OR contains 'culture'
SELECT * FROM wiki_articles
        WHERE TEXT_SEARCH(content, '(shandong AND university) OR culture', 'natural_language') > 0;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  8 | ShanDa is a famous university in Shandong.
  7 | Shandong has dozens of universities.
  3 | He graduated from the department of physics at Shandong University.
  6 | In 2006, the Spring Festival was approved by the State Council as part of China's first batch of national intangible cultural heritage.

-- Required (+) and excluded (-) terms, with optional boosting term
-- Must contain 'shandong', must not contain 'physics', and may contain 'famous' (boosts score)
SELECT id,
       content,
       TEXT_SEARCH(content, '+shandong -physics famous', 'natural_language') as score
FROM wiki_articles
WHERE TEXT_SEARCH(content, '+shandong -physics famous', 'natural_language') > 0
ORDER BY score DESC;

-- Result
 id |                     content                      |  score
----+--------------------------------------------------+----------
  8 | ShanDa is a famous university in Shandong.       |  2.92376
  7 | Shandong has dozens of universities.             | 0.863399
  2 | Li was born in 1962 in Wendeng County, Shandong. | 0.716338

-- Phrase search using double quotes
SELECT * FROM wiki_articles
        WHERE TEXT_SEARCH(content, '"shandong university"', 'natural_language') > 0;

-- Match all documents
SELECT * FROM wiki_articles
        WHERE TEXT_SEARCH(content, '*', 'natural_language') > 0;

Term search

-- Exact match: 'Spring Festival' exists verbatim in the index
SELECT * FROM wiki_articles
         WHERE TEXT_SEARCH(content, 'Spring Festival', 'term') > 0;

-- Result
 id |                                           content
----+----------------------------------------------------------------------------------------------
  4 | The Spring Festival, also known as the Lunar New Year, is China''s most important traditional festival.
  5 | The Spring Festival usually falls between late January and mid-February on the Gregorian calendar. ...
  6 | In 2006, the Spring Festival was approved by the State Council as part of China''s first batch of national intangible cultural heritage.

-- No match: 'shandong university' is split by jieba, so this exact string doesn't exist in the index
-- Pair term search with the keyword tokenizer for exact multi-word matches
SELECT * FROM wiki_articles
         WHERE TEXT_SEARCH(content, 'shandong university', 'term') > 0;

-- Result
 id | content
----+---------

Complex queries

Combine TEXT_SEARCH with other predicates:

-- Filter by both full-text match and primary key
SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university') > 0 AND id = 3;

-- Result
 id |                               content
----+---------------------------------------------------------------------
  3 | He graduated from the department of physics at Shandong University.

Return BM25 scores and get the top 3 results:

SELECT id,
       content,
       TEXT_SEARCH(content, 'shandong university') AS score,
       TOKENIZE(content, 'jieba')
  FROM wiki_articles
ORDER BY score DESC
LIMIT 3;

-- Result
id  |                               content                               |  score  |                     tokenize
----+---------------------------------------------------------------------+---------+--------------------------------------------------
  8 | ShanDa is a famous university in Shandong.                          | 2.74634 | {shanda,famous,univers,shandong}
  7 | Shandong has dozens of universities.                                | 2.74634 | {shandong,has,dozen,univers}
  3 | He graduated from the department of physics at Shandong University. | 2.38178 | {he,graduat,from,depart,physic,shandong,univers}

Use TEXT_SEARCH in both SELECT and WHERE to filter and rank in one pass:

SELECT id,
       content,
       TEXT_SEARCH(content, 'shandong university') AS score,
       TOKENIZE(content, 'jieba')
  FROM wiki_articles
 WHERE TEXT_SEARCH(content, 'shandong university') > 0
ORDER BY score DESC;

Combine with a JOIN to search within a filtered subset:

-- Find the most relevant documents about 'shandong university' from wiki sources only
CREATE TABLE article_source (id int primary key, source text);
INSERT INTO article_source VALUES (1, 'baike'), (2, 'wiki'), (3, 'wiki'), (4, 'baike'),
                                  (5, 'baike'), (6, 'baike'), (7, 'wiki'), (8, 'paper');

SELECT a.id,
       source, content,
       TEXT_SEARCH(content, 'shandong university') AS score,
       TOKENIZE(a.content, 'jieba')
  FROM wiki_articles a
  JOIN article_source b ON (a.id = b.id)
 WHERE TEXT_SEARCH(a.content, 'shandong university') > 0
   AND b.source = 'wiki'
ORDER BY score DESC;

-- Result
id  | source |                               content                               |  score  |                     tokenize
----+--------+---------------------------------------------------------------------+---------+--------------------------------------------------
  7 | wiki   | Shandong has dozens of universities.                                | 2.74634 | {shandong,has,dozen,univers}
  3 | wiki   | He graduated from the department of physics at Shandong University. | 2.38178 | {he,graduat,from,depart,physic,shandong,univers}
  2 | wiki   | Li was born in 1962 in Wendeng County, Shandong.                    | 1.09244 | {li,born,1962,wendeng,counti,shandong}

Rebuild indexes using Serverless Computing

Modifying certain table properties triggers compaction and index rebuilding, which consumes significant CPU resources. The approach depends on what you're modifying.

For `bitmap_columns`, `dictionary_encoding_columns`, or vector indexes:

Use the REBUILD syntax with Serverless Computing resources. Avoid ALTER TABLE ... SET for these properties. For details, see REBUILD.

ASYNC REBUILD TABLE <table_name>
WITH (
    rebuild_guc_hg_computing_resource = 'serverless'
)
SET (
    bitmap_columns = '<col1>,<col2>',
    dictionary_encoding_columns = '<col1>:on,<col2>:off',
    vectors = '{
    "<col_vector>": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "rabitq",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb" : 4096
        }
    }
    }'
);

For full-text index columns or column-oriented JSONB columns:

The REBUILD syntax is not supported for these types. Instead, create a new table, copy data using Serverless Computing, and swap the tables:

BEGIN;
-- Clean up any potential temporary tables
DROP TABLE IF EXISTS <table_new>;
-- Create a temporary table with the same structure
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('<table_new>', 'select * from <table>');
COMMIT;

-- Enable column-oriented storage for the target column (if needed)
ALTER TABLE <table_new> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);
-- Create the full-text index on the new table
CREATE INDEX <idx_name> ON <table_new> USING FULLTEXT (column_name);

-- Copy data using Serverless Computing to build indexes synchronously
SET hg_computing_resource = 'serverless';
INSERT INTO <table_new> SELECT * FROM <table>;
ANALYZE <table_new>;

BEGIN;
-- Swap old and new tables
DROP TABLE IF EXISTS <table>;
ALTER TABLE <table_new> RENAME TO <table>;
COMMIT;

For other properties (distribution_key, clustering_key, segment_key, storage format):

Use the REBUILD syntax with Serverless Computing resources.

Tokenizer-specific parameters

jieba

standard

ik

ngram

If the difference between max_gram and min_gram exceeds 3, the tokenizer generates too many tokens, increasing resource consumption and index build time. Override the limit with: SET hg_fulltext_index_max_ngram_diff = <value>.

pinyin

Default analyzer_params

Filters

Filters apply to each token in the order listed. Supported filters: