[pgsql-jp: 39118] RE: RE: Indexを使ったパフォーマンス向上

[chuuken kenkou]

忠犬KEN公です。

A1列の値ですが、「すべて0」または「殆どが0」になっていないでしょうか？

当方、PostgreSQL 8.0.0ですが、2万件程度のデータでテストしてみたところ、そういう推測に至りました。

>> これに対し、以下のようなSQLを投げ、EXPLAIN ANALIZEを入れると、利用されている
>> IndexはA1と表示されます。レコード件数が多いせいもあると思いますが、30秒近くかかります。
>>
>> select * from A where A1=1 order by A2 limit 200
>
> 当方はPostgreSQL 8.0.0ですが、2万件程度で擬似的な環境を作ってテストしてみたところ、
>
> select * from A where A1=1 order by A2 limit 200
>
> では、複数列インデクスは使われませんでした。
>
> select * from A where A1=1 order by A1,A2 limit 200
>
> では、複数列インデクスが使えました。

前回のメールの引用ですが、「where A1=1」の条件があり、「order by A2」のため、次のようになっていると推測します。

(1)「where A1=1」の条件があるため、A1のインデクス使用
→結果的に絞込みできず、全件ヒット
(2)「order by」でA1の指定がないため、インデクスによるソート抑止も効かず、全件ソート発生

>> 一方、上記でwhere A1=1を外すと、一瞬で結果が表示されます。この場合、A2が利用されている
>
> これは、「where A1=1」の条件がある場合と、等価ではないですよね？
> 無条件にA2のインデクスの先頭から200件なので、早くて当然だと思います。

これは、前回のメールの通り。
A1の値に関係なく、A2の先頭から200件をインデクスで見つけるだけなので早い。

>> また、Where A1=1を残し、order by A2 limit 200を外すと、5-6秒程度の検索時間になります。
>
> これも「where A1=1」の条件で絞り込んだ後のソートが不要になるので、検索条件とソートの両方がある場合に比べて、複数列インデクスが使えていないケースよりは早くなることは想像できます。

「where A1=1」により、A1のインデクスで全件検索になるが、「order by」がないので、全件ソートが発生しない分、早くなる。


また、斉藤さんのアドバイスに対し、

>set sort_memを大幅に大きくすると、1/3程度に改善されました。10Mや20M程度では変わりませんが、512Mなどにすると、改善がありました。おそらく、45万レコードがほぼ全てメモリ内で処理できる容量なのでしょうね。

という返信ですが、これも「where A1=1」で全件ヒットしていて、全件のソートが発生しているなら納得できます。

ただ、私のアドバイスに対する次の返信が合点がいきません。

>以下も試していたのですが、やはりindexはA1が利用されており、複合indexが利用されません。whereを抜くと使ってくれるのですが．．．

>> 未検証ですが、A1列とA2列に複合インデクスを定義し、
>> select * from A where A1=1 order by A1,A2 limit 200
>> とした場合、どうでしょうか？

インデクスの定義を(A1,A2)の順とし、「order by A1,A2」と、インデクスを構成する先頭列から順番にorder by指定されているでしょうか？
もしこれが勘違いであれば、すべて納得できます。

なお、私の推測が外れていて、A1の値が、「全件が0」または「殆どが0」でないなら、以下のSQLで値の種類の個数、重複数を調べて教えてください。

select
  count(distinct A1),
  sum(case A1 when 1 then 1 else 0 end),
  count(distinct A2)
 from A;

_________________________________________________________________
謹賀新年「進化する報道へ、一歩。」2008年もMSN産経ニュース
http://sankei.jp.msn.com/