pg_bigm (fuzzy query)

Differences between pg_bigm and pg_trgm

pg_trgm is a PolarDB for PostgreSQL extension that uses a 3-gram model for full-text search. pg_bigm is built on top of pg_trgm. The following table summarizes the differences.

Usage notes

• The maximum size of a column indexed with a pg_bigm GIN index is 107,374,180 bytes (~102 MB). Example:

  CREATE TABLE t1 (description text);
  
  CREATE INDEX t1_idx ON t1 USING gin (description gin_bigm_ops);
  
  INSERT INTO t1 SELECT repeat('A', 107374181);

• If your data is not ASCII-encoded, we recommend that you use UTF-8 encoding. To check the current database encoding:

  SELECT pg_encoding_to_char(encoding)
  FROM pg_database
  WHERE datname = current_database();

Basic operations

• Create the extension.

  CREATE EXTENSION pg_bigm;

• When creating a GIN index, specify the operator class provided by the pg_bigm extension.

  CREATE TABLE pg_tools (tool text, description text);
  
  INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Tool that allows a user to specify an optimizer HINT to PostgreSQL');
  INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Tool that allows a user to stabilize planner statistics in PostgreSQL');
  INSERT INTO pg_tools VALUES ('pg_bigm', 'Tool that provides 2-gram full text search capability in PostgreSQL');
  INSERT INTO pg_tools VALUES ('pg_trgm', 'Tool that provides 3-gram full text search capability in PostgreSQL');
  
  CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
  CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);

• Perform a full-text search.

  SELECT * FROM pg_tools WHERE description LIKE '%search%';

  Result:

    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)

• Use the =% operator to perform a similarity search.

  SELECT tool FROM pg_tools WHERE tool =% 'bigm';

  Result:

    tool   
  ---------
   pg_bigm
  (1 row)

• Remove the extension.

  DROP EXTENSION pg_bigm;

Built-in functions

• likequery

  • Purpose: Generates a string that can be recognized by the LIKE operator.

  • Parameter: One string parameter.

  • Returns: A search string compatible with the LIKE operator.

  • How it works:

    • Adds % characters before and after the keyword.

    • Uses \ to automatically escape % characters.

  • Examples:

    • SELECT likequery('pg_bigm has improved the full text search performance by 200%');

      Result:

                                   likequery
      -------------------------------------------------------------------
       %pg\_bigm has improved the full text search performance by 200\%%
      (1 row)

    • SELECT * FROM pg_tools WHERE description LIKE likequery('search');

      Result:

        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

  • Purpose: Returns all 2-gram elements of a given string as an array.

  • Parameter: One string parameter.

  • Returns: An array containing all 2-gram elements.

  • How it works:

    • Adds a leading and trailing space to the string.

    • Extracts all 2-gram substrings.

  • Example:

    SELECT show_bigm('full text search');

    Result:

                                show_bigm
    ------------------------------------------------------------------
     {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
    (1 row)

• bigm_similarity

  • Purpose: Calculates the similarity between two strings.

  • Parameters: Two string parameters.

  • Returns: A floating-point number representing the similarity.

  • How it works:

    • Counts the 2-gram elements shared by both strings.

    • The similarity range is [0, 1], where 0 means the strings are completely different and 1 means they are identical.

    Note

    • Because spaces are added before and after the string when calculating 2-grams, ABC and B have a similarity of 0, and ABC and A have a similarity of 0.25.

    • bigm_similarity is case-sensitive. For example, ABC and abc have a similarity of 0.

  • Examples:

    • SELECT bigm_similarity('full text search', 'text similarity search');

      Result:

       bigm_similarity 
      -----------------
              0.571429
      (1 row)

    • SELECT bigm_similarity('ABC', 'A');

      Result:

       bigm_similarity
      -----------------
                  0.25
      (1 row)

    • SELECT bigm_similarity('ABC', 'B');

      Result:

       bigm_similarity
      -----------------
                     0
      (1 row)

    • SELECT bigm_similarity('ABC', 'abc');

      Result:

       bigm_similarity
      -----------------
                     0
      (1 row)

• pg_gin_pending_stats

  • Purpose: Returns the number of pages and tuples in the pending list of a GIN index.

  • Parameter: The name or OID of the GIN index.

  • Returns: Two values - the number of pages and the number of tuples in the pending list.

    Note

    If the GIN index was created with FASTUPDATE set to False, the pending list does not exist and the function returns 0.

  • Example:

    SELECT * FROM pg_gin_pending_stats('pg_tools_idx');

    Result:

     pages | tuples
    -------+--------
         0 |      0
    (1 row)

Behavior control parameters

• pg_bigm.enable_recheck

  Controls whether a recheck step is performed after the GIN index scan.

  Note

  We recommend that you keep the default value (ON) to ensure result accuracy.

  Example:

  1. Prepare test data.

     CREATE TABLE tbl (doc text);
     
     INSERT INTO tbl VALUES('He is awaiting trial');
     INSERT INTO tbl VALUES('It was a trivial mistake');
     
     CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);

  2. Run the following query.

     • When pg_bigm.enable_recheck is set to on, a recheck is performed:

       SET enable_seqscan TO off;
       
       EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('trial');

       Result:

                                                          QUERY PLAN
       -----------------------------------------------------------------------------------------------------------------
        Bitmap Heap Scan on tbl  (cost=20.00..24.01 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=1)
          Recheck Cond: (doc ~~ '%trial%'::text)
          Rows Removed by Index Recheck: 1
          Heap Blocks: exact=1
          ->  Bitmap Index Scan on tbl_idx  (cost=0.00..20.00 rows=1 width=0) (actual time=0.013..0.013 rows=2 loops=1)
                Index Cond: (doc ~~ '%trial%'::text)
        Planning Time: 0.117 ms
        Execution Time: 0.043 ms
       (8 rows)

       Run the following query:

       SELECT * FROM tbl WHERE doc LIKE likequery('trial');

       Result:

                doc
       ----------------------
        He is awaiting trial
       (1 row)

     • When pg_bigm.enable_recheck is set to off, no recheck is performed:

       SET pg_bigm.enable_recheck = off;
       
       SELECT * FROM tbl WHERE doc LIKE likequery('trial');

       Result:

                  doc
       --------------------------
        He is awaiting trial
        It was a trivial mistake
       (2 rows)

• pg_bigm.gin_key_limit

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

  Note

  If using all 2-gram elements causes performance degradation, you can adjust this value to limit the number of 2-gram elements to improve performance.

• pg_bigm.similarity_limit

  Sets the similarity threshold. Tuples with a similarity score above this threshold are returned as results of a similarity search.