explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VIod

Settings
# exclusive inclusive rows x rows loops node
1. 0.640 198.031 ↓ 1.0 66 1

Hash Join (cost=5,728.81..282,143.50 rows=64 width=124) (actual time=1.748..198.031 rows=66 loops=1)

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

CTE managers

3. 0.052 0.373 ↓ 1.0 66 1

Nested Loop (cost=0.68..242.54 rows=64 width=40) (actual time=0.023..0.373 rows=66 loops=1)

4. 0.123 0.123 ↓ 1.0 66 1

Index Scan using partners_partnermanager_e8701ad4 on partners_partnermanager m_1 (cost=0.28..24.34 rows=64 width=8) (actual time=0.013..0.123 rows=66 loops=1)

  • Index Cond: (user_id < 500)
  • Filter: is_hired
  • Rows Removed by Filter: 57
5. 0.198 0.198 ↑ 1.0 1 66

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

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

CTE top_performer

7. 0.007 0.977 ↑ 32.0 2 1

Nested Loop Left Join (cost=85.05..5,484.19 rows=64 width=72) (actual time=0.691..0.977 rows=2 loops=1)

8. 0.003 0.964 ↑ 32.0 2 1

Nested Loop Left Join (cost=84.77..5,336.99 rows=64 width=44) (actual time=0.683..0.964 rows=2 loops=1)

9. 0.032 0.415 ↑ 32.0 2 1

HashAggregate (cost=1.44..2.08 rows=64 width=32) (actual time=0.413..0.415 rows=2 loops=1)

  • Group Key: managers.brand
10. 0.383 0.383 ↓ 1.0 66 1

CTE Scan on managers (cost=0.00..1.28 rows=64 width=32) (actual time=0.000..0.383 rows=66 loops=1)

11. 0.004 0.546 ↑ 1.0 1 2

Limit (cost=83.33..83.33 rows=1 width=12) (actual time=0.271..0.273 rows=1 loops=2)

12. 0.012 0.542 ↑ 14.0 1 2

