explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vGKE

Settings
# exclusive inclusive rows x rows loops node
1. 0.050 78,029.011 ↓ 2.0 2 1

Sort (cost=286,096.90..286,096.90 rows=1 width=808) (actual time=78,028.989..78,029.011 rows=2 loops=1)

  • Sort Key: t.member_id
  • Sort Method: quicksort Memory: 26kB
2.          

CTE contacts

3. 80.306 3,519.082 ↑ 1.0 4,314 1

Subquery Scan on cv (cost=4,353.14..4,612.22 rows=4,314 width=104) (actual time=3,160.608..3,519.082 rows=4,314 loops=1)

4. 235.920 3,438.776 ↑ 1.0 4,314 1

GroupAggregate (cost=4,353.14..4,536.73 rows=4,314 width=248) (actual time=3,160.583..3,438.776 rows=4,314 loops=1)

  • Group Key: co.id
5. 87.737 3,202.856 ↓ 1.1 4,903 1

Sort (cost=4,353.14..4,363.95 rows=4,325 width=309) (actual time=3,160.399..3,202.856 rows=4,903 loops=1)

  • Sort Key: co.id
  • Sort Method: quicksort Memory: 2653kB
6. 167.269 3,115.119 ↓ 1.1 4,903 1

Hash Right Join (cost=3,670.09..4,091.94 rows=4,325 width=309) (actual time=2,816.885..3,115.119 rows=4,903 loops=1)

  • Hash Cond: (a.id = ca.address_id)
7. 131.067 131.067 ↑ 1.0 13,716 1

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

8. 56.530 2,816.783 ↓ 1.1 4,903 1

