Hologres:Full-text inverted index (Beta)

Create an index

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

Modify an index

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

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

Delete an index

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

View indexes

Hologres provides the hologres.hg_index_properties system table, which you can use to view created full-text inverted indexes and their locations.

SELECT * FROM hologres.hg_index_properties;

You can run the following SQL statement to view the table and column that correspond to 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 result of the SELECT * FROM hologres.hg_index_properties; command.

• indexname: The name of the index you created.

TEXT_SEARCH 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 outputs tokenization results based on the tokenizer configuration. You can use this function to debug the tokenization effect 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 verify that the SQL statement used a full-text inverted index. If Fulltext Filter appears in the plan, the index was used successfully. For more information about execution plans, see EXPLAIN and EXPLAIN ANALYZE.

Sample SQL:

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

The following execution plan is returned. It contains the Fulltext Filter field, which indicates that the SQL statement 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 => '长江'::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

Prepare data

You can run the following SQL statements to create a test table and write data to it.

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

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

-- Write data.
INSERT INTO wiki_articles VALUES
  (1, 'The Yangtze River is the largest river in China and the third longest river in the world, with a total length of about 6,300 kilometers.'),
  (2, 'Li was born in 1962 in Wendeng County, Shandong.'),
  (3, 'He graduated from the department of physics at Shandong University.'),
  (4, 'Spring Festival, also known as the Lunar New Year, is the most important traditional festival in China.'),
  (5, 'Spring Festival usually falls between late January and mid-February in the Gregorian calendar. The main customs during the festival include pasting spring couplets, setting off firecrackers, having a New Year''s Eve dinner, and making New Year visits.'),
  (6, 'In 2006, Spring Festival was approved by the State Council as one of the first national intangible cultural heritage items.'),
  (7, 'Shandong has dozens of universities.'),
  (8, 'ShanDa is a famous university of Shandong.');

-- Compaction
VACUUM wiki_articles;

-- Query table data.
SELECT * FROM wiki_articles limit 1;

The following is a sample result:

id |                       content                       
---+---------------------------------------------------
 1 | The Yangtze River is the largest river in China and the third longest river in the world, with a total length of about 6,300 kilometers.

Different search examples

• Keyword match.

  -- (K1) Keyword match (the default operator is OR). Documents that contain 'shandong' or 'university' are matched.
  SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university') > 0;
  
  -- The following result is returned:
   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 of Shandong.
  
  -- (K2) Keyword match (the operator is AND). Documents must contain both 'shandong' and 'university' to be matched.
  SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university', operator => 'AND') > 0;
  
  -- The following result is returned:
   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 of Shandong.

• Phrase search.

  -- (P1) Phrase search (default slop = 0). A match occurs only if 'shandong' is 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.
    
   -- (P2) Phrase search with slop = 14. The distance between 'shandong' and 'university' cannot exceed 14 characters. This matches "Shandong has dozens of universities."
  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.
  
  -- (P3) Phrase search supports phrases that are not in order, but the slop is calculated differently and must be larger than for an in-order phrase.
  -- For example, 'university of Shandong' can also match the following query, but it is not matched if slop is 22.
  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 of Shandong.
  
  -- (P4) Punctuation is ignored (for the jieba tokenizer).
  -- This is true even if the text has a comma between '长河' and '全长', while the query string has a period.
  SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, '长河。全长', mode => 'phrase') > 0;
  
  -- Result
   id |                       content                       
  ----+-----------------------------------------------------
    1 | The Yangtze River is the largest river in China and the third longest river in the world, with a total length of about 6,300 kilometers.

