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

[Kan Ogawa]

小川です。

> upper関数は、半角文字に対してだけ効果あると理解しております。
> それで、translate関数で全角大字を半角大字に変換しております。
>
実はlower関数もそうなんですが、これはPostgreSQLの仕様ですか？

実は Atlassian Confluence (Enterprise Wiki)
で、PostgreSQLのlower関数の変換ルールが原因で、当該製品の機能に致命的な問題が潜在しているんです。
https://jira.atlassian.com/browse/CONF-20722

私の勤め先でも、translate関数を使ったカスタムlower関数を作成して、上記の問題を回避しています。
(MySQLなど、PostgreSQL以外のDBでは発生していません)

2013年6月4日 20:36  <yasuhara ＠ restored-live.jp>:
> 佐藤さん
>
> 安原です。
> ご連絡ありがとうございます。
>
> 該当のデータベースのロケールは、Cにしております。
>
> upper関数は、半角文字に対してだけ効果あると理解しております。
> それで、translate関数で全角大字を半角大字に変換しております。
>
>
> 返信先をMLに、気を付けます。
>
> (2013/06/03 11:39), Tomoaki Sato wrote:
>> 佐藤です。
>>
>> From: "安原@リストアード" <yasuhara ＠ restored-live.jp>
>> Date: Sun, 02 Jun 2013 13:30:59 +0900
>>
>>> ご回答ありがとうごさいます。
>>> いつもありがとうごさいます。
>>>
>>> ご丁寧なアドバイスで、修正の方向性がわかりました。
>>> インデックスの設定の方で進めていきたいと思います。
>> 一点だけ、
>>
>>>>> AND translate(upper(content),
>>>>> 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ',
>>>>> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') LIKE '%OTC%'
>> 見逃していたのですが、upper 関数で全角の英字を大文字に変換しているので、
>> ロケールが日本語 (ja_JP.UTF-8 など) になっていると思われます。
>>
>> 私の言ったやり方はロケールが C でないと、インデックスを使ってくれないか
>> もしれません。
>>
>> # その後どうなったか気になる人もいるので、返信は個人宛てだけでなく ML
>> # 宛てにもしておいたほうがいいですよ。
>>
>>>> 佐藤です。
>>>>
>>>>> 安原と申します。
>>>>>
>>>>> 親テーブル：記事発行テーブル 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
>>
>>
>