LIKE 演算子によるキーワード検索が遅すぎる、またはステミング、関連度ランキング、インデックスサポートといった言語的なインテリジェンスに欠ける場合、全文検索がこれらの問題を解決します。このガイドでは、PolarDB for PostgreSQL (Compatible with Oracle) で RUM 拡張機能を使用して全文検索を行う方法を説明します。拡張機能のインストール、テーブルとインデックスの作成、英語と中国語のテキストに対するランク付けされたクエリの実行までを網羅します。
前提条件
開始する前に、以下が準備できていることを確認してください：
PolarDB for PostgreSQL (Compatible with Oracle) クラスター
CREATE EXTENSION権限を持つデータベース接続認証情報
拡張機能のインストール
全文検索のために RUM 拡張機能をインストールします：
CREATE EXTENSION rum;中国語テキストをサポートするには、中国語の単語分割のための pg_jieba 拡張機能もインストールします：
CREATE EXTENSION pg_jieba;バージョン要件については、「」および「」をご参照ください。
テーブルの作成
英語テキスト用のテーブルを作成します：
CREATE TABLE test_english(id serial, t text, d timestamp);中国語テキスト用のテーブルを作成します。v 列には、インデックス作成とクエリに使用される、事前に計算された tsvector が格納されます：
CREATE TABLE test_chinese(id serial, t text, d timestamp, v tsvector);データの挿入
このガイドの例では、以下のテストデータを使用します。
英語テーブル (`test_english`)
| id | t | d |
|---|---|---|
| 1 | The situation is most beautiful | 2016-05-01 20:21:22 |
| 2 | It is a beautiful | 2016-05-01 21:21:22 |
| 3 | It looks like a beautiful place | 2016-05-01 22:21:22 |
| 4 | It looks l a beautiful place | 2016-05-02 00:21:22 |
| 5 | It looks like a beautiful places | 2016-05-02 02:21:22 |
| 6 | It looks like a beaut places | 2016-05-02 03:21:22 |
| 7 | It looks like a pig | 2016-05-02 03:21:22 |
中国語テーブル (`test_chinese`) — 4 行の中国の金融ニューステキスト。すべてタイムスタンプは 2016-05-01 20:21:22 です。
| id | t (抜粋) | d |
|---|---|---|
| 1 | 南财理财通数据显示，5月公募理财产品... | 2016-05-01 20:21:22 |
| 2 | 其中，信银理财"安盈象固收稳利..."... | 2016-05-01 20:21:22 |
| 3 | 5月新发产品募集规模最大的是信银理财... | 2016-05-01 20:21:22 |
| 4 | 南财理财通数据显示，"安盈象固收稳利..."... | 2016-05-01 20:21:22 |
英語テーブルにデータを入力します：
INSERT INTO test_english(t,d) VALUES ('The situation is most beautiful', '2016-05-01 20:21:22.326724');
INSERT INTO test_english(t,d) VALUES ('It is a beautiful', '2016-05-01 21:21:22.326724');
INSERT INTO test_english(t,d) VALUES ('It looks like a beautiful place', '2016-05-01 22:21:22.326724');
INSERT INTO test_english(t,d) VALUES ('It looks l a beautiful place', '2016-05-02 00:21:22.326724');
INSERT INTO test_english(t,d) VALUES ('It looks like a beautiful places', '2016-05-02 02:21:22.326724');
INSERT INTO test_english(t,d) VALUES ('It looks like a beaut places', '2016-05-02 03:21:22.326724');
INSERT INTO test_english(t,d) VALUES ('It looks like a pig', '2016-05-02 03:21:22.326724');中国語テーブルにデータを入力し、中国語の単語分割のための jiebacfg 構成を使用して tsvector 列を計算します：
INSERT INTO test_chinese(t,d) VALUES ('南财理财通数据显示，5月公募理财产品吸金榜前十名归属于5家机构，信银理财有5只产品上榜，南银理财占两席，民生理财、恒丰理财、浦银理财各占一席，上榜产品均为中低风险的封闭式固收类产品。从投资周期看，本月榜单产品中有6只产品投资周期为1-2年，3-6个月及6-12个月期限产品各有2只。', '2016-05-01 20:21:22.326724');
INSERT INTO test_chinese(t,d) VALUES ('其中，信银理财"安盈象固收稳利十四个月封闭式95号"理财产品夺得吸金榜冠军，募集规模达到106.204 亿元；民生理财"富竹固收优选14个月封闭39号"位列第二，募集规模为46.606亿元；信银理财"安盈象固收稳利十四个月封闭式94号"排名第三，募集规模为41.365 亿元。', '2016-05-01 20:21:22.326724');
INSERT INTO test_chinese(t,d) VALUES ('5月新发产品募集规模最大的是信银理财"安盈象固收稳利十四个月封闭式95号"，实际募集资金高达106.204亿元，该系列产品已蝉联4个月榜首。', '2016-05-01 20:21:22.326724');
INSERT INTO test_chinese(t,d) VALUES ('南财理财通数据显示，"安盈象固收稳利十四个月封闭式95号"理财产品成立于2024年05月16日，将于2025年07月16日到期，风险评级PR2（中低风险），产品有A、C、D、K类份额，投资周期1-2年，业绩比较基准2.90%~3.45%。', '2016-05-01 20:21:22.326724');
-- Populate the tsvector column. Run this after each INSERT, or automate it with a trigger.
UPDATE test_chinese
SET v = to_tsvector('jiebacfg', t);上記の UPDATE 文は既存の行にデータを入力します。本番環境では、書き込みのたびにこの UPDATE を実行するか、すべての INSERT または UPDATE で自動的に to_tsvector('jiebacfg', t) を呼び出すトリガーを作成することで、v 列を最新の状態に保ってください。
インデックスの作成
生のテキスト列に直接インデックスを付ける rum_text_ops を使用して、英語テーブルに RUM インデックスを作成します：
CREATE INDEX ON test_english USING rum (t rum_text_ops);事前に計算された tsvector 列にインデックスを付ける rum_tsvector_ops を使用して、中国語テーブルに RUM インデックスを作成します：
CREATE INDEX ON test_chinese USING rum (v rum_tsvector_ops);クエリの実行
全文検索クエリでは、主に 2 つの演算子を使用します：
| 演算子 | 説明 |
|---|---|
@@ | 一致演算子。tsvector が tsquery と一致する場合に true を返します。 |
<=> | 距離演算子 (RUM 固有)。浮動小数点数のスコアを返します。値が大きいほど関連性が低い (クエリ用語からの距離が大きい) ことを意味します。 |
英語テーブルへのクエリ
`beautiful` または `place` を含む行を照合：
SELECT * FROM test_english
WHERE to_tsvector('english', t) @@ to_tsquery('english', 'beautiful | place');出力：
id | t | d
----+----------------------------------+----------------------------
1 | The situation is most beautiful | 2016-05-01 20:21:22.326724
2 | It is a beautiful | 2016-05-01 21:21:22.326724
3 | It looks like a beautiful place | 2016-05-01 22:21:22.326724
4 | It looks l a beautiful place | 2016-05-02 00:21:22.326724
5 | It looks like a beautiful places | 2016-05-02 02:21:22.326724
6 | It looks like a beaut places | 2016-05-02 03:21:22.326724`beautiful` または `place` を含む行を、距離スコアで並べ替えて照合：
<=> 演算子は、一致する各行に距離スコアを割り当てます。スコアが高いほど関連性が低いことを意味するため、DESC で並べ替えると、関連性が最も低い行が最初に表示されます。クエリ用語の 1 つにしか一致しない行は、両方の用語に一致する行よりも高いスコアを受け取ります。
SELECT *, to_tsvector('english', t) <=> to_tsquery('english', 'beautiful | place') AS rank
FROM test_english
WHERE to_tsvector('english', t) @@ to_tsquery('english', 'beautiful | place')
ORDER BY to_tsvector('english', t) <=> to_tsquery('english', 'beautiful | place') DESC;出力：
id | t | d | rank
----+----------------------------------+----------------------------+---------
1 | The situation is most beautiful | 2016-05-01 20:21:22.326724 | 16.4493
2 | It is a beautiful | 2016-05-01 21:21:22.326724 | 16.4493
6 | It looks like a beaut places | 2016-05-02 03:21:22.326724 | 16.4493
3 | It looks like a beautiful place | 2016-05-01 22:21:22.326724 | 8.22467
4 | It looks l a beautiful place | 2016-05-02 00:21:22.326724 | 8.22467
5 | It looks like a beautiful places | 2016-05-02 02:21:22.326724 | 8.22467
(6 rows)行 1、2、6 はそれぞれ 1 つのクエリ用語 (beautiful または place のいずれか) にしか一致しないため、より高い距離スコア (16.4493) が付けられています。行 3〜5 は両方の用語に一致するため、距離はより低く (8.22467) なります。関連性が最も高い順に並べ替えるには、DESC を ASC に変更します。
中国語テーブルへのクエリ
`数据` または `理财` を含む行を、距離スコアで並べ替えて照合：
SELECT id, v <=> to_tsquery('jiebacfg', '数据 | 理财') as rank
FROM test_chinese
WHERE v @@ to_tsquery('jiebacfg', '数据 | 理财');出力：
id | rank
----+-----------
1 | 6.6024785
2 | 12.08523
3 | 16.44934
4 | 8.22467`v` が `数据` を含み、`数据 | 理财` への類似度でランク付けされた行を照合：
SELECT id, v <=> to_tsquery('jiebacfg', '数据 | 理财') as rank
FROM test_chinese
WHERE v @@ to_tsquery('jiebacfg', '数据');出力：
id | rank
----+-----------
1 | 6.6024785
4 | 8.22467拡張機能のアンインストール (任意)
DROP EXTENSION rum CASCADE;
DROP EXTENSION pg_jieba CASCADE;次のステップ
— 中国語の単語分割の構成とオプション
— RUM 拡張機能のリファレンス。バージョン要件と演算子の詳細を含みます。