AnalyticDB：pg_trgm

Pengenalan

Ekstensi pg_trgm memperkenalkan konsep trigram. Trigram terdiri dari tiga karakter berturut-turut yang diambil dari sebuah string. pg_trgm mengekstrak trigram yang terdiri dari tiga karakter. Trigram yang memiliki kurang dari tiga karakter berisi awalan hingga dua spasi atau akhiran hanya satu spasi. Contoh:

postgres=# SELECT show_trgm('abc');
        show_trgm
-------------------------
 {"  a"," ab","abc","bc "}
(1 baris)

Anda dapat mengukur kesamaan antara dua string dengan menghitung jumlah trigram yang dimiliki kedua string tersebut. Ide ini sangat efektif untuk mengukur kesamaan kata dalam banyak bahasa alami. Topik ini menjelaskan cara menggunakan ekstensi pg_trgm.

Catatan penggunaan

• AnalyticDB for PostgreSQL V6.0 instance versi V6.3.8.9 atau lebih baru mendukung ekstensi pg_trgm.

• AnalyticDB for PostgreSQL V7.0 instance versi V7.0.2 atau lebih baru mendukung ekstensi pg_trgm.

Instal ekstensi

Sebelum menggunakan ekstensi pg_trgm untuk melakukan pencarian kesamaan teks dalam instance AnalyticDB for PostgreSQL, instal ekstensi pg_trgm pada halaman Ekstensi dari instance tersebut. Untuk informasi lebih lanjut, lihat Instal, perbarui, dan hapus ekstensi.

Gunakan pencocokan trigram untuk melakukan pencarian kesamaan teks

Setelah menginstal ekstensi pg_trgm, Anda dapat menggunakan pencocokan trigram untuk mengukur kesamaan teks dan mengurutkan hasil pencarian dari kecocokan terbaik hingga terburuk. Sebagai contoh, buat tabel uji dan masukkan data ke dalam tabel.

CREATE TABLE test_trgm (t text);
INSERT INTO test_trgm values('word'), ('This is a pg_trgm test'), ('word test'), ('w0rd'), ('test word');

Anda dapat menggunakan salah satu metode berikut untuk melakukan pencarian kesamaan teks berdasarkan pencocokan trigram:

• Metode 1: Gunakan fungsi similarity() dan operator % dalam ekstensi pg_trgm. Sebagai contoh, kueri kesamaan antara nilai kolom t dan string word. Untuk informasi lebih lanjut tentang fungsi similarity() dan operator %, lihat bagian "Lampiran" dari topik ini.

  SELECT t, similarity(t, 'word') AS sml
    FROM test_trgm
    WHERE t % 'word'
    ORDER BY sml DESC, t;

  Nilai kolom yang berisi word ditampilkan dalam urutan menurun berdasarkan kesamaan.

       t     | sml
  -----------+-----
   word      |   1
   test word | 0.5
   word test | 0.5
  (3 baris)

• Metode 2: Gunakan operator <-> dalam ekstensi pg_trgm. Sebagai contoh, kueri jarak antara nilai kolom t dan string word. Jarak adalah kebalikan dari kesamaan. Nilai kolom ditampilkan dalam urutan jarak dari yang terdekat hingga yang terjauh. Untuk informasi lebih lanjut tentang operator <->, lihat bagian "Lampiran" dari topik ini.

  postgres=# SELECT t, t <-> 'word' AS dist
    FROM test_trgm
    ORDER BY dist LIMIT 10;
             t            | dist
  ------------------------+------
   word                   |    0
   word test              |  0.5
   test word              |  0.5
   w0rd                   | 0.75
   This is a pg_trgm test |    1
  (5 baris)

Gunakan indeks untuk mempercepat pencarian fuzzy