Hash (cost=3,616.03..3,616.03 rows=4,325 width=213) (actual time=2,816.776..2,816.783 rows=4,903 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1213kB
9. 192.818 2,760.253 ↓ 1.1 4,903 1

Hash Right Join (cost=3,235.77..3,616.03 rows=4,325 width=213) (actual time=2,596.080..2,760.253 rows=4,903 loops=1)

  • Hash Cond: (ca.contact_id = co.id)
10. 132.460 132.460 ↑ 1.0 13,716 1

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

11. 42.816 2,434.975 ↑ 1.0 4,315 1

Hash (cost=3,181.70..3,181.70 rows=4,325 width=201) (actual time=2,434.968..2,434.975 rows=4,315 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1021kB
12. 80.483 2,392.159 ↑ 1.0 4,315 1

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

  • Hash Cond: (cp.phone_id = p.id)
13. 79.602 2,105.111 ↑ 1.0 4,315 1

Hash Left Join (cost=2,378.40..2,864.55 rows=4,325 width=166) (actual time=1,884.092..2,105.111 rows=4,315 loops=1)

  • Hash Cond: (co.id = cc.contact_id)
14. 211.750 1,930.172 ↑ 1.0 4,315 1

Hash Right Join (cost=2,213.76..2,670.68 rows=4,325 width=166) (actual time=1,788.717..1,930.172 rows=4,315 loops=1)

  • Hash Cond: (e.id = ce.email_id)
15. 186.693 186.693 ↑ 1.0 16,630 1

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

16. 39.375 1,531.729 ↑ 1.0 4,315 1

Hash (cost=2,159.70..2,159.70 rows=4,325 width=92) (actual time=1,531.722..1,531.729 rows=4,315 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 622kB
17. 184.914 1,492.354 ↑ 1.0 4,315 1

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

  • Hash Cond: (ce.contact_id = co.id)
18. 141.224 141.224 ↑ 1.0 16,630 1

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

19. 39.240 1,166.216 ↑ 1.0 4,315 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 560kB
20. 194.520 1,126.976 ↑ 1.0 4,315 1

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

  • Hash Cond: (mc.contact_id = co.id)
21. 151.657 151.657 ↑ 1.0 16,053 1

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

22. 42.488 780.799 ↑ 1.0 4,314 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 522kB
23. 146.230 738.311 ↑ 1.0 4,314 1

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

  • Hash Cond: (cp.contact_id = co.id)
24. 99.550 99.550 ↑ 1.0 10,569 1

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

25. 51.211 492.531 ↑ 1.0 4,314 1

Hash (cost=831.31..831.31 rows=4,325 width=56) (actual time=492.524..492.531 rows=4,314 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 468kB
26. 212.291 441.320 ↑ 1.0 4,314 1

Hash Right Join (cost=481.80..831.31 rows=4,325 width=56) (actual time=271.142..441.320 rows=4,314 loops=1)

  • Hash Cond: (cm.contact_id = co.id)
27. 151.012 151.012 ↑ 1.0 16,673 1

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

28. 37.992 78.017 ↑ 1.0 4,314 1

Hash (cost=427.88..427.88 rows=4,314 width=56) (actual time=78.010..78.017 rows=4,314 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 468kB
29. 40.025 40.025 ↑ 1.0 4,314 1

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

  • Filter: (tenant_id = '1010'::bigint)
  • Rows Removed by Filter: 12316
30. 48.109 95.337 ↑ 1.0 5,006 1

Hash (cost=102.06..102.06 rows=5,006 width=8) (actual time=95.330..95.337 rows=5,006 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 260kB
31. 47.228 47.228 ↑ 1.0 5,006 1

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

32. 101.633 206.565 ↑ 1.0 10,569 1

Hash (cost=173.69..173.69 rows=10,569 width=51) (actual time=206.558..206.565 rows=10,569 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 964kB
33. 104.932 104.932 ↑ 1.0 10,569 1

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

34.          

CTE monthly_fee_paid_already

35. 8.834 44.108 ↓ 526.0 526 1

Group (cost=375.72..375.73 rows=1 width=8) (actual time=30.741..44.108 rows=526 loops=1)

  • Group Key: p_1.member_id
36. 8.978 35.274 ↓ 526.0 526 1

Sort (cost=375.72..375.73 rows=1 width=8) (actual time=30.726..35.274 rows=526 loops=1)

  • Sort Key: p_1.member_id
  • Sort Method: quicksort Memory: 49kB
37. 12.707 26.296 ↓ 526.0 526 1

Nested Loop (cost=0.28..375.71 rows=1 width=8) (actual time=0.181..26.296 rows=526 loops=1)

38. 6.751 6.751 ↓ 526.0 526 1

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

  • Filter: ((type_id = 101) AND (((metadata ->> 'month'::text))::integer = 2) AND (((metadata ->> 'year'::text))::integer = 2020))
  • Rows Removed by Filter: 5269
39. 6.838 6.838 ↑ 1.0 1 526

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

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

CTE targets

41. 0.097 78,028.910 ↓ 2.0 2 1

Nested Loop Left Join (cost=5,105.24..281,108.91 rows=1 width=700) (actual time=77,087.818..78,028.910 rows=2 loops=1)

  • 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))
  • Rows Removed by Join Filter: 2
42. 0.056 78,028.391 ↓ 2.0 2 1

Nested Loop Left Join (cost=5,104.99..281,108.59 rows=1 width=1,654) (actual time=77,087.566..78,028.391 rows=2 loops=1)

43. 2,701.372 78,028.291 ↓ 2.0 2 1

Merge Left Join (cost=5,104.71..281,100.28 rows=1 width=1,650) (actual time=77,087.523..78,028.291 rows=2 loops=1)

  • Merge Cond: (m.id = po.member_id)
  • Filter: (po.id IS NULL)
  • Rows Removed by Filter: 316322
44. 133.462 72,514.871 ↑ 935.1 4,394 1

Nested Loop Left Join (cost=1,662.87..248,305.61 rows=4,109,000 width=602) (actual time=9,277.654..72,514.871 rows=4,394 loops=1)

45. 138.301 72,297.923 ↑ 935.1 4,394 1

Nested Loop (cost=1,662.86..166,125.61 rows=4,109,000 width=598) (actual time=9,277.539..72,297.923 rows=4,394 loops=1)

46. 129.251 72,023.408 ↑ 935.1 4,394 1

Nested Loop (cost=1,662.86..83,945.60 rows=4,109,000 width=158) (actual time=9,277.475..72,023.408 rows=4,394 loops=1)

47. 116.808 4,506.557 ↑ 1.0 4,109 1

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

48. 63.814 4,319.896 ↑ 1.0 4,109 1

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

  • Merge Cond: (m.id = mfpa.member_id)
49. 64.673 4,194.829 ↑ 1.0 4,109 1

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

  • Sort Key: m.id
  • Sort Method: quicksort Memory: 1024kB
50. 72.797 4,130.156 ↑ 1.0 4,109 1

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

  • Hash Cond: (c.member_id = m.id)
51. 3,592.540 3,592.540 ↑ 1.0 4,314 1

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

52. 40.577 464.819 ↑ 1.0 4,109 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 630kB
53. 198.015 424.242 ↑ 1.0 4,109 1

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

  • Hash Cond: (mt.member_id = m.id)
54. 154.067 154.067 ↑ 1.0 16,017 1

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

55. 36.266 72.160 ↑ 1.0 4,109 1

Hash (cost=519.49..519.49 rows=4,109 width=29) (actual time=72.153..72.160 rows=4,109 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 315kB
56. 35.683 35.894 ↑ 1.0 4,109 1

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

  • Recheck Cond: (tenant_id = '1010'::bigint)
  • Heap Blocks: exact=330
57. 0.211 0.211 ↑ 1.0 4,109 1

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

  • Index Cond: (tenant_id = '1010'::bigint)
58. 8.436 61.253 ↓ 526.0 526 1

Sort (cost=0.03..0.04 rows=1 width=8) (actual time=57.397..61.253 rows=526 loops=1)

  • Sort Key: mfpa.member_id
  • Sort Method: quicksort Memory: 49kB
59. 52.817 52.817 ↓ 526.0 526 1

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

60. 69.853 69.853 ↑ 1.0 1 4,109

Function Scan on int8 currency (cost=0.02..0.03 rows=1 width=8) (actual time=0.010..0.017 rows=1 loops=4,109)

61. 67,387.600 67,387.600 ↑ 1,000.0 1 4,109

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

62. 136.214 136.214 ↑ 1.0 1 4,394

Function Scan on jsonb_to_record x (cost=0.01..0.01 rows=1 width=440) (actual time=0.023..0.031 rows=1 loops=4,394)

63. 83.486 83.486 ↑ 1.0 1 4,394

Function Scan on int4 target_amount (cost=0.01..0.02 rows=1 width=4) (actual time=0.011..0.019 rows=1 loops=4,394)

64. 2,626.016 2,812.048 ↓ 63.7 316,654 1

Materialize (cost=3,441.83..3,466.68 rows=4,970 width=1,056) (actual time=135.487..2,812.048 rows=316,654 loops=1)

65. 134.575 186.032 ↑ 1.0 4,970 1

Sort (cost=3,441.83..3,454.26 rows=4,970 width=1,056) (actual time=135.470..186.032 rows=4,970 loops=1)

  • Sort Key: po.member_id
  • Sort Method: external merge Disk: 5200kB
66. 51.457 51.457 ↑ 1.0 4,970 1

Seq Scan on payout po (cost=0.00..840.70 rows=4,970 width=1,056) (actual time=0.017..51.457 rows=4,970 loops=1)

67. 0.044 0.044 ↑ 1.0 1 2

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

  • Index Cond: ((tenant_integration_id = x.tenant_integration_id) AND ((integration_oid)::text = (x.integration_oid)::text))
68. 0.422 0.422 ↑ 1.0 1 2

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

69. 78,028.961 78,028.961 ↓ 2.0 2 1

CTE Scan on targets t (cost=0.00..0.02 rows=1 width=808) (actual time=77,087.836..78,028.961 rows=2 loops=1)

Planning time : 3.473 ms
Execution time : 78,032.204 ms