ApsaraDB RDS：Kueri fuzzy (pg_bigm)

Perbandingan dengan pg_trgm

pg_trgm adalah ekstensi lain untuk RDS for PostgreSQL yang menggunakan model 3-gram untuk pencarian teks penuh. Ekstensi pg_bigm didasarkan pada pg_trgm. Perbedaan utama antara keduanya adalah sebagai berikut:

Catatan

• Panjang kolom tempat indeks GIN dibuat tidak boleh melebihi 107.374.180 byte (sekitar 102 MB).

• Jika konten yang disimpan di database bersifat non-ASCII, Anda harus mengubah kodek database menjadi UTF8.

  Catatan

  Untuk memeriksa kodek database saat ini, jalankan perintah berikut: select pg_encoding_to_char(encoding) from pg_database where datname = current_database();.

Operasi dasar

• Buat ekstensi

  postgres=> create extension pg_bigm;
  CREATE EXTENSION

• Buat indeks

  postgres=> CREATE TABLE pg_tools (tool text, description text);
  CREATE TABLE
  postgres=> INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Alat yang memungkinkan pengguna menentukan HINT optimizer ke PostgreSQL');
  INSERT 0 1
  postgres=> INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Alat yang memungkinkan pengguna menstabilkan statistik planner di PostgreSQL');
  INSERT 0 1
  postgres=> INSERT INTO pg_tools VALUES ('pg_bigm', 'Alat yang menyediakan kemampuan pencarian teks penuh 2-gram di PostgreSQL');
  INSERT 0 1
  postgres=> INSERT INTO pg_tools VALUES ('pg_trgm', 'Alat yang menyediakan kemampuan pencarian teks penuh 3-gram di PostgreSQL');
  INSERT 0 1
  postgres=> CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
  CREATE INDEX
  postgres=> CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);
  CREATE INDEX

• Lakukan pencarian teks penuh

  postgres=> SELECT * FROM pg_tools WHERE description LIKE '%search%';
    tool   |                             description
  ---------+---------------------------------------------------------------------
   pg_bigm | Alat yang menyediakan kemampuan pencarian teks penuh 2-gram di PostgreSQL
   pg_trgm | Alat yang menyediakan kemampuan pencarian teks penuh 3-gram di PostgreSQL
  (2 rows)

• Lakukan pencarian kesamaan menggunakan operator =%

  postgres=> SET pg_bigm.similarity_limit TO 0.2;
  SET
  postgres=> SELECT tool FROM pg_tools WHERE tool =% 'bigm';
    tool
  ---------
   pg_bigm
   pg_trgm
  (2 rows)

• Hapus ekstensi

  postgres=> drop extension pg_bigm;
  DROP EXTENSION

Fungsi ekstensi umum

• Fungsi likequery

  • Fungsi: Menghasilkan string yang dapat dikenali oleh kata kunci LIKE.

  • Parameter: Satu parameter bertipe string.

  • Nilai kembali: String pencarian yang dapat digunakan dengan kata kunci LIKE.

  • Cara kerja:

    • Menambahkan simbol % sebelum dan sesudah kata kunci.

    • Menggunakan \ untuk secara otomatis meloloskan karakter %.

  • Contoh:

    postgres=> SELECT likequery('pg_bigm telah meningkatkan performa pencarian teks penuh sebesar 200%');
                                 likequery
    -------------------------------------------------------------------
     %pg\_bigm telah meningkatkan performa pencarian teks penuh sebesar 200\%%
    (1 row)
    
    postgres=> SELECT * FROM pg_tools WHERE description LIKE likequery('search');
      tool   |                             description
    ---------+---------------------------------------------------------------------
     pg_bigm | Alat yang menyediakan kemampuan pencarian teks penuh 2-gram di PostgreSQL
     pg_trgm | Alat yang menyediakan kemampuan pencarian teks penuh 3-gram di PostgreSQL
    (2 rows)

