QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Merge Right Join (cost=62642.05..62651.25 rows=12 width=74) (actual time=682187.16..698880.37 rows=362126 loops=1) Merge Cond: ("outer".service_id = "inner".service_id) Filter: (("outer".account_id = "inner".account_id) OR ("outer".account_id IS NULL)) -> Index Scan using exposure_service_id_key on exposure tse (cost=0.00..8.27 rows=300 width=8) (actual time=0.47..18.46 rows=300 loops=1) -> Sort (cost=62642.05..62642.08 rows=12 width=74) (actual time=676920.81..683084.38 rows=813179 loops=1) Sort Key: tss.service_id -> Hash Left Join (cost=62630.22..62641.84 rows=12 width=74) (actual time=590658.20..641520.35 rows=813179 loops=1) Hash Cond: ("outer".service_order_detail_id = "inner".service_order_detail_id_new) Filter: ("inner".service_order_detail_id_new IS NULL) -> Merge Join (cost=62622.49..62626.34 rows=12 width=74) (actual time=590651.37..621488.04 rows=829688 loops=1) Merge Cond: ("outer".account_id = "inner".parent_account_id) Join Filter: ("inner".account_id = "outer".parent_account_id) -> Index Scan using account_pkey on account aa (cost=0.00..1004.01 rows=19539 width=8) (actual time=0.43..0.88 rows=11 loops=1) -> Sort (cost=62622.49..62622.89 rows=158 width=82) (actual time=577505.17..591090.69 rows=1659376 loops=1) Sort Key: ab.parent_account_id -> Hash Join (cost=61817.14..62616.72 rows=158 width=82) (actual time=459071.50..511843.21 rows=1659376 loops=1) Hash Cond: ("outer".account_id = "inner".account_id) -> Seq Scan on breakdown ab (cost=0.00..602.67 rows=39067 width=8) (actual time=4.64..493.15 rows=39067 loops=1) -> Hash (cost=61816.94..61816.94 rows=79 width=78) (actual time=459061.75..459061.75 rows=0 loops=1) -> Hash Join (cost=61803.77..61816.94 rows=79 width=78) (actual time=287563.65..450075.88 rows=829688 loops=1) Hash Cond: ("outer".service_order_detail_type_id = "inner".service_order_detail_type_id) -> Hash Join (cost=61802.74..61814.33 rows=158 width=82) (actual time=287562.87..419673.93 rows=1121988 loops=1) Hash Cond: ("outer".service_semantic_type_id = "inner".service_semantic_type_id) -> Hash Join (cost=61801.71..61810.93 rows=158 width=74) (actual time=287562.31..383292.28 rows=1121988 loops=1) Hash Cond: ("outer".service_semantic_type_id = "inner".service_semantic_type_id) -> Merge Join (cost=61800.67..61807.52 rows=158 width=66) (actual time=287561.86..349895.76 rows=1121988 loops=1) Merge Cond: ("outer".service_id = "inner".service_id) -> Sort (cost=61660.77..61661.17 rows=158 width=66) (actual time=287532.78..298241.29 rows=1121988 loops=1) Sort Key: tsb.service_id -> Hash Join (cost=60582.21..61655.00 rows=158 width=66) (actual time=166995.46..234437.41 rows=1121988 loops=1) Hash Cond: ("outer".service_order_id = "inner".service_order_id) -> Seq Scan on service_order bso (cost=0.00..908.14 rows=32614 width=16) (actual time=5.57..1434.72 rows=32614 loops=1) -> Hash (cost=60581.82..60581.82 rows=158 width=54) (actual time=166989.39..166989.39 rows=0 loops=1) -> Hash Join (cost=1230.79..60581.82 rows=158 width=54) (actual time=1262.35..151200.29 rows=1121988 loops=1) Hash Cond: ("outer".account_id = "inner".account_id) -> Hash Join (cost=1226.78..52863.43 rows=1542558 width=50) (actual time=1261.63..100418.30 rows=1573190 loops=1) Hash Cond: ("outer".subservice_id = "inner".service_id) -> Hash Join (cost=1128.58..29626.86 rows=1542558 width=42) (actual time=1075.57..43565.86 rows=1573190 loops=1) Hash Cond: ("outer".service_id = "inner".service_id) -> Seq Scan on service_order_detail bsod (cost=0.00..1973.39 rows=47739 width=30) (actual time=0.14..1600.43 rows=47739 loops=1) -> Hash (cost=1061.26..1061.26 rows=26926 width=12) (actual time=1075.06..1075.06 rows=0 loops=1) -> Seq Scan on breakdown tsb (cost=0.00..1061.26 rows=26926 width=12) (actual time=93.94..643.40 rows=26926 loops=1) -> Hash (cost=95.96..95.96 rows=896 width=12) (actual time=185.74..185.74 rows=0 loops=1) -> Seq Scan on service tss (cost=0.00..95.96 rows=896 width=12) (actual time=168.07..179.79 rows=896 loops=1) -> Hash (cost=4.01..4.01 rows=1 width=4) (actual time=0.33..0.33 rows=0 loops=1) -> Index Scan using single_null_parent_account_hack on account ap (cost=0.00..4.01 rows=1 width=4) (actual time=0.26..0.28 rows=1 loops=1) Filter: (parent_account_id IS NULL) -> Sort (cost=139.90..142.14 rows=896 width=8) (actual time=28.11..6577.04 rows=1122010 loops=1) Sort Key: ss.service_id -> Seq Scan on service ss (cost=0.00..95.96 rows=896 width=8) (actual time=8.06..20.39 rows=896 loops=1) -> Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.13..0.13 rows=0 loops=1) -> Seq Scan on semantic_type tsst (cost=0.00..1.03 rows=3 width=16) (actual time=0.03..0.07 rows=3 loops=1) -> Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.26..0.26 rows=0 loops=1) -> Seq Scan on semantic_type sst (cost=0.00..1.03 rows=3 width=16) (actual time=0.16..0.19 rows=3 loops=1) -> Hash (cost=1.02..1.02 rows=1 width=4) (actual time=0.27..0.27 rows=0 loops=1) -> Seq Scan on service_order_detail_type (cost=0.00..1.02 rows=1 width=4) (actual time=0.18..0.20 rows=1 loops=1) Filter: ((service_order_detail_type_code)::text = 'create'::text) -> Hash (cost=6.78..6.78 rows=378 width=4) (actual time=6.18..6.18 rows=0 loops=1) -> Seq Scan on service_order_service_change bsosc (cost=0.00..6.78 rows=378 width=4) (actual time=0.17..3.66 rows=378 loops=1) Total runtime: 700880.59 msec (60 rows)