Hologres:Full-text inverted index

Create an index

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

Alter an index

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

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

Drop an index

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

View indexes

Hologres provides the hologres.hg_index_properties system table, where you can view the full-text inverted indexes created for a table and their corresponding positions.

SELECT * FROM hologres.hg_index_properties;

Run the following SQL statement to view the table and column associated with an 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;

Parameters:

• namespace: The value of the table_namespace field in the output of the SELECT * FROM hologres.hg_index_properties; command.

• indexname: The actual name of the index.

View index build progress

After you create a full-text inverted index, you can use the built-in function hg_show_build_index_progress to view the build progress of the index in real time. This function returns the build status of all full-text indexes on the specified table.

Example

SELECT * FROM hg_show_build_index_progress('<table_name>');

Usage notes

• To call this function, you must have the SELECT permission on the specified table.

• The function returns build information for all full-text indexes on the table, including the number of built files, the total number of files, the build progress in percent, and the estimated remaining time.

Return values

The result includes the following fields:

TEXT_SEARCH function

The TEXT_SEARCH function calculates the BM25 relevance score for a search source based on a search object.

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 '']
)

TOKENIZE function

The TOKENIZE function returns the results of tokenization based on the tokenizer configuration. You can use this function to debug the tokenization of a full-text inverted index.

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

Verify index usage

You can check the execution plan to see if an SQL query uses the full-text inverted index. The presence of Fulltext Filter in the plan indicates that the index is used successfully. For more information about execution plans, see EXPLAIN and EXPLAIN ANALYZE.

Example SQL:

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

The execution plan is as follows. It contains the Fulltext Filter field, which indicates that the SQL statement has successfully used the full-text inverted index.

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

Rebuild indexes using Serverless resources

Some property changes trigger compaction and index rebuilding, which can spike CPU usage. Handle these changes as follows:

Changes to `bitmap_columns`, `dictionary_encoding_columns`, or vector indexes

Use the REBUILD syntax with Serverless Computing resources instead of ALTER TABLE ... SET. For more information, 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
        }
    }
    }'
);

Changes to columnar storage for JSON data or full-text index columns

The REBUILD syntax is not yet supported for these changes. Use a temporary table instead:

BEGIN;
-- Clean up any existing temporary table
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;

-- Apply the new column properties to the temporary table
ALTER TABLE <table_new> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);
CREATE INDEX <idx_name> ON <table_new> USING FULLTEXT (column_name);

-- Insert data using Serverless resources (index building completes synchronously)
SET hg_computing_resource = 'serverless';
INSERT INTO <table_new> SELECT * FROM <table>;
ANALYZE <table_new>;

BEGIN;
-- Replace the original table with the temporary table
DROP TABLE IF EXISTS <table>;
ALTER TABLE <table_new> RENAME TO <table>;
COMMIT;

Other property changes (e.g., `distribution_key`, `clustering_key`, `segment_key`, storage format)

Use the REBUILD syntax with Serverless Computing resources.

analyzer_params requirements

The analyzer_params parameter must meet the following requirements:

• Must be a JSON string.

• The top-level JSON object supports two keys: tokenizer and filter. They are configured as follows:

  • filter: Optional. A JSON array used to configure filters. Multiple filters are applied in their specified order.

  • tokenizer: Required. A JSON object that configures tokenizer properties. This JSON object supports the following keys:

    • type: Required. The name of the tokenizer.

    • The parameters for the tokenizer object vary by tokenizer. For details, see the following table:

      Tokenizer	Parameter	Description	Value
      jieba	mode	The tokenization mode.	search (default): Lists multiple possible token combinations, allowing for redundancy. exact: Does not perform redundant splitting.
      	hmm	Determines whether to use a Hidden Markov Model to identify words not in the dictionary. This feature improves new word identification.	true (default): Uses the model. false: Does not use the model.
      standard	max_token_length	The maximum token length.	A positive integer. The default value is 255. If a token exceeds this length, it is split at intervals of max_token_length.
      ik	mode	The tokenization mode.	ik_max_word (default): Fine-grained tokenization that outputs all possible short words. ik_smart: A coarse-grained tokenization mode that prioritizes longer words to reduce the number of tokens. The output tokens do not overlap. This mode attempts to combine numerals and measure words into a single token.
      	enable_lowercase	Determines whether to convert tokens to lowercase.	true (default) false
      ngram	min_ngram	The minimum character length of a token.	A positive number. The default is 1. The maximum allowed difference from max_ngram is 3. Note: You can adjust the maximum difference by setting the following GUC, for example, SET hg_fulltext_index_max_ngram_diff = 5;
      	max_ngram	The maximum character length of a token.	A positive number. The default is 2. The value must be in the range [1, 255]. The maximum allowed difference from min_ngram is 3. Note A large difference between max_ngram and min_ngram causes the ngram tokenizer to generate many tokens, increasing resource consumption, storage, and index build time.
      	prefix_only	Determines whether to generate only prefix n-grams.	true false (default)
      pinyin	keep_first_letter	Determines whether to keep the first letter of each Chinese character.	true (default): Retains the pinyin initials. false: Do not keep the first letter.
      	keep_separate_first_letter	Determines whether to keep the first letter of each Chinese character as a separate token.	true: Retains the initial of each character. false (default): Do not keep as separate tokens.
      	limit_first_letter_length	The maximum length of the token that contains the combined first letters.	An integer. The default value is 16.
      	keep_full_pinyin	Determines whether to keep the full Pinyin of each Chinese character.	true (default): The pinyin is retained. false: Do not keep the full Pinyin.
      	keep_joined_full_pinyin	Determines whether to join the full Pinyin of each Chinese character into a single token.	true: Enables concatenation. false (default): Do not join the Pinyin.
      	keep_none_chinese	Determines whether to keep non-Chinese letters or numbers in the result.	true (default): Keeps them. false: Does not keep them.
      	keep_none_chinese_together	Determines whether to keep consecutive non-Chinese letters or numbers together as a single token.	true (default): Keeps letter and digit sequences together. false: Does not preserve the original form. Note This parameter takes effect only when keep_none_chinese is set to true.
      	keep_none_chinese_in_first_letter	Determines whether to keep non-Chinese letters or numbers in the first-letter token.	true (default): Retains the token. false: Does not keep them.
      	keep_none_chinese_in_joined_full_pinyin	Determines whether to keep non-Chinese letters or numbers in the joined full Pinyin token.	true: Retains the token. false (default): Does not keep them.
      	none_chinese_pinyin_tokenize	Determines whether to split non-Chinese letters into separate Pinyin terms if they form valid Pinyin.	true (default): Split them. false: Do not split them. Note This parameter takes effect only when keep_none_chinese and keep_none_chinese_together are set to true.
      	keep_original	Determines whether to keep the original input.	true: Keeps the original input. false (default): Does not keep the original input.
      	lowercase	Determines whether to convert non-Chinese letters to lowercase.	true (default) false
      	trim_whitespace	Determines whether to trim whitespace characters.	true (default) false
      	remove_duplicated_term	Determines whether to remove duplicate terms.	true: Removes stop words. false (default): Do not remove duplicates.
      	keep_separate_chinese	Determines whether to keep individual Chinese characters as separate tokens.	true: Retains the characters. false (default): Does not keep them.