• Fungsi show_bigm

  • Fungsi: Mengembalikan kumpulan semua elemen 2-gram untuk string tertentu.

  • Parameter: Satu parameter bertipe string.

  • Nilai kembali: Array yang berisi semua elemen 2-gram.

  • Cara kerja:

    • Menambahkan karakter spasi sebelum dan sesudah string.

    • Menghitung semua substring 2-gram.

  • Contoh:

    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)

• Fungsi bigm_similarity

  • Fungsi: Menghitung tingkat kesamaan antara dua string.

  • Parameter: Dua parameter bertipe string.

  • Nilai kembali: Bilangan titik mengambang yang menunjukkan tingkat kesamaan.

  • Cara kerja:

    • Menghitung elemen 2-gram yang sama antara kedua string.

    • Tingkat kesamaan berkisar dari 0 hingga 1. Nilai 0 menunjukkan bahwa kedua string sepenuhnya berbeda. Nilai 1 menunjukkan bahwa kedua string identik.

    Catatan

    • Karena spasi ditambahkan sebelum dan sesudah string ketika 2-gram dihitung, kesamaan antara ABC dan B adalah 0, dan kesamaan antara ABC dan A adalah 0,25.

    • Fungsi `bigm_similarity` peka huruf besar/kecil. Misalnya, kesamaan antara ABC dan abc adalah 0.

  • Contoh:

    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)

• Fungsi pg_gin_pending_stats

  • Fungsi: Mengembalikan jumlah halaman dan tupel dalam daftar tertunda indeks GIN.

  • Parameter: Nama atau OID indeks GIN.

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

    Catatan

    Jika parameter FASTUPDATE diatur ke False saat indeks GIN dibuat, indeks GIN tidak memiliki daftar tertunda. Dalam hal ini, fungsi mengembalikan 0.

  • Contoh:

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

Kontrol perilaku ekstensi

• pg_bigm.last_update

  Tanggal pembaruan terakhir ekstensi ini. Ini adalah parameter read-only dan tidak dapat diubah.

  Contoh:

  SHOW pg_bigm.last_update;

• pg_bigm.enable_recheck

  Menentukan apakah akan melakukan pengecekan ulang.

  Catatan

  Kami sarankan Anda tetap menggunakan nilai default (ON) untuk memastikan hasil yang benar.

  Contoh:

  postgres=> CREATE TABLE tbl (doc text);
  CREATE TABLE
  postgres=> INSERT INTO tbl VALUES('Dia sedang menunggu persidangan');
  INSERT 0 1
  postgres=> INSERT INTO tbl VALUES('Itu adalah kesalahan sepele');
  INSERT 0 1
  postgres=> CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
  CREATE INDEX
  postgres=> SET enable_seqscan TO off;
  SET
  postgres=> EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('persidangan');
                                                     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 ~~ '%persidangan%'::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 ~~ '%persidangan%'::text)
   Planning Time: 0.117 ms
   Execution Time: 0.043 ms
  (8 rows)
  
  postgres=>
  postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('persidangan');
           doc
  ----------------------
   Dia sedang menunggu persidangan
  (1 row)
  
  postgres=> SET pg_bigm.enable_recheck = off;
  SET
  postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('persidangan');
             doc
  --------------------------
   Dia sedang menunggu persidangan
   Itu adalah kesalahan sepele
  (2 rows)

• pg_bigm.gin_key_limit

  Membatasi jumlah maksimum elemen 2-gram yang digunakan untuk pencarian teks penuh. Nilai default adalah 0, yang berarti semua elemen 2-gram digunakan.

  Catatan

  Jika Anda menemukan bahwa menggunakan semua elemen 2-gram menurunkan performa, Anda dapat menyesuaikan parameter ini untuk membatasi jumlah elemen 2-gram dan meningkatkan performa.

• pg_bigm.similarity_limit

  Menetapkan ambang batas kesamaan. Tupel dengan tingkat kesamaan yang melebihi ambang ini dikembalikan oleh pencarian kesamaan.