• Natural language query.

  -- (N1) Natural language query: Without any symbols, this is equivalent to a keyword match by default. It is the same as (K1).
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, 'shandong university', 'natural_language') > 0;
   id |                               content                               
  ----+---------------------------------------------------------------------
    7 | Shandong has dozens of universities.
    2 | Li was born in 1962 in Wendeng County, Shandong.
    3 | He graduated from the department of physics at Shandong University.
    8 | ShanDa is a famous university of Shandong.
  
  -- (N2) Natural language query: Keyword match. A match occurs if the document contains ('shandong' AND 'university') OR 'culture'. The AND operator has higher precedence than OR.
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, '(shandong AND university) OR culture', 'natural_language') > 0;
  -- Equivalent to
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, 'shandong AND university OR culture', 'natural_language') > 0;
  -- Equivalent to
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, '(+shandong +university) culture', 'natural_language') > 0;
  
  -- Result
   id |                               content                               
  ----+---------------------------------------------------------------------
    8 | ShanDa is a famous university of Shandong.
    7 | Shandong has dozens of universities.
    3 | He graduated from the department of physics at Shandong University.
    6 | In 2006, Spring Festival was approved by the State Council as one of the first national intangible cultural heritage items.
  
  -- (N3) Natural language query: Keyword match. Must contain 'shandong', must not contain 'university', and may contain 'culture'.
  --      In this query, the keyword 'culture' is not preceded by a + or - symbol. It does not affect which rows are matched, but it does affect the match score. Rows that contain 'culture' have a higher score.
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, '+shandong -university culture', 'natural_language') > 0;
   id |                     content                      
  ----+--------------------------------------------------
    2 | Li was born in 1962 in Wendeng County, Shandong.
  
  -- Must contain 'shandong', must not contain 'physics', and may contain 'famous'. The relevance score is higher if it contains 'famous'.
  -- Note: This query calculates scores on a single shard. The calculated BM25 scores may vary depending on the number of shards and file organization.
  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 of Shandong.       |  2.92376
    7 | Shandong has dozens of universities.             | 0.863399
    2 | Li was born in 1962 in Wendeng County, Shandong. | 0.716338
  
  -- (N4) Natural language query: Phrase search. Equivalent to (P1). The phrase must be enclosed in double quotation marks (""). If the phrase contains a double quotation mark, escape it with a backslash (\).
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, '"shandong university"', 'natural_language') > 0;
          
  -- Result
   id |                               content                               
  ----+---------------------------------------------------------------------
    3 | He graduated from the department of physics at Shandong University.
  
  -- (N5) Natural language query: Phrase search. Equivalent to (P2). Supports the ~ syntax to set slop.
  SELECT * FROM wiki_articles
          WHERE TEXT_SEARCH(content, '"shandong university"~23', 'natural_language') > 0;
          
  -- Result
   id |                               content                               
  ----+---------------------------------------------------------------------
    8 | ShanDa is a famous university of Shandong.
    7 | Shandong has dozens of universities.
    3 | He graduated from the department of physics at Shandong University.
  
  -- (N6) Natural language query: Match all documents.
  SELECT * FROM wiki_articles                                                                                  
          WHERE TEXT_SEARCH(content, '*', 'natural_language') > 0;
          
  -- Result
   id |                                           content                                            
  ----+----------------------------------------------------------------------------------------------
    1 | The Yangtze River is the largest river in China and the third longest river in the world, with a total length of about 6,300 kilometers.
    2 | Li was born in 1962 in Wendeng County, Shandong.
    3 | He graduated from the department of physics at Shandong University.
    4 | Spring Festival, also known as the Lunar New Year, is the most important traditional festival in China.
    5 | Spring Festival usually falls between late January and mid-February in the Gregorian calendar. The main customs during the festival include pasting spring couplets, setting off firecrackers, having a New Year''s Eve dinner, and making New Year visits.
    6 | In 2006, Spring Festival was approved by the State Council as one of the first national intangible cultural heritage items.
    7 | Shandong has dozens of universities.
    8 | ShanDa is a famous university of Shandong.

Complex query examples

• Query in conjunction with a primary key (PK).

  -- Search for text that contains 'shandong' or 'university' and has an id of 3.
  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.
    
  
  -- Search for text that contains 'shandong' or 'university', or has an id less than 2.
  SELECT * FROM wiki_articles WHERE TEXT_SEARCH(content, 'shandong university') > 0 OR id < 2;
  
  -- Result
   id |                               content                               
  ----+---------------------------------------------------------------------
    2 | Li was born in 1962 in Wendeng County, Shandong.
    8 | ShanDa is a famous university of Shandong.
    1 | The Yangtze River is the largest river in China and the third longest river in the world, with a total length of about 6,300 kilometers.
    3 | He graduated from the department of physics at Shandong University.
    7 | Shandong has dozens of universities.

• Find the scores and retrieve the top three.

  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 of 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 the TEXT_SEARCH function in both the output and WHERE clauses.

  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;
  
  -- Result
  id  |                               content                               |  score  |                     tokenize                     
  ----+---------------------------------------------------------------------+---------+--------------------------------------------------
    7 | Shandong has dozens of universities.                                | 2.74634 | {shandong,has,dozen,univers}
    8 | ShanDa is a famous university of Shandong.                          | 2.74634 | {shanda,famous,univers,shandong}
    3 | He graduated from the department of physics at Shandong University. | 2.38178 | {he,graduat,from,depart,physic,shandong,univers}
    2 | Li was born in 1962 in Wendeng County, Shandong.                    | 1.09244 | {li,born,1962,wendeng,counti,shandong}

• Search for documents from the wiki source that are most relevant to 'shandong university'.

  -- Source table for the JOIN.
  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'),
                                    (9, 'http_log'), (10, 'http_log'), (11, 'http_log');
                                    
  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}

Different tokenizer examples

• Use the default jieba tokenizer. The default is search mode, which creates more token variations to improve search results.

  SELECT TOKENIZE('他来到北京清华大学', 'jieba');
  
  -- Result
                  tokenize                
  --------------------------------------
  {他,来到,北京,清华,华大,大学,清华大学}

