Mempercepat Pencarian Teks Lengkap Menggunakan pg_bigm dan Indeks GIN - PolarDB

Ekstensi pg_bigm di PolarDB for PostgreSQL membuat indeks Generalized Inverted Index (GIN) 2-gram yang digunakan untuk mempercepat pencarian teks penuh.

Prasyarat

Sebuah kluster PolarDB for PostgreSQL yang menjalankan salah satu versi mesin berikut telah dibuat:

PostgreSQL 14 (versi revisi 14.5.2.0 atau lebih baru)
PostgreSQL 11 (versi revisi 1.1.28 atau lebih baru)

Catatan

Anda dapat mengeksekusi salah satu pernyataan berikut untuk melihat versi revisi dari kluster PolarDB for PostgreSQL:

PostgreSQL 14
```
SELECT version();
```
PostgreSQL 11
```
SHOW polar_version;
```

Perbandingan antara ekstensi pg_bigm dan ekstensi pg_trgm

Ekstensi pg_trgm di PolarDB for PostgreSQL menggunakan model 3-gram untuk mengimplementasikan pencarian teks penuh. Ekstensi pg_bigm dikembangkan berdasarkan ekstensi pg_trgm. Tabel berikut menjelaskan perbedaan antara kedua ekstensi tersebut.

Fungsionalitas	pg_trgm	pg_bigm
Model pencocokan frase	3-gram	2-gram
Jenis indeks	GIN dan Generalized Search Tree (GiST)	GIN
Operator	`LIKE`, `ILIKE`, `~`, dan `~*`	`LIKE`
Pencarian teks penuh non-alfabet	Tidak didukung	Didukung
Pencarian teks penuh menggunakan kata kunci yang berisi 1 hingga 2 karakter	Lambat	Cepat
Pencarian kesamaan	Didukung	Didukung
Ukuran maksimum kolom yang diindeks	238.609.291 byte (sekitar 228 MB)	107.374.180 byte (sekitar 102 MB)

Catatan penggunaan

Ukuran kolom tempat Anda membuat indeks GIN tidak boleh melebihi 107.374.180 byte, yaitu 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 dikodekan dalam format ASCII, kami sarankan Anda mengubah format pengkodean menjadi UTF-8. Eksekusi pernyataan berikut untuk menanyakan format pengkodean basis data saat ini:
```
SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = current_database();
```

Operasi dasar

Buat ekstensi pg_bigm.
```
CREATE EXTENSION pg_bigm;
```

Saat membuat indeks GIN, Anda harus menentukan operator yang disediakan oleh ekstensi pg_bigm.

CREATE TABLE pg_tools (tool text, description text);

INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Alat yang memungkinkan pengguna menentukan HINT optimizer ke PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Alat yang memungkinkan pengguna menstabilkan statistik planner di PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_bigm', 'Alat yang menyediakan kemampuan pencarian teks penuh 2-gram di PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_trgm', 'Alat yang menyediakan kemampuan pencarian teks penuh 3-gram di 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);

Lakukan pencarian teks penuh.

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

Contoh hasil:

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

Gunakan operator =% untuk melakukan pencarian kesamaan.

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

Contoh hasil:

  tool   
---------
 pg_bigm
(1 baris)

Hapus ekstensi pg_bigm.
```
DROP EXTENSION pg_bigm;
```

Fungsi dasar

likequery

Tujuan: Menghasilkan string yang dapat diidentifikasi berdasarkan kata kunci LIKE.
Parameter permintaan: Satu parameter permintaan bertipe STRING.
Nilai kembali: String yang dapat diidentifikasi berdasarkan kata kunci LIKE.
Implementasi:
- Tambahkan tanda persen (%) sebelum dan sesudah kata kunci.
- Gunakan garis miring terbalik (\) 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 baris)

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

Contoh hasil:

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

show_bigm
- Tujuan: Mendapatkan semua elemen 2-gram dari sebuah string.
- Parameter permintaan: Satu parameter permintaan 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 baris)
```
bigm_similarity
- Tujuan: Mendapatkan kesamaan antara dua string.
- Parameter permintaan: Dua parameter permintaan bertipe STRING.
- Nilai kembali: Bilangan titik mengambang yang menunjukkan kesamaan antara dua string.
- Implementasi:
  - Identifikasi elemen 2-gram yang termasuk dalam kedua string tersebut.
  - Nilai kembali berkisar dari 0 hingga 1. Nilai 0 menunjukkan bahwa kedua string berbeda. Nilai 1 menunjukkan bahwa kedua string sama.
  Catatan
  - Fungsi ini menambahkan spasi sebelum dan sesudah setiap string. Oleh karena itu, kesamaan antara string ABC dan string B adalah 0, dan kesamaan antara string ABC dan string A adalah 0,25.
  - Fungsi ini peka terhadap huruf besar/kecil. Misalnya, fungsi ini menentukan bahwa 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 baris)
```
- ```
SELECT bigm_similarity('ABC', 'A');
```
    Contoh hasil:
```
 bigm_similarity
-----------------
            0.25
(1 baris)
```
  - ```
  SELECT bigm_similarity('ABC', 'B');
```
  Contoh hasil:
```
   bigm_similarity
  -----------------
                 0
  (1 baris)
```
- ```
SELECT bigm_similarity('ABC', 'abc');
```
    Contoh hasil:
```
 bigm_similarity
-----------------
               0
(1 baris)
```
pg_gin_pending_stats
- Tujuan: Mendapatkan jumlah halaman dan jumlah tupel dalam daftar tertunda indeks GIN.
- Parameter permintaan: Satu parameter yang menentukan nama atau OID indeks GIN.
- Nilai kembali: Jumlah halaman dan jumlah tupel dalam daftar tertunda indeks GIN.
  Catatan
  Jika Anda mengatur parameter FASTUPDATE ke False untuk indeks GIN, indeks GIN tidak memiliki daftar tertunda. Dalam hal 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 baris)
```

Parameter kontrol perilaku

pg_bigm.enable_recheck

Parameter ini menentukan apakah akan melakukan pengecekan ulang.

Catatan

Kami merekomendasikan Anda menggunakan nilai default on. Dengan cara ini, Anda dapat memperoleh hasil query yang akurat.

Contoh:

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

Eksekusi pernyataan berikut.

Gunakan nilai default 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 baris)

Eksekusi pernyataan berikut:

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

Contoh hasil:

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

Atur 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 baris)

pg_bigm.gin_key_limit
Parameter ini menentukan jumlah maksimum elemen 2-gram yang dapat digunakan untuk melakukan pencarian teks penuh. Nilai default adalah 0, yang menentukan bahwa semua elemen 2-gram digunakan.
Catatan
Jika kinerja query menurun karena penggunaan semua elemen 2-gram, Anda dapat mengurangi nilai parameter ini.
pg_bigm.similarity_limit
Parameter ini menentukan ambang batas kesamaan. Tupel yang kesamaannya melebihi ambang batas yang ditentukan dikembalikan sebagai hasil pencarian kesamaan.