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

[Nobuyasu Jinnai (Aset ITM)]

神内と申します。横から失礼をいたしておりますがよろしくお願いします。

uniq ではないところにインデックスを張っても無意味というものでしょう。
＃コンピュータはそんなに賢くありません。

できれば乱数ぐらいの勢いが有難いものです。

--神内

From: chuuken kenkou <ken_ken_1962 ＠ hotmail.com>
Subject: [pgsql-jp: 39119] RE: RE: RE: Indexを使ったパフォーマンス向上
Date: Thu, 31 Jan 2008 00:37:33 +0900

> 
> 
> 
> 
> 失礼しました。
> 
> > 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;
> >