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

[花田 茂]

花田です。

(2014/03/14 17:27), prod2011 ＠ yahoo.co.jp wrote:
> 仕様としては、遅い実行計画のb.col6 をOrder byに入れないといけません。
> 
> 今回の質問としましては、order by でb.col6をいれた遅い方で早くなる方法が
> ないか？というい質問です。(わかりずらくてすいません）

いえ、この点はきちんと伝わっています。私のコメントが(分かりづらかった|紛
らわしかった)と思います。すみません。

>> 早い実行計画(ORDER
> 
>> BY b.c6なし)ではa.c4, a.c5でのソートをインデックスで保証しており、結合結
>> 果の最初の500件を取得した時点で両テーブルのスキャンを途中で止められてい
>> ることが大きなポイントだと思います。
> 
> 
> ここがよくわかっていませんでした。
> order by で b.col6をつけても、 a.col4,a.col5の順番はかわらないので、
> table_a_index2 を使ってくれてもいいのではないか？と思ったのです。
> やはり、order by は、indexで順番を完全に保証できないとだめなのですね。

結合のOuter側(EXPLAINでの上側)がORDER BYのキー群を先頭に含むインデックス
スキャンに出来る場合、ネステッドループ結合を使うことで明示ソートをスキッ
プできるということで、「保証できないとダメ」というのはちょっと違うかもし
れません。

今回はc6の値がtable_b側にしかないので、最終結果(どの500件を選べばよいか)
がソートするまで分からないので、c4, c5で作ったインデックスは使いようがな
いといったところでしょうか。

> 
> データを ヘッダ ボディと2テーブルにする場合に、注意しないといけないですね。
> テーブル設計を含めて、見直しすることにいたします。

ヘッダとボディという正規化での分割なのであれば、仕方ないような気がします
(table_aがヘッダでtable_bがボディの1:N関係ですよね？)。

更新頻度と検索のリアルタイム性の要件が低い場合限定かもしれませんが、検索
専用のMaterialized Viewsや中間表を作成しておき、c4, c5, c6でインデックス
を作成するという手もアリかもしれません。

または、どーんとメモリを積んでshared_buffersやwork_memを増やしてしまうか :-P

-- 
株式会社メトロシステムズ
  花田 茂
Mail : hanada ＠ metrosystems.co.jp
 Tel : 03-5951-1219
 Fax : 03-5951-2929