• Use a custom jieba tokenizer in exact mode, which does not create extra token variations.

  SELECT TOKENIZE('他来到北京清华大学', 'jieba', '{"tokenizer": {"type": "jieba", "mode": "exact"}}');
  
  -- Result
          tokenize         
  -----------------------
  {他,来到,北京,清华大学}

• Tokenizer comparison.

  SELECT TOKENIZE('他来到北京清华大学', 'jieba') as jieba,
         TOKENIZE('他来到北京清华大学', 'keyword') as keyword,
         TOKENIZE('他来到北京清华大学', 'whitespace') as whitespace,
         TOKENIZE('他来到北京清华大学', 'simple') as simple,
         TOKENIZE('他来到北京清华大学', 'standard') as standard,
         TOKENIZE('他来到北京清华大学', 'icu') as icu;
         
  -- Result
  -[ RECORD 1 ]--------------------------------------
  jieba      | {他,来到,北京,清华,华大,大学,清华大学}
  keyword    | {他来到北京清华大学}
  whitespace | {他来到北京清华大学}
  simple     | {他来到北京清华大学}
  standard   | {他,来,到,北,京,清,华,大,学}
  icu        | {他,来到,北京,清华大学}

• Tokenization effect comparison for http_logs.

  SELECT TOKENIZE('211.11.9.0 - - [1998-06-21T15:00:01-05:00] \"GET /english/index.html HTTP/1.0\" 304 0', 'jieba') as jieba,
         TOKENIZE('211.11.9.0 - - [1998-06-21T15:00:01-05:00] \"GET /english/index.html HTTP/1.0\" 304 0', 'keyword') as keyword,
         TOKENIZE('211.11.9.0 - - [1998-06-21T15:00:01-05:00] \"GET /english/index.html HTTP/1.0\" 304 0', 'whitespace') as whitespace,
         TOKENIZE('211.11.9.0 - - [1998-06-21T15:00:01-05:00] \"GET /english/index.html HTTP/1.0\" 304 0', 'simple') as simple,
         TOKENIZE('211.11.9.0 - - [1998-06-21T15:00:01-05:00] \"GET /english/index.html HTTP/1.0\" 304 0', 'standard') as standard,
         TOKENIZE('211.11.9.0 - - [1998-06-21T15:00:01-05:00] \"GET /english/index.html HTTP/1.0\" 304 0', 'icu') as icu;
         
  -- Result
  -[ RECORD 1 ]-----------------------------------------------------------------------------------------------
  jieba      | {211.11,9.0,1998-06,21t15,00,01-05,00,get,english,index,html,http,1.0,304,0}
  keyword    | {"211.11.9.0 - - [1998-06-21T15:00:01-05:00] \\\"GET /english/index.html HTTP/1.0\\\" 304 0"}
  whitespace | {211.11.9.0,-,-,[1998-06-21T15:00:01-05:00],"\\\"GET",/english/index.html,"HTTP/1.0\\\"",304,0}
  simple     | {211,11,9,0,1998,06,21t15,00,01,05,00,get,english,index,html,http,1,0,304,0}
  standard   | {211.11.9.0,1998,06,21t15,00,01,05,00,get,english,index.html,http,1.0,304,0}
  icu        | {211.11.9.0,1998,06,21t15,00,01,05,00,get,english,index.html,http,1.0,304,0}

analyzer_params configuration requirements

The configuration requirements for the analyzer_params parameter are as follows:

• Only JSON strings are supported.

• The top level of the JSON supports two keys, tokenizer and filter, which have the following values:

  Parameter	Description
  tokenizer	Required. The value is a JSON object used to configure tokenizer properties. The JSON object supports the following keys: type: Required. The tokenizer name. mode: Optional. Defines the tokenization mode. This is supported only by the jieba tokenizer. The values are: search (default): Creates multiple token combinations, which allows for redundancy. For example, tokenizing "传统节日" (traditional festival) produces three tokens: "传统" (traditional), "节日" (festival), and "传统节日" (traditional festival). exact: Prevents redundant splitting during tokenization. For example, tokenizing "传统节日" (traditional festival) produces only one token: "传统节日" (traditional festival). hmm: Optional. Defines whether to use a Hidden Markov Model to identify words not in the dictionary to improve new word detection. This is supported only by the jieba tokenizer. The values are: true (default): Use the model. false: Do not use the model.
  filter	Optional. The value is a JSON array used to configure token filter properties. If you configure multiple token filter properties, they are applied to each token in the specified order.

Default analyzer_params configurations

The default analyzer_params configurations for different tokenizers are as follows:

{
  "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"
  ]
}

春节
，
即
农历
新年
，
是
中国
最重
要
的
传统
节日
。

Filter configurations in analyzer_params

Hologres supports the following filters (token filter properties) 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"