explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vALU

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

Sort (cost=286,178.34..286,178.34 rows=1 width=808) (actual rows= loops=)

  • Sort Key: t.member_id
2.          

CTE contacts

3. 0.000 0.000 ↓ 0.0

Subquery Scan on cv (cost=4,353.14..4,612.22 rows=4,314 width=104) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=4,353.14..4,536.73 rows=4,314 width=248) (actual rows= loops=)

  • Group Key: co.id
5. 0.000 0.000 ↓ 0.0

Sort (cost=4,353.14..4,363.95 rows=4,325 width=309) (actual rows= loops=)

  • Sort Key: co.id
6. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=3,670.09..4,091.94 rows=4,325 width=309) (actual rows= loops=)

  • Hash Cond: (a.id = ca.address_id)
7. 0.000 0.000 ↓ 0.0

Seq Scan on address a (cost=0.00..327.16 rows=13,716 width=112) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash (cost=3,616.03..3,616.03 rows=4,325 width=213) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=3,235.77..3,616.03 rows=4,325 width=213) (actual rows= loops=)

  • Hash Cond: (ca.contact_id = co.id)
10. 0.000 0.000 ↓ 0.0

Seq Scan on contact_address ca (cost=0.00..293.16 rows=13,716 width=20) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash (cost=3,181.70..3,181.70 rows=4,325 width=201) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,684.20..3,181.70 rows=4,325 width=201) (actual rows= loops=)

  • Hash Cond: (cp.phone_id = p.id)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,378.40..2,864.55 rows=4,325 width=166) (actual rows= loops=)

  • Hash Cond: (co.id = cc.contact_id)
14. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,213.76..2,670.68 rows=4,325 width=166) (actual rows= loops=)

  • Hash Cond: (e.id = ce.email_id)
15. 0.000 0.000 ↓ 0.0

Seq Scan on email e (cost=0.00..351.30 rows=16,630 width=86) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=2,159.70..2,159.70 rows=4,325 width=92) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,715.79..2,159.70 rows=4,325 width=92) (actual rows= loops=)

  • Hash Cond: (ce.contact_id = co.id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on contact_email ce (cost=0.00..338.30 rows=16,630 width=20) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=1,661.73..1,661.73 rows=4,325 width=80) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,233.25..1,661.73 rows=4,325 width=80) (actual rows= loops=)

  • Hash Cond: (mc.contact_id = co.id)
21. 0.000 0.000 ↓ 0.0

Seq Scan on member_contact mc (cost=0.00..326.53 rows=16,053 width=20) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=1,179.18..1,179.18 rows=4,325 width=68) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=885.37..1,179.18 rows=4,325 width=68) (actual rows= loops=)

  • Hash Cond: (cp.contact_id = co.id)
24. 0.000 0.000 ↓ 0.0

Seq Scan on contact_phone cp (cost=0.00..226.69 rows=10,569 width=20) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=831.31..831.31 rows=4,325 width=56) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=481.80..831.31 rows=4,325 width=56) (actual rows= loops=)

  • Hash Cond: (cm.contact_id = co.id)
27. 0.000 0.000 ↓ 0.0

Seq Scan on contact_method cm (cost=0.00..305.73 rows=16,673 width=8) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=427.88..427.88 rows=4,314 width=56) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on contact co (cost=0.00..427.88 rows=4,314 width=56) (actual rows= loops=)

  • Filter: (tenant_id = '1010'::bigint)
30. 0.000 0.000 ↓ 0.0

