PolarDB:pg_bigm

Perbandingan antara ekstensi pg_bigm dan ekstensi pg_trgm

Ekstensi pg_trgm di PolarDB for PostgreSQL (Kompatibel dengan Oracle) menggunakan model 3-gram untuk pencarian teks penuh. Ekstensi pg_bigm dikembangkan berdasarkan pg_trgm. Tabel berikut menunjukkan perbedaan utama antara kedua ekstensi tersebut.

Catatan penggunaan

• Ukuran kolom tempat indeks GIN dibuat tidak boleh melebihi 107.374.180 byte (sekitar 102 MB). Contoh pernyataan:

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

• Jika data dalam kluster PolarDB Anda tidak menggunakan format ASCII, disarankan untuk mengubahnya ke UTF-8. Gunakan pernyataan berikut untuk memeriksa format pengkodean basis data saat ini:

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

Operasi dasar

• Membuat ekstensi pg_bigm.

  CREATE EXTENSION pg_bigm;

• Saat membuat indeks GIN, tentukan operator yang disediakan oleh ekstensi pg_bigm.

  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);

• Melakukan pencarian teks penuh.

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

  Contoh hasil:

    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)

• Gunakan operator =% untuk pencarian kesamaan.

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

  Contoh hasil:

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

• Menghapus ekstensi pg_bigm.

  DROP EXTENSION pg_bigm;

Fungsi dasar

• likequery

  • Tujuan: Menghasilkan string yang dapat diidentifikasi berdasarkan kata kunci LIKE.

  • Parameter permintaan: Satu parameter bertipe STRING.

  • Nilai kembali: String yang dapat diidentifikasi berdasarkan kata kunci LIKE.

  • Implementasi:

    • Tambahkan tanda persen (%) sebelum dan sesudah kata kunci.

    • Gunakan backslash (\) untuk meloloskan tanda persen (%).

  • Contoh:

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

      Contoh hasil:

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

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

      Contoh hasil:

        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

  • Tujuan: Mendapatkan semua elemen 2-gram dari sebuah string.

  • Parameter permintaan: Satu parameter bertipe STRING.

  • Nilai kembali: Array yang terdiri dari semua elemen 2-gram dari sebuah string.

  • Implementasi:

    • Tambahkan spasi sebelum dan sesudah string.

    • Identifikasi semua elemen 2-gram dalam string.

  • Contoh:

    SELECT show_bigm('full text search');

    Contoh hasil:

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

• bigm_similarity

  • Tujuan: Mendapatkan tingkat kesamaan antara dua string.

  • Parameter permintaan: Dua parameter bertipe STRING.

  • Nilai kembali: Bilangan floating-point yang menunjukkan tingkat kesamaan antara dua string.

  • Implementasi:

    • Identifikasi elemen 2-gram yang termasuk dalam kedua string.

    • Nilai kembali berkisar antara 0 hingga 1. Nilai 0 menunjukkan bahwa kedua string berbeda, sedangkan nilai 1 menunjukkan bahwa kedua string sama.

    Catatan

    • Fungsi ini menambahkan spasi sebelum dan sesudah setiap string. Oleh karena itu, tingkat kesamaan antara string ABC dan string B adalah 0, serta tingkat kesamaan antara string ABC dan string A adalah 0,25.

    • Fungsi ini peka huruf besar/kecil. Sebagai contoh, fungsi ini menentukan bahwa tingkat kesamaan antara string ABC dan string abc adalah 0.

  • Contoh:

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

      Contoh hasil:

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

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

      Contoh hasil:

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

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

      Contoh hasil:

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

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

      Contoh hasil:

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

• pg_gin_pending_stats

  • Tujuan: Mendapatkan jumlah halaman dan jumlah tupel dalam daftar tertunda indeks GIN.

  • Parameter permintaan: Nama atau OID dari indeks GIN.

  • Nilai kembali: Jumlah halaman dan jumlah tupel dalam daftar tertunda indeks GIN.

    Catatan

    Jika parameter FASTUPDATE diatur ke False untuk indeks GIN, indeks GIN tidak memiliki daftar tertunda. Dalam kasus ini, fungsi ini mengembalikan dua nilai, 0 dan 0.

  • Contoh:

    SELECT * FROM pg_gin_pending_stats('pg_tools_idx');

    Contoh hasil:

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

Parameter kontrol perilaku

• pg_bigm.enable_recheck

  Parameter ini menentukan apakah pengecekan ulang dilakukan.

  Catatan

  Disarankan untuk menggunakan nilai default on agar hasil kueri tetap akurat.

  Contoh:

  1. Siapkan data uji.

     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. Eksekusi pernyataan berikut.

     • Gunakan nilai default dari parameter pg_bigm.enable_recheck untuk melakukan pengecekan ulang.

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

       Contoh hasil:

                                                          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)

       Eksekusi pernyataan berikut:

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

       Contoh hasil:

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

     • Setel parameter pg_bigm.enable_recheck ke off untuk mencegah pengecekan ulang.

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

       Contoh hasil:

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

• pg_bigm.gin_key_limit

  Parameter ini menentukan jumlah maksimum elemen 2-gram yang digunakan untuk pencarian teks penuh. Nilai defaultnya adalah 0, yang berarti semua elemen 2-gram digunakan.

  Catatan

  Jika performa kueri menurun karena penggunaan semua elemen 2-gram, turunkan nilai parameter ini.

• pg_bigm.similarity_limit

  Parameter ini menentukan ambang batas kesamaan. Tupel dengan tingkat kesamaan melebihi ambang batas akan dikembalikan sebagai hasil pencarian kesamaan.