Default analyzer_params

The following table shows the default analyzer_params configuration for different tokenizers.

{
  "tokenizer": {
    "type": "jieba", 
    "mode": "search",
    "hmm": true
  }, 
  "filter": [
    "removepunct",
    "lowercase",
    {"type": "stop", "stop_words": ["_english_"]},
    {"type": "stemmer", "language": "english"}
  ]
}

{
  "tokenizer": {
    "type": "whitespace"
  }
}

{
  "tokenizer": {
    "type": "keyword"
  }
}

{
  "tokenizer": {
    "type": "simple"
  }, 
  "filter": [
    "lowercase"
  ]
}

{
  "tokenizer": {
    "type": "standard",
    "max_token_length": 255
  }, 
  "filter": [
    "lowercase"
  ]
}

{
  "tokenizer": {
    "type": "icu"
  }, 
  "filter": [
    "removepunct",
    "lowercase"
  ]
}

{
  "tokenizer": {
    "type": "ik",
    "mode": "ik_max_word",
    "enable_lowercase": true
  },
  "filter": [
    {"type": "stop", "stop_words": ["_english_"]},
    {"type": "stemmer", "language": "english"}
  ]
}

{
  "tokenizer": {
    "type": "ngram",
    "min_gram": 1,
    "max_gram": 2,
    "prefix_only": false
  }
}

{
  "tokenizer": {
    "type": "pinyin",
    "keep_first_letter": true,
    "keep_separate_first_letter": false,
    "keep_full_pinyin": true,
    "keep_joined_full_pinyin": false,
    "keep_none_chinese": true,
    "keep_none_chinese_together": true,
    "none_chinese_pinyin_tokenize": true,
    "keep_original": false,
    "limit_first_letter_length": 16,
    "lowercase": true,
    "trim_whitespace": true,
    "keep_none_chinese_in_first_letter": true,
    "keep_none_chinese_in_joined_full_pinyin": false,
    "remove_duplicated_term": false,
    "ignore_pinyin_offset": true,
    "fixed_pinyin_offset": false,
    "keep_separate_chinese": false
  }
}

filter configuration

Hologres supports the following filters in analyzer_params.

"lowercase"

"_english_"
"_danish_"
"_dutch_"
"_finnish_"
"_french_"
"_german_"
"_hungarian_"
"_italian_"
"_norwegian_"
"_portuguese_"
"_russian_"
"_spanish_"
"_swedish_"

"arabic",
"danish",
"dutch",
"english",
"finnish",
"french",
"german",
"greek",
"hungarian",
"italian",
"norwegian",
"portuguese",
"romanian",
"russian",
"spanish",
"swedish",
"tamil",
"turkish"

{"type": "length", "max": 10}

"removepunct"

{
  "type": "pinyin",
  "keep_first_letter": true,
  "keep_separate_first_letter": false,
  "keep_full_pinyin": true,
  "keep_joined_full_pinyin": false,
  "keep_none_chinese": true,
  "keep_none_chinese_together": true,
  "none_chinese_pinyin_tokenize": true,
  "keep_original": false,
  "limit_first_letter_length": 16,
  "lowercase": true,
  "trim_whitespace": true,
  "keep_none_chinese_in_first_letter": true,
  "keep_none_chinese_in_joined_full_pinyin": false,
  "remove_duplicated_term": false,
  "ignore_pinyin_offset": true,
  "fixed_pinyin_offset": false,
  "keep_separate_chinese": false
}