PolarDB:rum (akselerasi pencarian teks penuh)

Skenario

Tinjau tabel berikut untuk memahami perbedaan utama antara ekstensi RUM dan indeks GIN native. Informasi ini dapat membantu Anda memilih opsi terbaik untuk skenario bisnis Anda.

Ekstensi RUM menukar ruang dengan waktu. Ekstensi ini meningkatkan ukuran indeks dan overhead write untuk secara signifikan meningkatkan performa dalam skenario kueri tertentu, terutama pengurutan. Jika bisnis inti Anda melibatkan pencarian teks penuh dengan pengurutan kompleks, ekstensi RUM adalah pilihan ideal. Namun, jika bisnis Anda terutama bersifat write-intensive atau hanya memerlukan pencocokan teks sederhana, indeks GIN lebih hemat biaya.

Perhatian

• Performa write dan ukuran indeks: Untuk mempercepat kueri, indeks RUM menyimpan informasi tambahan, seperti posisi kata. Hal ini membuat ukuran indeks lebih besar dibandingkan indeks GIN dan meningkatkan overhead pembuatan indeks selama operasi write dan update data. Oleh karena itu, Anda harus mengevaluasi secara hati-hati biaya ekstensi RUM dalam skenario yang intensif write dan ketika storage space menjadi pertimbangan.

• Skema yang tidak mendukung pencarian awalan: Indeks yang dibuat dengan kelas operator rum_tsvector_hash_ops atau rum_tsvector_hash_addon_ops tidak mendukung pencarian awalan. Hal ini karena mereka menyimpan nilai hash dari lexeme, bukan teks aslinya.

Instalasi dan uninstall ekstensi

CREATE EXTENSION rum;

DROP EXTENSION rum;

Penggunaan

Ekstensi RUM menyediakan beberapa kelas operator untuk mendukung berbagai tipe data dan skenario kueri. Anda dapat memilih kelas operator yang sesuai untuk membuat indeks berdasarkan kebutuhan spesifik Anda.

Skenario 1: Mempercepat pengurutan berdasarkan relevansi untuk hasil pencarian teks penuh

Saat Anda perlu mengurutkan hasil pencarian teks penuh berdasarkan relevansi, Anda dapat menggunakan indeks RUM untuk menghindari overhead pengurutan tambahan yang diperlukan oleh indeks GIN dan mencapai pengurutan berkinerja tinggi.

1. Persiapkan data: Pertama, buat tabel uji.

   CREATE TABLE t1(
     t text,
     t_vec tsvector GENERATED ALWAYS AS (to_tsvector('pg_catalog.english', t)) STORED
   );
   
   -- Masukkan data uji
   INSERT INTO t1(t) VALUES ('The situation is most beautiful');
   INSERT INTO t1(t) VALUES ('It is a beautiful');
   INSERT INTO t1(t) VALUES ('It looks like a beautiful place');

2. Buat indeks RUM: Gunakan kelas operator rum_tsvector_ops untuk membuat indeks RUM pada kolom tsvector.

   CREATE INDEX t1_t_vec_idx ON t1 USING rum (t_vec rum_tsvector_ops);

3. Jalankan kueri pengurutan berdasarkan relevansi: Gunakan operator <=> untuk melakukan kueri dan pengurutan. Operator ini menghitung jarak antara kueri dan teks. Jarak yang lebih kecil menunjukkan relevansi yang lebih tinggi. Oleh karena itu, penggunaan ORDER BY mengurutkan hasil berdasarkan relevansi.

   SET enable_seqscan TO off;
   
   SELECT t, t_vec <=> to_tsquery('english', 'beautiful | place') AS rank
   FROM t1
   WHERE t_vec @@ to_tsquery('english', 'beautiful | place')
   ORDER BY t_vec <=> to_tsquery('english', 'beautiful | place');

   Hasil berikut dikembalikan:

                   t                |  rank   
   ---------------------------------+---------
    It looks like a beautiful place | 8.22467
    The situation is most beautiful | 16.4493
    It is a beautiful               | 16.4493

Skenario 2: Mempercepat pengurutan gabungan pencarian teks penuh dan kolom tambahan

Dalam skenario seperti analisis log atau pencarian E-dagang, Anda sering perlu melakukan pencarian teks penuh dan mengurutkan hasil berdasarkan bidang tambahan, seperti timestamp atau harga. Fitur RUM add-on memungkinkan Anda menyimpan informasi dari kolom tambahan di dalam indeks. Hal ini memungkinkan kueri dan pengurutan gabungan yang efisien.

1. Persiapkan data: Buat tabel yang berisi kolom tsvector dan kolom timestamp, lalu masukkan data sampel.

   CREATE TABLE tsts (id int, t tsvector, d timestamp);
   INSERT INTO tsts VALUES
   (354, to_tsvector('wr qh'), '2016-05-16 14:21:22.326724'),
   (355, to_tsvector('wr qh'), '2016-05-16 13:21:22.326724'),
   (356, to_tsvector('ts op'), '2016-05-16 18:21:22.326724'),
   (358, to_tsvector('ts op'), '2016-05-16 23:21:22.326724'),
   (371, to_tsvector('wr qh'), '2016-05-17 06:21:22.326724'),
   (406, to_tsvector('wr qh'), '2016-05-18 17:21:22.326724'),
   (415, to_tsvector('wr qh'), '2016-05-19 02:21:22.326724');

