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

[北村 英志]

谷田様

> 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)