Sort (cost=83.33..83.36 rows=14 width=12) (actual time=0.271..0.271 rows=1 loops=2)

  • Sort Key: (count(*)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
13. 0.014 0.530 ↑ 1.8 8 2

GroupAggregate (cost=83.01..83.26 rows=14 width=12) (actual time=0.258..0.265 rows=8 loops=2)

  • Group Key: u_1.id
14. 0.018 0.516 ↑ 1.0 14 2

Sort (cost=83.01..83.05 rows=14 width=4) (actual time=0.256..0.258 rows=14 loops=2)

  • Sort Key: u_1.id
  • Sort Method: quicksort Memory: 26kB
15. 0.022 0.498 ↑ 1.0 14 2

Nested Loop (cost=0.98..82.74 rows=14 width=4) (actual time=0.066..0.249 rows=14 loops=2)

16. 0.032 0.406 ↓ 1.3 18 2

Nested Loop (cost=0.70..76.95 rows=14 width=4) (actual time=0.041..0.203 rows=18 loops=2)

17. 0.042 0.304 ↓ 1.3 18 2

Nested Loop (cost=0.43..72.84 rows=14 width=4) (actual time=0.036..0.152 rows=18 loops=2)

18. 0.046 0.046 ↓ 1.2 27 2

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd (cost=0.14..19.48 rows=23 width=4) (actual time=0.004..0.023 rows=27 loops=2)

  • Heap Fetches: 34
19. 0.216 0.216 ↑ 1.0 1 54

Index Scan using idx_account_partner_contract on partners_partnercontract c (cost=0.29..2.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=54)

  • Index Cond: (partner_id = sd.partner_id)
  • Filter: ((brand)::text = (managers.brand)::text)
  • Rows Removed by Filter: 1
20. 0.070 0.070 ↑ 1.0 1 35

Index Scan using partners_partnermanager_pkey on partners_partnermanager manager (cost=0.28..0.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=35)

  • Index Cond: (id = c.account_manager_id)
21. 0.070 0.070 ↑ 1.0 1 35

Index Only Scan using auth_user_pkey on auth_user u_1 (cost=0.28..0.41 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=35)

  • Index Cond: (id = manager.user_id)
  • Filter: (id <> 3)
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
22. 0.006 0.006 ↑ 1.0 1 2

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

  • Index Cond: (id = u_1.id)
23. 0.059 0.059 ↓ 1.0 66 1

CTE Scan on managers m (cost=0.00..1.28 rows=64 width=40) (actual time=0.025..0.059 rows=66 loops=1)

24. 0.002 0.982 ↑ 32.0 2 1

Hash (cost=1.28..1.28 rows=64 width=72) (actual time=0.982..0.982 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.980 0.980 ↑ 32.0 2 1

CTE Scan on top_performer top (cost=0.00..1.28 rows=64 width=72) (actual time=0.692..0.980 rows=2 loops=1)

26.          

SubPlan (for Hash Join)

27. 1.848 55.968 ↑ 1.0 1 66

Aggregate (cost=1,311.94..1,311.95 rows=1 width=32) (actual time=0.848..0.848 rows=1 loops=66)

28. 11.553 54.120 ↑ 3.2 153 66

Nested Loop (cost=0.58..1,310.73 rows=482 width=6) (actual time=0.008..0.820 rows=153 loops=66)

29. 12.210 12.210 ↑ 3.2 153 66

Index Scan using idx_contract_manager_partner on partners_partnercontract c_1 (cost=0.29..471.21 rows=483 width=4) (actual time=0.005..0.185 rows=153 loops=66)

  • Index Cond: (account_manager_id = m.manager_id)
30. 30.357 30.357 ↑ 1.0 1 10,119

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

  • Index Cond: (contract_id = c_1.id)
31. 0.066 11.154 ↑ 1.0 1 66

Aggregate (cost=103.20..103.21 rows=1 width=8) (actual time=0.169..0.169 rows=1 loops=66)

32. 0.000 11.088 ↓ 0.0 0 66

Nested Loop (cost=0.72..103.14 rows=23 width=0) (actual time=0.157..0.168 rows=0 loops=66)

33. 1.188 5.940 ↓ 1.2 27 66

Nested Loop (cost=0.43..49.55 rows=23 width=4) (actual time=0.007..0.090 rows=27 loops=66)

34. 1.188 1.188 ↓ 1.2 27 66

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd_2 (cost=0.14..19.48 rows=23 width=4) (actual time=0.003..0.018 rows=27 loops=66)

  • Heap Fetches: 1,122
35. 3.564 3.564 ↑ 1.0 1 1,782

Index Only Scan using partners_partner_pkey on partners_partner p_1 (cost=0.29..1.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,782)

  • Index Cond: (id = sd_2.partner_id)
  • Heap Fetches: 396
36. 1.782 5.346 ↓ 0.0 0 1,782

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

37. 3.564 3.564 ↓ 0.0 0 1,782

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

  • Index Cond: ((account_manager_id = m.manager_id) AND (partner_id = p_1.id))
  • Heap Fetches: 10
38. 0.132 17.820 ↑ 1.0 1 66

Aggregate (cost=476.06..476.07 rows=1 width=8) (actual time=0.270..0.270 rows=1 loops=66)

39. 17.688 17.688 ↑ 3.0 3 66

Index Scan using idx_contract_manager_partner on partners_partnercontract c_3 (cost=0.29..476.04 rows=9 width=0) (actual time=0.016..0.268 rows=3 loops=66)

  • 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: 150
40. 0.066 45.474 ↑ 1.0 1 66

Aggregate (cost=1,049.74..1,049.75 rows=1 width=8) (actual time=0.689..0.689 rows=1 loops=66)

41. 5.415 45.408 ↑ 3.0 1 66

Nested Loop (cost=0.58..1,049.74 rows=3 width=0) (actual time=0.407..0.688 rows=1 loops=66)

42. 9.636 9.636 ↑ 3.2 153 66

Index Scan using idx_contract_manager_partner on partners_partnercontract c_4 (cost=0.29..471.21 rows=483 width=4) (actual time=0.003..0.146 rows=153 loops=66)

  • Index Cond: (account_manager_id = m.manager_id)
43. 30.357 30.357 ↓ 0.0 0 10,119

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.20 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=10,119)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
44. 0.132 8.976 ↑ 1.0 1 66

Aggregate (cost=525.06..525.07 rows=1 width=8) (actual time=0.136..0.136 rows=1 loops=66)

45. 1.997 8.844 ↑ 2.0 3 66

Hash Join (cost=242.81..525.05 rows=6 width=0) (actual time=0.022..0.134 rows=3 loops=66)

  • Hash Cond: (c_5.partner_id = sd_3.partner_id)
46. 6.270 6.270 ↑ 3.2 153 66

Index Only Scan using idx_contract_manager_partner on partners_partnercontract c_5 (cost=0.29..281.26 rows=483 width=4) (actual time=0.004..0.095 rows=153 loops=66)

  • Index Cond: (account_manager_id = m.manager_id)
  • Heap Fetches: 6,875
47. 0.108 0.577 ↓ 1.0 444 1

Hash (cost=236.99..236.99 rows=443 width=4) (actual time=0.577..0.577 rows=444 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
48. 0.469 0.469 ↓ 1.0 444 1

Index Only Scan using idx_avg_cancellation_percent_month_partner_stat_data on partners_partnerstatdata sd_3 (cost=0.28..236.99 rows=443 width=4) (actual time=0.032..0.469 rows=444 loops=1)

  • Index Cond: (avg_cancellation_percent_month >= '55'::numeric)
  • Heap Fetches: 338
49. 0.066 25.344 ↑ 1.0 1 66

Aggregate (cost=175.50..175.51 rows=1 width=8) (actual time=0.384..0.384 rows=1 loops=66)

50. 3.960 25.278 ↓ 0.0 0 66

Nested Loop (cost=0.56..175.50 rows=1 width=0) (actual time=0.361..0.383 rows=0 loops=66)

51. 4.686 4.686 ↓ 1.1 84 66

Index Only Scan using idx_credit_limit_remain_share_contract_credit_risk on partners_partnercreditrisk cr_1 (cost=0.27..6.62 rows=77 width=4) (actual time=0.003..0.071 rows=84 loops=66)

  • Index Cond: (credit_limit_remain_share <= 30)
  • Heap Fetches: 5,544
52. 16.632 16.632 ↓ 0.0 0 5,544

Index Scan using partners_partnercontract_pkey on partners_partnercontract c_6 (cost=0.29..2.19 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=5,544)

  • Index Cond: (id = cr_1.contract_id)
  • Filter: (account_manager_id = m.manager_id)
  • Rows Removed by Filter: 1
53. 0.792 31.614 ↑ 1.0 1 66

Aggregate (cost=677.37..677.38 rows=1 width=8) (actual time=0.479..0.479 rows=1 loops=66)

54. 2.862 30.822 ↑ 2.8 30 66

Nested Loop (cost=0.57..677.16 rows=84 width=4) (actual time=0.010..0.467 rows=30 loops=66)

55. 7.722 7.722 ↑ 3.2 153 66

Index Only Scan using idx_contract_manager_partner on partners_partnercontract c_7 (cost=0.29..281.26 rows=483 width=4) (actual time=0.004..0.117 rows=153 loops=66)

  • Index Cond: (account_manager_id = m.manager_id)
  • Heap Fetches: 6,875
56. 20.238 20.238 ↓ 0.0 0 10,119

Index Only Scan using idx_partner_to_reactivate on partners_partnerstatdata sd_4 (cost=0.28..0.82 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=10,119)

  • Index Cond: (partner_id = c_7.partner_id)
  • Heap Fetches: 1,525
Planning time : 4.721 ms
Execution time : 198.400 ms