配列型インデックス
co-sakaです。こんにちは。
複数列インデックス - Ludia開発日記(昔の日記)の続きです。
マルチカラムインデックスはOR検索があまり高速でない。
他に方法がないのか？
これに対する解決方法を紹介します。
以下のテーブルで説明します。
=# CREATE TABLE book (title TEXT, header TEXT, body TEXT, author TEXT);
=# INSERT INTO book VALUES('我輩は猫である', '日本',
'我輩は猫である。名前はまだない。',
'夏目漱石');
=# INSERT INTO book VALUES('日本国憲法', '日本',
'日本国民は、正当に選挙された国会における代表者を通じて行動し',
'伊藤博文');
=# INSERT INTO book VALUES('学問のすすめ', 'Japan',
'天は人の上に人を造らず。人の下に人を造らずと云えり。',
'福沢諭吉');
=# INSERT INTO book VALUES('Ludiaのすすめ', 'Japan',
'日本語の全文検索が可能',
'co-saka');マルチカラムインデックスを用いると、以下のようになります。
=# CREATE INDEX idx1 ON book USING fulltextb(title, header, body, author);
=# SELECT title FROM book WHERE title @@ '日本' OR header @@ '日本' or
body @@ '日本' OR author @@ '日本'; -- クエリ１
title
----------------
我輩は猫である
日本国憲法
Ludiaのすすめ
(3 rows)
=# SELECT title FROM book WHERE title @@ '日本'; -- クエリ２
title
------------
日本国憲法
(1 row)クエリ１の場合、マルチカラムインデックスに対して、
@@の数だけ検索が実行されます。
（そして４つの検索結果が結合されます。）
これはあまり好ましくありません。
そこで、以下のように配列型インデックスを作成します。
=# CREATE INDEX idx2 ON book USING fulltextab((array[title, header, body, author]));
=# SELECT title FROM book WHERE array[title, header, body, author] @@ '日本'; -- クエリ３
title
----------------
日本国憲法
我輩は猫である
Ludiaのすすめ
(3 rows)
=# SELECT title FROM book WHERE array[title, header, body, author] @@ '*W1 検索'; -- クエリ４
title
------------
日本国憲法
(1 row)配列型インデックスを用いると、クエリ３のように、検索が１つのみ実行されます。
クエリ１とクエリ３は同じ検索結果。クエリ３の方が高速です。
クエリ２とクエリ４は同じ検索結果。検索時間はほぼ等しいです。
また、この配列型インデックスを用いると、
以下のようにカラムごとの重み付け検索が可能です。
=# CREATE INDEX idx2 ON tab USING fulltexta((array[title, header, body, author]));
=# SELECT *, pgs2getscore(ctid) AS score FROM tab
WHERE array[title, header, body, author] @@ '*W1:10,2:3,3:1,4:10 日本'
ORDER BY score DESC; -- クエリ５
title | score
----------------+-------
日本国憲法 | 14
我輩は猫である | 3
Ludiaのすすめ | 1
(3 rows)この例では、titleを10、headerを3、bodyを1、authorを10に重み付けして、
検索してソートしてます。
（*W の書式は http://qwik.jp/senna/query.html を見てください。）
このように、OR検索ではマルチカラムインデックスより便利な
配列型インデックスはLudia1.4.0で登場します。
お楽しみに！
# 配列型インデックスはLudia1.3.1でもそれっぽく動きます･･･。
