AnalyticDB:pg_bigm

likequery()

likequery(text) converts a search keyword into a pattern string that LIKE can handle. It wraps the keyword with % and escapes any %, _, or \ characters in the input:

postgres=> SELECT likequery('pg_bigm has improved the full text search performance by 200%');
                            likequery
-------------------------------------------------------------------
 %pg\_bigm has improved the full text search performance by 200\%%
(1 row)

postgres=> SELECT * FROM pg_tools WHERE description LIKE likequery('search');
  tool   |                      description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram full text search capability in PostgreSQL
 pg_trgm | Tool that provides 3-gram full text search capability in PostgreSQL
(2 rows)

show_bigm()

show_bigm(text) returns an array of all 2-gram elements in a string:

postgres=> SELECT show_bigm('full text search');
                           show_bigm
------------------------------------------------------------------
 {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
(1 row)

bigm_similarity()

bigm_similarity(text, text) returns a floating-point number from 0 to 1 representing the greatest similarity between the 2-gram sets of two strings. A value of 0 means completely dissimilar; a value of 1 means identical.

postgres=> SELECT bigm_similarity('full text search', 'text similarity search');
 bigm_similarity
-----------------
       0.5714286
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'A');
 bigm_similarity
-----------------
            0.25
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'B');
 bigm_similarity
-----------------
               0
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'abc');
 bigm_similarity
-----------------
               0
(1 row)

pg_gin_pending_stats()

pg_gin_pending_stats(index) returns the number of pages and tuples in the pending list of a GIN index. This is useful when fastupdate is enabled (the default), because GIN indexes batch new entries in a pending list before merging them into the main index:

postgres=> SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
 pages | tuples
-------+--------
     0 |      0
(1 row)

When an index is created with fastupdate = off, there is no pending list and the function always returns 0:

CREATE INDEX ON your_table_name (your_column_name gin_bigm_ops) WITH (fastupdate = off);

pg_bigm.last_update

Read-only. The date on which the pg_bigm extension was last updated.

pg_bigm.enable_recheck

Default: on

Controls whether the database performs a recheck after retrieving candidates from the GIN index. Leave this at the default value on.

Why this matters: GIN full-text search works in two phases. First, a bitmap index scan retrieves candidate tuples based on 2-gram matching. Then, a recheck (heap scan) verifies that each candidate actually matches the LIKE pattern—because 2-gram indexing can produce false positives. Setting enable_recheck to off skips this step, which may return incorrect results.

pg_bigm.gin_key_limit

Default: 0 (use all 2-gram elements)

Sets the maximum number of 2-gram elements used during a full-text search query. The default value of 0 means all 2-gram elements of the search keyword are used.

When to change this: For very long search keywords, using all 2-gram elements can slow down queries significantly. Set this parameter to a small positive value to limit the number of 2-gram elements used and improve performance. Note that using fewer elements increases the number of false-positive candidates that the recheck phase must filter out.

pg_bigm.similarity_limit

Sets the minimum similarity threshold for the =% similarity search operator. Only tuples whose similarity exceeds this threshold are returned.

When to change this: Increase the value to narrow results when similarity queries return too many loosely matching rows. Decrease it to broaden results for shorter or less precise search terms.

Operators

Index operator classes

Function reference