Hash (cost=102.06..102.06 rows=5,006 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on customer_contact cc (cost=0.00..102.06 rows=5,006 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=173.69..173.69 rows=10,569 width=51) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on phone p (cost=0.00..173.69 rows=10,569 width=51) (actual rows= loops=)

34.          

CTE monthly_fee_paid_already

35. 0.000 0.000 ↓ 0.0

Group (cost=375.72..375.73 rows=1 width=8) (actual rows= loops=)

  • Group Key: p_1.member_id
36. 0.000 0.000 ↓ 0.0

Sort (cost=375.72..375.73 rows=1 width=8) (actual rows= loops=)

  • Sort Key: p_1.member_id
37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..375.71 rows=1 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on deduction d (cost=0.00..367.41 rows=1 width=8) (actual rows= loops=)

  • Filter: ((type_id = 101) AND (((metadata ->> 'month'::text))::integer = 2) AND (((metadata ->> 'year'::text))::integer = 2020))
39. 0.000 0.000 ↓ 0.0

Index Scan using payout_pkey on payout p_1 (cost=0.28..8.30 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = d.payout_id)
40.          

CTE targets

41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,113.73..281,190.35 rows=1 width=700) (actual rows= loops=)

  • Join Filter: (((po.metadata #>> '{origination,integration_oid}'::text[]) = (x.integration_oid)::text) AND ((po.metadata #>> '{origination,tenant_integration_id}'::text[]) = ((x.tenant_integration_id)::character varying)::text))
42. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,113.48..281,190.03 rows=1 width=1,654) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,113.20..281,181.72 rows=1 width=1,650) (actual rows= loops=)

  • Merge Cond: (m.id = po.member_id)
  • Filter: (po.id IS NULL)
44. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,662.87..248,305.61 rows=4,109,000 width=602) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,662.86..166,125.61 rows=4,109,000 width=598) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,662.86..83,945.60 rows=4,109,000 width=158) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,662.61..1,765.35 rows=4,109 width=126) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,662.59..1,683.16 rows=4,109 width=118) (actual rows= loops=)

  • Merge Cond: (m.id = mfpa.member_id)
49. 0.000 0.000 ↓ 0.0

Sort (cost=1,662.56..1,672.84 rows=4,109 width=110) (actual rows= loops=)

  • Sort Key: m.id
50. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,302.44..1,415.93 rows=4,109 width=110) (actual rows= loops=)

  • Hash Cond: (c.member_id = m.id)
51. 0.000 0.000 ↓ 0.0

CTE Scan on contacts c (cost=0.00..86.28 rows=4,314 width=40) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=1,251.08..1,251.08 rows=4,109 width=78) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=570.85..1,251.08 rows=4,109 width=78) (actual rows= loops=)

  • Hash Cond: (mt.member_id = m.id)
54. 0.000 0.000 ↓ 0.0

Seq Scan on member_tree mt (cost=0.00..638.17 rows=16,017 width=57) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=519.49..519.49 rows=4,109 width=29) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on member m (cost=100.13..519.49 rows=4,109 width=29) (actual rows= loops=)

  • Recheck Cond: (tenant_id = '1010'::bigint)
57. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on member_tenant_id_tenant_oid_key (cost=0.00..99.10 rows=4,109 width=0) (actual rows= loops=)

  • Index Cond: (tenant_id = '1010'::bigint)
58. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=8) (actual rows= loops=)

  • Sort Key: mfpa.member_id
59. 0.000 0.000 ↓ 0.0

CTE Scan on monthly_fee_paid_already mfpa (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Function Scan on int8 currency (cost=0.02..0.03 rows=1 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Function Scan on tmp_upcoming_payouts raw (cost=0.25..10.25 rows=1,000 width=32) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_to_record x (cost=0.01..0.01 rows=1 width=440) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Function Scan on int4 target_amount (cost=0.01..0.02 rows=1 width=4) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Materialize (cost=3,450.33..3,475.27 rows=4,989 width=1,056) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Sort (cost=3,450.33..3,462.80 rows=4,989 width=1,056) (actual rows= loops=)

  • Sort Key: po.member_id
66. 0.000 0.000 ↓ 0.0

Seq Scan on payout po (cost=0.00..840.89 rows=4,989 width=1,056) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Index Scan using commerce_sale_tenant_integration_id_integration_oid on sale cs (cost=0.28..8.30 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((tenant_integration_id = x.tenant_integration_id) AND ((integration_oid)::text = (x.integration_oid)::text))
68. 0.000 0.000 ↓ 0.0

Function Scan on get_period p_2 (cost=0.25..0.26 rows=1 width=8) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

CTE Scan on targets t (cost=0.00..0.02 rows=1 width=808) (actual rows= loops=)