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

chuuken kenkou ken_ken_1962 @ hotmail.com
2008年 1月 31日 (木) 00:37:33 JST





失礼しました。

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

は、「すべて1」または「殆どが1」の誤りでした。訂正します。

> Subject: [pgsql-jp: 39118] RE: RE: Indexを使ったパフォーマンス向上
>
> 忠犬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/

_________________________________________________________________
「ペットの悩み、みんなにきいたら解決した。」安心のペットライフにMSN相談箱
http://questionbox.msn.co.jp/c531.html



pgsql-jp メーリングリストの案内