explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 93aP

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=624.24..625.59 rows=49 width=334) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=624.24..624.36 rows=49 width=334) (actual rows= loops=)

  • Sort Key: pc.id, cl.client_name, m.wcb_claim_number, ds.detailed_status, pc.total_loss_amount, (claim_data.total_allocated_to_pc_case(pc.id)), pc.total_recovered_amount, ((((COALESCE(rrep.first_nm, ''::character varying))::text || ' '::text) || (COALESCE(rrep.last_nm, ''::character varying))::text)), (string_agg((CASE WHEN pt.is_adverse THEN string_agg(btrim((((COALESCE(poi.first_name, ''::character varying))::text || ' '::text) || (COALESCE(poi.last_name, ''::character varying))::text)), ', '::text) ELSE NULL::text END), ', '::text)), (CASE WHEN ((claim_data.total_allocated_to_pc_case(pc.id))::double precision < pc.total_recovered_amount) THEN 'blank'::text WHEN (cp.allocated_amount > cp.paid_amount) THEN 'Over allocation'::text ELSE NULL::text END)
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=507.92..622.86 rows=49 width=334) (actual rows= loops=)

  • Hash Cond: (rcp.claim_pharma_id = cp.id)
4. 0.000 0.000 ↓ 0.0

Merge Join (cost=181.93..271.09 rows=49 width=304) (actual rows= loops=)

  • Merge Cond: (pc.id = rp.pc_case_id)
5. 0.000 0.000 ↓ 0.0

Merge Join (cost=167.75..260.05 rows=109 width=320) (actual rows= loops=)

  • Merge Cond: (pc.id = rcp.pc_case_id)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=155.27..256.29 rows=83 width=304) (actual rows= loops=)

  • Join Filter: (pc.client_id = cl.id)
7. 0.000 0.000 ↓ 0.0

Merge Join (cost=155.27..251.52 rows=83 width=297) (actual rows= loops=)

  • Merge Cond: (pc.id = c.pc_case_id)
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=148.54..280.62 rows=113 width=289) (actual rows= loops=)

  • Merge Cond: (pc.id = pc_1.id)
9. 0.000 0.000 ↓ 0.0

Sort (cost=48.06..48.34 rows=113 width=257) (actual rows= loops=)

  • Sort Key: pc.id
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6.93..44.21 rows=113 width=257) (actual rows= loops=)

  • Hash Cond: (pc.pc_case_detailed_status_id = ds.id)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5.82..42.05 rows=113 width=236) (actual rows= loops=)

  • Hash Cond: (pc.recovery_rep = rrep.user_id)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.19..38.53 rows=113 width=44) (actual rows= loops=)

  • Hash Cond: (pc.master_case_id = m.id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on pc_case pc (cost=0.00..33.47 rows=147 width=48) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=2.53..2.53 rows=53 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on master_case m (cost=0.00..2.53 rows=53 width=12) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=2.28..2.28 rows=28 width=200) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on "user" rrep (cost=0.00..2.28 rows=28 width=200) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=29) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on pc_case_detailed_status ds (cost=0.00..1.05 rows=5 width=29) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Materialize (cost=100.48..230.36 rows=200 width=40) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=100.48..227.86 rows=200 width=520) (actual rows= loops=)

  • Group Key: pc_1.id
22. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=100.48..195.66 rows=423 width=90) (actual rows= loops=)

  • Group Key: pc_1.id, pt.id, pt.is_adverse, ppt.is_adverse, poi.liability_assesment, poi.carrier_claim_number, poi.adjuster_phone, poi.adjuster_name, parent.poi_type_id, parent.liability_assesment, poi.fax
23. 0.000 0.000 ↓ 0.0

Sort (cost=100.48..101.54 rows=423 width=90) (actual rows= loops=)

  • Sort Key: pc_1.id, pt.id, pt.is_adverse, ppt.is_adverse, poi.liability_assesment, poi.carrier_claim_number, poi.adjuster_phone, poi.adjuster_name, parent.poi_type_id, parent.liability_assesment, poi.fax
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=61.75..82.03 rows=423 width=90) (actual rows= loops=)

  • Hash Cond: (pcp.poi_id = poi.id)
25. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=35.31..49.48 rows=423 width=16) (actual rows= loops=)

  • Hash Cond: (pcp.case_id = pc_1.id)
26. 0.000 0.000 ↓ 0.0

Seq Scan on pc_case_pois pcp (cost=0.00..8.32 rows=432 width=16) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=33.47..33.47 rows=147 width=8) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on pc_case pc_1 (cost=0.00..33.47 rows=147 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=23.96..23.96 rows=199 width=90) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=11.79..23.96 rows=199 width=90) (actual rows= loops=)

  • Hash Cond: (parent.poi_type_id = ppt.id)
31. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10.64..20.80 rows=199 width=89) (actual rows= loops=)

  • Hash Cond: (poi.party_of_interests_id = parent.id)
32. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.16..10.15 rows=199 width=89) (actual rows= loops=)

  • Hash Cond: (poi.poi_type_id = pt.id)
33. 0.000 0.000 ↓ 0.0

Seq Scan on party_of_interests poi (cost=0.00..6.99 rows=199 width=88) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=7 width=5) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on poi_type pt (cost=0.00..1.07 rows=7 width=5) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=6.99..6.99 rows=199 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on party_of_interests parent (cost=0.00..6.99 rows=199 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=7 width=5) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on poi_type ppt (cost=0.00..1.07 rows=7 width=5) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=6.73..7.00 rows=108 width=8) (actual rows= loops=)

  • Sort Key: c.pc_case_id
41. 0.000 0.000 ↓ 0.0

Seq Scan on claim c (cost=0.00..3.08 rows=108 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.04 rows=3 width=23) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on client cl (cost=0.00..1.03 rows=3 width=23) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=11.26..11.74 rows=193 width=16) (actual rows= loops=)

  • Sort Key: rcp.pc_case_id
45. 0.000 0.000 ↓ 0.0

Seq Scan on pc_case_related_claims_pharma rcp (cost=0.00..3.93 rows=193 width=16) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Sort (cost=9.93..10.14 rows=84 width=8) (actual rows= loops=)

  • Sort Key: rp.pc_case_id
47. 0.000 0.000 ↓ 0.0

Seq Scan on recovery_payments rp (cost=0.00..7.25 rows=84 width=8) (actual rows= loops=)

  • Filter: ((status)::text = 'PENDING'::text)
48. 0.000 0.000 ↓ 0.0

Hash (cost=324.26..324.26 rows=139 width=46) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Subquery Scan on cp (cost=320.43..324.26 rows=139 width=46) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=320.43..322.87 rows=139 width=19) (actual rows= loops=)

  • Group Key: cp_1.id
  • Filter: (sum(a.amount) > cp_1.paid_amount)
51. 0.000 0.000 ↓ 0.0

Hash Join (cost=312.22..319.39 rows=139 width=19) (actual rows= loops=)

  • Hash Cond: (a.claim_pharma_id = cp_1.id)
  • -> Hash (cost=239.32..239.32 rows=5,832 width=14)' -> Seq Scan on claims_pharma cp_1 (cost=0.00..239.32 rows=5,832 width=14)'
52. 0.000 0.000 ↓ 0.0

Seq Scan on allocations a (cost=0.00..4.39 rows=139 width=13) (actual rows= loops=)