explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rHwt

Settings
# exclusive inclusive rows x rows loops node
1. 0.251 134.196 ↑ 1.0 61 1

Hash Join (cost=21,241.27..286,527.54 rows=63 width=108) (actual time=3.380..134.196 rows=61 loops=1)

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

CTE managers

3. 0.048 0.262 ↑ 1.0 61 1

Nested Loop (cost=0.68..236.12 rows=63 width=40) (actual time=0.025..0.262 rows=61 loops=1)

4. 0.092 0.092 ↑ 1.0 61 1

Index Scan using partners_partnermanager_e8701ad4 on partners_partnermanager m_1 (cost=0.28..22.34 rows=63 width=8) (actual time=0.012..0.092 rows=61 loops=1)

  • Index Cond: (user_id < 500)
  • Filter: is_hired
  • Rows Removed by Filter: 55
5. 0.122 0.122 ↑ 1.0 1 61

Index Scan using profile_userconfig_user_id_key on profile_userconfig p (cost=0.41..3.39 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=61)

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

CTE top_performer

7. 0.003 2.308 ↑ 31.5 2 1

Subquery Scan on m_2 (cost=1.42..21,003.10 rows=63 width=64) (actual time=1.448..2.308 rows=2 loops=1)

8. 0.015 0.271 ↑ 31.5 2 1

HashAggregate (cost=1.42..2.05 rows=63 width=32) (actual time=0.270..0.271 rows=2 loops=1)

  • Group Key: managers.brand
9. 0.256 0.256 ↑ 1.0 61 1

CTE Scan on managers (cost=0.00..1.26 rows=63 width=32) (actual time=0.000..0.256 rows=61 loops=1)

10.          

SubPlan (forSubquery Scan)

11. 0.006 2.034 ↑ 1.0 1 2

Limit (cost=330.72..333.34 rows=1 width=32) (actual time=1.015..1.017 rows=1 loops=2)

12. 0.010 2.028 ↑ 1.0 1 2

Nested Loop (cost=330.72..333.34 rows=1 width=32) (actual time=1.014..1.014 rows=1 loops=2)

13. 0.002 2.002 ↑ 1.0 1 2

Nested Loop (cost=330.44..332.47 rows=1 width=4) (actual time=1.001..1.001 rows=1 loops=2)

14. 0.000 1.988 ↑ 1.0 1 2

Limit (cost=330.16..330.17 rows=1 width=12) (actual time=0.994..0.994 rows=1 loops=2)

15. 0.024 1.988 ↑ 92.0 1 2

Sort (cost=330.16..330.39 rows=92 width=12) (actual time=0.994..0.994 rows=1 loops=2)

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

HashAggregate (cost=328.78..329.70 rows=92 width=12) (actual time=0.979..0.982 rows=22 loops=2)

  • Group Key: c.account_manager_id
17. 0.182 1.916 ↓ 1.0 102 2

Nested Loop (cost=0.72..328.29 rows=99 width=4) (actual time=0.215..0.958 rows=102 loops=2)

18. 0.024 0.792 ↑ 1.0 157 2

Nested Loop (cost=0.43..251.42 rows=159 width=8) (actual time=0.015..0.396 rows=157 loops=2)

19. 0.140 0.140 ↑ 1.0 157 2

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd (cost=0.14..51.53 rows=159 width=4) (actual time=0.008..0.070 rows=157 loops=2)

  • Heap Fetches: 122
20. 0.628 0.628 ↑ 1.0 1 314

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

  • Index Cond: (id = sd.partner_id)
  • Heap Fetches: 64
21. 0.942 0.942 ↑ 1.0 1 314

Index Scan using partners_partnercontract_4e98b6eb on partners_partnercontract c (cost=0.29..0.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=314)

  • Index Cond: (partner_id = p_1.id)
  • Filter: ((brand)::text = (m_2.brand)::text)
  • Rows Removed by Filter: 1
22. 0.012 0.012 ↑ 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.006..0.006 rows=1 loops=2)

  • Index Cond: (id = c.account_manager_id)
23. 0.016 0.016 ↑ 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.008..0.008 rows=1 loops=2)

  • Index Cond: (id = partners_partnermanager.user_id)
24. 0.054 0.054 ↑ 1.0 61 1

CTE Scan on managers m (cost=0.00..1.26 rows=63 width=40) (actual time=0.026..0.054 rows=61 loops=1)

25. 0.004 2.314 ↑ 31.5 2 1

