[pgsql-jp: 41599] Re: order by での indexの利用について

[Hiroki Kataoka]

片岡です。

ヘッダ（table_a）に対してボディ（table_b）の数（1:NのN）に上限があるなら、b.col6でソートしない速いバージョンのSELECT文をサブクエリーにしてLIMIT
500+N（正確には499+Nでいいかもしれないけど次頁があることを知りたければ500+Nですね）までを検索させ、外側のSELECT文でb.col6を含めて再ソートさせて500件を切り出すという手がありそうですが、もしもOFFSETで後ろのほうを取り出すことも考えているならやめたほうがよさそうです。

お手軽に設計変更するなら、a.col4とa.col5と同じ値をtable_bにも持たせてb.col4〜6の複合インデックスを作成して、table_bを結合のOUTERにすればシンプルですし、OFFSETで後ろのほうをアクセスするとしても最小限の速度低下で済みそうです。

2014年3月14日 17:27  <prod2011 ＠ yahoo.co.jp>:
> Prodです。
>
> 花田様アドバイスありがとうございます。
>
> 仕様としては、遅い実行計画のb.col6 をOrder byに入れないといけません。
>
> 今回の質問としましては、order by でb.col6をいれた遅い方で早くなる方法が
> ないか？というい質問です。(わかりずらくてすいません）
>
> 速い方を提示したのは、order by で b.col6を外すと、indexを使った検索となり、
> 速くなるのに、order byで b.col6を入れると indexを使ってくれず
> なぜindexを使わなくなってしまうのかがわからなかったため、その違いとして
> 提示させていただきました。
>
>> 早い実行計画(ORDER
>
>> BY b.c6なし)ではa.c4, a.c5でのソートをインデックスで保証しており、結合結
>> 果の最初の500件を取得した時点で両テーブルのスキャンを途中で止められてい
>> ることが大きなポイントだと思います。
>
>
> ここがよくわかっていませんでした。
> order by で b.col6をつけても、 a.col4,a.col5の順番はかわらないので、
> table_a_index2 を使ってくれてもいいのではないか？と思ったのです。
> やはり、order by は、indexで順番を完全に保証できないとだめなのですね。
>
> データを ヘッダ ボディと2テーブルにする場合に、注意しないといけないですね。
> テーブル設計を含めて、見直しすることにいたします。
>
> アドバイスありがとうございました。
> とても、参考になりました。
>
>
> ----- Original Message -----
>> From: 花田 茂 <hanada ＠ metrosystems.co.jp>
>> To: prod2011 ＠ yahoo.co.jp; PostgreSQL Japanese Mailing List <pgsql-jp ＠ ml.postgresql.jp>
>> Cc:
>> Date: 2014/3/14, Fri 16:50
>> Subject: Re: [pgsql-jp: 41593] Re: order by での indexの利用について
>>
>> 花田です。
>>
>> (2014/03/14 15:00), prod2011 ＠ yahoo.co.jp wrote:
>>>  ここで、order by のb.col6があるとき、ないとき、で全然速度がことなります。
>>>  目標としている速度は、2以内秒 です。
>>
>> そもそも、仕様上 b.c6でソートした結果が必要なのであれば、遅いほうのクエ
>> リが本来の性能ではないでしょうか(ORDER BYからb.c6を外した結果は本来取得
>> すべきものとは異なっているのだと思います)。b.c6とb.c1の相関によっては
>> ORDER BYで省略してもよいかもしれませんが、これは許容されるチューニングな
>> のでしょうか？(なんとなく違う気がしています)
>>
>> それは置いておいて…
>>
>> 今回のケースの性能差ですが、遅い実行計画(ORDER BY b.c6あり)ではLimit前に
>> aとbの結合結果全件をソートする必要があるのに対して、早い実行計画(ORDER
>> BY b.c6なし)ではa.c4, a.c5でのソートをインデックスで保証しており、結合結
>> 果の最初の500件を取得した時点で両テーブルのスキャンを途中で止められてい
>> ることが大きなポイントだと思います。これにより、スキャンコストだけでなく
>> 結合コストも大きく削減できています。
>>
>> b.c6でのソートをしたうえで大幅に早くするには、テーブル設計を見直す必要が
>> あるかもしれません。
>>
>> --
>> 株式会社メトロシステムズ
>>   花田 茂
>> Mail : hanada ＠ metrosystems.co.jp
>> Tel : 03-5951-1219
>> Fax : 03-5951-2929
>>



-- 
Hiroki Kataoka