[pgsql-jp: 34373] Re: パフォーマンスチューニング

[Takanori Oba]

大羽です。

北村 英志 wrote:
>>explain analyzeの結果を見せて頂くことはできますか？
> 
> 少々見づらいと思いますが・・・
> 
> EXPLAIN ANALYZE SELECT DISTINCT as.id, as.able_id, as.mem_id, as.feed_id, as.ldata_id, as.ts_fetch, as.ts_filter, as.ts_trigger, al.type FROM ablesub as, able al, fdata fd WHERE al.status='OKAY' AND al.type='MLST' AND al.id=as.able_id AND as.feed_id=fd.feed_id AND ((as.ldata_id ISNULL AND fd.ts_inst>=as.ts_fetch) OR fd.id>as.ldata_id) LIMIT 20;
>                                                                                     QUERY PLAN
> 
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=61511.87..61520.49 rows=20 width=60) (actual time=35383.214..35383.278 rows=1 loops=1)
>    ->  Unique  (cost=61511.87..63998.42 rows=5771 width=60) (actual time=35383.207..35383.268 rows=1 loops=1)
>          ->  Sort  (cost=61511.87..61760.52 rows=99462 width=60) (actual time=35383.203..35383.205 rows=18 loops=1)
>                Sort Key: as.id, as.able_id, as.mem_id, as.feed_id, as.ldata_id, as.ts_fetch, as.ts_filter, as.ts_trigger, al."type"
>                ->  Hash Join  (cost=4516.62..50155.60 rows=99462 width=60) (actual time=6313.877..35382.972 rows=18 loops=1)
>                      Hash Cond: ("outer".feed_id = "inner".feed_id)
>                      Join Filter: ((("inner".ldata_id IS NULL) OR ("outer".id > "inner".ldata_id)) AND (("outer".ts_inst >= "inner".ts_fetch) OR ("outer".id > "inner".ldata_id)))
>                      ->  Seq Scan on fdata fd  (cost=0.00..27777.54 rows=227554 width=20) (actual time=0.006..25332.762 rows=227670 loops=1)
>                      ->  Hash  (cost=4511.81..4511.81 rows=1924 width=60) (actual time=558.671..558.671 rows=0 loops=1)
>                            ->  Hash Join  (cost=3.83..4511.81 rows=1924 width=60) (actual time=0.518..536.029 rows=10538 loops=1)
>                                  Hash Cond: ("outer".able_id = "inner".id)
>                                  ->  Seq Scan on ablesub as  (cost=0.00..4322.49 rows=33249 width=52) (actual time=0.007..357.254 rows=33279 loops=1)
>                                  ->  Hash  (cost=3.81..3.81 rows=7 width=12) (actual time=0.462..0.462 rows=0 loops=1)
>                                        ->  Seq Scan on able al  (cost=0.00..3.81 rows=7 width=12) (actual time=0.107..0.437 rows=1 loops=1)
>                                              Filter: ((status = 'OKAY'::bpchar) AND ("type" = 'MLST'::bpchar))
>  Total runtime: 35384.060 ms
> (16 rows)

完全に全表走査(Seq Scan)になっていますね・・・。

REINDEXやVACUUM FULLしてもインデックスを使った問い合わせ(Index Scan)に
ならないようであれば、テーブル設計、インデックス設計、SQLのどれかが
マズいと思います。
-- 
oba ＠ uchida.co.jp