Hash (cost=1.26..1.26 rows=63 width=64) (actual time=2.314..2.314 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 2.310 2.310 ↑ 31.5 2 1

CTE Scan on top_performer top (cost=0.00..1.26 rows=63 width=64) (actual time=1.449..2.310 rows=2 loops=1)

27.          

SubPlan (forHash Join)

28. 0.854 30.927 ↑ 1.0 1 61

Aggregate (cost=1,262.72..1,262.73 rows=1 width=32) (actual time=0.507..0.507 rows=1 loops=61)

29. 4.819 30.073 ↑ 3.4 136 61

Nested Loop (cost=0.70..1,261.57 rows=460 width=6) (actual time=0.006..0.493 rows=136 loops=61)

30. 8.662 8.662 ↑ 3.4 136 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_1 (cost=0.41..449.89 rows=464 width=4) (actual time=0.004..0.142 rows=136 loops=61)

  • Index Cond: (account_manager_id = m.manager_id)
31. 16.592 16.592 ↑ 1.0 1 8,296

Index Scan using partners_partnercontractstatdata_pkey on partners_partnercontractstatdata sd_1 (cost=0.29..1.75 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=8,296)

  • Index Cond: (contract_id = c_1.id)
32. 0.061 42.090 ↑ 1.0 1 61

Aggregate (cost=622.29..622.30 rows=1 width=8) (actual time=0.690..0.690 rows=1 loops=61)

33. 5.734 42.029 ↑ 159.0 1 61

Nested Loop (cost=0.72..621.89 rows=159 width=0) (actual time=0.608..0.689 rows=1 loops=61)

34. 4.819 17.141 ↑ 1.0 157 61

Nested Loop (cost=0.43..251.42 rows=159 width=4) (actual time=0.007..0.281 rows=157 loops=61)

35. 2.745 2.745 ↑ 1.0 157 61

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd_2 (cost=0.14..51.53 rows=159 width=4) (actual time=0.004..0.045 rows=157 loops=61)

  • Heap Fetches: 3721
36. 9.577 9.577 ↑ 1.0 1 9,577

Index Only Scan using partners_partner_pkey on partners_partner p_2 (cost=0.29..1.26 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=9,577)

  • Index Cond: (id = sd_2.partner_id)
  • Heap Fetches: 1952
37. 0.000 19.154 ↓ 0.0 0 9,577

Limit (cost=0.29..2.31 rows=1 width=18,493) (actual time=0.002..0.002 rows=0 loops=9,577)

38. 19.154 19.154 ↓ 0.0 0 9,577

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

  • Index Cond: (partner_id = p_2.id)
  • Filter: (account_manager_id = m.manager_id)
  • Rows Removed by Filter: 1
39. 0.061 11.956 ↑ 1.0 1 61

Aggregate (cost=454.54..454.55 rows=1 width=8) (actual time=0.196..0.196 rows=1 loops=61)

40. 11.895 11.895 ↑ 3.0 2 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_3 (cost=0.41..454.53 rows=6 width=0) (actual time=0.015..0.195 rows=2 loops=61)

  • 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: 134
41. 0.061 24.705 ↑ 1.0 1 61

Aggregate (cost=1,013.58..1,013.59 rows=1 width=8) (actual time=0.404..0.405 rows=1 loops=61)

42. 1.525 24.644 ↑ 3.0 1 61

Nested Loop (cost=0.70..1,013.57 rows=3 width=0) (actual time=0.110..0.404 rows=1 loops=61)

43. 6.527 6.527 ↑ 3.4 136 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_4 (cost=0.41..449.89 rows=464 width=4) (actual time=0.002..0.107 rows=136 loops=61)

  • Index Cond: (account_manager_id = m.manager_id)
44. 16.592 16.592 ↓ 0.0 0 8,296

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.21 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=8,296)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
45. 0.061 10.187 ↑ 1.0 1 61

Aggregate (cost=697.46..697.47 rows=1 width=8) (actual time=0.167..0.167 rows=1 loops=61)

46. 1.210 10.126 ↑ 3.5 4 61

Hash Join (cost=246.73..697.42 rows=14 width=0) (actual time=0.023..0.166 rows=4 loops=61)

  • Hash Cond: (c_5.partner_id = sd_3.partner_id)
47. 8.235 8.235 ↑ 3.4 136 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_5 (cost=0.41..449.89 rows=464 width=4) (actual time=0.002..0.135 rows=136 loops=61)

  • Index Cond: (account_manager_id = m.manager_id)
48. 0.155 0.681 ↓ 1.0 1,062 1

Hash (cost=233.65..233.65 rows=1,013 width=4) (actual time=0.681..0.681 rows=1,062 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 54kB
49. 0.526 0.526 ↓ 1.0 1,062 1

Index Only Scan using idx_avg_cancellation_percent_month_partner_stat_data on partners_partnerstatdata sd_3 (cost=0.28..233.65 rows=1,013 width=4) (actual time=0.019..0.526 rows=1,062 loops=1)

  • Index Cond: (avg_cancellation_percent_month >= '55'::numeric)
  • Heap Fetches: 436
50. 0.061 11.712 ↑ 1.0 1 61

Aggregate (cost=160.22..160.23 rows=1 width=8) (actual time=0.192..0.192 rows=1 loops=61)

51. 1.342 11.651 ↓ 0.0 0 61

Nested Loop (cost=0.44..160.21 rows=1 width=0) (actual time=0.174..0.191 rows=0 loops=61)

52. 2.257 2.257 ↓ 1.3 66 61

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.004..0.037 rows=66 loops=61)

  • Index Cond: (credit_limit_remain_share <= 30)
  • Heap Fetches: 4026
53. 8.052 8.052 ↓ 0.0 0 4,026

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

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