[pgsql-jp: 37045] SELECTの性能

[河本陽一]

こうもとです。

　SELECTで思ったような性能が出なくて悩んでいます。

　PostgreSQLは、7.4.7を使用しています。
　以下のSQLを実行しようとしています。

SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f2='xxx' AND f3=0 AND t1.f4=false AND f5 IS NOT NULL ORDER BY f6 DESC LIMIT 300;

　t1テーブルは、全42089件中、条件に一致する件数は555件です。
　EXPLAIN ANALYZEの結果は以下のようになりました。

 Limit  (cost=1948.21..1948.96 rows=300 width=143) (actual time=2670.140..2705.224 rows=300 loops=1)
   ->  Sort  (cost=1948.21..1949.56 rows=540 width=143) (actual time=2670.061..2681.358 rows=300 loops=1)
         Sort Key: t1.f6
         ->  Hash Join  (cost=6.26..1923.70 rows=540 width=143) (actual time=27.005..2646.867 rows=509 loops=1)
               Hash Cond: ("outer".f1 = "inner".f1)
               ->  Seq Scan on t1  (cost=0.00..1750.34 rows=32342 width=119) (actual time=1.225..1334.014 rows=32445 loops=1)
                     Filter: ((f3 = 0) AND (f4 = false) AND (f5 IS NOT NULL))
               ->  Hash  (cost=6.25..6.25 rows=3 width=28) (actual time=1.550..1.550 rows=0 loops=1)
                     ->  Seq Scan on t2  (cost=0.00..6.25 rows=3 width=28) (actual time=0.407..1.299 rows=3 loops=1)
                           Filter: (f2 = 'xxx'::text)
 Total runtime: 2716.774 ms

　これを見ると、t1を順次検索しているところでコストがかかっているよう
なので、以下のようなインデックスを作成しているのですが使用されません。

CREATE INDEX t1_type1_idx ON t1 (f3, f4, f5);


　ためしに結合をやめて以下のSQLにすると50ms程度で実行できました。

SELECT * FROM t1 WHERE f3=0 AND t1.f4=false AND f5 IS NOT NULL ORDER BY f6 DESC LIMIT 300;


　このことから想像するに、以前こちらで質問(pgsql-jp: 36833)したとき
に話題に出た、副問い合わせ関連の問題ではないかと思います。
　が、いまいちSQLが理解できていないせいか、どうすれば早くなるのかわ
かりません。

＃さくっと8.0系に移行すればこのような問題もなくなるかもしれませんが、
＃パッケージ管理が大変になりそうなので踏み切れません。
＃Debian 3.1(serge)を使用しています。


　テーブルは以下のようになっています。

CREATE TABLE t2 (
  f1 serial PRIMARY KEY,
  f2 text
);
CREATE INDEX t2_f2_idx ON t2 (f2);
CREATE TABLE t1 (
  f1 int REFERENCES t2,
  f7 int,
  f3 int,
  f4 bool,
  f5 bigint UNIQUE,
  f6 timestamp,
  PRIMARY KEY (f1, f7)
);

======================================================================
河本陽一(こうもとよういち)
mailto:komoto.yoichi ＠ kcc.co.jp