Sebelum ekstensi pg_trgm tersedia, operator LIKE dapat digunakan untuk melakukan pencarian fuzzy. Namun, operator LIKE tidak mendukung indeks dan memberikan kinerja kueri yang buruk. Ekstensi pg_trgm menyediakan operator indeks Generalized Search Tree (GiST) dan Generalized Inverted Index (GIN) yang memungkinkan Anda membuat indeks untuk kolom teks guna mempercepat pencarian kesamaan. Contoh berikut menjelaskan cara menggunakan ekstensi pg_trgm dan indeks GIN untuk mempercepat pencarian fuzzy:

Buat tabel uji dan masukkan data ke dalam tabel.

CREATE TABLE test_trgm (t text);
INSERT INTO test_trgm 
SELECT md5(random()::text) FROM generate_series(1,1000000) i; 

Gunakan pernyataan EXPLAIN ANALYZE untuk melakukan pencarian fuzzy pada tabel. Rencana eksekusi menunjukkan bahwa pemindaian sekuensial dilakukan pada seluruh tabel. Ini menghasilkan kinerja kueri yang buruk dalam skenario yang melibatkan sejumlah besar data.

postgres=# explain analyze  SELECT * FROM test_trgm WHERE t LIKE '%abcd%';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..259.63 rows=422 width=32) (actual time=137.606..179.674 rows=431 loops=1)
   ->  Seq Scan on test_trgm  (cost=0.00..254.00 rows=141 width=32) (actual time=0.961..136.977 rows=146 loops=1)
         Filter: (t ~~ '%abcd%'::text)
         Rows Removed by Filter: 333458
 Optimizer: Postgres-based planner
 Planning Time: 0.328 ms
   (slice0)    Executor memory: 37K bytes.
   (slice1)    Executor memory: 36K bytes avg x 3 workers, 36K bytes max (seg0).
 Memory used:  128000kB
 Execution Time: 180.533 ms
(10 baris)

Gunakan operator indeks gin_trgm_ops dalam ekstensi pg_trgm untuk membuat indeks GIN. Untuk informasi lebih lanjut tentang operator indeks gin_trgm_ops, lihat bagian "Lampiran" dari topik ini.

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

Gunakan pernyataan EXPLAIN ANALYZE untuk melakukan pencarian fuzzy pada tabel. Rencana eksekusi menunjukkan bahwa pemindaian indeks bitmap dilakukan. Kinerja kueri meningkat secara signifikan.

postgres=# explain analyze  SELECT * FROM test_trgm WHERE t LIKE '%abcd%';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=48.67..882.67 rows=8000 width=32) (actual time=4.353..4.550 rows=431 loops=1)
   ->  Bitmap Heap Scan on test_trgm  (cost=48.67..776.00 rows=2667 width=32) (actual time=1.612..3.091 rows=146 loops=1)
         Recheck Cond: (t ~~ '%abcd%'::text)
         Rows Removed by Index Recheck: 10
         ->  Bitmap Index Scan on trgm_idx  (cost=0.00..48.00 rows=2667 width=0) (actual time=1.536..1.537 rows=163 loops=1)
               Index Cond: (t ~~ '%abcd%'::text)
 Optimizer: Postgres-based planner
 Planning Time: 1.353 ms
   (slice0)    Executor memory: 44K bytes.
   (slice1)    Executor memory: 2438K bytes avg x 3 workers, 2438K bytes max (seg0).
 Memory used:  128000kB
 Execution Time: 5.385 ms
(12 baris)

Lampiran

Parameter Grand Unified Configuration (GUC)

pg_trgm.similarity_threshold

Menentukan ambang batas kesamaan saat ini yang digunakan oleh operator %. Ambang batas harus antara 0 dan 1. Nilai default: 0.3.

pg_trgm.word_similarity_threshold

Menentukan ambang batas kesamaan kata saat ini yang digunakan oleh operator <% dan %>. Ambang batas harus antara 0 dan 1. Nilai default: 0.6.

Fungsi

Operator

Operator indeks

Referensi

Untuk informasi tentang fungsionalitas ekstensi pg_trgm yang didukung oleh ApsaraDB RDS for PostgreSQL, lihat Ekstensi yang Didukung.