[pgsql-jp: 40716] 1:Nの結合に対して更に結合を行う場合の検索性能

武田 憲太郎 takeda @ youmind.jp
2011年 3月 21日 (月) 20:54:28 JST


武田と申します。結合のチューニングについて質問させて下さい。

例えば以下のようなSQLがあったとします。

select * from t1
 natural join t2
 natural join t3
where
 t1pkey = [pkey]

以下のような条件を想定します。

・t1に対するwhere t1pkey = [pkey]の結果は常に1件
・t1→t2の対応関係は1:N。
・t2→t3の対応関係は1:1。
・t1→t3と直接結合するのは不可。必ずt2を経由する必要がある。
・【※】Nの値は不定だが極めて少ないことは予め分かっている。

例えば「ID○○番のマイミクのニックネームと画像URLが欲しい」
みたいな処理です。
(t1…ユーザーマスタ / t2…フレンドのリレーション / t3…プロフィール)

Nの値が極めて少ない、つまりt1 join t2の行数が極めて少ないのだから
t2→t3もIndexScanとなって欲しい、というのがコーディングの意図です。
実際、データ件数が少ないうちはIndexScanとなります。

ところが、予想外と言うか予想通りというか、t1,t2の件数が増えると
あるタイミングで突然SeqScanに変わります。(突然ガクンと性能落ちます)
Nの値にアプリケーション側で制限をかけていても、変わるときは変わります。

対策として、

・effective_cache_sizeを増加させる

一旦はこれで解決します。ところが、更に件数が増えると
やはり再度SeqScanとなってしまうため、イタチごっこな気がします。

・サブクエリ

冒頭のSQLを例えば以下のように書き換えます。

select
 *,
 (select t3c1 from t3 where pkey = t2.pkey) as t3c1,
 (select t3c2 from t3 where pkey = t2.pkey) as t3c2,
 (select t3c3 from t3 where pkey = t2.pkey) as t3c3,
  …
 (select t3cn from t3 where pkey = t2.pkey) as t3cn
from t1
 natural join t2
where
 t1pkey = [pkey]

これであれば件数に関係なく全てIndexScanとなります、が、
可読性が落ちる上、t1 join t2の結果一行一行に対して
カラム数の文だけサブクエリが走るコストも気になります。


もし「アナライザがそう判断している、従ってそういうもの」であれば
諦めるまでなのですが、設定値のチューニングなどで回避できるのであれば
その方が望ましいのではないかと思いました。

ちなみに、そもそも発生条件が不明なので参考になるか解りませんが、
8.4.4/9.0.1/9.0.2にて上記現象が発生することを確認しています。
回避方法、又は発生条件をご存じの方いらっしゃいませんでしょうか。





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