2. Buat indeks RUM dengan kolom tambahan: Gunakan kelas operator rum_tsvector_addon_ops dan tentukan kolom tambahan serta kolom indeks utama dalam klausa WITH.

   CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, d) WITH (attach = 'd', to = 't');

   Catatan

   Sintaks kunci WITH (attach = 'd', to = 't') melampirkan nilai kolom d (kolom tambahan, yaitu timestamp) ke entri indeks kolom t (kolom indeks utama bertipe tsvector). Hal ini memungkinkan database menggunakan indeks pada kolom t untuk pencarian teks penuh dan informasi kolom tambahan d untuk pengurutan efisien dalam satu pemindaian indeks. Proses ini menghindari lookup ke tabel dan secara signifikan meningkatkan performa.

3. Jalankan kueri pengurutan gabungan: Lakukan kueri untuk catatan yang berisi kata tertentu dan urutkan berdasarkan kedekatan timestamp-nya terhadap waktu target.

   SET enable_seqscan TO off;
   
   EXPLAIN (costs off)
   SELECT id, d, d <=> '2016-05-16 14:21:25' AS distance
   FROM tsts
   WHERE t @@ 'wr&qh'
   ORDER BY d <=> '2016-05-16 14:21:25'
   LIMIT 5;

   Rencana eksekusi berikut menunjukkan bahwa pengurutan dan penyaringan keduanya diselesaikan dalam satu pemindaian indeks.

                                     QUERY PLAN                                  
   ------------------------------------------------------------------------------
    Limit
      ->  Index Scan using tsts_idx on tsts
            Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
            Order By: (d <=> '2016-05-16 14:21:25'::timestamp without time zone)

   Hasil berikut dikembalikan:

    id  |             d              |   distance    
   -----+----------------------------+---------------
    354 | 2016-05-16 14:21:22.326724 |      2.673276
    355 | 2016-05-16 13:21:22.326724 |   3602.673276
    371 | 2016-05-17 06:21:22.326724 |  57597.326724
    406 | 2016-05-18 17:21:22.326724 | 183597.326724
    415 | 2016-05-19 02:21:22.326724 | 215997.326724

Skenario 3: Mempercepat kueri array dan pengurutan berdasarkan kemiripan

Untuk skenario seperti sistem tagging atau persona pengguna, Anda perlu melakukan kueri array yang berisi elemen tertentu secara efisien dan mengurutkannya berdasarkan tingkat tumpang tindih atau kemiripan.

1. Persiapkan data:

   CREATE TABLE test_array (id serial, i int2[]);
   INSERT INTO test_array(i) VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');

2. Buat indeks RUM untuk array: Gunakan kelas operator rum_anyarray_ops untuk membuat indeks pada kolom array.

   CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);

3. Jalankan kueri array dan pengurutan: Lakukan kueri untuk catatan yang berisi elemen 1 dan urutkan berdasarkan kemiripan dengan {1}.

   SELECT *
   FROM test_array
   WHERE i && '{1}' -- Operator '&&' menunjukkan tumpang tindih array.
   ORDER BY i <=> '{1}' ASC; -- Operator '<=>' menghitung jarak antar array. Nilai yang lebih kecil menunjukkan kemiripan yang lebih besar.

   Hasil berikut dikembalikan:

        i
   -----------
    {1}
    {1,2}
    {1,2,3}
    {1,2,3,4}

Skenario 4: Indeks terbalik untuk mencocokkan aturan kueri dengan cepat

Saat membangun sistem untuk langganan pengguna atau pencocokan aturan notifikasi, Anda perlu mencocokkan data baru, seperti artikel, dengan banyak aturan kueri yang ada, seperti kata kunci langganan pengguna. Ekstensi RUM mendukung pembuatan indeks pada tipe tsquery untuk melakukan pencocokan terbalik yang efisien.

1. Persiapkan data aturan kueri:

   CREATE TABLE query (id serial, q tsquery, tag text);
   INSERT INTO query (q, tag) VALUES
   ('supernova & star', 'sn'),
   ('black', 'color'),
   ('big & bang & black & hole', 'bang'),
   ('spiral & galaxy', 'shape'),
   ('black & hole', 'color');

2. Buat indeks RUM untuk tsquery:

   CREATE INDEX query_idx ON query USING rum(q rum_tsquery_ops);

3. Jalankan kueri pencocokan terbalik: Gunakan tsvector dari artikel baru untuk mencocokkan semua aturan tsquery yang memenuhi syarat.

   SELECT *
   FROM query
   WHERE to_tsvector('black holes never exists before we think about them') @@ q;

   Hasil berikut dikembalikan:

   id   |    q     |  tag
   -----+----------+-------
   2    | 'black'  | color