[pgsql-jp: 41406] Re: 複数のLIKE句 OR で繋ぐと遅い

[Tomoaki Sato]

佐藤です。

> 安原と申します。
> 
> 親テーブル：記事発行テーブル tb_publish と、
> 子テーブル：記事本文テーブル tb_content （5万レコードくらい）
> がありまして、
> tb_content の本文カラム`content`（TEXT型）には、
> 日本語・半角英数字・全角英数字を平均500文字が含まれています。
> 
> contentに対して、全角英数字、半角英数字の中間一致を、かけるSQL文を
> 作りましたが（下記）。
> 
> SELECT * FROM tb_publish pub, tb_content con
> WHERE pub.publish_id = con.publish_id
> AND ( content LIKE '%ＴＥＳＴ%' OR content LIKE '%ｔｅｓｔ%' OR content
> LIKE '%TEST%' OR '%test%' )
> AND publish_date >= '2013-04-01' AND publish_date <= '2013-05-30'
> ORDER BY publish_date DESC OFFSET 0 LIMIT 10 ;
> 
> LIKE句の文字いかんによっては、3000ms 〜 25000ms かかるも場合があります。
> 又、発行日publish_date を from 1998-04-01 to 2013-05-30 にすると、700ms
> くらい、と少し速くなります。
> 
> 条件抽出のアルゴリズムなどは勉強不足なので分かりませんが、
> 上記SQL文の欠点を指摘して頂けると幸いです。

中間一致検索だと content 列にインデックスを定義していても基本的に使えな
いので、速いかどうかは publish_date 列の範囲でどの程度絞り込めるか、テー
ブルの初めのほうに一致するデータが固まっているかによります。

中間一致検索を速くするには全文検索用のインデックスを定義します。全文検
索については以下の URL が参考になります。

  テキスト検索の方法とインデックス (Let's postgres):
    http://lets.postgresql.jp/documents/technical/text-processing/3

  データベース組み込みの全文検索を使うには (前編):
    https://docs.google.com/present/edit?id=0AdFeMWLZE80XZGhrMnNtcGdfM2g0NGNucmR2

  データベース組み込みの全文検索を使うには (後編):
    https://docs.google.com/present/edit?id=0AdFeMWLZE80XZGhrMnNtcGdfMTFoZ3NwNXRnMw

上記に載っていない情報としては、最近リリースされた pg_bigm という
pg_trgm の 2 文字版があります。

  pg_bigm:
    http://pgbigm.sourceforge.jp/

> 因みに、content側テキストを半角英数字に整形してから中間一致させる以下の
> SQL文の速度を
> 測定してみましたが、
> 
> SELECT * FROM tb_publish pub, tb_content con
> WHERE pub.publish_id = con.publish_id
> AND translate(upper(content),
> 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ',
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') LIKE '%OTC%'
> AND publish_date >= '2013-04-01' AND publish_date <= '2013-05-30'
> ORDER BY publish_date DESC OFFSET 0 LIMIT 10 ;
> 
> 80000ms くらいかかってしまうことがわかりました。
> publish_date の範囲していかんによっては、いつ終了するかも分からないとき
> もあります。

ただ、全文検索用のインデックスを定義しても、検索条件に関数が含まれてい
ると使えないので、関数の結果に対してインデックスを定義します。

例えば、pg_bigm を使って上記の SELECT 文を速くするインデックスを定義す
るなら以下のようにします。

create function my_upper(text) returns text as $$
    select translate(upper($1),
                     'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ',
                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
$$ language sql immutable returns null on null input;

create index on tb_content using gin ((my_upper(content)) gin_bigm_ops);

select * from tb_publish as p, tb_content as c
    where p.publish_id = c.publish_id
        and my_upper(content) like '%OTC%';
        and publish_date >= '2013-04-01'
        and publish_date <= '2013-05-30'
    order by publish_date desc
    limit 10 offset 0;


----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan