explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 757e

Settings
# exclusive inclusive rows x rows loops node
1. 5.381 1,285.753 ↓ 1.0 468 1

Hash Join (cost=62,814.53..1,899,426.73 rows=459 width=108) (actual time=11.923..1,285.753 rows=468 loops=1)

  • Hash Cond: ((m.brand)::text = (top.brand)::text)
2.          

CTE managers

3. 0.703 6.240 ↓ 1.0 468 1

Nested Loop (cost=0.41..1,466.31 rows=459 width=40) (actual time=0.026..6.240 rows=468 loops=1)

4. 4.133 4.133 ↓ 1.0 468 1

Seq Scan on partners_partnermanager m_1 (cost=0.00..27.74 rows=459 width=8) (actual time=0.012..4.133 rows=468 loops=1)

  • Filter: is_hired
  • Rows Removed by Filter: 419
5. 1.404 1.404 ↑ 1.0 1 468

Index Scan using profile_userconfig_user_id_key on profile_userconfig p (cost=0.41..3.13 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=468)

  • Index Cond: (user_id = m_1.user_id)
6.          

CTE top_performer

7. 0.006 10.376 ↑ 100.0 2 1

Subquery Scan on m_2 (cost=10.33..61,341.73 rows=200 width=64) (actual time=8.490..10.376 rows=2 loops=1)

8. 0.204 6.668 ↑ 100.0 2 1

HashAggregate (cost=10.33..12.33 rows=200 width=32) (actual time=6.664..6.668 rows=2 loops=1)

  • Group Key: managers.brand
9. 6.464 6.464 ↓ 1.0 468 1

CTE Scan on managers (cost=0.00..9.18 rows=459 width=32) (actual time=0.000..6.464 rows=468 loops=1)

10.          

SubPlan (forSubquery Scan)

11. 0.008 3.702 ↑ 1.0 1 2

Limit (cost=304.01..306.64 rows=1 width=32) (actual time=1.848..1.851 rows=1 loops=2)

12. 0.014 3.694 ↑ 1.0 1 2

Nested Loop (cost=304.01..306.64 rows=1 width=32) (actual time=1.847..1.847 rows=1 loops=2)

13. 0.006 3.668 ↑ 1.0 1 2

Nested Loop (cost=303.73..305.77 rows=1 width=4) (actual time=1.834..1.834 rows=1 loops=2)

14. 0.002 3.648 ↑ 1.0 1 2

Limit (cost=303.46..303.46 rows=1 width=12) (actual time=1.824..1.824 rows=1 loops=2)

15. 0.026 3.646 ↑ 92.0 1 2

Sort (cost=303.46..303.69 rows=92 width=12) (actual time=1.823..1.823 rows=1 loops=2)

  • Sort Key: (count(*)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
16. 0.122 3.620 ↑ 4.2 22 2

HashAggregate (cost=302.08..303.00 rows=92 width=12) (actual time=1.804..1.810 rows=22 loops=2)

  • Group Key: c.account_manager_id
17. 0.316 3.498 ↑ 1.0 106 2

Nested Loop (cost=0.72..301.55 rows=106 width=4) (actual time=0.278..1.749 rows=106 loops=2)

18. 0.266 1.522 ↑ 1.0 166 2

Nested Loop (cost=0.43..219.95 rows=170 width=8) (actual time=0.014..0.761 rows=166 loops=2)

19. 0.260 0.260 ↑ 1.0 166 2

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd (cost=0.14..9.67 rows=170 width=4) (actual time=0.008..0.130 rows=166 loops=2)

  • Heap Fetches: 124
20. 0.996 0.996 ↑ 1.0 1 332

Index Only Scan using partners_partner_pkey on partners_partner p_1 (cost=0.29..1.24 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=332)

  • Index Cond: (id = sd.partner_id)
  • Heap Fetches: 70
21. 1.660 1.660 ↑ 1.0 1 332

Index Scan using idx_account_partner_contract on partners_partnercontract c (cost=0.29..0.47 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=332)

  • Index Cond: (partner_id = p_1.id)
  • Filter: ((brand)::text = (m_2.brand)::text)
  • Rows Removed by Filter: 1
22. 0.014 0.014 ↑ 1.0 1 2

Index Scan using partners_partnermanager_pkey on partners_partnermanager (cost=0.28..2.29 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=2)

  • Index Cond: (id = c.account_manager_id)
23. 0.012 0.012 ↑ 1.0 1 2

Index Scan using auth_user_pkey on auth_user u (cost=0.28..0.87 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=2)

  • Index Cond: (id = partners_partnermanager.user_id)
24. 0.304 0.304 ↓ 1.0 468 1

CTE Scan on managers m (cost=0.00..9.18 rows=459 width=40) (actual time=0.027..0.304 rows=468 loops=1)

25. 0.004 10.384 ↑ 100.0 2 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=10.384..10.384 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 10.380 10.380 ↑ 100.0 2 1

CTE Scan on top_performer top (cost=0.00..4.00 rows=200 width=64) (actual time=8.492..10.380 rows=2 loops=1)

27.          

SubPlan (forHash Join)

28. 8.424 234.468 ↑ 1.0 1 468

Aggregate (cost=1,255.82..1,255.83 rows=1 width=32) (actual time=0.501..0.501 rows=1 loops=468)

29. 38.739 226.044 ↑ 5.1 90 468

Nested Loop (cost=0.70..1,254.67 rows=460 width=6) (actual time=0.008..0.483 rows=90 loops=468)

30. 60.840 60.840 ↑ 5.1 90 468

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_1 (cost=0.41..446.91 rows=461 width=4) (actual time=0.005..0.130 rows=90 loops=468)

  • Index Cond: (account_manager_id = m.manager_id)
31. 126.465 126.465 ↑ 1.0 1 42,155

Index Scan using partners_partnercontractstatdata_pkey on partners_partnercontractstatdata sd_1 (cost=0.29..1.75 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=42,155)

  • Index Cond: (contract_id = c_1.id)
32. 0.936 485.316 ↑ 1.0 1 468

Aggregate (cost=616.47..616.48 rows=1 width=8) (actual time=1.037..1.037 rows=1 loops=468)

33. 0.000 484.380 ↓ 0.0 0 468

Nested Loop (cost=0.72..616.05 rows=170 width=0) (actual time=0.971..1.035 rows=0 loops=468)

34. 57.564 251.316 ↑ 1.0 166 468

Nested Loop (cost=0.43..219.95 rows=170 width=4) (actual time=0.009..0.537 rows=166 loops=468)

35. 38.376 38.376 ↑ 1.0 166 468

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd_2 (cost=0.14..9.67 rows=170 width=4) (actual time=0.005..0.082 rows=166 loops=468)

  • Heap Fetches: 29016
36. 155.376 155.376 ↑ 1.0 1 77,688

Index Only Scan using partners_partner_pkey on partners_partner p_2 (cost=0.29..1.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=77,688)

  • Index Cond: (id = sd_2.partner_id)
  • Heap Fetches: 16380
37. 77.688 233.064 ↓ 0.0 0 77,688

Limit (cost=0.29..2.31 rows=1 width=18,493) (actual time=0.003..0.003 rows=0 loops=77,688)

38. 155.376 155.376 ↓ 0.0 0 77,688

Index Only Scan using idx_account_partner_contract on partners_partnercontract c_2 (cost=0.29..2.31 rows=1 width=18,493) (actual time=0.002..0.002 rows=0 loops=77,688)

  • Index Cond: ((partner_id = p_2.id) AND (account_manager_id = m.manager_id))
  • Heap Fetches: 28
39. 0.468 81.432 ↑ 1.0 1 468

Aggregate (cost=451.53..451.54 rows=1 width=8) (actual time=0.174..0.174 rows=1 loops=468)

40. 80.964 80.964 ↑ 3.0 2 468

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_3 (cost=0.41..451.52 rows=6 width=0) (actual time=0.023..0.173 rows=2 loops=468)

  • Index Cond: (account_manager_id = m.manager_id)
  • Filter: (((punishment_rules)::text ~~ '%%rule_credit_limit_is_zero_for_mpb_calculation%%'::text) OR ((punishment_rules)::text ~~ '%%rule_cant_reserve_any_deposit_rates%%'::text) OR (((punishment_rules)::text ~~ '%%rule_cant_reserve_any_deposit_rates%%'::text) AND ((punishment_rules)::text ~~ '%%rule_cant_reserve_payment_type_now_rates%%'::text)))
  • Rows Removed by Filter: 88
41. 0.468 183.924 ↑ 1.0 1 468

Aggregate (cost=1,008.68..1,008.69 rows=1 width=8) (actual time=0.393..0.393 rows=1 loops=468)

42. 16.275 183.456 ↓ 0.0 0 468

Nested Loop (cost=0.70..1,008.67 rows=3 width=0) (actual time=0.100..0.392 rows=0 loops=468)

43. 40.716 40.716 ↑ 5.1 90 468

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_4 (cost=0.41..446.91 rows=461 width=4) (actual time=0.004..0.087 rows=90 loops=468)

  • Index Cond: (account_manager_id = m.manager_id)
44. 126.465 126.465 ↓ 0.0 0 42,155

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.22 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=42,155)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
45. 0.468 99.684 ↑ 1.0 1 468

Aggregate (cost=508.49..508.50 rows=1 width=8) (actual time=0.213..0.213 rows=1 loops=468)

46. 9.852 99.216 ↑ 5.0 3 468

Hash Join (cost=60.75..508.45 rows=15 width=0) (actual time=0.059..0.212 rows=3 loops=468)

  • Hash Cond: (c_5.partner_id = sd_3.partner_id)
47. 88.452 88.452 ↑ 5.1 90 468

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_5 (cost=0.41..446.91 rows=461 width=4) (actual time=0.050..0.189 rows=90 loops=468)

  • Index Cond: (account_manager_id = m.manager_id)
48. 0.226 0.912 ↓ 1.0 1,096 1

Hash (cost=46.95..46.95 rows=1,071 width=4) (actual time=0.912..0.912 rows=1,096 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 55kB
49. 0.686 0.686 ↓ 1.0 1,096 1

Index Only Scan using idx_avg_cancellation_percent_month_partner_stat_data on partners_partnerstatdata sd_3 (cost=0.28..46.95 rows=1,071 width=4) (actual time=0.016..0.686 rows=1,096 loops=1)

  • Index Cond: (avg_cancellation_percent_month >= '55'::numeric)
  • Heap Fetches: 394
50. 0.468 184.860 ↑ 1.0 1 468

Aggregate (cost=160.22..160.23 rows=1 width=8) (actual time=0.395..0.395 rows=1 loops=468)

51. 24.804 184.392 ↓ 0.0 0 468

Nested Loop (cost=0.44..160.21 rows=1 width=0) (actual time=0.339..0.394 rows=0 loops=468)

52. 36.036 36.036 ↓ 1.3 66 468

Index Only Scan using idx_credit_limit_remain_share_contract_credit_risk on partners_partnercreditrisk cr_1 (cost=0.15..47.39 rows=51 width=4) (actual time=0.005..0.077 rows=66 loops=468)

  • Index Cond: (credit_limit_remain_share <= 30)
  • Heap Fetches: 30888
53. 123.552 123.552 ↓ 0.0 0 30,888

Index Scan using partners_partnercontract_pkey on partners_partnercontract c_6 (cost=0.29..2.21 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=30,888)

  • Index Cond: (id = cr_1.contract_id)
  • Filter: (account_manager_id = m.manager_id)
  • Rows Removed by Filter: 1
Planning time : 5.013 ms
Execution time : 1,286.225 ms